Nested SQL question

[Mark Geerdes]

Working on a nested SQL that I think should work, but is giving me wrong data:

 

ExecuteSQL ( 

"SELECT COUNT (DISTINCT pgc1."C1FD~Date")

FROM "PGC1~Field Days" pgc1

WHERE pgc1."C1FD~Date" BETWEEN ? AND ?

AND pgc1."C1FD~Primary" IN (

'Height Pole'

)

"

; 

"" ; "" ;

ScheduledTimeOff::q1StartDate ; ScheduledTimeOff::q1EndDate ; 

ScheduledTimeOff::id_EmployeeNumber )

 

gives me 42

 

ExecuteSQL ( 

"SELECT r.workingDaysSQLList

FROM "@Resources" r

" ; 

"" ; ""  )

 

gives me this:

 

Flatbed,Escort,Tillerman,Height Pole,Assem/Disassem,Route Survey,Push Truck,Pull Truck,Load/Unload,Care/Maintenance (In-Field),Off-Duty (In-Field),Repositioning/Travel,Mentoring,Supervision,Training (In-Field),Training (In-Classroom),Shop/Office Work

 

Note, Height Pole is 3rd in list

 

ExecuteSQL ( 

"SELECT COUNT (DISTINCT pgc1."C1FD~Date")

FROM "PGC1~Field Days" pgc1

WHERE pgc1."C1FD~Date" BETWEEN ? AND ?

AND pgc1."C1FD~Primary" IN (

SELECT r.workingDaysSQLList

FROM "@Resources" r

)

"

; 

"" ; "" ;

ScheduledTimeOff::q1StartDate ; ScheduledTimeOff::q1EndDate ; 

ScheduledTimeOff::id_EmployeeNumber )

 

gives me 0

 

what's wrong with my nested select? it should work.

[Wim Decorte]

just answered your question on Technet...

 

I think the issue is that the result of the 2nd ExecuteSQL is jus a string of values and the IN clause expects each text value to be surrounded in single quotes.

[Mark Geerdes]

I had tried to put single quotes into another field, and then doing the nested select to get to that list, but still came up with 0. In trying to work around this issue, I came up with this:

 

ExecuteSQL ( 

"SELECT COUNT (DISTINCT "C1FD~Date")

FROM "PGC1~Field Days"

WHERE "C1FD~Date" BETWEEN ? AND ?

AND "C1FD~Primary" IN ( " & STO » Resources::workingDaysSQLList & " )

AND "C1FD~EMPNo" = ?

"

; 

"" ; "" ;

ScheduledTimeOff::q1StartDate ; ScheduledTimeOff::q1EndDate ; 

ScheduledTimeOff::id_EmployeeNumber )

 

This works, but I would still like any ideas on why the nested SELECT didn't work if anyone has any ideas.

[Mark Geerdes]

okay, fixed the issue. I thought I had tried this once before posting here, but maybe my syntax was wrong. The trick is to make sure you are selecting from multiple records, not one large pre-formatted list in a single field. If you run the nested select on it's own, you should get a return delimited list instead of a comma separated list. Here is the statement that works for anyone else trying to figure this out:

 

 

ExecuteSQL ( 

"SELECT COUNT (DISTINCT "C1FD~Date")

FROM "PGC1~Field Days"

WHERE "C1FD~Date" BETWEEN ? AND ?

AND "C1FD~Primary" IN ( 

SELECT workingDays FROM "@WorkingDayList"

 )

AND "C1FD~EMPNo" = ?

"

; 

"" ; "" ;

ScheduledTimeOff::q1StartDate ; ScheduledTimeOff::q1EndDate ; 

ScheduledTimeOff::id_EmployeeNumber )

[Fitch]

I noticed you are using BETWEEN. You might might want to test using date >= and date<= instead. I have found this to be much, much faster than BETWEEN, although it might not be noticeable if you're querying a smaller range to begin with.