IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Using column function while using JOIN


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Hariharan Sukumar

New User


Joined: 14 Mar 2011
Posts: 14
Location: India

PostPosted: Tue Jun 14, 2011 12:57 pm
Reply with quote

Hi,

I need to retrieve the number of records from 2 tables using count(*). let me explain with an example .....

select a.sts, a.count(*), b.sts, b.count(*)
from taba a join tabb b
where a.sts = b.sts;

it is showing error for a.count(*)

tell me solution for this .....

Pls provide the query ......
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Tue Jun 14, 2011 1:12 pm
Reply with quote

What error?
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Tue Jun 14, 2011 1:17 pm
Reply with quote

Use group by clause
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India

PostPosted: Tue Jun 14, 2011 1:24 pm
Reply with quote

Quote:
Use group by clause
Please explain how to use group by clause in this scenario?
Back to top
View user's profile Send private message
Hariharan Sukumar

New User


Joined: 14 Mar 2011
Posts: 14
Location: India

PostPosted: Tue Jun 14, 2011 1:25 pm
Reply with quote

SELECT R.STS,
R.COUNT(*),
I.STS,
I.COUNT(*)
FROM TABA R, TABB I
WHERE R.RDATE= CURRENT DATE
AND I.IDATE= CURRENT DATE
GROUP BY R.STS, I.STS
WITH UR;

DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "*". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: ALL DISTINCT EXPRESSION

i'm getting this error ...
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Jun 14, 2011 1:28 pm
Reply with quote

Code:
select * from
(select sts , count(*) as cnt from taba group by sts) a
join
(selec sts , count(*) as cnt from tabb group by sts) b
on a.sts = b.sts;
Back to top
View user's profile Send private message
Hariharan Sukumar

New User


Joined: 14 Mar 2011
Posts: 14
Location: India

PostPosted: Tue Jun 14, 2011 1:57 pm
Reply with quote

I had issued 1 more condition know, both should be in current date.

Also if i run in spufi,
the close bracket at the end of inner select queries is in pink color ....

what will be the problem .....
Back to top
View user's profile Send private message
Hariharan Sukumar

New User


Joined: 14 Mar 2011
Posts: 14
Location: India

PostPosted: Tue Jun 14, 2011 2:05 pm
Reply with quote

Hi,

I executed the query u gave but it is showing the following error...

DSNT408I SQLCODE = -206, ERROR: A.STS IS NOT VALID IN THE CONTEXT
WHERE IT IS USED
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Jun 14, 2011 2:33 pm
Reply with quote

maybe because I typed selec instead of select
Back to top
View user's profile Send private message
Hariharan Sukumar

New User


Joined: 14 Mar 2011
Posts: 14
Location: India

PostPosted: Tue Jun 14, 2011 4:02 pm
Reply with quote

Hearty thanks .....

I got the desired output but with repeatation...

can u say how to solve it.... i used distinct but hadnt worked ...

pls refer my query and output .....

SELECT * FROM
(SELECT STS AS R_STS,
COUNT(*) AS R_CNT
FROM TABA
GROUP BY STS) R
JOIN
(SELECT STS AS I_STS,
COUNT(*) AS I_CNT
FROM TABB
GROUP BY STS) I
ON R.STS = I.TXNDE_TXNSTS
WITH UR;


---------+---------+---------+---------+-----
R_STS R_CNT I_STS I_CNT
---------+---------+---------+---------+-----
AWST 1 RECV 3
AWST 1 REJD 36
AWST 1 SETT 153383
SETT 153498 RECV 3
SETT 153498 REJD 36
SETT 153498 SETT 153383
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Tue Jun 14, 2011 7:18 pm
Reply with quote

So what output are you expecting? All those rows are distinct.
Back to top
View user's profile Send private message
Hariharan Sukumar

New User


Joined: 14 Mar 2011
Posts: 14
Location: India

PostPosted: Wed Jun 15, 2011 9:53 am
Reply with quote

I need the output as .....

---------+---------+---------+---------+-----
R_STS R_CNT I_STS I_CNT
---------+---------+---------+---------+-----
AWST 1 RECV 3
SETT 153498 REJD 36
- - SETT 153383
Back to top
View user's profile Send private message
Hariharan Sukumar

New User


Joined: 14 Mar 2011
Posts: 14
Location: India

PostPosted: Wed Jun 15, 2011 11:13 am
Reply with quote

Actually i have 5 values in tabA and 4 in tabB for STS

i mean in taba, for sts -- awst,held,pend,rejd,sett

in tabb, for sts -- recv,canc,rejd,sett

the query i issued displays the value which is having value (>0) .. output shown in previous post....

I need the putput as

---------+---------+---------+---------+-----
R_STS R_CNT I_STS I_CNT
---------+---------+---------+---------+-----
AWST 1 RECV 3
HELD 0 CANC 0
PEND 0 REJD 36
REJD 0 SETT 153383
SETT 153498 - -

Is it possible to get output like this ....

I tried, IN in WHERE clause for both the tables .....

but not worked ....

pls provide suitable query .....
Back to top
View user's profile Send private message
Hariharan Sukumar

New User


Joined: 14 Mar 2011
Posts: 14
Location: India

PostPosted: Wed Jun 15, 2011 2:59 pm
Reply with quote

Hi,

Pls provide me the solution ..... icon_idea.gif
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Jun 15, 2011 3:03 pm
Reply with quote

Quote:
Pls provide me the solution .....


please do not pester/solicit for replies

replying is on ...
voluntary basis
on our own time
free of charge
with no commitment

