user3004842 user3004842 - 1 month ago 10
SQL Question

Case Statement not working in PSQUERY Where Clause

I have a field bill type id which has suppose x, y and z values. Bill Type ID is a prompt and the condition is when psquery is run and if bill type id value is blank then it should return all the rows with bill type id x,y and z.

Following is the case Statement and Decode function written:

(CASE
WHEN A.BILL_TYPE_ID = ' ' THEN A.BILL_TYPE_ID LIKE ' %'
ELSE A.BILL_TYPE_ID = :3
END ))


EDIT (from comment):

SELECT
C.NAME1,
TO_CHAR(A.FROM_DT,'YYYY-MM-DD'),
B.LINE_SEQ_NUM,
B.IDENTIFIER,
B.DESCR,
B.GROSS_EXTENDED_AMT,
A.INVOICE_AMOUNT,
C.SETID,
C.CUST_ID
FROM PS_BI_HDR A, PS_BI_LINE B, PS_CUSTOMER C
WHERE
(
A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.INVOICE = B.INVOICE
AND A.BILL_TO_CUST_ID = C.CUST_ID
AND A.BUSINESS_UNIT = :1
AND A.BILL_STATUS = :2
AND
(
A.BILL_TYPE_ID =
(
CASE
WHEN A.BILL_TYPE_ID = ' ' THEN A.BILL_TYPE_ID LIKE ' %'
ELSE A.BILL_TYPE_ID = :3
END
)
)
) ORDER BY 1, 3


With above case statement I am getting error: Missing Keyword and I am not able to debug which keyword is missing. Please advise.

Decode(:3,' ','%',:3)


Using Decode function I am not getting error but when bill type id is blank then it does not return any rows. Please help.

Answer

In the case where the input list is empty, you could just compare A.BILL_TYPE_ID to itself:

SELECT 
  C.NAME1, 
  TO_CHAR(A.FROM_DT,'YYYY-MM-DD'), 
  B.LINE_SEQ_NUM,  
  B.IDENTIFIER,  
  B.DESCR,  
  B.GROSS_EXTENDED_AMT,  
  A.INVOICE_AMOUNT, 
  C.SETID, 
  C.CUST_ID 
FROM PS_BI_HDR A, PS_BI_LINE B, PS_CUSTOMER C 
WHERE 
( 
  A.BUSINESS_UNIT = B.BUSINESS_UNIT 
  AND A.INVOICE = B.INVOICE 
  AND A.BILL_TO_CUST_ID = C.CUST_ID 
  AND A.BUSINESS_UNIT = :1 
  AND A.BILL_STATUS = :2 
  AND 
  ( 
    A.BILL_TYPE_ID = 
    (
      CASE 
        WHEN :3 = ' ' THEN A.BILL_TYPE_ID
        ELSE :3 
      END 
    )
  ) 
) ORDER BY 1, 3
Comments