Forum Stats

  • 3,837,690 Users
  • 2,262,286 Discussions
  • 7,900,363 Comments

Discussions

Select statement

612097
612097 Member Posts: 33
edited Feb 25, 2008 12:35PM in SQL & PL/SQL
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

Comments

  • Chaitanya.S.S.K
    Chaitanya.S.S.K Member Posts: 1,249 Blue Ribbon
    sample data and expected output would help us suggest you.
  • 612097
    612097 Member Posts: 33
    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...
  • 546595
    546595 Member Posts: 2,399
    i think you are looking for this, use case or decode will help you
    http://www.databasejournal.com/features/oracle/article.php/3344871
  • Chaitanya.S.S.K
    Chaitanya.S.S.K Member Posts: 1,249 Blue Ribbon
    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.
  • 612097
    612097 Member Posts: 33
    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.
  • 581702
    581702 Member Posts: 2
    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.
  • Chaitanya.S.S.K
    Chaitanya.S.S.K Member Posts: 1,249 Blue Ribbon
    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> 
  • 615436
    615436 Member Posts: 22
    edited Feb 18, 2008 2:16PM
    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
    ;
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Feb 18, 2008 4:26PM
    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)
    585154
    551338

    similar threads(OTN-Japan)
    http://otn.oracle.co.jp/forum/thread.jspa?threadID=35002855
    http://otn.oracle.co.jp/forum/thread.jspa?threadID=35003244
  • 612097
    612097 Member Posts: 33
    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
This discussion has been closed.