OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » TRYKY QUERY

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

TRYKY QUERY [message #247488]

Tue, 26 June 2007 02:02

S384492
Messages: 7
Registered: June 2007

Junior Member

HI ,
I HAVE A TABLE WITH 4 COLUMNS AND DATA LIKE THIS:

CLOC ASLOC UD LOC

PCHS CRB031 AKE2203EK
AKE2203EK D
PCHS AJAY AKE2205EK
AKE2205EK D
PCHS ANIL AK474747P X

BUT I WANT TO DISPLAY LIKE THIS:

CLOC ASLOC UD LOC

PCHS CRB031 AKE2203EK D

PCHS AJAY AKE2205EK D

PCHS ANIL AK474747P X

Report message to a moderator

Re: TRYKY QUERY [message #247501 is a reply to message #247488]

Tue, 26 June 2007 02:45

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Also explain in words what you want.

Regards
Michel

Report message to a moderator

Re: TRYKY QUERY [message #247586 is a reply to message #247488]

Tue, 26 June 2007 07:47

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Maybe the same answer would apply to the question tryky procedure?

Report message to a moderator

Re: TRYKY QUERY [message #247600 is a reply to message #247488]

Tue, 26 June 2007 08:30

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Since nothing lines up, I can't tell what is in what column. Please modify your post and add code tags. Then maybe we can help.

Report message to a moderator

Re: TRYKY QUERY [message #247728 is a reply to message #247488]

Wed, 27 June 2007 01:08

S384492
Messages: 7
Registered: June 2007

Junior Member

HI ,
I have a table with 4 columns and data like this:

CLOC	ASLOC	UD	      LOC
PCHS	CRB031	AKE2203EK	
		AKE2203EK	D
PCHS	AJAY	AKE2205EK	
		AKE2205EK	X
PCHS	ANIL	AKE4747EK	P

But I want to display like this:

CLOC   ASLOC       UD          LOC
PCHS   CRB031    AKE2203EK      D
PCHS   AJAY      AKE2205EK      X
PCHS   ANIL      AK47474EK      P

[Updated on: Wed, 27 June 2007 01:56] by Moderator

Report message to a moderator

Re: TRYKY QUERY [message #247747 is a reply to message #247488]

Wed, 27 June 2007 02:03

caliguardo
Messages: 107
Registered: February 2007
Location: Chennai

Senior Member

Y don't u post the create and insert scripts for the table. That would be easier for us to try the solution.
Check out this,

select * from table_name a,(select distinct loc , UD from table_name) b
where a.UD = b.UD

Report message to a moderator

Re: TRYKY QUERY [message #247754 is a reply to message #247747]

Wed, 27 June 2007 02:17

Maaher
Messages: 7065
Registered: December 2001

Senior Member

caliguardo wrote on Wed, 27 June 2007 09:03

@caliguardo, try to write real words (y=why, u=you). Thank you.

Furthermore, did you test your query?

Here's my script:

CREATE TABLE yourtable( cloc  VARCHAR2(5)
                      , asloc VARCHAR2(10)
                      , ud    VARCHAR2(10)
                      , loc   VARCHAR2(5)
                      )
/

INSERT INTO yourtable(cloc, asloc, ud, loc) VALUES ('PCHS','CRB031', 'AKE2203EK', NULL);
INSERT INTO yourtable(cloc, asloc, ud, loc) VALUES (NULL  ,NULL    , 'AKE2203EK', 'D' );
INSERT INTO yourtable(cloc, asloc, ud, loc) VALUES ('PCHS','AJAY'  , 'AKE2205EK', NULL);
INSERT INTO yourtable(cloc, asloc, ud, loc) VALUES (NULL  ,NULL    , 'AKE2205EK', 'X' );
INSERT INTO yourtable(cloc, asloc, ud, loc) VALUES ('PCHS','ANIL'  , 'AKE4747EK', 'P' );

SELECT *
FROM   yourtable
/

SELECT * 
FROM   yourtable a
   ,  ( SELECT DISTINCT 
               loc
             , UD 
        FROM   yourtable
      ) b
Where a.ud = b.ud
/

DROP TABLE yourtable
/

And here's the output:

SQL> @orafaq

Table created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


CLOC  ASLOC      UD         LOC
----- ---------- ---------- -----
PCHS  CRB031     AKE2203EK
                 AKE2203EK  D
PCHS  AJAY       AKE2205EK
                 AKE2205EK  X
PCHS  ANIL       AKE4747EK  P


CLOC  ASLOC      UD         LOC   LOC   UD
----- ---------- ---------- ----- ----- ----------
                 AKE2203EK  D           AKE2203EK
PCHS  CRB031     AKE2203EK              AKE2203EK
                 AKE2205EK  X     X     AKE2205EK
PCHS  AJAY       AKE2205EK        X     AKE2205EK
                 AKE2203EK  D     D     AKE2203EK
PCHS  CRB031     AKE2203EK        D     AKE2203EK
PCHS  ANIL       AKE4747EK  P     P     AKE4747EK
                 AKE2205EK  X           AKE2205EK
PCHS  AJAY       AKE2205EK              AKE2205EK

9 rows selected.


Table dropped.

It seems to me that it doesn't quite produce the desired output. No offence intended Wink

.

@S384492: Can you explain the logic. How do you get to that output? I can't see it.

MHE

Report message to a moderator

Re: TRYKY QUERY [message #247760 is a reply to message #247488]

Wed, 27 June 2007 02:38

balaji23_d
Messages: 123
Registered: February 2007

Senior Member

hi all,

what caligardo submitted the query.. I am not getting the desired output..because i have to create one table and applied your query..But unfortunately i did not get the exact output..pls submit the exact query..

Report message to a moderator

Re: TRYKY QUERY [message #247780 is a reply to message #247488]

Wed, 27 June 2007 03:51

caliguardo
Messages: 107
Registered: February 2007
Location: Chennai

Senior Member

sorry for Submitting the query without testing.my database was not working and so i couldn't test.

Report message to a moderator

Re: TRYKY QUERY [message #247788 is a reply to message #247780]

Wed, 27 June 2007 04:11

Maaher
Messages: 7065
Registered: December 2001

Senior Member

caliguardo wrote on Wed, 27 June 2007 10:51

sorry for Submitting the query without testing.my database was not working and so i couldn't test.

Have a look here: http://apex.oracle.com/i/index.html

If you apply for a workspace, you have a database waiting for you at all times. Very Happy

MHE

Report message to a moderator

Re: TRYKY QUERY [message #247790 is a reply to message #247488]

Wed, 27 June 2007 04:13

caliguardo
Messages: 107
Registered: February 2007
Location: Chennai

Senior Member

This is simply great.Many Thanks Maaher.

Report message to a moderator

Re: TRYKY QUERY [message #247862 is a reply to message #247488]

Wed, 27 June 2007 07:02

S384492
Messages: 7
Registered: June 2007

Junior Member

i got the result.

select * from
(select distinct nvl(a.cloc,b.cloc) cloc ,nvl(a.asloc,b.asloc) asloc,a.ud ud ,nvl(a.loc,b.LOC) loc from yourtable a, yourtable b
where
a.UD=b.UD)p
where
ASLOC is not null
and cloc is not null
and ud is not null
and loc is not null

Report message to a moderator

Re: TRYKY QUERY [message #247900 is a reply to message #247488]

Wed, 27 June 2007 08:12

Bill B
Messages: 1971
Registered: December 2004

Senior Member

how about this

select a.cloc,a.asloc,a.ud,nvl(b.loc,a.loc)
from my_table a, my_table b
where a.cloc is not null
and b.cloc is null
and a.ud = b.ud(+);

Report message to a moderator

Re: TRYKY QUERY [message #248219 is a reply to message #247900]

Thu, 28 June 2007 05:52

tarunj
Messages: 23
Registered: April 2007
Location: Noida

Junior Member

SELECT CLOC, ACLOC,UD,(SELECT loc
FROM test_backup t1 WHERE t.level = t1.level
AND t1.loc != '0') AS LOC FROM test_backup t where t.id!=''

Report message to a moderator

Previous Topic:	how to get the QUARTERLY data
Next Topic:	Cartesian product in joins

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 20:55:33 CST 2024