Home » SQL & PL/SQL » SQL & PL/SQL » UNION RELATED
UNION RELATED [message #267387] Thu, 13 September 2007 03:06 Go to next message
sharif_shahin
Messages: 2
Registered: August 2007
Location: Dhaka
Junior Member
Hi ALL
I need a solution like that

suppose

SELECT 'CL-2' CL_TYPE, TR_AMT
FROM T_TRANS
WHERE ACC_TYPE = '41'
UNION ALL
SELECT 'CL-3' CL_TYPE, tr_amt
FROM T_TRANS
WHERE ACC_TYPE = '42'


OUTPUT LIKE

CL_TYPE ---- TR_AMT
-------- --------
CL-2 ---- 100
CL-2 ---- 500
CL-3 ---- 300
CL-3 ---- 700

I NEED AT LEAST ONE ROW FOR EACH SEGMENT EVEN NO DATA RETURNS IN ANY ONE QUERY
LIKE

CL_TYPE -- TR_AMT
--------- ---------
CL-2 --- 100
CL-2 --- 500
CL-3 --- 0

IF SECOND QUERY RETURNS NO ROWS

OR

CL_TYPE -- TR_AMT
--------- ---------
CL-2 --- 0
CL-3 --- 300
CL-3 --- 700

ANYBODY HELP ME

[mod-edit] removed illiterate IM speak words.










[Updated on: Thu, 13 September 2007 13:11] by Moderator

Report message to a moderator

Re: UNION RELATED [message #267403 is a reply to message #267387] Thu, 13 September 2007 03:46 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:

I NEED AT LEAST ONE ROW FOR EACH SEGMENT EVEN NO DATA RETURNS IN ANY ONE QUERY


What does it mean? Not clear to me. Format your post nicely.
Re: UNION RELATED [message #267427 is a reply to message #267387] Thu, 13 September 2007 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I NEED AT LEAST YOU READ AND FOLLOW THE GUIDELINES AND YOU FORMAT YOUR POST.
AND RELEASE CAPS LOCK.

OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).


Regards
Michel

[Updated on: Thu, 13 September 2007 04:47]

Report message to a moderator

Re: UNION RELATED [message #267435 is a reply to message #267403] Thu, 13 September 2007 05:03 Go to previous messageGo to next message
wely2002eg11
Messages: 1
Registered: June 2007
Junior Member
hi,
If I give you a pl/sql solution to this problem , Is it enough?
Re: UNION RELATED [message #267451 is a reply to message #267435] Thu, 13 September 2007 05:41 Go to previous messageGo to next message
sharif_shahin
Messages: 2
Registered: August 2007
Location: Dhaka
Junior Member
No, I need SQL solution
Re: UNION RELATED [message #267468 is a reply to message #267451] Thu, 13 September 2007 06:53 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Sharif, the answer is to "generate" the descriptions for the buckets and outerjoin your actual results to that.

At asktom.com there is a nice example generating records using level and connect by. And I believe it's here on orafaq too, if you search for calendar you'll find it.

Regards,
Sabine
Previous Topic: Problem in creating table with user defined data type
Next Topic: Simpler Query
Goto Forum:
  


Current Time: Tue Dec 06 08:20:46 CST 2016

Total time taken to generate the page: 0.20649 seconds