Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

nested query

Status
Not open for further replies.
PappaG

PappaG

Technical User
Nov 21, 2003
288
GB
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');
 
Sort by date Sort by votes
The areas I've marked in bold look a bit dodgy.

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');
 
Upvote 0 Downvote
i used to use access and i had a primary query that appended @NIVR to the D.NAME field and this field was used to link to O.OBJECT_ID. I had been told i can use a nested query to achive this so i have tried to combine the two...if you need any further info please let me know. I am using dbvis and the error i get is "keyword from not found where expected
 
Upvote 0 Downvote
Below is the access query i started with

primary query QryCFG_DN_Groups@NIVR

SELECT DISTINCT CONFIG02_CFG_GROUP.NAME, CONFIG02_CFG_DN.NAME & "@NIVR" AS GROUPOFQUEUE
FROM CONFIG02_CFG_GROUP INNER JOIN (CONFIG02_CFG_DN INNER JOIN CONFIG02_CFG_DN_GROUP ON CONFIG02_CFG_DN.DBID = CONFIG02_CFG_DN_GROUP.DN_DBID) ON CONFIG02_CFG_GROUP.DBID = CONFIG02_CFG_DN_GROUP.GROUP_DBID
WHERE (((CONFIG02_CFG_GROUP.NAME)="VCC_Groups_Auctions"));

secondary query QryCFGDatamartGroupsOfQueueDaily

SELECT [QryCFG_DN_Groups@NIVR].NAME, DATAMART01_T_QUEUE_DAY.DATE_YYYYMMDD, DATAMART01_V_QUEUE_DAY.AV_T_ANSWERED, DATAMART01_V_QUEUE_DAY.AV_T_ABANDONED, DATAMART01_V_QUEUE_DAY.AV_T_DISTRIBUTED, DATAMART01_V_QUEUE_DAY.MAX_T_ABANDONED, DATAMART01_V_QUEUE_DAY.MAX_T_ANSWERED, DATAMART01_V_QUEUE_DAY.PC_N_ABANDOVED, DATAMART01_V_QUEUE_DAY.PC_N_ANSWERED, DATAMART01_V_QUEUE_DAY.SERVICE_FACTOR, DATAMART01_V_QUEUE_DAY.N_ABANDONED, DATAMART01_V_QUEUE_DAY.N_ANSWERED, DATAMART01_V_QUEUE_DAY.N_DISTRIBUTED, DATAMART01_V_QUEUE_DAY.N_DISTRIB_IN_TR, DATAMART01_V_QUEUE_DAY.N_ENTERED, DATAMART01_V_QUEUE_DAY.N_ABANDONED_IN_TR, DATAMART01_V_QUEUE_DAY.T_ABANDONED, DATAMART01_V_QUEUE_DAY.T_ANSWERED, DATAMART01_V_QUEUE_DAY.T_DISTRIBUTED, DATAMART01_V_QUEUE_DAY.PC_N_DISTRIB
FROM [QryCFG_DN_Groups@NIVR] INNER JOIN (DATAMART01_T_QUEUE_DAY INNER JOIN (DATAMART01_V_QUEUE_DAY INNER JOIN DATAMART01_O_QUEUE_DAY ON DATAMART01_V_QUEUE_DAY.OBJECT_ID = DATAMART01_O_QUEUE_DAY.OBJECT_ID) ON DATAMART01_T_QUEUE_DAY.TIME_KEY = DATAMART01_V_QUEUE_DAY.TIME_KEY) ON [QryCFG_DN_Groups@NIVR].GROUPOFQUEUE = DATAMART01_O_QUEUE_DAY.OBJECT_NAME;

 
Upvote 0 Downvote
The problem isn't with CONCAT. It's the bracket after "VQNIVR".
 
Upvote 0 Downvote
Still struggling, error is missing right peranthesis

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
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');
 
Upvote 0 Downvote
As I said, this bit looks dodgy:

INNER JOIN O.OBJECT_NAME ON VQNIVR

O.OBJECT_NAME isn't a table.
 
Upvote 0 Downvote
Also, this bit:

CONFIG02.CFG_GROUP G
CONFIG02.CFG_DN D

It looks like two successive table names. I would expect them to be separated by a join statement.
 
