| View previous topic :: View next topic |
| Author |
Message |
psmadhusudhan
Beginner
Joined: 28 Nov 2006
Posts: 143
Topics: 48
|
 Posted: Tue Mar 11, 2008 10:35 am Post subject: Query help |
 |
|
I am using following query:
| Code: | SELECT
DISTINCT A.CDE_HDQTR_CRW,
A.TXT_CRW_HDQTR,
A.CDE_HQ_PREFIX,
B.CDE_LOB,
A.CDE_INACTV
FROM TJMSBCIT.TJMSCHDQ A,
TJMSBCIT.TLABAL00 B
WHERE
A.CDE_INACTV NOT IN ('I')
AND A.CDE_HDQTR_CRW = B.CDE_HDQTR_CRW
WITH UR; |
which fetches following results:
| Code: | CDE_HDQTR_CRW TXT_CRW_HDQTR CDE_HQ_PREFIX CDE_LOB CDE_INACTV
BARH22 22 LONDON SOUTH WEST DMA 200 AL 0
BARH22 22 LONDON SOUTH WEST DMA 200 AL-DMA 0
BARH33 33 LONDON S CENTRAL DMA 200 AL 0
BARH33 33 LONDON S CENTRAL DMA 200 AL-DMA 0
BARH42 42 LONDON S&SE DMA 200 AL 0
BARH42 42 LONDON S&SE DMA 200 AL-DMA 0
BILL11 11 SWINDON MCG NEW SERV METERS 500 MCG 0
BILL11 11 SWINDON MCG NEW SERV METERS 500 MCG-NEWMTR 0
BILL12 12 OXFORD MCG NEW SERV METERS 500 MCG 0
BILL12 12 OXFORD MCG NEW SERV METERS 500 MCG-NEWMTR 0
BILL13 13 WYCOMBE MCG NEW SERV METERS 500 MCG 0
BILL13 13 WYCOMBE MCG NEW SERV METERS 500 MCG-NEWMTR 0 |
Can somebody help me in modifying the query and getting the below result:
| Code: | CDE_HDQTR_CRW TXT_CRW_HDQTR CDE_HQ_PREFIX CDE_LOB CDE_INACTV
BARH22 22 LONDON SOUTH WEST DMA 200 AL 0
BARH33 33 LONDON S CENTRAL DMA 200 AL 0
BARH42 42 LONDON S&SE DMA 200 AL 0
BILL11 11 SWINDON MCG NEW SERV METERS 500 MCG 0
BILL12 12 OXFORD MCG NEW SERV METERS 500 MCG 0 |
Please help me.
_________________
Thanks
Madhu Sudhan |
|
| Back to top |
|
 |
jsharon1248
Intermediate
Joined: 08 Aug 2007
Posts: 291
Topics: 2
Location: Chicago
|
| Posted: Tue Mar 11, 2008 11:39 am Post subject: |
|
|
| What have you tried so far? |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Tue Mar 11, 2008 12:06 pm Post subject: |
|
|
psmadhusudhan,
The only difference I see is the column CDE_LOB why dont u remove that from the distinct clause? If you need the column then try this
| Code: |
SELECT DISTINCT
Z.CDE_HDQTR_CRW
,Z.TXT_CRW_HDQTR
,Z.CDE_HQ_PREFIX
,Z.CDE_LOB
,Z.CDE_INACTV
FROM (SELECT A.CDE_HDQTR_CRW AS CDE_HADQTR_CRW
,A.TXT_CRW_HDQTR AS TXT_CRW_HDQTR
,A.CDE_HQ_PREFIX AS CD_HQ_PREFIX
,SUBSTR(B.CDE_LOB,1,3) AS CDE_LOB
,A.CDE_INACTV AS CDE_INACTV
FROM TJMSBCIT.TJMSCHDQ A
,TJMSBCIT.TLABAL00 B
WHERE A.CDE_INACTV NOT IN ('I')
AND A.CDE_HDQTR_CRW = B.CDE_HDQTR_CRW
WITH UR) Z;
|
Hope this helps..
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
CraigG
Intermediate
Joined: 02 May 2007
Posts: 202
Topics: 0
Location: Viginia, USA
|
| Posted: Tue Mar 11, 2008 1:02 pm Post subject: |
|
|
A.CDE_HDQTR_CRW = B.CDE_HDQTR_CRW
You have multiple matchs on this field. You would need to narrow that down to a single match for each row in TJMSBCIT.TJMSCHDQ . |
|
| Back to top |
|
|
psmadhusudhan
Beginner
Joined: 28 Nov 2006
Posts: 143
Topics: 48
|
| Posted: Wed Mar 12, 2008 2:58 am Post subject: |
|
|
Kolusu the query is giving following result:
| Code: | CDE_HADQTR_CRW TXT_CRW_HDQTR CD_HQ_PREFIX CDE_LOB CDE_INACT
---------+---------+---------+---------+---------+---------+---------+---------+
BARH22 22 LONDON SOUTH WEST DMA 200 AL 0
BARH22 22 LONDON SOUTH WEST DMA 200 AL- 0
BARH33 33 LONDON S CENTRAL DMA 200 AL 0
BARH33 33 LONDON S CENTRAL DMA 200 AL- 0
BARH42 42 LONDON S&SE DMA 200 AL 0
BARH42 42 LONDON S&SE DMA 200 AL- 0
BILL11 11 SWINDON MCG NEW SERV METERS 500 MCG 0
BILL12 12 OXFORD MCG NEW SERV METERS 500 MCG 0 |
I guess I need to explain the data. For the combination of CDE_HDQTR_CRW, TXT_CRW_HDQTR, CDE_HQ_PREFIX I need to have only one row in the output with all five fields. I can't use GROUPBY as I need specify CDE_LOB also in the clause, which will again give all rows for a particular combination of CDE_HDQTR_CRW, TXT_CRW_HDQTR, CDE_HQ_PREFIX. Any one value of CDE_LOB for this combination is sufficient. Is this possible to get such a output through a query.
_________________
Thanks
Madhu Sudhan |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
