Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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
This topic is locked: you cannot edit posts or make replies. Issue in suppressing leading zeros &a... Vijay_Sirisha COBOL Programming 21 Thu Jun 07, 2018 8:01 pm
No new posts CICS-MQ connection setup issue kaushikv CICS 3 Wed Mar 21, 2018 8:10 pm
No new posts Need Help!!CEE library issue while br... kaushikv CICS 9 Fri Mar 09, 2018 6:06 pm
No new posts Tape file issue abdulrafi JCL & VSAM 2 Thu Feb 08, 2018 12:01 pm
No new posts ISSUE IN copying Sequential file reco... thesumitk JCL & VSAM 2 Wed Dec 13, 2017 3:07 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us