7.3.4 Select Statement
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: 7.3.4 Select Statement

  1. #1
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968

    Question

    This query works in 8i, however we need to rewrite it to work in 7.3.4. We are in the process of converting our many databases to 8.1.7 but we need to get this to work in the mean time. Does anyone have any suggestions on how to make this work with 7.3.4. Any help would be appreciated.
    Thanks in advace

    SELECT TB_CASE.CASEID,
    TB_CASE.PRODUCT,
    TB_CUST.CUSTNAME,
    TB_CASE.REASON,
    TB_CASE.PRIORITY,
    TB_CASE.DUEDATE,
    TB_CASE.SYSSTATUS,
    TB_CASE.ASSIGNUSER,
    TB_CASE.ACCOUNT,
    TB_CASE.ENTRYDATETIME,
    ( SELECT Count(caseid) FROM tb_activity
    WHERE tb_activity.caseid = tb_case.caseid
    AND ( sysstatus = 'O' OR sysstatus = 'N'
    ) ) activity_count
    FROM TB_CASE,
    TB_CUST
    WHERE ( tb_case.bank = tb_cust.bank (+)) and
    ( tb_case.cust = tb_cust.cust (+)) and
    ( ( TB_CASE.SYSSTATUS = 'O' ) OR
    ( TB_CASE.SYSSTATUS = 'N' ) )
    /

    this space intentionally left blank

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    What's the problem? Are you getting an error message?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    When I run this query on a 7.3.4 database I get the following results:
    1 SELECT "TB_CASE"."CASEID",
    2 "TB_CASE"."PRODUCT",
    3 "TB_CUST"."CUSTNAME",
    4 "TB_CASE"."REASON",
    5 "TB_CASE"."PRIORITY",
    6 "TB_CASE"."DUEDATE",
    7 "TB_CASE"."SYSSTATUS",
    8 "TB_CASE"."ASSIGNUSER",
    9 "TB_CASE"."ACCOUNT",
    10 "TB_CASE"."ENTRYDATETIME",
    11 ( SELECT Count(caseid) FROM tb_activity
    12 WHERE tb_activity.caseid = tb_case.caseid
    13 AND ( sysstatus = 'O' OR sysstatus = 'N' ) ) "activity_count"
    14 FROM "TB_CASE",
    15 "TB_CUST"
    16 WHERE ( tb_case.bank = tb_cust.bank (+)) and
    17 ( tb_case.cust = tb_cust.cust (+)) and
    18 ( ( "TB_CASE"."SYSSTATUS" = 'O' ) OR
    19* ( "TB_CASE"."SYSSTATUS" = 'N' ) )
    BSG1.WORLD> /
    ( SELECT Count(caseid) FROM tb_activity
    *
    ERROR at line 11:
    ORA-00936: missing expression

    I works in both 8i and 9i. But it does not work in 7.3.4. So I need to get it working for 7.3.4 until we are able to upgrade our 7.3.4 databases. We have at least 30 databases running this product on 7.3.4. The query is using an enhancement that is valid in 8i or above.

  4. #4
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    Yes, queries acting like SELECT columns are not allowed in 7.3.4, you will need to encapsulate the ( SELECT COUNT ....) in a function, and call this function in the main SELECT.
    Check the 7.3.4 PL/SQL manual for more details.

    []s

    Chiappa

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