Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Is there any possibility to do this with SQL??

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sijayapal

New User


Joined: 23 Dec 2008
Posts: 69
Location: India

PostPosted: Tue Aug 25, 2009 6:22 pm    Post subject: Is there any possibility to do this with SQL??
Reply with quote

Hi,

I have two tables table1 and table2. Both tables have a field named "column". I need to get all the values of the field column in a single select.
Select Column
From Table1, Table2
Where condition.

column must display datas from both the tables. Is there any possible way to Achieve this?
icon_question.gif
Back to top
View user's profile Send private message

Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Tue Aug 25, 2009 6:31 pm    Post subject:
Reply with quote

Sija,
You can use UNION.
Back to top
View user's profile Send private message
sijayapal

New User


Joined: 23 Dec 2008
Posts: 69
Location: India

PostPosted: Tue Aug 25, 2009 6:36 pm    Post subject:
Reply with quote

Srihari,

Tats right i can use union. Is there any other way???
Union consumes lot of time and performance degrades.
Back to top
View user's profile Send private message
dejunzhu

Active User


Joined: 08 May 2008
Posts: 390
Location: China

PostPosted: Tue Aug 25, 2009 7:14 pm    Post subject:
Reply with quote

You can use INTERSECT in your SQL statement.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10231
Location: italy

PostPosted: Tue Aug 25, 2009 7:26 pm    Post subject: Reply to: Is there any possibility to do this with SQL??
Reply with quote

UNION vs INTERSECT
check
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/dsnsqk15/4.3?ACTION=MATCHES&REQUEST=intersect&TYPE=FUZZY&SHELF=DSNSHKA3&DT=20081120084907&CASE=&searchTopic=TOPIC&searchText=TEXT&searchIndex=INDEX&rank=RANK&ScrollTOP=FIRSTHIT#FIRSTHIT
and meditate icon_biggrin.gif
Back to top
View user's profile Send private message
apandey

New User


Joined: 31 Aug 2009
Posts: 73
Location: Mumbai

PostPosted: Mon Aug 31, 2009 2:04 pm    Post subject:
Reply with quote

Again,Here also i will suggest to use OUTER JOIN. Joins are explicitly meant to fetch the data from one or more tables. Unions bascially joins
the results of 2 tables by removing duplicates.
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Mon Aug 31, 2009 2:08 pm    Post subject:
Reply with quote

Hi,
Quote:
Again,Here also i will suggest

Where else???? icon_rolleyes.gif
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Mon Sep 07, 2009 1:59 pm    Post subject: Is there any possibility to do this with SQL??..Try this
Reply with quote

Hi sijayapal,

We can do it...Please check whether the below query returns results as you expect.

SELECT T1.COLUMN || T2.COLUMN AS COLUMN
FROM TABLE1 T1
LEFT JOIN TABLE2 T2
ON <CONDITION>
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Tue Sep 08, 2009 7:36 am    Post subject: Re: Is there any possibility to do this with SQL??..Try this
Reply with quote

ajeshrn wrote:
Hi sijayapal,

We can do it...Please check whether the below query returns results as you expect.

SELECT T1.COLUMN || T2.COLUMN AS COLUMN
FROM TABLE1 T1
LEFT JOIN TABLE2 T2
ON <CONDITION>

May I ask you: Why you use "LEFT JOIN" not "FULL JOIN" ?

Something like this:

Code:
SELECT coalesce(T1.COLUMN, '') || coalesce(T2.COLUMN, '') AS COLUMN
     FROM TABLE1 T1
Full JOIN TABLE2 T2
ON <CONDITION>


Thanks.
Back to top
View user's profile Send private message
mallik4u

New User


Joined: 17 Sep 2008
Posts: 75
Location: bangalore

PostPosted: Tue Sep 08, 2009 11:09 am    Post subject:
Reply with quote

Hi Sijayapal,

Using JOINs we will be able to combine two combine two tables (results).
Ex:
T1. COLUMN
ABC
BBC
CBC
T2.COLUMN
ZBC
YBC
XBC

If we use JOIN (either FULL/LEFT/RIGHT) then two columns will be comined (concatinated) into one column like the following.
COLUMN
ABC ZBC
BBC YBC
CBC XBC.

But if you want the second table column to join after the first table column then we have use UNION/UNION ALL.
COLUMN
ABC
BBC
CBC
ZBC
YBC
XBC.

Reading your question/doubt, i feel that you have to use UNION/UNIONALL.

Please let me know if my understanding is wrong.
Back to top
View user's profile Send private message
ajeshrn

New User


Joined: 25 Mar 2009
Posts: 78
Location: India

PostPosted: Tue Sep 08, 2009 1:11 pm    Post subject:
Reply with quote

Hi lkhiger,

Using a LEFT JOIN / FULL JOIN depends on the requirement.I have given a sample query which will fetch the records of TABLE1 and TABLE2 in the same row.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts any possibility to minimize CPU time sonali12_9 DB2 8 Mon Dec 14, 2009 1:46 pm
No new posts Is there any possibility of reading a... mightyscorpashwin All Other Mainframe Topics 10 Thu Mar 27, 2008 4:10 pm
No new posts Is ther any possibility of passing ar... lucky.rohith COBOL Programming 4 Thu Aug 24, 2006 12:28 am
No new posts Possibility of having more than one p... edwin DB2 3 Mon May 02, 2005 8:03 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us