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
 

 

If there is more than one office No related to a terminal

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

New User


Joined: 01 Nov 2006
Posts: 56

PostPosted: Sat Jul 27, 2013 2:38 pm    Post subject: If there is more than one office No related to a terminal
Reply with quote

Hai

i have a table A that has three fields

terminal-id
product-id
office-number

terminal/product is primary key

How can I quickly find out if there is more than one office-number related to a terminal-id?

example

row 1
terminal A
product GTA
office 1245

row 2
terminal A
product SDG
office 1249

Here two offices are related to the same terminal

thnx
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Jul 30, 2013 3:41 pm    Post subject:
Reply with quote

revdpoel,

Try this,
Code:
select terminalid, count(*)
from table
group by terminalid
having count(*) > 1


Thanks,
sushanth
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jul 30, 2013 8:53 pm    Post subject:
Reply with quote

Hi Sushanth,

What if there were 2 rows with the same office# and multiple products?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Jul 31, 2013 10:12 am    Post subject:
Reply with quote

Hi D.sch.

This can do that,
Code:
SELECT officeno,
REPLACE(                                                         
REPLACE(                                                         
REPLACE(                                                         
CAST(XML2CLOB(XMLAGG(XMLELEMENT(NAME X , productid))) AS VARCHAR(200))
,'<X>','')                                                       
,'</X>',' ')                                                     
,'<X/>',' ')                                                     
FROM tbqual.tablename
GROUP BY officeno                                           
;                                                                 


revdpoel, this is for you

Code:
SELECT terminalid,
REPLACE(                                                         
REPLACE(                                                         
REPLACE(                                                         
CAST(XML2CLOB(XMLAGG(XMLELEMENT(NAME X , officeno))) AS VARCHAR(200))
,'<X>','')                                                       
,'</X>',' ')                                                     
,'<X/>',' ')                                                     
FROM tbqual.tablename
GROUP BY terminalid                                           
;                                                                 

(USED XML just to do row to column transformation)


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 SQL Order By related question Joseph K Thomas DB2 8 Fri Mar 18, 2016 12:53 pm
No new posts JCL sort related Mani453 DFSORT/ICETOOL 13 Sun Dec 20, 2015 1:04 pm
No new posts Query related to inserting an auto po... Gopalakrishnan V DB2 4 Fri Dec 18, 2015 12:09 am
No new posts Related to BIND parameters in DB2 sivareddy123 ABENDS & Debugging 2 Fri Jan 30, 2015 4:12 pm
No new posts Cursor related Issues sivareddy123 DB2 2 Fri Jan 30, 2015 4:01 pm


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