how to use concat and group by at the same time?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: how to use concat and group by at the same time?

Hybrid View

  1. #1
    Join Date
    May 2003
    Posts
    18

    how to use concat and group by at the same time?

    Hi everybody --

    Sorry this is such a basic question, but how can I GROUP BY the product of a CONCAT(), as in:

    SELECT CONCAT(colnum1,colnum2)
    FROM table
    GROUP BY [the concatted thing]

    I tried to name the product, but that didn't help. Does anyone have a clue? Thanks!

  2. #2
    Join Date
    Jan 2001
    Location
    San Diego, CA
    Posts
    81
    Why , this one doesn't work?

    SELECT colnum1||colnum2
    FROM table
    GROUP BY colnum1||colnum2


    Eugene Roytman
    OCP

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I'm surprised you didn't try:

    SELECT CONCAT(colnum1,colnum2)
    FROM table
    GROUP BY CONCAT(colnum1,colnum2)


  4. #4
    Join Date
    May 2003
    Posts
    18
    Eugene -- You were right. That was it!
    DaPi -- That also works!

    Thanks for all your help. I just don't know my SQL that well. Have a good day, and cheers!

  5. #5
    Join Date
    May 2003
    Posts
    18

    Talking

    Hi everybody --

    The problem just got a little more interesting.

    We finally settled on this for the SQL:

    SELECT
    TO_CHAR(numcol)||charcol as newcol
    FROM
    TABLE
    GROUP BY
    TO_CHAR(numcol)||charcol
    ORDER BY
    newcol;

    The statement returns the expected value (ex: 99999G) when run from the db.

    We then embedded this statement into a C program, and though it compiled and ran, the results were bad (-412628).

    Someone suggested that our C preprocessor -- the thing that translates the SQL into the appropriate C code -- was older than the Oracle version we are using, and the solution was to rewrite the SQL so that the preprocessor could handle it. The DBAs were not able to help, and suggested that we turn to someone who knows Pro*C.

    Does anyone here have a clue? Any help would be greatly appreciated. Thanks!

  6. #6
    Join Date
    Jul 2002
    Location
    Slovenia
    Posts
    42
    What if you make view and then from c just write select * from that view
    Andrej

  7. #7
    Join Date
    May 2003
    Posts
    18
    I get the same results with that view. The problem is that somewhere between the translation of the SQL to the C, soemthing is getting mangled. Thanks, though!

  8. #8
    Join Date
    May 2003
    Posts
    18
    Nevermind -- we got it! It was misformatted when it ran though our C function. I'll never do that again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width