• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Devaka Cooray
  • Tim Cooke
Sheriffs:
  • Rob Spoor
  • Liutauras Vilda
  • paul wheaton
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Mikalai Zaikin
  • Carey Brown
  • Piet Souris
Bartenders:
  • Stephan van Hulst

how to put Nested conditions in sql query

 
Ranch Hand
Posts: 1283
Netbeans IDE Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I need to check for some multipul conditions in sql

like if 'this ' and 'this' then 'this'

or when 'this' and 'this' then 'this'

I saw when and then but what i have to do is not working in that

I want to check this in simple sql query only (plsql not required)
Please help me out...
kaustubh
 
Ranch Hand
Posts: 36
1
VI Editor Chrome Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Have you tried DECODE?
 
Rancher
Posts: 1337
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You may also wish to look into stored procedures if the logic gets more complicated. Of course, any time one contemplates stored procedures, one should think about moving the logic into the application code.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Nitesh Sakargayan wrote:Have you tried DECODE?



Another alternative is a CASE expression, which is ANSI compliant and may result in more readable SQL code for complicated expresions.
 
Kaustubh G Sharma
Ranch Hand
Posts: 1283
Netbeans IDE Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
For example:

You could use the decode function in an SQL statement as follows:

SELECT supplier_name,
decode(supplier_id, 10000, 'IBM',
10001, 'Microsoft',
10002, 'Hewlett Packard',
'Gateway') result
FROM suppliers;



The above decode statement is equivalent to the following IF-THEN-ELSE statement:



but i have to test on the condition like if abx=123 and xyz=3423 then "do this"

also I have some perticular common case which I have to do in 1 shot like
if caseid=1 or 2 0r 3 .... then do this
else do this.....

if there's any option like this please let me know...
 
Kaustubh G Sharma
Ranch Hand
Posts: 1283
Netbeans IDE Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Martin Vajsar wrote:
Another alternative is a CASE expression, which is ANSI compliant and may result in more readable SQL code for complicated expresions.



sorry brother but link is not working at my side
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Kaustubh G Sharma wrote:sorry brother but link is not working at my side


The link should be fine, so if you cannot access the Oracle site, try to search for sql case expression in Google. However, if you do work with Oracle, obtain the documenation. You'll need it. The link leads to book named Oracle® Database SQL Reference.

The case expression is an expresion, that is it does not "do" things, but returns some value. It can be used like this:


The value1, value2, value3 can be any value or expression, even another CASE Expression, so you can have it pretty complex. The case expression may be used in where or order by clauses as well.

If you need to "do" things, post the SQL code you would use to "do it" and separately the conditions. I can try to put in the conditions for you.
 
Kaustubh G Sharma
Ranch Hand
Posts: 1283
Netbeans IDE Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I find some solution for the above problem but it is taking too much of time to extract data...please genralize and ease it...


 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Your SQL could look something like this:Several notes:
  • The messageId field appears to be a number, however you compared it to strings in your query. That leads to implicit conversions and is potentially dangerous; always avoid these.
  • The outgoing list of valid IDs is strange; some nubers were repeated. I've sorted it and put it into an IN list so that it would be more apparent.
  • The WHERE clause limits the list of valid messageId's. Numbers that do not appear there will never make it into selected rows (eg. messageId will never equal to 1 in the CASE expression), it is therefore useless and possibly confusing to list them in the CASE expression conditions. If you remove them, the lists will be shorter and more manageable.
  • When direction is 'incoming', the value of checkbox will be either 'true' or null, when direction is 'outgoing', the value of checkbox will be either 'false' or null. For other values of direction it will be always null. Is this the expected behaviour? It seems a bit strange to me.
  • If your query is slow, it probably is not because of the CASE expression. Try replacing the computed value of checkbox field with a constant or null and re-run the statement. I'd expect the runtime to be practically equal. The problem could lie in the way tables are joined in your query, may be a missing index or something.

  •  
    Kaustubh G Sharma
    Ranch Hand
    Posts: 1283
    Netbeans IDE Chrome Java
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Thanks you very much Martin...Your answers really helpful to me. I always get help from javaranch but definetely this is the best one....

    Keep Rocking...

    and yup thanks for spending your quality time with my sql problem...
    Enjoy Maadi
    Kaustubh
     
    Power corrupts. Absolute power xxxxxxxxxxxxxxxx is kinda neat.
    Smokeless wood heat with a rocket mass heater
    https://woodheat.net
    reply
      Bookmark Topic Watch Topic
    • New Topic