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;

Attention to details is lacking.

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;

But, again, an ORDER BY clause is pointless on a SELECT statement that returns 1 row!!

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

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.

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;

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;

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

DB2 won't just not return a row without an error code.

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;*/

Since you only get one row with Fetch, perhaps you're printing the wrong array entry.

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