Re: Embedded static sql case

If I understand you correctly, you want to select a specific company, or all companies.

What about this approach?

WHERE COMPANY BETWEEN :LO_CMPNY and :HI_CMPNY

Before you open the cursor:

If you want a specific company set both LO_CMPNY and HI_CMPNY to the same value, the company you want.

If you want all companies, set LO_CMPNY to *LOVAL and HI_CMPNY to *HIVAL.

Sam

On 1/29/2013 9:28 AM, Horn, Jim wrote:

I know I could do this with dynamic sql but how about static -

I want to put options in the select criteria based on something entered
into the program. Is there some kind of case statement that would work?
Something like -
WHERE
(CASE WHEN 0 = :@COMPANY THEN 1
WHEN 0 < :@COMPANY AND A.ITCOMPANY = @COMPANY THEN 1
ELSE 0
END) = 1

where @company is 0 or a company# entered into the program

Jim