if You have time constraint a forum is not the best place to ask for help
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Jun 15, 2011 7:27 pm
Reply with quote

I think you oversimplified the problem , so we are missing information to help you.
My guess (I'm feeling psychic) : you've added a where-clause in the subselect and this eliminates output rows which you do want.
You don't want to count everything, but you do want output even when the count is 0.

Code:
select * from
(select sts , sum( case when RDATE= CURRENT DATE then 1 else 0 end) as cnt from taba group by sts) a
join
(selec sts , sum( case when IDATE= CURRENT DATE then 1 else 0 end)  as cnt from tabb group by sts) b
on a.sts = b.sts;
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 275
Location: Mumbai

PostPosted: Thu Jun 16, 2011 10:48 am
Reply with quote

Hi,

Do you want to join these two tables as
Code:
a.sts = b.sts

as your one query says this
where as other query says
Code:
R.STS = I.TXNDE_TXNSTS


It will be great if you are bit more clear on requirement

Regards,
Chandan
Back to top
View user's profile Send private message
Hariharan Sukumar

New User


Joined: 14 Mar 2011
Posts: 14
Location: India

PostPosted: Thu Jun 16, 2011 12:26 pm
Reply with quote

Initially i posted the following query

SELECT * FROM
(SELECT STS AS R_STS,COUNT(*) AS R_CNT
FROM TAB_R
WHERE R_DATE = CURRENT DATE
GROUP BY STS)R
JOIN
(SELECT STS AS I_STS,COUNT(*) AS I_CNT
FROM TAB_I
WHERE I_DATE = CURRENT DATE
GROUP BY STS)I
ON R.R_STS = I.I_STS
WITH UR;

OUTPUT-1:
---------
R_STS R_CNT I_STS I_CNT
---- ----------- ---- -----------
SETT 35349 SETT 35234

Later I tried the following query

SELECT * FROM
(SELECT STS AS R_STS,COUNT(*) AS R_CNT
FROM TAB_R
WHERE R_DATE = CURRENT DATE
GROUP BY STS)R,
(SELECT STS AS I_STS,COUNT(*) AS I_CNT
FROM TAB_I
WHERE I_DATE = CURRENT DATE
GROUP BY STS)I
WITH UR;

OUTPUT-2:
--------
R_STS R_CNT I_STS I_CNT
---- ----------- ---- -----------
AWST 4 RECV 8
AWST 4 REJD 2
AWST 4 SETT 36754
HELD 1 RECV 8
HELD 1 REJD 2
HELD 1 SETT 36754
SETT 36869 RECV 8
SETT 36869 REJD 2
SETT 36869 SETT 36754

See when i removed the R.R_STS = I.I_STS it is repeating ....

I need the output as ....

OUTPUT-3:
---------
R_STS R_CNT I_STS I_CNT
---- ----------- ---- -----------
AWST 4 CANC 0
HELD 1 REJD 2
PEND 0 RECV 8
REJD 0 SETT 36754
SETT 36869 - -

I TRIED THE QUERY U GAVE, BUT IT IS SHOWING OUTPUT-2 ONLY...
NOW I HOPE U UNDERSTAND MY EXPECTATION ....
KINDLY SEND ME THE QUERY FOR THE SAME ....
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Thu Jun 16, 2011 1:24 pm
Reply with quote

Hariharan,

Is it SO difficult to put code tags around your queries/outputs? It makes it so much easier to read and keeps the spacing.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Jun 16, 2011 2:10 pm
Reply with quote

You want two things on the same line (result row) which have no relation to each other, besides row number ?

1st: Requirement is ridiculous
2nd: if no rows exists with STS = 'CANC' how would 'CANC' suddenly appear in the output ?
3th: your knowledge of DB2 is to limited. You have no understanding of cartesian products, join conditions, inner and outer joins,....

the closest useful result you would get with outer join:

Code:
select * from
(select sts , sum( case when RDATE= CURRENT DATE then 1 else 0 end) as cnt from taba group by sts) a
full outer join
(selec sts , sum( case when IDATE= CURRENT DATE then 1 else 0 end)  as cnt from tabb group by sts) b
on a.sts = b.sts;
Back to top
View user's profile Send private message
Hariharan Sukumar

New User


Joined: 14 Mar 2011
Posts: 14
Location: India

PostPosted: Thu Jun 16, 2011 3:01 pm
Reply with quote

Thanks yaar,

I got the exact result as i expected...

I used full outer join and got the thing ...

SELECT * FROM
(SELECT STS AS R_STS,COUNT(*) AS R_CNT
FROM TAB_R
WHERE R_DATE = CURRENT DATE
GROUP BY STS)R
FULL OUTER JOIN
(SELECT STS AS I_STS,COUNT(*) AS I_CNT
FROM TAB_I
WHERE I_DATE = CURRENT DATE
GROUP BY STS)I
ON R.R_STS = I.I_STS
WITH UR;

OUTPUT:
-----------
R_STS R_CNT I_STS I_CNT
---- ----------- ---- -----------
AWST 1 - -
CANC 1 CANC 1
- - RECV 6
- - REJD 16
SETT 108739 SETT 108625

THANKS FOR TELLING THE KEYWORD FULL OUTER JOIN ..NOW I'M CLEAR ....
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts How to load to DB2 with column level ... DB2 6
No new posts RC query -Time column CA Products 3
No new posts Calling an Open C library function in... CICS 1
No new posts DATE2 function SYNCSORT 15
No new posts Join multiple records using splice DFSORT/ICETOOL 5
Search our Forums:

Back to Top