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
 

 

SQL Concat Issue

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

New User


Joined: 04 Oct 2011
Posts: 25
Location: INDIA

PostPosted: Tue Feb 14, 2012 1:05 pm    Post subject: SQL Concat Issue
Reply with quote

Input Data:
aaa 3123 dave thomas
bbb 4244 catherine
aaa 2130 david
ccc 4244 herry

Query output should be as :
aaa 3123 dave thomas 2130 david
bbb 4244 catherine
ccc 4244 herry

that the above mentioned input file data is in a Table.
can we get the required above mentioned output from SQL Query?
Back to top
View user's profile Send private message

Nikhil Jain

New User


Joined: 20 Jul 2011
Posts: 16
Location: India

PostPosted: Tue Feb 14, 2012 2:52 pm    Post subject: Reply to: SQL Concat Issue
Reply with quote

Hi,

You can try writing a query similar to the below given query -

Code:
WITH NIK (RNUM,PSTL_ID,CITY_NAME) AS         
(                                                   
   SELECT ROW_NUMBER() OVER(ORDER BY CITY_NAME DESC), 
   PSTL_ID,CITY_NAME                           
   FROM ABC
   WHERE PSTL_ID='123-456'                     
)                                                   
  SELECT A.PSTL_ID,A.CITY_NAME                 
    ,(                                               
      SELECT B.CITY_NAME                               
      FROM NIK B                                     
      WHERE B.PSTL_ID='123-456'               
      AND RNUM=2                                     
     ) AS COL2                                       
    ,(                                               
      SELECT B.CITY_NAME                               
      FROM NIK B                     
      WHERE B.PSTL_ID='123-456'
      AND RNUM=3                     
     ) AS COL3                       
    ,(                               
      SELECT B.CITY_NAME               
      FROM NIK B                     
      WHERE B.PSTL_ID='123-456'
      AND RNUM=4                     
     ) AS COL4                       
    FROM NIK A                       
    WHERE A.PSTL_ID='123-456' 
    FETCH FIRST 1 ROWS ONLY         


NB : You have to be very sure of the maximum no. of records/rows that can be returned against a particular where clause. In my case, I knew for a particular PSTL_ID, there can be max 4 Cities. Hope it helps!!
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Feb 15, 2012 7:46 pm    Post subject:
Reply with quote

Hey sysdba,

Try this link and see the samples given by GuyC

That should do the trick.

Thanks,
Sushanth
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 Issue with NDM process to transmit ES... chetanambi All Other Mainframe Topics 6 Wed May 03, 2017 10:52 am
No new posts Execessive parameter issue Sumeendar JCL & VSAM 5 Mon Dec 19, 2016 4:35 pm
No new posts DFHRESPONSE returns issue divated CICS 3 Wed Nov 02, 2016 6:32 pm
No new posts Can sending 5 MB data between cobol p... Kevin Vaz CICS 12 Tue Oct 18, 2016 4:50 pm
No new posts REXX Screen not working due to LINKED... sundarkudos CLIST & REXX 1 Mon May 09, 2016 1:44 pm


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