Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    4

    Question Unanswered: ORA-00936: missing expression

    Howdy all,
    I can get the below sql to run in sybase and ms-sql server, but I cannot get it to run in oracle. I get the ORA-00936: missing expression error. Any ideas?

    SELECT PH."project_key",
    PH."fed_aid_num_key",
    PH."proj_status_key",
    PH."division_key",
    PH."project_title",
    PH."region_key",
    PH."record_date",
    PH."owner_id" ,
    (select back_color from lookup_table
    where record_no = PH.division_key)
    FROM "project_header" PH ;

    Thanks,
    ..............Gary

  2. #2
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Use of the double quotes makes column names case sensitive:

    SQL> select DuMmy from dual;

    DUM
    ---
    X


    SQL> select "DUMMY" from dual;

    DUM
    ---
    X

    SQL> select "dummy" from dual;
    select "dummy" from dual
    *
    ERROR at line 1:
    ORA-00904: invalid column name

    Try getting rid of them.

    HTH
    Al

  3. #3
    Join Date
    Nov 2003
    Posts
    4

    Question

    Alberto, I appreciate the input, but when I removed the quotes it made no difference. However, when I removed or commented out the select statement within the select statement (see below), it worked, but without the data in the internal select. In Sybase and MS-SQL Server you can embed select statements in select statements. Is this not a valid statement in Oracle? Again, thanks!
    .............Gary

    SELECT PH.project_key,
    PH.fed_aid_num_key,
    PH.proj_status_key,
    PH.division_key,
    PH.project_title,
    PH.region_key,
    PH.record_date,
    PH.owner_id
    /*(select back_color from lookup_table
    where record_no = PH.division_key)*/
    FROM project_header PH ;

    Originally posted by alberto.dellera
    Use of the double quotes makes column names case sensitive:

    SQL> select DuMmy from dual;

    DUM
    ---
    X


    SQL> select "DUMMY" from dual;

    DUM
    ---
    X

    SQL> select "dummy" from dual;
    select "dummy" from dual
    *
    ERROR at line 1:
    ORA-00904: invalid column name

    Try getting rid of them.

    HTH
    Al

  4. #4
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by proptions
    Alberto, I appreciate the input, but when I removed the quotes it made no difference. However, when I removed or commented out the select statement within the select statement (see below), it worked, but without the data in the internal select. In Sybase and MS-SQL Server you can embed select statements in select statements. Is this not a valid statement in Oracle? Again, thanks!
    .............Gary

    SELECT PH.project_key,
    PH.fed_aid_num_key,
    PH.proj_status_key,
    PH.division_key,
    PH.project_title,
    PH.region_key,
    PH.record_date,
    PH.owner_id
    /*(select back_color from lookup_table
    where record_no = PH.division_key)*/
    FROM project_header PH ;
    Yes, it is a valid construct from at least 8i (which version are you using?).

    E.g. in 8.1.7.4:

    SQL> select dummy, (select count(*) from user_tables) from dual;

    D (SELECTCOUNT(*)FROMUSER_TABLES)
    - -------------------------------
    X 54

    Does the subquery work in isolation ?
    Al

  5. #5
    Join Date
    Nov 2003
    Posts
    4
    Al,
    I am using Oracle 8.0.5 running on a Win 2000 Server. Yes, the subquery does work. Again, Thanks,
    .......Gary

    Originally posted by alberto.dellera
    Yes, it is a valid construct from at least 8i (which version are you using?).

    E.g. in 8.1.7.4:

    SQL> select dummy, (select count(*) from user_tables) from dual;

    D (SELECTCOUNT(*)FROMUSER_TABLES)
    - -------------------------------
    X 54

    Does the subquery work in isolation ?
    Al

  6. #6
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by proptions
    Al,
    I am using Oracle 8.0.5 running on a Win 2000 Server. Yes, the subquery does work. Again, Thanks,
    .......Gary
    I've checked, they are valid since 8.1.5 onwards.

    What about upgrading ? 8i is much more powerful (and still supported). Better again, 9i.

    But, you could use an outer join in 8.0:

    SELECT PH.project_key,
    PH.fed_aid_num_key,
    PH.proj_status_key,
    PH.division_key,
    PH.project_title,
    PH.region_key,
    PH.record_date,
    PH.owner_id,
    lt.back_color
    FROM project_header PH, lookup_table lt
    where ph.division_key = lt.record_no(+);

    Should do the trick (not tested it, correct any typos).
    Al

  7. #7
    Join Date
    Nov 2003
    Posts
    4
    Al,
    Thanks. I tried some joins and they didn't work for some reason. This code is for a commercial application and I need to make sure that it will work on older versions of Oracle just in case my client/customers have not upgraded - as well as MS-SQL Server, DB@, and Sybase. I beleive I have a way around this. The application is built in Powerbuilder and I can build some functions to call from within my datawindow to replace the need for a nested select. Again, thanks for all your help.
    ........Gary
    Originally posted by alberto.dellera
    I've checked, they are valid since 8.1.5 onwards.

    What about upgrading ? 8i is much more powerful (and still supported). Better again, 9i.

    But, you could use an outer join in 8.0:

    SELECT PH.project_key,
    PH.fed_aid_num_key,
    PH.proj_status_key,
    PH.division_key,
    PH.project_title,
    PH.region_key,
    PH.record_date,
    PH.owner_id,
    lt.back_color
    FROM project_header PH, lookup_table lt
    where ph.division_key = lt.record_no(+);

    Should do the trick (not tested it, correct any typos).
    Al

Posting Permissions

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