CODEWITHSUNDEEP
sqlpostgresql
USERRR5
USERRR5

Reputation: 430

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

Upvotes: 0

Views: 177

Answers (1)

Dai
Dai

Reputation: 154995

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.

Upvotes: 1

Related Questions