Using column function while using JOIN

Hariharan Sukumar · New User Joined: 14 Mar 2011 Posts: 14 Location: India

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 ......

Nic Clouston · Posted: Tue Jun 14, 2011 1:12 pm

What error?

gylbharat · Posted: Tue Jun 14, 2011 1:17 pm

Use group by clause

vasanthz · Posted: Tue Jun 14, 2011 1:24 pm

Hariharan Sukumar · New User Joined: 14 Mar 2011 Posts: 14 Location: India

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 ...

GuyC · Posted: Tue Jun 14, 2011 1:28 pm

Hariharan Sukumar · New User Joined: 14 Mar 2011 Posts: 14 Location: India

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 .....

Hariharan Sukumar · New User Joined: 14 Mar 2011 Posts: 14 Location: India

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

GuyC · Posted: Tue Jun 14, 2011 2:33 pm

maybe because I typed selec instead of select

Hariharan Sukumar · New User Joined: 14 Mar 2011 Posts: 14 Location: India

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

Nic Clouston · Posted: Tue Jun 14, 2011 7:18 pm

So what output are you expecting? All those rows are distinct.

Hariharan Sukumar · New User Joined: 14 Mar 2011 Posts: 14 Location: India

I need the output as .....

---------+---------+---------+---------+-----
R_STS R_CNT I_STS I_CNT
---------+---------+---------+---------+-----
AWST 1 RECV 3
SETT 153498 REJD 36
- - SETT 153383

Hariharan Sukumar · New User Joined: 14 Mar 2011 Posts: 14 Location: India

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 .....

Hariharan Sukumar · New User Joined: 14 Mar 2011 Posts: 14 Location: India

Hi,

Pls provide me the solution .....

enrico-sorichetti · Posted: Wed Jun 15, 2011 3:03 pm

GuyC · Posted: Wed Jun 15, 2011 7:27 pm

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.

chandan.inst · Posted: Thu Jun 16, 2011 10:48 am

Hi,

Do you want to join these two tables as

Hariharan Sukumar · New User Joined: 14 Mar 2011 Posts: 14 Location: India

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 ....

Nic Clouston · Posted: Thu Jun 16, 2011 1:24 pm

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.

GuyC · Posted: Thu Jun 16, 2011 2:10 pm

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:

Hariharan Sukumar · New User Joined: 14 Mar 2011 Posts: 14 Location: India

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 ....