Home » SQL & PL/SQL » SQL & PL/SQL » TRYKY QUERY
TRYKY QUERY [message #247488] Tue, 26 June 2007 02:02 Go to next message
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
Re: TRYKY QUERY [message #247501 is a reply to message #247488] Tue, 26 June 2007 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: TRYKY QUERY [message #247586 is a reply to message #247488] Tue, 26 June 2007 07:47 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Maybe the same answer would apply to the question tryky procedure?
Re: TRYKY QUERY [message #247600 is a reply to message #247488] Tue, 26 June 2007 08:30 Go to previous messageGo to next message
Bill B
Messages: 1484
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.
Re: TRYKY QUERY [message #247728 is a reply to message #247488] Wed, 27 June 2007 01:08 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: TRYKY QUERY [message #247754 is a reply to message #247747] Wed, 27 June 2007 02:17 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
caliguardo wrote on Wed, 27 June 2007 09:03
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


@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
Re: TRYKY QUERY [message #247760 is a reply to message #247488] Wed, 27 June 2007 02:38 Go to previous messageGo to next message
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..
Re: TRYKY QUERY [message #247780 is a reply to message #247488] Wed, 27 June 2007 03:51 Go to previous messageGo to next message
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.
Re: TRYKY QUERY [message #247788 is a reply to message #247780] Wed, 27 June 2007 04:11 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: TRYKY QUERY [message #247790 is a reply to message #247488] Wed, 27 June 2007 04:13 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

This is simply great.Many Thanks Maaher.
Re: TRYKY QUERY [message #247862 is a reply to message #247488] Wed, 27 June 2007 07:02 Go to previous messageGo to next message
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
Re: TRYKY QUERY [message #247900 is a reply to message #247488] Wed, 27 June 2007 08:12 Go to previous messageGo to next message
Bill B
Messages: 1484
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(+);
Re: TRYKY QUERY [message #248219 is a reply to message #247900] Thu, 28 June 2007 05:52 Go to previous message
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!=''
Previous Topic: how to get the QUARTERLY data
Next Topic: Cartesian product in joins
Goto Forum:
  


Current Time: Fri Dec 09 07:56:40 CST 2016

Total time taken to generate the page: 0.13193 seconds