June 21, 2006 at 9:51 am
Hi,
My company recently added an AS400 database and I've been trying to use SQL for my reports etc. The problem is that apparently DB2 uses slightly different syntax than 2k5. I am trying to write a query that will display a category of items and group them by vendor all in one report (I don't need any summaries) just want then grouped. However when ever I add the group by statement my script bombs.
<script>
select CCITEM, CCPDCL, CCDESC, CCISRC
from ICITEMP
where CCPDCL <> 'PHAR' and CCITYP = '0' and CCSTTS <> 'I'
and CCITEM in (select IBITEM from ICITMBP where IBWHSI <> '7')
group by CCISRC /*this is where the problem is*/
order by CCITEM
</script>
Is there a way to get these items grouped by CCISRC in DB2?
June 21, 2006 at 12:04 pm
In SQL Server you need to have all the SELECT columns in the GROUP BY.
ie. GROUP BY CCISRC, CCITEM, CCPDCL, CCDESC
For DB2, you should post the question in a DB2 forum - this site is for SQL Server and you might not find a good solution here.
-SQLBill
June 21, 2006 at 6:53 pm
But in any form of SQL, if you have a group by clause, any column in the select list must either be enclosed in an aggregate function, or must also appear in the group by clause.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 22, 2006 at 8:20 am
Ok Cool, That being so how would I script to get my results to appear like:
PHARMA
CCITEM CCPDCL CCDESC
PH00001 PHAR desc1
AH00052 PHAR desc2
.
.
SD15225 PHAR descN
----------------------
DENK
CCITEM CCPDCL CCDESC
DF52451 PHAR descX
GH00001 PHAR descY
.
.
LP99999 PHAR descZ
-----------------------
.
.
.
etc
Where PHARMA and DENK are the values in the column I grouped by and then under each heading the records for that particular group are listed.
June 22, 2006 at 8:33 am
You want to present the results in a format QA isn't really equipped to deliver. You could do it, perhaps using a t ble valued function and some dynamic sql - but don't!
If you need to presnt you results in this format, you should (yes, ideally!) use a presentation layer: a report, hierarchical grid control, web page presenting XML data. whatever.
In SQL, you should just return a flat recordset, like:
PHARMA PH00001 PHAR desc1
PHARMA AH00052 PHAR desc2
.
.
PHARMA SD15225 PHAR descN
DENK GH00001 PHAR descY
.
.
DENK LP99999 PHAR descZ
.
.
I realise this isn't a complete answer. You basically need to join the tables, and you probably don't even need a group by clause. If you need more info, supply your table DDL.
cheers
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 26, 2006 at 2:20 am
But why are you using any group by clause if you do not have any aggregat (SUM, COUNT, AVG...) function?
And why are you using the embedded select statement where a simple join between ICITEMP and ICITMBP would be more efficient?
Bye
Gabor
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply