Embedded SQL Order By

This is a discussion on Embedded SQL Order By within the C Programming forums, part of the General Programming Boards category; I am getting compiling errors when I add the order by to the existing code. Does anyone know why I ...

  1. #1
    Registered User
    Join Date
    Apr 2008
    Posts
    115

    Embedded SQL Order By

    I am getting compiling errors when I add the order by to the existing code. Does anyone know why I am getting this error and how to order by cc column? I get this error no matter where I put the Order By statement. I am trying to avoid using qsort function and hope I can order by within the sql code. Thanks.

    Error message:
    "project.c", line 56.10: 1506-045 (S) Undeclared identifier ORDER.

    Code:
    EXEC SQL SELECT cc, cc_sn, cc_desc, cc_grp
             INTO :cc_lkup2[y].cc, :cc_lkup2[y].cc_sn, :cc_lkup2[y].cc_desc, :cc_lkup2  [y].cc_grp
             FROM cc_lkup;
             EXEC SQL BEGIN;
             ++y;
             ORDER BY cc;
             EXEC SQL END;

  2. #2
    Registered User
    Join Date
    Apr 2008
    Posts
    115

    FORGOT TO REMOVE ; AFTER FROM cc_lkup

    Attention to details is lacking.

  3. #3
    Jack of many languages Dino's Avatar
    Join Date
    Nov 2007
    Location
    Katy, Texas
    Posts
    2,309
    Code:
    EXEC SQL SELECT cc, cc_sn, cc_desc, cc_grp
             INTO :cc_lkup2[y].cc, :cc_lkup2[y].cc_sn, :cc_lkup2[y].cc_desc, :cc_lkup2  [y].cc_grp
             FROM cc_lkup
             ORDER BY cc;
    
             EXEC SQL BEGIN;
             ++y;
             EXEC SQL END;
    Mac and Windows cross platform programmer. Ruby lover.

    Quote of the Day
    12/20: Mario F.:I never was, am not, and never will be, one to shut up in the face of something I think is fundamentally wrong.

    Amen brother!

  4. #4
    Jack of many languages Dino's Avatar
    Join Date
    Nov 2007
    Location
    Katy, Texas
    Posts
    2,309
    But, again, an ORDER BY clause is pointless on a SELECT statement that returns 1 row!!
    Mac and Windows cross platform programmer. Ruby lover.

    Quote of the Day
    12/20: Mario F.:I never was, am not, and never will be, one to shut up in the face of something I think is fundamentally wrong.

    Amen brother!

  5. #5
    Registered User
    Join Date
    Apr 2008
    Posts
    115

    I am returning all rows in the table

    My code is storing all records of the table into the array of structs. I can re post code if you would like. It is working great right now but I still have a lot more to do. Thanks for your feedback.

    -Carl

  6. #6
    Jack of many languages Dino's Avatar
    Join Date
    Nov 2007
    Location
    Katy, Texas
    Posts
    2,309
    Well then, I learned something then. SELECT INTO on DB2 on z/OS only returns a single row, and if more than 1 row qualifies for the result, you get an error.

    Apparently, on your platform with DB2, this is not the case.

    Best of luck - glad you got it working.
    Mac and Windows cross platform programmer. Ruby lover.

    Quote of the Day
    12/20: Mario F.:I never was, am not, and never will be, one to shut up in the face of something I think is fundamentally wrong.

    Amen brother!

  7. #7
    Registered User
    Join Date
    Apr 2008
    Posts
    115

    Any advice changing this to CURSOR?

    I used the first set of code below to place all the records from table cc_lkup into my array of structs. Now I want to accomplish the same thing but use a cursor. I tried the second set of code below but get a syntax error 'y'. Any suggestions? Thanks.

    Code:
     EXEC SQL SELECT cc, cc_sn, cc_desc, cc_grp
                INTO :cc_lkup2[y].cc, :cc_lkup2[y].cc_sn, :cc_lkup2[y].cc_desc, :cc_lkup2[y].cc_grp
                FROM cc_lkup
                WHERE cc_grp != ''
                ORDER BY cc;
                EXEC SQL BEGIN;
                ++y;
                EXEC SQL END;*/

    Code:
      EXEC SQL DECLARE c1 CURSOR for
    
        SELECT cc, cc_sn, cc_desc, cc_grp
        FROM cc_lkup
        WHERE cc_grp != ''
        ORDER BY cc;
    
        EXEC SQL OPEN c1;
    
        EXEC SQL FETCH c1
        INTO :cc_lkup2[y].cc, :cc_lkup2[y].cc_sn, :cc_lkup2[y].cc_desc, :cc_lkup2[y].cc_grp
        y = y + 1;
    
        EXEC SQL CLOSE c1;

  8. #8
    Jack of many languages Dino's Avatar
    Join Date
    Nov 2007
    Location
    Katy, Texas
    Posts
    2,309
    My hat of guessing, borrowing from member tabstop, says
    Code:
        EXEC SQL FETCH c1
        INTO :cc_lkup2[y].cc, :cc_lkup2[y].cc_sn, :cc_lkup2[y].cc_desc, :cc_lkup2[y].cc_grp ;
        y = y + 1;
    Mac and Windows cross platform programmer. Ruby lover.

    Quote of the Day
    12/20: Mario F.:I never was, am not, and never will be, one to shut up in the face of something I think is fundamentally wrong.

    Amen brother!

  9. #9
    Registered User
    Join Date
    Apr 2008
    Posts
    115

    Thanks for pointing that out - Still a problem

    That helped. Thanks. It now compiles with no errors but there are no results in my array. I think I may need a loop or something inside the fetch command. This stuff is over my head but I gotta figure it out. Think of anything let me know. Thanks again.

    -Carl

  10. #10
    Jack of many languages Dino's Avatar
    Join Date
    Nov 2007
    Location
    Katy, Texas
    Posts
    2,309
    DB2 won't just not return a row without an error code.
    Mac and Windows cross platform programmer. Ruby lover.

    Quote of the Day
    12/20: Mario F.:I never was, am not, and never will be, one to shut up in the face of something I think is fundamentally wrong.

    Amen brother!

  11. #11
    Registered User
    Join Date
    Apr 2008
    Posts
    115

    Well...

    When doing printf on my array with this code I get no results:
    Code:
     EXEC SQL DECLARE c1 CURSOR for
    
        SELECT cc, cc_sn, cc_desc, cc_grp
        FROM cc_lkup
        WHERE cc_grp != ''
        ORDER BY cc;
    
        EXEC SQL OPEN c1;
    
        EXEC SQL FETCH c1
        INTO :cc_lkup2[y].cc, :cc_lkup2[y].cc_sn, :cc_lkup2[y].cc_desc, :cc_lkup2[y].cc_grp;
        y = y + 1;
    
        EXEC SQL CLOSE c1;
    But with this code I get all the records displayed: - Hell I give up till tomorrow
    Code:
     EXEC SQL SELECT cc, cc_sn, cc_desc, cc_grp
                INTO :cc_lkup2[y].cc, :cc_lkup2[y].cc_sn, :cc_lkup2[y].cc_desc, :cc_lkup2[y].cc_grp
                FROM cc_lkup
                WHERE cc_grp != ''
                ORDER BY cc;
                EXEC SQL BEGIN;
                ++y;
                EXEC SQL END;*/

  12. #12
    Jack of many languages Dino's Avatar
    Join Date
    Nov 2007
    Location
    Katy, Texas
    Posts
    2,309
    Since you only get one row with Fetch, perhaps you're printing the wrong array entry.
    Mac and Windows cross platform programmer. Ruby lover.

    Quote of the Day
    12/20: Mario F.:I never was, am not, and never will be, one to shut up in the face of something I think is fundamentally wrong.

    Amen brother!

  13. #13
    Registered User
    Join Date
    Apr 2008
    Posts
    115

    Here is the for loop I use to print the results without the formating

    Code:
       for( x = 1; x < row_count; x++ ) /* set x =1 due to first record cc_lkup2[0] = NULL*/
            {
            printf("%-12d %-10s %-40s %s\n",  cc_lkup2[x].cc,  cc_lkup2[x].cc_sn,  cc_lkup2[x].cc_desc,  cc_lkup2[x].cc_grp);
            }
    I am using begin... end to loop thru first set of code and it works... I probably have to figure out a way to loop thru the cursor in a similar manner..

    Thanks,
    Carl

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Help uderstanding a function with embedded SQL
    By cjohnman in forum C Programming
    Replies: 0
    Last Post: 04-29-2008, 02:13 PM
  2. Please help create a loop for embedded SQL
    By cjohnman in forum C Programming
    Replies: 4
    Last Post: 04-24-2008, 07:46 AM
  3. Embedded SQL
    By sarac in forum C Programming
    Replies: 1
    Last Post: 05-04-2006, 10:09 AM
  4. Problem with embedded SQL in C/C++ (ECPG)
    By NeuralClone in forum C Programming
    Replies: 4
    Last Post: 10-21-2005, 06:16 PM
  5. Embedded SQL ... should be quick ...
    By MadGooseXP in forum C Programming
    Replies: 3
    Last Post: 11-05-2002, 08:34 PM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21