nested query

[PappaG]

hi all only just getting to grasps with basic queries and below is my fist attempt at a nested query. No supprise to me its not working can anyone see where i have went wrong

SELECT VQNIVR, T.DATE_YYYYMMDD, V.AV_T_ANSWERED, V.AV_T_ABANDONED, V.AV_T_DISTRIBUTED, V.MAX_T_ABANDONED, V.MAX_T_ANSWERED, V.PC_N_ABANDOVED, V.PC_N_ANSWERED, V.SERVICE_FACTOR, V.N_ABANDONED, V.N_ANSWERED, V.N_DISTRIBUTED, V.N_DISTRIB_IN_TR, V.N_ENTERED, V.N_ABANDONED_IN_TR, V.T_ABANDONED, V.T_ANSWERED, V.T_DISTRIBUTED, V.PC_N_DISTRIB
FROM DATAMART01.T_QUEUE_DAY T
INNER JOIN DATAMART01.V_QUEUE_DAY V
INNER JOIN DATAMART01.O_QUEUE_DAY O ON V.OBJECT_ID = O.OBJECT_ID
ON T.TIME_KEY = V.TIME_KEY AND O.OBJECT_NAME IN(
SELECT DISTINCT CONCAT (D.NAME,'@NIVR') AS VQNIVR)
FROM (CONFIG02.CFG_GROUP G INNER JOIN
(CONFIG02.CFG_DN D INNER JOIN CONFIG02.CFG_DN_GROUP DG ON
D.DBID = DG.DN_DBID) ON
G.DBID = DG.GROUP_DBID) INNER JOIN O.OBJECT_NAME ON VQNIVR
WHERE G.NAME = 'T_FLY' AND T.DATE_YYYYMMDD = '20080122');

 

[jimirvine]

Thanks for the correction Chris (don't know why I had that in my head)

 

[karluk]

jimirvine is right that this is an 8i compatibility issue, but it's not the lack of availability of the "exists" operator. Rather, it's that the "inner join" syntax wasn't supported in 8i. Here is a simple inner join query executed first in 8.1.7 and then in 9.2.0.8.

  1  select a.dummy from dual a
  2  inner join dual b
  3* on a.dummy = b.dummy
SQL> /
inner join dual b
*
ERROR at line 2:
ORA-00933: SQL command not properly ended

  1  select a.dummy from dual a
  2  inner join dual b
  3* on a.dummy = b.dummy
SQL> /

D
-
X

As you can see the exact same query works in Oracle 9.2.0.8 but results in a ORA-00933 error in Oracle 8.1.7. This means that you will have to rewrite your query to use the old inner join syntax. For example, the following query works in both 8.1.7 and 9.2.0.8.

  1  select a.dummy from dual a, dual b
  2* where a.dummy = b.dummy
SQL> /

D
-
X

 

[jimirvine]

Thanks for that too karluk (Not from Glasgow are you?) I was fairly sure that ANSI join syntax wasn't available in 8, but I don't have an 8db anymore to test on.