Re: SQL select with dynamic 'in' list in the where clause

["Golovach, Michael T." <Mike.Golovach@VTMEDNET.ORG>]

Pankaj,

Thank you.  I just had my face in the book and had arrived at the  square
bracket solution, although I must admit that I was not sure why that was the
solution.
Again, thanks for the help.
Michael

> -----Original Message-----
> From: Pankaj Bedekar [SMTP:BedekarP@ALCONEMARKETING.COM]
> Sent: Tuesday, July 20, 1999 11:33 AM
> To:   Multiple recipients of list SQR-USERS
> Subject:      Re: SQL select with dynamic 'in' list in the where clause
> 
> the error is very explanatory .... 
> u cannot say 
> where sc.COMPANY in '('100')' .... since char vars are replaced by
> 'var'.... 
> use 
> where sc.COMPANY in [$paygroup_list] 
> so that result will be 
> where sc.COMPANY in ('100') 
> 
> pankaj 
> -----Original Message----- 
> From: Golovach, Michael T. [ <mailto:Mike.Golovach@VTMEDNET.ORG>] 
> Sent: Tuesday, July 20, 1999 8:13 AM 
> To: Multiple recipients of list SQR-USERS 
> Subject: SQL select with dynamic 'in' list in the where clause 
> 
> 
> Hello all, 
> 
> I am in a PeopleSoft 6.02, Sybase 11.9.2, Windows NT 4.0, SQR 3
> environment. 
> 
> I am trying to run a select with a dynamically built 'in' lists for the 
> where clause as follows: 
> 
> ! Do the salaried employee count 
> 
> BEGIN-SELECT 
> 
> sc.EMPLID 
> sc.COMPRATE 
> sc.STD_HOURS 
> 
>      IF &sc.EMPLID != $last_emplid 
>         LET #sal_emps = #sal_emps + 1 
>         LET $last_emplid = &sc.EMPLID 
>         END-IF 
>      LET #sal_hours = #sal_hours + (&sc.STD_HOURS * 2) 
>      LET #sal_wages = #sal_wages + &sc.COMPRATE 
> 
> from PS_JOB sc 
> where sc.COMPANY in $company_list 
> ! where sc.COMPANY in ('100') 
> and   sc.PAYGROUP in $paygroup_list 
> ! and   sc.PAYGROUP in ('BIW','LOA','RPG') 
> and   sc.EMPL_TYPE = 'S' 
> and   sc.EMPL_STATUS in ('A','L','P') 
> and   sc.EFFDT = (select max(sc1.EFFDT) from PS_JOB sc1 
>                  where sc.EMPLID = sc1.EMPLID 
>                  and sc.EMPL_RCD# = sc1.EMPL_RCD# 
>                  and sc1.EFFDT <= $PAY_END_DT) 
> and   sc.EFFSEQ = (select max(sc2.EFFSEQ) from PS_JOB sc2 
>                  where sc.EMPLID = sc2.EMPLID 
>                  and sc.EMPL_RCD# = sc2.EMPL_RCD# 
>                  and sc.EFFDT = sc2.EFFDT) 
> 
> END-SELECT 
> 
> The variables $company_list and $paygroup_list were built as follows: 
>   Company List: ('100') 
>   PayGroup List: ('BIW','LOA','RPG') 
> The two lines above were cut from the sqr.log file, the results of a 
> display. 
> 
> I get the following messages: 
> (SQR 5528) Sybase DBRESULTS error in cursor 12: 
>    (102) Incorrect syntax near '('100') '. 
> 
>    (156) Incorrect syntax near the keyword 'and'. 
> 
>    (107) The column prefix 'sc' does not match with a table name or alias 
> name used in the query. Either the table is not specified in the FROM
> clause 
> or it has a correlation name which must be used instead. 
> 
> Error on line 1027: 
>    (SQR 3723) Problem executing cursor. 
> 
> SQRW: Program Aborting. 
> 
> The problem stems from the use of the variable $company_list in this case.
> 
> Note the hardcoded condition as a comment beneath each variable.  When I
> use 
> the hardcoded condition and comment out the condition with the variable,
> the 
> select will work. 
> 
> What am I doing wrong here?  Or, what is the book not telling me? 
> 
> Michael 
> ---------------------------------------------------- 
> Michael T. Golovach 
> Information Services 
> Fletcher Allen Health Care 
> 111 Colchester Ave 
> Burlington, VT 50401 
> Tel:802-847-8259   Fax: 802-847-6101 
> E-mail: Mike.Golovach@vtmednet.org 
> ---------------------------------------------------- 
>