Link to home
Create AccountLog in
Avatar of Wilder1626
Wilder1626Flag for Canada

asked on

SQL - Where clause with If statement no2

Hello,

This is to follow my first topic: SQL - Where clause with If statement

I would like to update that part of the query:
and lower(nvl(o.owner, 'all')) like '%' || (lower(nvl(nullif('?USER_ID', 'all'), ''))) || '%' 

Open in new window


I would like to add a 3rd criteria. If i put star it would search where o.owner <> NULL , and also the rest of the query criteria.

How can i do that?

Thanks again

=l.id in(select l.id from load l, ord_load ol, ord o, ord_item oi
where l.id = ol.load_id
and l.sched_num = ol.sched_num
and o.id = ol.ord_id
and o.id =  oi.ord_id
and o.sched_num = ol.sched_num
and o.split_id = ol.split_id
and l.status <> ('C')
and lower(nvl(o.owner, 'all')) like '%' || (lower(nvl(nullif('?USER_ID', 'all'), ''))) || '%' 
and (o.pass_char1 is null
or oi.weight = '0'
or oi.pieces = '0'
or oi.cube = '0'
or oi.pallets = '0')
)

Open in new window

SOLUTION
Avatar of chaau
chaau
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Wilder1626

ASKER

Hi chaau

By adding 2 times : '?USER_ID' it's adding 2 textbox that prompt for a value in it.

I need to do this with only 1:
 '?USER_ID'

Open in new window


PortletPaul, when i try with :
and lower(nvl(o.owner, 'all')) like '%' || (lower(nvl(nullif( to_char(replace('?USER_ID','*','%')) , 'all'), ''))) || '%' 

Open in new window


It search also when o.owner is NULL when it should search when <> NULL.

when i was saying star, it was really to write down "STAR". if i write STAR, it would get me the data where o.owner <> NULL
mmm, can you try this please
and lower(o.owner) like '%' || (lower(nvl(nullif( to_char(replace('?USER_ID','*','%')) , 'all'), ''))) || '%' 

Open in new window

edit, sorry this may be useless... I can't mimic this
just so I understand, with that user input of:

'*'    is to be: "where o.owner IS NULL
'e'    is to be: "where o.owner like '%e%'
null  is to be: (the equivalent of) where 1=1

if user puts in 'ab*ab': is it acceptable to treats as: where o.owner like '%ab%ab%' ?

and: can you default this user input value?
@Wilder1626.

Just FYI, you do not need to perform these types of complex operations at SQL level. There absolutely no excuse to prepare the proper parameters at the application level and pass them on to the SQL engine. Just wondering, what prevents you from passing an additional parameter to the query? Why do not you just have three queries in your application, prepare the statements at the application level and send the prepared queries to the engine?

It will also protect your application from SQL Injections.

So, in your case you would have the application to deal with the user input. If they type "ALL" or "STAR", you would just use "and o.owner IS NOT NULL", otherwise, you would surround the value typed by the user with "%" on both ends and pass the string to the query with the filter as easy as "and o.owner LIKE '?USER_ID'".

Please note, that the least complicated query you use the more chance it will run faster. Sometimes when you use functions around the columns the engine switches to full table scan
Hi PortletPaul
Almost
'STAR' is to be: Where o.owner is not NULL
'ALL' is to be: Won't use the o.owner filter.
'e' is to be: Where o.owner like '%e%'
'th' is to be: Where o.owner like '%th%'

etc...

Hi chaau, i wish i could make it easier in the application but i can.

Since users don't have access to the tables, i need to find a simple way for them to query there data trough the application. But i do understand that the more the query gets  complicated, the more it may run for a long time.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hello all

Thanks again for your help.

Based on all the test i did in the app, i guess i will not be able to do what i want to do.

To limited on what i can do.

Thanks again for all the help.

I will stay the same i was before from my first post.

Thanks again