Home » SQL & PL/SQL » SQL & PL/SQL » Problem in Query
Problem in Query [message #189316] Thu, 24 August 2006 02:42 Go to next message
annappa
Messages: 5
Registered: August 2006
Location: Bangalore
Junior Member
Hi All,

Table T1 and 4 column: ID, name, value, date

The value are..

ID, name, value, date
1, A, A1, 1/1/2006
1, B, B1, 1/1/2006
1, C, C1, 1/1/2006

2, A, A2, 1/1/2006
2, B, A2, 1/1/2006
2, C, A2, 1/1/2006


3, A, A3, 1/1/2006
3, B, A3, 1/1/2006
3, C, A3, 1/1/2006

4, A, A4, 1/1/2006
4, B, A4, 1/1/2006
4, C, A4, 1/1/2006


1, A, A1, 2/1/2006
1, B, B1, 2/1/2006
1, C, C1, 2/1/2006

2, A, A2, 2/1/2006
2, B, A2, 2/1/2006
2, C, A2, 2/1/2006


3, A, A3, 2/1/2006
3, B, A3, 2/1/2006
3, C, A3, 2/1/2006

4, A, A4, 2/1/2006
4, B, A4, 2/1/2006
4, C, A4, 2/1/2006



1, A, A1, 3/1/2006
1, B, B1, 3/1/2006
1, C, C1, 3/1/2006

2, A, A2, 3/1/2006
2, B, A2, 3/1/2006
2, C, A2, 3/1/2006


3, A, A3, 3/1/2006
3, B, A3, 3/1/2006
3, C, A3, 3/1/2006

4, A, A4, 3/1/2006
4, B, A4, 3/1/2006
4, C, A4, 3/1/2006


I need result like :

ID, A_JAN, B_JAN , C_JAN, A_FEB, B_FEB, C_FEB, A_MAR, B_MAR, C_MAR
1, A1, B1, C1, A1, B1, C1, A1, B1, C1
2, A2, B2, C2, A2, B2, C2, A2, B2, C2
3, A3, B3, C3, A3, B3, C3, A3, B3, C3


Cretiria:

I need retrive data based MONTH
if "DATE" is JAN then if "NAME" is A then display the corresponding "VALUE" in A_JAN Column.


I used wrote this query but it is not working....

SELECT
CASE WHEN ( edit_dat = TO_DATE('1/3/2006','mm/dd/YYYY') ) THEN (DECODE(FIELD_NAME, 'Owning Organization',field_value)) END FROM pqm.ats_field_audits
DECODE(FIELD_NAME, 'Owning Organization',field_value) FROM pqm.ats_field_audits WHERE edit_dat = TO_DATE('1/3/2006','mm/dd/YYYY')


SELECT t.id, t.res1, t.res2,t.res3 FROM
(SELECT application_id id,
CASE WHEN ( DATE = TO_DATE('1/1/2006','mm/dd/YYYY') )
THEN DECODE(NAME, 'Owning Organization',field_value)
ELSE '0'
END AS res1 ,
CASE WHEN ( DATE = TO_DATE('1/1/2006','mm/dd/YYYY') )
THEN DECODE(FIELD_NAME, 'Process ID',field_value)
ELSE '0'
END AS res2 ,
CASE WHEN ( DATE = TO_DATE('1/1/2006','mm/dd/YYYY') )
THEN DECODE(FIELD_NAME, 'Retired Indicator',field_value)
ELSE '0'
END AS res3 ,

CASE WHEN ( DATE = TO_DATE('2/1/2006','mm/dd/YYYY') )
THEN DECODE(FIELD_NAME, 'Owning Organization',field_value)
ELSE '0'
END AS res1 ,
CASE WHEN ( DATE = TO_DATE('2/1/2006','mm/dd/YYYY') )
THEN DECODE(FIELD_NAME, 'Process ID',field_value)
ELSE '0'
END AS res2 ,
CASE WHEN ( DATE = TO_DATE('2/1/2006','mm/dd/YYYY') )
THEN DECODE(FIELD_NAME, 'Retired Indicator',field_value)
ELSE '0'
END AS res3 ,
CASE WHEN ( DATE = TO_DATE('3/1/2006','mm/dd/YYYY') )
THEN DECODE(FIELD_NAME, 'Owning Organization',field_value)
ELSE '0'
END AS res1 ,
CASE WHEN ( DATE = TO_DATE('3/1/2006','mm/dd/YYYY') )
THEN DECODE(FIELD_NAME, 'Process ID',field_value)
ELSE '0'
END AS res2 ,
CASE WHEN ( DATE = TO_DATE('3/1/2006','mm/dd/YYYY') )
THEN DECODE(FIELD_NAME, 'Retired Indicator',field_value)
ELSE '0'
END AS res3



FROM
pqm.ats_field_audits) t
WHERE t.res1 ! = '0' and t.res1 != '0' and t.res3 !='0' ORDER BY t.id
Re: Urgent Problem in Query [message #189337 is a reply to message #189316] Thu, 24 August 2006 03:39 Go to previous messageGo to next message
annappa
Messages: 5
Registered: August 2006
Location: Bangalore
Junior Member
Hi..

I tried lot..but i missed some thing...pls. help me...

Regards,
DRA..
Re: Urgent Problem in Query [message #189401 is a reply to message #189337] Thu, 24 August 2006 08:26 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
You're playing with fire by having column names such as NAME and DATE, which are reserved words.
Re: Urgent Problem in Query [message #189402 is a reply to message #189401] Thu, 24 August 2006 08:29 Go to previous messageGo to next message
annappa
Messages: 5
Registered: August 2006
Location: Bangalore
Junior Member
Thanks for your reply..

For showing here..i took that names, this is not realy names.

DRA..
Re: Urgent Problem in Query [message #189406 is a reply to message #189402] Thu, 24 August 2006 08:42 Go to previous message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Annappa,
try this method

CREATE TABLE XYZ(ID NUMBER, NAME VARCHAR2(10), VALUE VARCHAR2(6), DATE1 DATE);

SQL> SELECT * FROM XYZ;

        ID NAME                 VALUE  DATE1
---------- -------------------- ------ ---------
         1 A                    A1     01-JAN-06
         1 B                    B1     01-JAN-06
         1 C                    C1     01-JAN-06
         2 A                    A2     01-JAN-06
         2 B                    B2     01-JAN-06
         2 C                    C2     01-JAN-06
         3 A                    A3     01-JAN-06
         3 B                    B3     01-JAN-06
         3 C                    C3     01-JAN-06
         4 A                    A4     01-JAN-06
         4 B                    B4     01-JAN-06
         4 C                    C4     01-JAN-06
         1 A                    A1     02-JAN-06
         1 B                    B1     02-JAN-06
         1 C                    C1     02-JAN-06
         2 A                    A2     02-JAN-06
         2 B                    B2     02-JAN-06
         2 C                    C2     02-JAN-06
         3 A                    A3     02-JAN-06
         3 B                    B3     02-JAN-06
         3 C                    C3     02-JAN-06
         4 A                    A4     02-JAN-06
         4 B                    B4     02-JAN-06
         4 C                    C4     02-JAN-06
         1 A                    A1     03-JAN-06
         1 B                    B1     03-JAN-06
         1 C                    C1     03-JAN-06
         2 A                    A2     03-JAN-06
         2 B                    B2     03-JAN-06
         2 C                    C2     03-JAN-06
         3 A                    A3     03-JAN-06
         3 B                    B3     03-JAN-06
         3 C                    C3     03-JAN-06
         4 A                    A4     03-JAN-06
         4 B                    B4     03-JAN-06
         4 C                    C4     03-JAN-06

36 rows selected.

SELECT ID,
MAX(DECODE(SEQ,1,VALUE,null)) A_JAN,
MAX(DECODE(SEQ,2,VALUE,null)) B_JAN,
MAX(DECODE(SEQ,3,VALUE,null)) C_JAN,
MAX(DECODE(SEQ,4,VALUE,null)) A_FEB,
MAX(DECODE(SEQ,5,VALUE,null)) B_FEB,
MAX(DECODE(SEQ,6,VALUE,null)) C_FEB,
MAX(DECODE(SEQ,7,VALUE,null)) A_MAR,
MAX(DECODE(SEQ,8,VALUE,null)) B_MAR,
MAX(DECODE(SEQ,9,VALUE,null)) C_MAR
FROM (SELECT T.*, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE1) SEQ FROM XYZ T)
WHERE ID IN (1,2,3)
GROUP BY ID;

        ID A_JAN  B_JAN  C_JAN  A_FEB  B_FEB  C_FEB  A_MAR  B_MAR  C_MAR
---------- ------ ------ ------ ------ ------ ------ ------ ------ ------
         1 A1     B1     C1     A1     B1     C1     A1     B1     C1
         2 A2     B2     C2     A2     C2     B2     A2     B2     C2
         3 A3     B3     C3     A3     B3     C3     A3     B3     C3

Previous Topic: Structure of Table created using CTAS
Next Topic: View running SQL
Goto Forum:
  


Current Time: Sun Dec 11 06:09:33 CST 2016

Total time taken to generate the page: 0.10270 seconds