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
 

 

using column function while using join
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: using column function while using join
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: 1715
Location: UK

PostPosted: Tue Jun 14, 2011 1:12 pm    Post subject:
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    Post subject:
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: 1445
Location: Azeroth

PostPosted: Tue Jun 14, 2011 1:24 pm    Post subject:
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    Post subject: Reply to: using column function while using join
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: 1278
Location: Belgium

PostPosted: Tue Jun 14, 2011 1:28 pm    Post subject:
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    Post subject:
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    Post subject:
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: 1278
Location: Belgium

PostPosted: Tue Jun 14, 2011 2:33 pm    Post subject:
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    Post subject:
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: 1715
Location: UK

PostPosted: Tue Jun 14, 2011 7:18 pm    Post subject:
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    Post subject: Reply to: using column function while using join
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    Post subject:
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    Post subject:
Reply with quote

Hi,

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

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Wed Jun 15, 2011 3:03 pm    Post subject: Reply to: using column function while using join
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: 1278
Location: Belgium

PostPosted: Wed Jun 15, 2011 7:27 pm    Post subject:
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: 269
Location: Mumbai

PostPosted: Thu Jun 16, 2011 10:48 am    Post subject:
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    Post subject: Reply to: using column function while using join
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: 1715
Location: UK

PostPosted: Thu Jun 16, 2011 1:24 pm    Post subject:
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: 1278
Location: Belgium

PostPosted: Thu Jun 16, 2011 2:10 pm    Post subject:
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
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts How can we have the varying lenth of ... Gunapala CN DFSORT/ICETOOL 6 Fri Oct 14, 2016 7:31 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am


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