DB2 - Group by syntax

  • 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?


    Just beyond your imagination...

    Bajan_mo

  • 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

  • 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

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


    Just beyond your imagination...

    Bajan_mo

  • 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:

    TYPE   CCITEM  CCPDCL CCDESC
    ------ ------- ------ ------

    PHARMA PH00001 PHAR   desc1

    PHARMA AH00052 PHAR   desc2

    .

    .

    PHARMA SD15225 PHAR   descN

    DENK   DF52451 PHAR   descX

    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

  • 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