1 2 Previous Next 15 Replies Latest reply: Feb 25, 2008 11:35 AM by 479343 RSS

    Select statement

    612097
      Hi all,
      I've been having really hard time getting this query done. Here is the scenario. I have a table with columns client_score, client_favor, client_dependable and value_percent. Now I need to get all the clients whom meet all the following criteria.
      client_score greater than 60 value_percent
      and
      client_favor less than 40 value_percent
      and
      client_dependable greater than 50 value_percent.
      I do not know if I can have case statement within where clause in pl/sql. Any suggestion would be appreciated.

      thank you.
        • 1. Re: Select statement
          479343
          sample data and expected output would help us suggest you.
          • 2. Re: Select statement
            612097
            I am really sorry that I totally messed up the content. Actually I have columns which are Client_Code, Value_Percent and Cleint_ID.

            sample data
            client_code | value_percent | client_id
            ---------------------------------------
            CLS 30
            CLF 40
            CLD 50

            and so on... this table is joined with clients where I have all the client information. Now I need to get all the clients whom meets all this criteria
            CLS < 30 and
            CLF > 40 and
            CLD < 20 ...

            I hope I made it clear...
            • 3. Re: Select statement
              546595
              i think you are looking for this, use case or decode will help you
              http://www.databasejournal.com/features/oracle/article.php/3344871
              • 4. Re: Select statement
                479343
                Sorry to say..but the required information is still missing. Can you post the sample data of client table and the expected output as well. Otherwise we keep guessing and it waste of time for you as well as us.
                • 5. Re: Select statement
                  612097
                  Thank you for quick replies. I really appreciate this help. Alright here is some more sample data
                  table clients
                  client_Id, client_name, client_address, client_from this contains information about clients.

                  table clients_results with columns
                  client_id | client_code | value_percent
                  ------------------------------------------------------
                  1...........| CLS ..........| 30
                  1...........| CLF ..........| 40
                  1...........| CLD ..........| 50
                  2...........| CLS ..........| 40
                  2...........| CLF ..........| 40
                  2...........| CLD ..........| 45
                  and so on...

                  here is the query like
                  select * from clients c join client_results cr on c.client_id = cr.client_id where

                  case
                  when cr.client_code = 'CLS' then value_percent < 45
                  when cr.cleint_code = 'CLD' then value_percent > 25
                  when cr.client_code = 'CLF' then value_percent < 50
                  end

                  I believe that I cannot use case in the where clause. Overall I need to get all the clients whom meet the criterias of
                  CLS < 45 and
                  CLD > 25 and
                  CLF < 50

                  Please let me know if you need more information.
                  Thank you.
                  • 6. Re: Select statement
                    581702
                    select distinct a.* from client a,client_results b
                    where a.client_id=b.client_id and client_code='CLS' and value_percent<45
                    union
                    select distinct a.* from client a,client_results b
                    where a.client_id=b.client_id and client_code='CLD' and value_percent>25
                    union
                    select distinct a.* from client a,client_results b
                    where a.client_id=b.client_id and client_code='CLF' and value_percent<50
                    /


                    G.
                    • 7. Re: Select statement
                      479343
                      something like this could help:
                      Connected to:
                      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
                      With the Partitioning, OLAP and Data Mining options
                      
                      SQL> with clients as(
                        2      select 1 client_id, 'abc' client_name,'1234 aaa' address from dual union all
                        3      select 2, 'abc', '5678 qqq' from dual union all
                        4      select 3, 'xyz', '8888 ttt' from dual ),
                        5  --
                        6  client_results as(
                        7      select 1 client_id,'CLS' client_code, 30 value_percent from dual union all
                        8      select 1,'CLF', 40 from dual union all
                        9      select 1,'CLD', 50 from dual union all
                       10      select 2,'CLS', 40 from dual union all
                       11      select 2,'CLF', 40 from dual union all
                       12      select 2,'CLD', 15 from dual union all
                       13      select 3,'CLS', 20 from dual union all
                       14      select 3,'CLF', 10 from dual union all
                       15      select 3,'CLD', 60 from dual 
                       16  )
                       17  --
                       18  select a.* from clients a ,
                       19                  (select client_id, 
                       20                          max(case when client_code = 'CLS' then value_percent end) cls,
                       21                          max(case when client_code = 'CLF' then value_percent end) clf,
                       22                          max(case when client_code = 'CLD' then value_percent end) cld
                       23                  from client_results
                       24                  group by client_id) b
                       25  where 
                       26         a.client_id = b.client_id 
                       27         and b.cls < 45
                       28         and b.cld > 25 
                       29         and b.clf < 50;
                      
                       CLIENT_ID CLI ADDRESS
                      ---------- --- --------
                               1 abc 1234 aaa
                               3 xyz 8888 ttt
                      
                      SQL> 
                      • 8. Re: Select statement
                        615436
                        Another idea:
                        WITH clients AS (
                        SELECT 1 AS client_id, 'abc' AS client_name, '1234 aaa' AS address FROM dual UNION ALL
                        SELECT 2, 'cde', '4567 bbb' FROM dual UNION ALL
                        SELECT 3, 'xyz', '7890 ccc' FROM dual UNION ALL
                        SELECT 4, 'efg', '2345 ddd' FROM dual UNION ALL
                        SELECT 5, 'ijk', '3456 eee' FROM dual UNION ALL
                        SELECT 6, 'ufj', '9999 fff' FROM dual UNION ALL
                        )
                        ,clients_results AS (
                        SELECT 1 AS client_id, 'CLS' AS client_code, 30 AS value_percent FROM dual UNION ALL
                        SELECT 1, 'CLF', 40 FROM dual UNION ALL
                        SELECT 1, 'CLD', 50 FROM dual UNION ALL
                        SELECT 2, 'CLS', 40 FROM dual UNION ALL
                        SELECT 2, 'CLF', 40 FROM dual UNION ALL
                        SELECT 2, 'CLD', 10 FROM dual UNION ALL
                        SELECT 3, 'CLS', 20 FROM dual UNION ALL
                        SELECT 3, 'CLF', 10 FROM dual UNION ALL
                        SELECT 3, 'CLD', 60 FROM dual UNION ALL
                        SELECT 4, 'CLS', 40 FROM dual UNION ALL
                        SELECT 4, 'CLF', 40 FROM dual UNION ALL
                        SELECT 4, 'CLD', NULL FROM dual UNION ALL
                        SELECT 5, 'CLS', 40 FROM dual UNION ALL
                        SELECT 5, 'CLF', 40 FROM dual UNION ALL
                        )
                        SELECT c.client_id
                             , MAX(client_name) AS client_name
                             , MAX(address)     AS address
                          FROM clients         c
                               JOIN
                               clients_results cr
                                 ON cr.client_id = c.client_id
                        WHERE CASE
                               WHEN client_code = 'CLS' THEN SIGN(45-value_percent)
                               WHEN client_code = 'CLD' THEN SIGN(value_percent-25)
                               WHEN client_code = 'CLF' THEN SIGN(50-value_percent)
                               END
                               = 1
                        GROUP BY c.client_id
                        HAVING COUNT(*) = 3
                        ;
                        • 9. Re: Select statement
                          Aketi Jyuuzou
                          WITH clients AS (
                          SELECT 1 AS client_id, 'abc' AS client_name, '1234 aaa' AS address FROM dual UNION ALL
                          SELECT 2, 'cde', '4567 bbb' FROM dual UNION ALL
                          SELECT 3, 'xyz', '7890 ccc' FROM dual UNION ALL
                          SELECT 4, 'efg', '2345 ddd' FROM dual UNION ALL
                          SELECT 5, 'ijk', '3456 eee' FROM dual UNION ALL
                          SELECT 6, 'ufj', '9999 fff' FROM dual)
                          ,clients_results AS (
                          SELECT 1 AS client_id, 'CLS' AS client_code, 30 AS value_percent FROM dual UNION ALL
                          SELECT 1, 'CLF', 40 FROM dual UNION ALL
                          SELECT 1, 'CLD', 50 FROM dual UNION ALL
                          SELECT 2, 'CLS', 40 FROM dual UNION ALL
                          SELECT 2, 'CLF', 40 FROM dual UNION ALL
                          SELECT 2, 'CLD', 10 FROM dual UNION ALL
                          SELECT 3, 'CLS', 20 FROM dual UNION ALL
                          SELECT 3, 'CLF', 10 FROM dual UNION ALL
                          SELECT 3, 'CLD', 60 FROM dual UNION ALL
                          SELECT 4, 'CLS', 40 FROM dual UNION ALL
                          SELECT 4, 'CLF', 40 FROM dual UNION ALL
                          SELECT 4, 'CLD', NULL FROM dual UNION ALL
                          SELECT 5, 'CLS', 40 FROM dual UNION ALL
                          SELECT 5, 'CLF', 40 FROM dual)
                          select client_id,client_name,address
                            from clients a
                          where exists(select 1 from clients_results b
                                         where b.client_id = a.client_id
                                        having max(case when b.client_code = 'CLS' and value_percent < 45 then 1 else 0 end)
                                             * max(case when b.client_code = 'CLD' and value_percent > 25 then 1 else 0 end)
                                             * max(case when b.client_code = 'CLF' and value_percent < 50 then 1 else 0 end) = 1);
                          CLIENT_ID  CLI  ADDRESS
                          ---------  ---  --------
                                  1  abc  1234 aaa
                                  3  xyz  7890 ccc
                          similar threads(OTN)
                          Help with Corelated sub-query
                          Newbie: Simple select with 'IN ALL'

                          similar threads(OTN-Japan)
                          http://otn.oracle.co.jp/forum/thread.jspa?threadID=35002855
                          http://otn.oracle.co.jp/forum/thread.jspa?threadID=35003244
                          • 10. Re: Select statement
                            612097
                            Hi,
                            Thank you all for the reply. I am little new to Oracle and I see most of you have wrote.. so much select with dual union and so on which I am so confused with. I am totally lost at this point.
                            Can someone please explain me why there are so much select statements? Lets say if I have more client_codes than what I have posted ( more like 15 client_codes) do I have to write this select for each and for all the possible values.. I am really confused now.
                            It will be really appreciated if someone can explain me this in little more detail.

                            Thank you so much
                            • 11. Re: Select statement
                              603036
                              haha, no... those "select...from dual" statements are for the other posters to generate a working set of data since they don't have your specific table. so based on the information you provided, they set up their own temp table to execute the query.

                              as far as i know, since i just started reading this thread, is that all you need is the last 7 lines of the last solution. (I mean Aketi's solution... and wow... didn't realize there's an OTN in Japan too...)

                              Message was edited by:
                              RACER
                              • 12. Re: Select statement
                                612097
                                OOOpppsssss... I guess much more to learn in pl/sql :).

                                Anyways Thank you Racer,

                                I"ll try that solution.
                                • 13. Re: Select statement
                                  603036
                                  no problemo...
                                  • 14. Re: Select statement
                                    612097
                                    Thank you so much all for helping me to resolve the issue I had. You guys all gave me good suggestions that helped me learn.
                                    One quick question. I know its expensive to you "IN" operator in SQL Server - T-SQL. Is it the same way in Oracle as well? Where could I find more information on which operators are the best to use and inexpensive and vise versa?

                                    Thank you so much again.
                                    1 2 Previous Next