SQL Database query

Post Reply
User avatar
vernonvd
Member
Posts: 38
Joined: Tue Oct 09, 2012 9:21 am
Location: Pretoria, South Africa

SQL Database query

Post by vernonvd »

i am trying to use a SQL statement in Private Data. its not working. :oops: 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
laurentd
Member
Posts: 137
Joined: Wed Mar 13, 2019 2:06 pm

Re: SQL Database query

Post 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"]
Laurent De Wilde, Solution Architect @ Enfocus
User avatar
vernonvd
Member
Posts: 38
Joined: Tue Oct 09, 2012 9:21 am
Location: Pretoria, South Africa

Re: SQL Database query

Post 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"]
laurentd
Member
Posts: 137
Joined: Wed Mar 13, 2019 2:06 pm

Re: SQL Database query

Post 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"]
Laurent De Wilde, Solution Architect @ Enfocus
User avatar
vernonvd
Member
Posts: 38
Joined: Tue Oct 09, 2012 9:21 am
Location: Pretoria, South Africa

Re: SQL Database query

Post by vernonvd »

Thanks Laurentd

i made a small change to the sample query you send and voilà!! :mrgreen: 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"]
Post Reply