SQL Statement works in build window but parse error in define window

Post Reply
cwswitch
Member
Posts: 78
Joined: Fri Feb 24, 2017 12:25 am

SQL Statement works in build window but parse error in define window

Post by cwswitch »

Here is my code - I'm looking for jobs with "outwork" that do not fit certain criteria.

Code: Select all

SELECT Factor FROM OutworkInput
WHERE EstimateID =(SELECT EstimateID FROM MainJobDetails WHERE JobNo = '[Job.NameProper]') AND 
Code NOT IN ('LF_FREIGHT_01', 'LF_FREIGHT_02','LF_FREIGHT_03', 'LF_FREIGHT_04','E_INSTALL','DIG_PRO_CLICK')
It returns 1 correctly when I'm in the build SQL window but has a parse error and fails once I OK that window

Image
Displays 1 for the test file with Outwork. Displays "SQL statement executed successfully but returned no value or information" for files with no outwork. - This is what I expect as so in both cases it's executing and working, giving me the value I'm looking for or nothing.

Image
You can see the errors start as soon as I OK. The black non-error message was produced when I was in the build window, the red errors start as soon as I OK.

Image
Subsequently I never receive a 1 so always have a false
enfocus
Newbie
Posts: 14
Joined: Fri May 12, 2017 9:10 am

Re: SQL Statement works in build window but parse error in define window

Post by enfocus »

If there seems to be an issue with the software itself, please report it to Enfocus Support via the support portal:
https://www.enfocus.com/supportportal

If it is the first time you do so, please also mention your Switch maintenance contract number in the case.
sander
Advanced member
Posts: 274
Joined: Wed Oct 01, 2014 8:58 am
Location: The Netherlands

Re: SQL Statement works in build window but parse error in define window

Post by sander »

Just to mention: Personally I never use the Build SQL statement / manual edit window because it kept crashing my Switch all the time. It made me very aggressive when I started using SQL in Switch :mrgreen:

Does your statement work if you put it in a 'Database connect' configurator or do you get the same parse error then?
cwswitch
Member
Posts: 78
Joined: Fri Feb 24, 2017 12:25 am

Re: SQL Statement works in build window but parse error in define window

Post by cwswitch »

sander wrote:Does your statement work if you put it in a 'Database connect' configurator or do you get the same parse error then?
woo hoo, yes.

Thanks!
sander
Advanced member
Posts: 274
Joined: Wed Oct 01, 2014 8:58 am
Location: The Netherlands

Re: SQL Statement works in build window but parse error in define window

Post by sander »

Haha, good to hear!
matthias
Newbie
Posts: 4
Joined: Wed Feb 15, 2017 10:01 am

Re: SQL Statement works in build window but parse error in define window

Post by matthias »

solution is pretty easy (although not too clear):

remove all carriage returns from the built SQl query and it will work fine
User avatar
billy.olvestad
Member
Posts: 51
Joined: Mon Nov 19, 2018 10:08 pm
Location: Sweden
Contact:

Re: SQL Statement works in build window but parse error in define window

Post by billy.olvestad »

matthias wrote: Wed Oct 04, 2017 3:47 pm solution is pretty easy (although not too clear):

remove all carriage returns from the built SQl query and it will work fine
Woohoo! I just had the same problem a few years later, and I have been pulling my hair out because of this. Removing the carriage returns that switch puts in is the solution. Thank you!!
Post Reply