Page 1 of 1
SQL Database query
Posted: Fri Aug 19, 2022 9:09 am
by vernonvd
i am trying to use a SQL statement in Private Data. its not working.
if i test the SQL in the "Build SQL statement" and do a validate test it works. but as soon as i close the "Build SQL statement" and test then nothing. when i open the "Build SQL statement" window i see this message the SQL statement is available for manual editing only'
herewith the SQL Query
Code: Select all
SELECT
CASE
WHEN ColoursReverse > 0 THEN 2 ELSE 1 END
FROM EstimatePart INNER JOIN MainJobDetails ON EstimatePart.EstimateID = MainJobDetails.EstimateID
WHERE JobNo = '19224'
Regards
vvd
Re: SQL Database query
Posted: Fri Aug 19, 2022 10:57 am
by laurentd
Here is an example:
name=[Database.Text:SQL="SELECT Name FROM EnfocusTeam WHERE Initials = '[Metadata.Text:Dataset="Submit",Model="XML",Path="/field-list/field[tag='Initials']/value"]'",Connection="SwitchPublishPrint"]
email=[Database.Text:SQL="SELECT Email FROM EnfocusTeam WHERE Initials = '[Metadata.Text:Dataset="Submit",Model="XML",Path="/field-list/field[tag='Initials']/value"]'",Connection="SwitchPublishPrint"]
Re: SQL Database query
Posted: Fri Aug 19, 2022 11:13 am
by vernonvd
Hi Laurentd
that part works fine, i want the return vaule to be "1" if ColoursReverse is 0 or "2" if ColoursReverse is not 0
laurentd wrote: ↑Fri Aug 19, 2022 10:57 am
Here is an example:
name=[Database.Text:SQL="SELECT Name FROM EnfocusTeam WHERE Initials = '[Metadata.Text:Dataset="Submit",Model="XML",Path="/field-list/field[tag='Initials']/value"]'",Connection="SwitchPublishPrint"]
email=[Database.Text:SQL="SELECT Email FROM EnfocusTeam WHERE Initials = '[Metadata.Text:Dataset="Submit",Model="XML",Path="/field-list/field[tag='Initials']/value"]'",Connection="SwitchPublishPrint"]
Re: SQL Database query
Posted: Fri Aug 19, 2022 12:27 pm
by laurentd
This CASE example works fine:
printed=[Database.Text:SQL="SELECT CASE WHEN PrintedOn IS NULL OR PrintedOn = ' ' THEN 'was not printed yet' ELSE 'was already printed' END FROM EnfocusTeam WHERE Initials = '[Metadata.Text:Dataset="Submit",Model="XML",Path="/field-list/field[tag='Initials']/value"]'",Connection="SwitchPublishPrint"]
Re: SQL Database query
Posted: Fri Aug 19, 2022 12:55 pm
by vernonvd
Thanks Laurentd
i made a small change to the sample query you send and voilà!!
its working.
Code: Select all
[Database.Text:SQL="SELECT CASE WHEN ColoursReverse > 0 OR 0 = ' ' THEN '2' ELSE '1' END FROM EstimatePart INNER JOIN MainJobDetails ON EstimatePart.EstimateID = MainJobDetails.EstimateID WHERE JobNo = '19244'",Connection="Tharstern"]