Re: Help with SQL statement

Hi,

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

Mit freunlichen Grüßen / Best regards

Birgitta Hauser

"If you think education is expensive, try ignorance." (Derek Bok)

To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
Sent: Tuesday, December 05, 2006 21:03
Subject: 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)

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.