USERRR5 USERRR5 - 2 months ago 7
SQL Question

Case statement is not working on query

I am working on jasper report.I have a table named

user
I have 2 parameters
internal
,
external
.When I use internal then my query needs to show the users where
username LIKE '%_@__%.__%'
and when I use external then my query needs to show the users where
username NOT LIKE '%_@__%.__%'
.Like when
internal
then report will show 2,3,4,5 no row and when
external
then report will show only one row..My query is

SELECT case
when $P{internal} = 'internal' then

id end as cid,
designation,division_name,pin_no,username FROM application_user where username LIKE
'%_@__%.__%'

else
id end as cid,
designation,division_name,pin_no,username FROM application_user where username NOT LIKE
'%_@__%.__%'


but it is not working
enter image description here

Please let me know If i'm not clear

Dai Dai
Answer

Projection statements cannot be parameterised in SQL directly (but you can in Dynamic SQL, obviously).

Your test expression should be evaluated in the WHERE block, not SELECT. The SQL you posted is not valid and won't run, so I'm curious how you're getting the results you're seeing.

Try this:

SELECT
    id AS cid,
    designation,
    division_name,
    pin_no,
    username
FROM
    application_user
WHERE
    ( $P{internal}  = 'internal' AND username     LIKE '%_@__%.__%' )
    OR
    ( $P{internal} <> 'internal' AND username NOT LIKE '%_@__%.__%' )

Note that this will not necessarily result in the best runtime execution plan because of the different effective query "shape" depending on the parameter value. Ideally you should have two different queries selected by your application code which have the different username predicates.