7.3.4 Select Statement

[gandolf989]

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' ) )
/

[marist89]

What's the problem? Are you getting an error message?

[gandolf989]

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.

[JChiappa]

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