Forum | Synoptix

Learn, Create, Report
It is currently Sat May 25, 2019 8:54 am

All times are UTC - 7 hours [ DST ]




Post new topic Reply to topic  [ 4 posts ] 
Author Message
PostPosted: Mon Jun 12, 2017 11:35 am 
User avatar

Joined: Tue Jun 06, 2017 11:15 am
Posts: 3
Position: Not Provided
How do I go about adding a case statement, which creates a new column based on that case statement within advanced configurator?
Also, soitem.fdescmemo doesn't seem to able to be added through the UI, unless I code it in. I understand it slows down the query, but it's the only way with certain specific parts.

sql scripting example:
SELECT SO.fsoldby,
CASE
WHEN SI.fpartno LIKE '%TS%'
OR SI.fpartno LIKE '%CAP%'
OR SI.fpartno LIKE '%HOOD%'
OR SI.fpartno LIKE '%GS%'
OR SI.fpartno LIKE '%SL 3%'
OR SI.fpartno LIKE '%SL'
OR SI.fdescmemo LIKE '%SL1%'
OR SI.fdescmemo LIKE '%SL3%'
OR SI.fdescmemo LIKE '%SL VERSION%'
OR SI.fpartno LIKE '%LW%'
OR SI.fpartno LIKE '%HA%'
THEN 20
WHEN SI.fdescmemo like '%KILT%' THEN 50
WHEN SI.fdescmemo like '%VEST%'
OR (SI.fdescmemo LIKE '%FULL WRAP AROUND%' AND SI.fdescmemo LIKE '%OUTLAST VEST ONLY%')
THEN 50
WHEN SI.fdescmemo LIKE '%FRONTAL%'
THEN 80
WHEN SI.fdescmemo LIKE '%FULL WRAP AROUND%' AND SI.fdescmemo NOT LIKE '%OUTLAST VEST ONLY%'THEN 100
END AS OUTLAST_TOTAL
,SI.fpartno,
SI.fdescmemo,
SO.forderdate
FROM SOITEM SI
LEFT JOIN SOMAST SO ON SI.fsono=SO.fsono
WHERE
SI.fpartno NOT LIKE '%REPAIR'
AND SI.fpartno <> 'BILL ONLY'
AND SI.FPARTNO <> 'MISC'
AND SI.fdescmemo LIKE '%OUTLAST%'
AND SO.forderdate <= '2017-05-31'
AND SO.forderdate >= '2017-05-01'


Top
   
 
PostPosted: Mon Jun 12, 2017 12:37 pm 
User avatar

Joined: Tue Jun 06, 2017 11:15 am
Posts: 3
Position: Not Provided
If I try to add it with a comma after @field, I don't get any results:

SELECT @FIELD FROM somast INNER JOIN soitem ON somast.fsono = soitem.fsono INNER JOIN slcdpm ON somast.fcustno = slcdpm.fcustno INNER JOIN sorels ON soitem.fsono = sorels.fsono AND soitem.finumber = sorels.finumber LEFT OUTER JOIN inmast ON sorels.fpartno = inmast.fpartno AND sorels.fpartrev = inmast.frev AND soitem.fac = inmast.fac WHERE sorels.fmasterrel <> 1 AND CONVERT(char(10), somast.forderdate, 120) >= '@BEGINDATE' AND CONVERT(char(10), somast.forderdate, 120) <= '@ENDDATE'
AND soitem.fpartno NOT LIKE '%REPAIR' AND soitem.fpartno <> 'BILL ONLY' AND soitem.fpartno <> 'MISC' AND soitem.fdescmemo LIKE '%outlast%'


Top
   
 
PostPosted: Mon Jun 12, 2017 1:18 pm 
User avatar

Joined: Tue Jun 06, 2017 11:15 am
Posts: 3
Position: Not Provided
No change. Meaning not even an empty query.

SELECT @FIELD, CASE WHEN soitem.fpartno like '%TS' THEN 20 END AS OUTLAST FROM somast INNER JOIN soitem ON somast.fsono = soitem.fsono INNER JOIN slcdpm ON somast.fcustno = slcdpm.fcustno INNER JOIN sorels ON soitem.fsono = sorels.fsono AND soitem.finumber = sorels.finumber LEFT OUTER JOIN inmast ON sorels.fpartno = inmast.fpartno AND sorels.fpartrev = inmast.frev AND soitem.fac = inmast.fac WHERE sorels.fmasterrel <> 1 AND CONVERT(char(10), somast.forderdate, 120) >= '@BEGINDATE' AND CONVERT(char(10), somast.forderdate, 120) <= '@ENDDATE'
AND soitem.fpartno NOT LIKE '%REPAIR' AND soitem.fpartno <> 'BILL ONLY' AND soitem.fpartno <> 'MISC' AND soitem.fdescmemo LIKE '%outlast%'


Top
   
 
PostPosted: Thu Apr 18, 2019 3:54 pm 
User avatar

Joined: Fri Jul 28, 2017 4:02 pm
Posts: 9
Position: Not Provided
bumedIT wrote:
No change. Meaning not even an empty query.

SELECT @FIELD, CASE WHEN soitem.fpartno like '%TS' THEN 20 END AS OUTLAST FROM somast INNER JOIN soitem ON somast.fsono = soitem.fsono INNER JOIN slcdpm ON somast.fcustno = slcdpm.fcustno INNER JOIN sorels ON soitem.fsono = sorels.fsono AND soitem.finumber = sorels.finumber LEFT OUTER JOIN inmast ON sorels.fpartno = inmast.fpartno AND sorels.fpartrev = inmast.frev AND soitem.fac = inmast.fac WHERE sorels.fmasterrel <> 1 AND CONVERT(char(10), somast.forderdate, 120) >= '@BEGINDATE' AND CONVERT(char(10), somast.forderdate, 120) <= '@ENDDATE'
AND soitem.fpartno NOT LIKE '%REPAIR' AND soitem.fpartno <> 'BILL ONLY' AND soitem.fpartno <> 'MISC' AND soitem.fdescmemo LIKE '%outlast%'


Sad that no one ever answered this.

The answer is to use a user defined field and then put code in the "custom source of calculation."

Example of working code;

COUNT(CASE WHEN ladetail.fcompqty>0 THEN 1 ELSE NULL END)

So don't try to build a select statement, the AR engine does that, you just want to put in the parameters as shown above.


Top
   
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 

All times are UTC - 7 hours [ DST ]


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
cron
Powered by phpBB® Forum Software © phpBB Group
Powered by Reputation System © Pico88