IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this community and its apps will no longer be available. More details available on our FAQ.
Topic
  • 3 replies
  • Latest Post - ‏2019-07-11T04:44:21Z by B.Hauser
Tony McNeil
Tony McNeil
1 Post

Pinned topic Conditional WHERE in embedded SQL.

‏2019-07-09T15:54:16Z | rpg sql

Hi All, I have a problem with row selection based on a program variable in embedded RPG SQL.

BGABCD is a column, a value is present in all rows. It is char(3).

PCompany is the program variable to select rows. It is char(3).

I want to select rows where BGABCD = PCompany, but if PCompany is blank then select all rows.

Various attempts listed below.

 

WHERE :PCompany in (BGABCD, '   ')

 

Selects all rows, no matter what the value of PCompany. I was hoping it would select on value of PCompany if this is not blank.

 

WHERE ((:PCompany = BGABCD) OR (:PCompany = '   '))

 

Same as above.

 

WHERE (BGABCD = SELECT(CASE

                WHEN :PCompany = '   ' THEN BGABCD

                ELSE :PCompany END))

 

Selects no rows.

 

Any help appreciated.

Thanks, Tony.

 

  • Andreas Bott
    Andreas Bott
    20 Posts

    Re: Conditional WHERE in embedded SQL.

    ‏2019-07-10T07:27:47Z  

    Hi,

     

     

    i am no SQL Expert, but perhaps i can help you.

     

    where (:PCompany = ' ' ) Or

               ((:PCompany <> ' ' ) and (:PCompany = BGABCD))

     

    This should work. Easy but funtional i think.

     

     

    Greetz

  • Phil5471
    Phil5471
    3 Posts

    Re: Conditional WHERE in embedded SQL.

    ‏2019-07-10T13:36:43Z  

    The syntax of your first example is correct.  A simple test is shown below.  

    select * from qiws.qcustcdt where '  ' in(state,' ') returns all rows in the file.

    select * from qiws.qcustcdt where 'TX' in(state,' ') returns 2 rows.

    Copy the embedded SQL in the program and paste it into Run SQL Scripts. Replace :Pcompany with a company value. Does it return the correct rows?  If so, then the statement is correct. If not then something else in the statement is causing the problem. Without seeing the entire statement I can only speculate what the problem might be.

    I hope you find it soon. These things can wear on you.

    When you find the cause please post it here.

     

  • B.Hauser
    B.Hauser
    128 Posts

    Re: Conditional WHERE in embedded SQL.

    ‏2019-07-11T04:44:21Z  

    or ...

     

    When     Company  = Case When :ParCompan  <> '' Then :ParCompany Else Company End
          and OrderNo = Case When :ParOrcerNo  > 0  Then :ParOrderNo Else OrderNo End
    
    ...
    

     

    Birgitta