RPGLE SQL join with count

[carolyngalloway]

I want to join these two queries to be just one query and can never seem to get the correct results. Query1 is for a file that has multiple records for ELER and ELDIVISON and I want the count. Query2 is a file that contains the description for the ELER, ELDIVISON so one record per ELER, ELDIVISON. I still want just the final list to be from Query1 with the list (I dont want every record from FILEB if there are no records in FILEA.

Any Help Appreciated

Query1:
Select ELER,         
       ELDIVISON,    
       count(*)      
From  FILEA
Where ELTYPE = 'I'   
Group by ELER,       
         ELDIVISON   
Order by ELER,       
         ELDIVISON; 

Query2:

Select CDDESC                
  from FILEB    
 where CDER   = :ELER and   
       CDTYPE = 'DIV' and    
       CDFIVE = :ELDIVISON; 

[MichaelCatalani]

Re: RPGLE SQL join with count

If I understand fully what you are trying to do (and I maybe dont )

select Eler, ElDivision, count(*) from FILEA
 join FILEB  
on ( FILEA.Eler, FILEA.ElDivision ) =                
   ( FILEB.Cder, FILEB.CDFIVE ) 
where FILEB.Cder = :Eler AND 
      FILEB.CdType = 'DIV' AND 
      FILEB.Cdfive = :ElDivision
group by Eler, ElDivision 
order by Eler, ElDivision

[B.Hauser]

Re: RPGLE SQL join with count

You may also specify your SQL statements either as CTE (Common Table Expressions) or Sub-Selects and Join the Results:

With x as (Select ELER,  ELDIVISON, count(*) Counter     
               From  FILEA
               Where ELTYPE = 'I'   
               Group by ELER, ELDIVISON)
Select ELER, ELDIVISION, CDDESC, Counter
    From x Join Fileb  on     ELER       = CDER 
                          and ELDIVISION = CDFIVE 
                          and CDTYPE     = 'DIV'
    Order By ELER, ELDIVISION;

or

Select ELER, ELDIVISION
   From (Select ELER, ELDIVISION, CDDESC, Count(*) Counter
            From FileA
            Where ELTYPE = 'I'
            Group By ELER, ELDIVISION) x
          join FileB   on    ELER       = CDER 
                         and ELDIVISION = CDFIVE 
                         and CDTYPE     = 'DIV'
Order By ELER, ELDIVISION

Birgitta

[carolyngalloway]

Re: RPGLE SQL join with count

Thanks for the quick reply!!! But it still not quite what I am trying to accomplish

What I need returned is FILEA.Eler, FILEA.ElDivision, FILEA.count(*) and FILEB.CDDESC

The examples give me the same result that I get from Query1. That was why I have the query2 to retrieve the description of the division - I was hoping that I could just get the description with the count in one query.

[B.Hauser]

Re: RPGLE SQL join with count

I changed my previous examples, so CDDESC is returned, too.

Birgitta

[carolyngalloway]

Re: RPGLE SQL join with count

I tried something similar to that but even with your changes I get "Column ____ not in specified tables." returned - SQL0206

First query example I get
Column ELDIVISION not in specified tables.

2nd query example I get
Column CDDESC not in specified tables

Would it help if I sent some example data for the files?

[carolyngalloway]

Re: RPGLE SQL join with count

Here is data and what I would expect returned:

ELER ELDIVISION ELTYPE
AAA 111 I
AAA 111 I
AAA 222 E
AAA 222 I
BBB 111 I
BBB 444 I
BBB 444 I
CCC 333 E
DDD 111 I


CDER CDFIVE CDTYPE CDDESC
AAA 111 DIV Company A Division 1
AAA 222 DIV Company A Division 2
BBB 111 DIV Company B Division 1
BBB 444 DIV Company B Division 4
CCC 333 DIV Company C Division 3
DDD 111 DIV Company D Division 1


Results

ELER ELDIVISION Count CDDES
AAA 111 2 Company A Division 1
AAA 222 1 Company A Division 2
BBB 111 1 Company B Division 1
BBB 444 2 Company B Division 4
DDD 111 1 Company D Division 1

[kausix777]

Re: RPGLE SQL join with count

Not sure about the first query error but the 2nd one may be something like this:

Select ELER, ELDIVISION, CDDESC
   From (Select ELER, ELDIVISION, Count(*) Counter
            From FileA
            Where ELTYPE = 'I'
            Group By ELER, ELDIVISION) x
          
    join FileB   on    ELER       = CDER 
                         and ELDIVISION = CDFIVE 
                         and CDTYPE     = 'DIV'
Order By ELER, ELDIVISION

The reason is that CDDESC is from File B.

[carolyngalloway]

Re: RPGLE SQL join with count

THANKS!!! I added counter to the first select and it works perfectly.

Select ELER, ELDIVISION, CDDESC, Counter
From (Select ELER, ELDIVISION, Count(*) Counter
From FileA
Where ELTYPE = 'I'
Group By ELER, ELDIVISION) x
join FileB on ELER = CDER 
and ELDIVISION = CDFIVE 
and CDTYPE = 'DIV'
Order By ELER, ELDIVISION