Re: Help with SQL statement

But QRAREA and QRBANK are not char fields and I am getting the following 
when I try to run this...


 Argument *N of function CONCAT not valid.
 
 
 
 
Thanks,
 
 
Jay Vaughn
iSeries Senior Programmer/Analyst
RateWatch
jayv@xxxxxxxxxxxxxx
251-633-8929


-----Original Message-----
From: "HauserBirgitta" <Hauser@xxxxxxxxxxxxxxx>
To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
Date: Tue, 5 Dec 2006 20:55:04 +0100
Subject: Re: Help with SQL statement


Hi Jay,

you have to concatenate the fields in the Count(Distinct ...)

SELECT  COUNT(DISTINCT QRSTCD concat QRAREA concat QRBANK) 
   FROM REPTDTL JOIN REPTINST ON     QRSTCD=RISTCD 
                                 AND QRAREA=RIAREA 
                                 AND QRBANK=RIBANK  
                                 AND QRBRPT=RIREPT 
   WHERE     QRRSTA = 'A' AND QRSAMP <> 'Y' 
         AND RIRSTA = 'A' AND RIRINS = 'BUSIC0K'
  
Mit freunlichen Grüßen / Best regards 

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les 
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) 




----- Original Message ----- 
From: "Jay Vaughn" <jayv@xxxxxxxxxxxxxx>
To: <rpg400-l@xxxxxxxxxxxx>
Sent: Tuesday, December 05, 2006 20:45
Subject: Help with SQL statement

I am getting the following error on the following sql statement.  I want to 

simply retrieve a number of records "grouped by qrstcd,qrarea,qrbank)...

What am I missing..?
---------------------

Number of arguments for function COUNT not valid.

SELECT COUNT(DISTINCT QRSTCD,QRAREA,QRBANK) FROM REPTDTL            
JOIN REPTINST ON QRSTCD=RISTCD AND QRAREA=RIAREA AND QRBANK=RIBANK  
AND QRBRPT=RIREPT WHERE QRRSTA = 'A' AND QRSAMP <> 'Y' AND RIRSTA = 
'A' AND RIRINS = 'BUSIC0K'                                          





Thanks,


Jay Vaughn
iSeries Senior Programmer/Analyst
RateWatch
jayv@xxxxxxxxxxxxxx
251-633-8929
-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.