Upvote 0 Downvote
I applied a bit of formatting to make the whole thing legible:
Code: 
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');
Access has a (nasty)habit of re-ordering ON clauses within nested parentheses. I hate this, as soon as you try to tidy things up by removing some of these parens, it all goes to pot (which is where Dagon's last comment comes from, the join is there, it's just in the wrong place. (there are several instances of this problem.
Initially, have a look at your joins.
Try to alter them to follow this structure:
Code: 
FROM tableA
JOIN tableB
ON join_expression
JOIN tableC
ON join_expression
AND join_expression
JOIN tableD
ON join_expression
Also, as Dagon said, you have an issue with an extra bracket.
However, O is a table, it is the alias of DATAMART01.O_QUEUE_DAY in the outer query, making this a correlated subquery Now, depending on the size of the subquery, you may find that an EXISTS or an INLINE VIEW may be far better options for this situation (but that's another story and we can look at that a bit later, once you get this query working)
 
Upvote 0 Downvote
Edit for the above, of course, this is 8i and I don't think the EXISTS operator is available yet :(. Looks like the inline view might be the way to go (depending on circumstances)
 
Upvote 0 Downvote
Thanks jimirvine / dagon still can't get this to run but i will plug away at it with your comments in mind. I have about six queries that all follow the same structure so looks like i have a fun couple of days ahead
 
Upvote 0 Downvote
Good luck PappaG. Really focus on the joins, it is by removing the parentheses from the access equivalent that is causing you the problems. (That and working with a really old version of Oracle :)
 
Upvote 0 Downvote
PS, remeMber to come back and post what you got (solution or otherwise)
 
Upvote 0 Downvote
Hi All

Got it in the end...first prob was VQNIVR in my select changed to O.OBJECT_NAME. Removed INNER JOIN O.OBJECT_NAME
ON VQNIVR and changed a couple of bracket positions. The below is my now working query....thanks guys

SELECT
O.OBJECT_NAME, 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
WHERE T.DATE_YYYYMMDD = '20080124'
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
WHERE G.NAME = 'T_FLY');
 
Upvote 0 Downvote
Hi pappaG
Unfortunately, the code that you have posted would result in a syntax error
Code: 
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
Is simply not valid.
Recheck my previous post which shows you the easy way:
Code: 
FROM tableA
JOIN tableB
ON join_expression
JOIN tableC
ON join_expression
AND join_expression
JOIN tableD
ON join_expression
Which should change your code to:
Code: 
FROM          DATAMART01.T_QUEUE_DAY T
INNER JOIN    DATAMART01.V_QUEUE_DAY V
ON T.TIME_KEY = V.TIME_KEY
INNER JOIN    DATAMART01.O_QUEUE_DAY O
ON V.OBJECT_ID = O.OBJECT_ID
You'll need to do something similar with the inner query as well before that code will do anything but error out on you.
 
Upvote 0 Downvote
Hi Jimirvine what are the issue's that it would cause. I have already ran the query i posted and get the results i would expect without any error?
 
Upvote 0 Downvote
Definatly 8i we did intend to upgrade this but due to other application compatibility we stuck with 8i
 
Upvote 0 Downvote
That's strange, because as far as I'm aware, ANSI join syntax was not available until v9i. I must be wrong (not a rare occassion)
 
Upvote 0 Downvote
This is 8i and I don't think the EXISTS operator is available yet :(. Looks like the inline view might be the way to go
Click to expand...
Coming late to this, but the EXISTS operator has been available for ages - a least as far back as Oracle 5, probably further still.

Its inline views that are the more recent introduction, arriving in either 8 or 8i if I remember correctly.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

ssmith999
  • Locked
  • Question
Query help w/ fiscal calendar query
Replies
1
Views
138
johnherman
johnherman
rk68
  • Locked
  • Question
auto execute procedure/query on record insert
Replies
13
Views
184
rk68
rk68
Ken
  • Locked
  • Question
How to make SQL sub query work in Oracle, getting error ?
Replies
4
Views
285
Beilstwh
Beilstwh
AlexTardif
  • Locked
  • Question
Endless query??
Replies
3
Views
169
Beilstwh
Beilstwh
vhalle
  • Locked
  • Question
query
Replies
3
Views
159
johnherman
johnherman

Part and Inventory Search

Sponsor

Back
Top