Home » SQL & PL/SQL » SQL & PL/SQL » Compute complex SQL query with decode
Compute complex SQL query with decode [message #243602] Thu, 07 June 2007 22:52 Go to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

Need to get help in computing the query base on below data:

Category Registration_Date
A 20-Jan-2001
A 15-Jan-2002
B 15-Mar-2002
A 25-Apr-2004
B 26-May-2005
A 28-Nov-2005

How do I query out those people who are in category A and Registration_Date is greater than 31-May-2002 and all in category B. I need two result, one is the records and another one is the number of employee by using the count function.

pls advise........
Re: Compute complex SQL query with decode [message #243603 is a reply to message #243602] Thu, 07 June 2007 22:57 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
1) Why did you NOT read & FOLLOW the posting guideline in the #1 STICKY post at the top of this forum?
http://www.orafaq.com/forum/t/42428/74940/
2) Do you know the difference between PL/SQL & SQL (other than the "PL")?
3) Other than you not knowing the answer, what make you think your question requires an EXPERT to answer it?
4) You need to realize that we (TINW) don't do homework assignments for struggling students. You need to do your own work.

[Updated on: Thu, 07 June 2007 23:03] by Moderator

Report message to a moderator

Re: Compute complex SQL query with decode [message #243623 is a reply to message #243603] Fri, 08 June 2007 01:48 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

I do know all the rules, due to decode function that I'm going use in my package, so I categorize it in PL/SQL and I might possible hit problem in my package after applying.And for your information that I did try my own, but fail to get what I want,my query is as below:

SELECT name,DECODE (LEAST(t1.registration_date, "),to_date(31-May-2002), DECODE (t1.category, 'A', 'N','Y'), 'Y') AS PASS FROM t1 TableA, t2 TableB WHERE t1.e_id = "3" AND t1.e_id = t2.e_id ORDER by PASS;

I still be able to achieve the result by using the if then else statement in my package base on the result from the SQL statement aboved, so I just would like to know is there any better way.

Well, I really sad to hear that "You need to realize that we (TINW) don't do homework assignments for struggling students. You need to do your own work", I know this, but I just feel that sometime is due to we really have no idea on that, so that is why we post our question here, is not to say that we don't want to try our self and moreover it is very hard to know whether the user follow this -> "Did I try myself? Or am I just hoping that someone else is willing to do my work?"


May be you can get a better advise to me, why type of question that we should post in this forums.

pls advise....
Re: Compute complex SQL query with decode [message #243627 is a reply to message #243623] Fri, 08 June 2007 02:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hi,

You cannot blame anacedent for thinking you were out for an easy solution for your homework, given the fact that you showed nothing about your own efforts in your original post.
Next time, you'd better include these Smile
Your query has some flaws:
LEAST(t1.registration_date, ")
least of something and NULL is always NULL. Besides, you compare a date to a string.
to_date(31-May-2002)
to_date requires a string (preferrably two: also an explicit format): to_date('31-May-2002', 'dd-Mon-yyyy')

Since I don't really understand your requirements, I cannot tell you if your query will lead to the desired result.
To be really able to help you, supply us with
- create table statements
- some inserts (enough to make your problem clear)
- desired output
- possibly, your output and an explanation why it's not good
Re: Compute complex SQL query with decode [message #243631 is a reply to message #243623] Fri, 08 June 2007 02:14 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
@ying: anacedent was probably pointing at the kind of information we're looking for and how it is presented.
from the sticky
  1. Choose a topic that makes sense, perhaps the error message. A good topic title might be "ORA-1555 during pl/sql loop" A less fortunate choice of title might be "URGNT PLZ!!!!!!!!!!!!" Avoid the following keywords: urgent/please/help/now/immediately (and derivatives)
  2. Choose the appropriate forum, if in doubt: don't worry. The moderators can always move it.
  3. If relevant, post the error code(s).
  4. If you want to post error codes or SQL*Plus output, just copy everything that is on your screen when the error occurred, for instance:
    SQL> insert into t2 values (t2_s.nextval, x1) returning id into :x2;
    insert into t2 values (t2_s.nextval, x1) returning id into :x2
    *
    ERROR at line 1:
    ORA-00984: column not allowed here
  5. Post Database and OS version. By doing so, you avoid reactions using features you can't use.
  6. Post any code with the formatting tags:
    [code] Enter your code here.[/code]
    You can also paste the code in your message, select it and hit this button http://www.orafaq.com/forum/theme/orafaq/images/b_code.gif You can also make use of the formatter page of this site. It can be found here: http://www.orafaq.com/utilities/sqlformatter.htm . Just copy your code into the page hit "Format code" and copy the formatted code to your message.
  7. Show us what you did (if you tried it yourself), including errors and/or why the result is not what you want.
  8. Provide DDL (CREATE TABLE, etc.) as appropriate instead of listing the table structure. It'll help people setting up a test set (yes, some people really do that)
  9. Provide INSERT statements for sample data instead of pasting in or mocking up the results of a SELECT statement.
  10. Provide your expected result set.
  11. If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.



In your case:
- If you provide sample data within code tags, it is readable, we can copy/paste it and try something.
- If you provide your expected result based on that sample data, people that don't have a thorough knowledge of English can help too.
- If you format your SQL statement and put it within code tags, it is readable.

I suggest you read that sticky Wink Don't forget to read the second sticky too.

As for your question I will get back to you. Let me try something.

@anacedent: take a deep breath and relax, although I do understand you. We have to ask for details over and over again. In the end it takes 20 replies before we know what the OP is after.

MHE

[Updated on: Fri, 08 June 2007 02:16]

Report message to a moderator

Re: Compute complex SQL query with decode [message #243634 is a reply to message #243627] Fri, 08 June 2007 02:42 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Dear Frank,

Thank you so much for your advise....
Actually, the sample I provided is the simplified version from the real one, but the result I want to achieve is the same. forget to add the to_date('31-May-2002', 'dd-Mon-yyyy') is because in actual, the registration_date is compared with another date which is in another table.

May be I provide the sample again.

Name_Staff Category Registration_Date
Andy A 20-Jan-2001
Michelle A 15-Jan-2002
Sharon B 15-Mar-2002
Richards A 25-Apr-2004
Leanne B 26-May-2005
Ivy A 28-Nov-2005

 SELECT name_staff,DECODE (LEAST(t1.registration_date, "),to_date('31-May-2002', 'dd-Mon-yyyy'), DECODE (t1.category, 'A', 'N','Y'), 'Y') AS PASS FROM t1 TableA, t2 TableB WHERE t1.e_id = "3" AND t1.e_id = t2.e_id ORDER by PASS; ]


With this I can get all the employees (category A and all in category B), but I require to further filter my records by using the if then else statement, e.g if the PASS = "N" then.

So I just wonder whether I can achieve by using one SQL statement. I try to revise the SQL Statement as below but failed:
SELECT name_staff,DECODE (LEAST(t1.registration_date, "),to_date('31-May-2002', 'dd-Mon-yyyy'), DECODE (t1.category, 'A', 'N','Y'), 'Y') AS PASS FROM t1 TableA, t2 TableB WHERE t1.e_id = "3" AND t1.e_id = t2.e_id and PASS = "Y" ORDER by PASS;


But error occur, must use column name.

pls advise....




Re: Compute complex SQL query with decode [message #243638 is a reply to message #243634] Fri, 08 June 2007 02:56 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I still don't get it. So far, I've distilled the following DDL/DML:

1. The create table statement
CREATE TABLE yourtable( name_staff        VARCHAR2(15)
                      , category          VARCHAR2(1)
                      , registration_date DATE
                      )
/
2. The Insert statements:
INSERT INTO yourtable VALUES ('Andy'    ,'A', TO_DATE('20-Jan-2001','DD-Mon-YYYY'));
INSERT INTO yourtable VALUES ('Michelle','A', TO_DATE('15-Jan-2002','DD-Mon-YYYY'));
INSERT INTO yourtable VALUES ('Sharon'  ,'B', TO_DATE('15-Mar-2002','DD-Mon-YYYY'));
INSERT INTO yourtable VALUES ('Richards','A', TO_DATE('25-Apr-2004','DD-Mon-YYYY'));
INSERT INTO yourtable VALUES ('Leanne'  ,'B', TO_DATE('26-May-2005','DD-Mon-YYYY'));
INSERT INTO yourtable VALUES ('Ivy'     ,'A', TO_DATE('28-Nov-2005','DD-Mon-YYYY'));


3. Your try:
SELECT name_staff
     , DECODE ( LEAST(t1.registration_date, <?> ) -- Some mysterious double quote...
              , to_date('31-May-2002', 'dd-Mon-yyyy'), DECODE ( t1.category, 'A', 'N','Y')
              , 'Y'
              ) AS PASS 
FROM t1 TableA 
   , t2 TableB 
WHERE t1.e_id = "3" -- If it is a number: no double quotes, if it is a varchar2: single quotes, no double quotes
AND   t1.e_id = t2.e_id 
ORDER by PASS;


Edit: I think I get it. You want only those that have pass='Y', right? If so: move the entire decode construction to the where clause.

MHE

[Updated on: Fri, 08 June 2007 03:00]

Report message to a moderator

Re: Compute complex SQL query with decode [message #243642 is a reply to message #243638] Fri, 08 June 2007 03:15 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Dear Maaher,

Don't know what to say, you are correct, why I never think of...you really solve my problem. Thank you so so so much.

I have revised my stateemnt as below:
SELECT name_staff
     , DECODE (LEAST (t1.registration_date
                    , TO_DATE ('31-May-2002', 'dd-Mon-yyyy')
                     )
             , TO_DATE ('31-May-2002', 'dd-Mon-yyyy'), DECODE (t1.CATEGORY
                                                             , 'A', 'N'
                                                             , 'Y'
                                                              )
             , 'Y'
              ) AS pass
FROM   t1 tablea
     , t2 tableb
WHERE  t1.e_id = 3
AND    t1.e_id = t2.e_id
AND    DECODE (LEAST (t1.registration_date
                    , TO_DATE ('31-May-2002', 'dd-Mon-yyyy')
                     )
             , TO_DATE ('31-May-2002', 'dd-Mon-yyyy'), DECODE (t1.CATEGORY
                                                             , 'A', 'N'
                                                             , 'Y'
                                                              )
             , 'Y'
              ) = 'Y'


Again, Thank you.

[Updated on: Fri, 08 June 2007 03:34] by Moderator

Report message to a moderator

Re: Compute complex SQL query with decode [message #243644 is a reply to message #243642] Fri, 08 June 2007 03:31 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I came up with the following.

My script:
CREATE TABLE yourtable( name_staff        VARCHAR2(15)
                      , category          VARCHAR2(1)
                      , registration_date DATE
                      )
/

INSERT INTO yourtable VALUES ('Andy'    ,'A', TO_DATE('20-Jan-2001','DD-Mon-YYYY'));
INSERT INTO yourtable VALUES ('Michelle','A', TO_DATE('15-Jan-2002','DD-Mon-YYYY'));
INSERT INTO yourtable VALUES ('Sharon'  ,'B', TO_DATE('15-Mar-2002','DD-Mon-YYYY'));
INSERT INTO yourtable VALUES ('Richards','A', TO_DATE('25-Apr-2004','DD-Mon-YYYY'));
INSERT INTO yourtable VALUES ('Leanne'  ,'B', TO_DATE('26-May-2005','DD-Mon-YYYY'));
INSERT INTO yourtable VALUES ('Ivy'     ,'A', TO_DATE('28-Nov-2005','DD-Mon-YYYY'));


SELECT name_staff
     , category
     , DECODE (LEAST ( t1.registration_date
                     , TO_DATE ('31-May-2002', 'dd-Mon-yyyy')
                     ) 
              , TO_DATE ('31-May-2002', 'dd-Mon-yyyy'), 'vast'
              , 'reg'
              ) x
     , DECODE (LEAST ( t1.registration_date
                       , TO_DATE ('31-May-2002', 'dd-Mon-yyyy')
                       ) 
               , TO_DATE ('31-May-2002', 'dd-Mon-yyyy'), DECODE (t1.CATEGORY
                                                               , 'A', 'N'
                                                               , 'Y'
                                                                )
               , 'Y'
                ) AS pass
FROM     yourtable t1
/

SELECT name_staff
     , category
     , registration_date
FROM   yourtable
WHERE  CASE category
       WHEN  'A' THEN registration_date
       ELSE TO_DATE ('01-Jun-2002', 'dd-Mon-yyyy')
       END > TO_DATE ('31-May-2002', 'dd-Mon-yyyy')
/

SELECT name_staff
     , category
     , registration_date
FROM   yourtable
WHERE  CASE LEAST ( registration_date, TO_DATE ('31-May-2002', 'dd-Mon-yyyy')) 
       WHEN  TO_DATE ('31-May-2002', 'dd-Mon-yyyy') THEN 'B'
       ELSE category
       END != 'A'
/
     
DROP TABLE yourtable
/


If I run it, you'll see that the 2 last selects will return the result what you're looking for. (At least, that's what I understood):
SQL> @orafaq

Table created.


1 row created.
...<SNIP>...

NAME_STAFF      C X    P
--------------- - ---- -
Andy            A reg  Y
Michelle        A reg  Y
Sharon          B reg  Y
Richards        A vast N
Leanne          B vast Y
Ivy             A vast N

6 rows selected.


NAME_STAFF      C REGISTRAT
--------------- - ---------
Sharon          B 15-MAR-02
Richards        A 25-APR-04
Leanne          B 26-MAY-05
Ivy             A 28-NOV-05


NAME_STAFF      C REGISTRAT
--------------- - ---------
Sharon          B 15-MAR-02
Richards        A 25-APR-04
Leanne          B 26-MAY-05
Ivy             A 28-NOV-05


Table dropped.


MHE
Re: Compute complex SQL query with decode [message #243649 is a reply to message #243644] Fri, 08 June 2007 03:43 Go to previous messageGo to next message
ying
Messages: 143
Registered: May 2002
Senior Member
YUp, this is what I want, but due to the database version (8i)it only support decode.

Again, thanks for your effort.

Re: Compute complex SQL query with decode [message #243652 is a reply to message #243649] Fri, 08 June 2007 04:03 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
ying wrote on Fri, 08 June 2007 10:43
YUp, this is what I want, but due to the database version (8i)it only support decode.
And this is the reason why we ask Oracle versions.

You can easily rewrite the CASE as a DECODE:
SELECT name_staff
     , category
     , registration_date
FROM   yourtable
WHERE  Decode( category
             ,  'A', registration_date
             , TO_DATE ('01-Jun-2002', 'dd-Mon-yyyy')
             ) > TO_DATE ('31-May-2002', 'dd-Mon-yyyy')
/

SELECT name_staff
     , category
     , registration_date
FROM   yourtable
WHERE  Decode( LEAST ( registration_date, TO_DATE ('31-May-2002', 'dd-Mon-yyyy')) 
             , TO_DATE ('31-May-2002', 'dd-Mon-yyyy'), 'B'
             , category
             ) != 'A'
/


MHE
Previous Topic: Split the dates into weeks
Next Topic: How to insert a blob object into a table - in sql
Goto Forum:
  


Current Time: Tue Dec 06 02:54:00 CST 2016

Total time taken to generate the page: 0.12127 seconds