Oracle sql with when case when

[ngjinyi0620]

SELECT *
FROM  monthly_report
WHERE type = $P{TYPE}
AND (CASE WHEN ($P{REPORT_TYPE} IS NULL) THEN (1=1) ELSE (report_type= $P{REPORT_TYPE}) END)

I get error on missing right parenthesis. No matter, how i edit i still get error. May i know is syxtax error or logic error?

The sql query run well in postgres database and show error in oracle sql..

thanks in advance.

[jgust]

You can simplify the condtion from the weird CASE  and 1=1 to a simple OR statement.  To me this is more readable.

SELECT *
FROM  monthly_report
WHERE type = $P{TYPE}
  AND ($P{REPORT_TYPE} IS NULL
    OR report_type= $P{REPORT_TYPE});

 

[jgust]

You can simplify the condition from the weird CASE  and 1=1 to a simple OR statement.  This is more readable.

SELECT *
FROM  monthly_report
WHERE type = $P{TYPE}
  AND ($P{REPORT_TYPE} IS NULL
    OR report_type= $P{REPORT_TYPE});

[ngjinyi0620]

Hi,jgust. Thanks for your reply. After try your method, it can read field in dataset and query dialog without error..

but when i try to review, it show there are compilation errors please fix the design.

Here is my complete query for my case

SELECT *
FROM  monthly_report
WHERE report_type = $P{REPORT_TYPE}
AND ($P{CASE_TYPE} is null OR CASE_TYPE= $P{CASE_TYPE} )
AND ($P{USE_CASE} is null OR USE_CASE = $P{USE_CASE})
AND ($P{REPORT_CODE} is null OR REPORT_CODE = $P{REPORT_CODE}) 
AND DATE BETWEEN $P{From_Date} AND $P{To_Date}
AND ($P{Country} is null OR CTRY = $P{Country})
AND ($P{Refer_Code} is null OR lower(HH) like lower('%' || $P{Refer_Code} || '%'))
AND ($P{Reference_No} is null OR lower(PH_REF_NO) like lower('%' || $P{Reference_No} || '%'))
AND ($P{ROI_No} is null OR lower(ROI) like lower('%' || $P{ROI_No} || '%'))
AND ($P{Company_Name}is null OR lower(COMPANY) like lower('%' || $P{Company_Name} || '%'))
AND ($P{Name} is null OR lower(NAME) like lower('%' || $P{Name} || '%'))
AND ($P{Encode_No} is null OR lower(ENCODE_NO) like lower('%' || $P{Encode_No} || '%'))
AND (STATUS= $P{STATUS} OR STATUS not in ('AAA', 'AAB'))

[ngjinyi0620]

Sorry, the compilation error is cause by i forget change the field. But it still show error in execute SQL query. May i know what is the error of  my query?

[jgust]

The syntax works so in order to debug you should comment out all of your WHERE conditions and add them one by one until you find your syntax error.