Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Oracle Subquery bad Join

Posted on 2016-11-07
11
67 Views
Last Modified: 2016-11-08
Experts,
In the below query, I am trying to add a subquery (row 2-6)
My link is the column INVT_ACCESS which is null in some rows in the main query. Where it is not null there should be only a single record in the C_INVT table.
I have tried many variations, but keep getting the error "Single row subquery returns more than one row". I suspect I am not properly referencing INVT_ACCESS from the main query.

SELECT CUST_CODE, OP_CODE, JOB_TP_DES, DOC_NUM, DOC_LINE_NUM, LAB_UNIT, INVT_ACCESS,
(select INVT_QTY_BKD_FACT as IQBP
   FROM C_INVT
   WHERE C_INVT.Comp_Code = 'A1'
   AND C_INVT.Invt_access = INVT_ACCESS) IQBP2,
CASE
WHEN MINS < 0 THEN 1
ELSE MINS
END as MINS
FROM (
SELECT LAB.CUST_CODE,LAB.OP_CODE,JOB.JOB_TP_DES,LAB.DOC_NUM,
LAB.DOC_LINE_NUM,
LAB.LAB_UNIT,
CASE
WHEN LAB.JOB_TP_Code IN('PI','LO') THEN
   (SELECT INVT_ACCESS from E_ORD_D5
   Where comp_code = Lab.Comp_code
   and ord_num = Lab.doc_num
   and ord_line_num = Lab.DOC_LINE_NUM)
WHEN LAB.JOB_TP_Code IN('PU','UN') THEN
   (SELECT INVT_ACCESS from E_RCPT_D5
   Where comp_code = Lab.Comp_code
   and rcpt_num = Lab.doc_num
   and rcpt_line_num = Lab.DOC_LINE_NUM)
END as INVT_ACCESS,
LAB_START_DATE,
ROUND(((LEAD(LAB_START_DATE) OVER(order by OP_CODE,LAB_START_DATE)-LAB_START_DATE) *1440),2) MINS
FROM M_JOB_TP_H JOB,C_LAB LAB 
where lab.comp_code='A1' 
and lab.comp_code=job.comp_code 
and lab.job_tp_code=job.job_tp_code 
AND LAB.OP_CODE in ('KHANA','DEXTER')
AND (TRUNC(LAB_START_DATE)>trunc(SYSDATE -5))
AND (TRUNC(LAB_START_DATE)<trunc(SYSDATE-3))
order by LAB.OP_CODE,LAB_START_DATE
)

Open in new window


Any help would be appreciated.
0
Comment
Question by:JDCam
  • 5
  • 5
11 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41877100
Not sure it has anything to do with INVT_ACCESS.

My guess is one of the two selects in the case statement is returning more than one row.
0
 

Author Comment

by:JDCam
ID: 41877130
I added MAX(INVT_ACCESS) to the CASE statements, but made no difference. If I run just the lower query the returned data looks good.

I tried adding MAX(INVT_QTY_BKD_FACT) to the new subquery. in hopes of returning the higher where 2 or more values were returned.  Instead it returns every row with the same, which is the highest value in the table, and no regard for the join on INVT_ACCESS
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 41877136
One of these or may be both could be returning more than one record here....

CASE
WHEN LAB.JOB_TP_Code IN('PI','LO') THEN
   (SELECT INVT_ACCESS from E_ORD_D5
   Where comp_code = Lab.Comp_code
   and ord_num = Lab.doc_num
   and ord_line_num = Lab.DOC_LINE_NUM)
WHEN LAB.JOB_TP_Code IN('PU','UN') THEN
   (SELECT INVT_ACCESS from E_RCPT_D5
   Where comp_code = Lab.Comp_code
   and rcpt_num = Lab.doc_num
   and rcpt_line_num = Lab.DOC_LINE_NUM)
END as INVT_ACCESS,

Can you provide table creation script/insert statements with test data in order for me to setup and help. If you cannot provide this information, i would suggest you try to run your query by hardcoding input values for the above selects so that you can see whether it works fine or it gives an error. It is possible that for some input values, the query may work fine but the challenge is to find an input value with which you can get to the same error.

Once you have a clue about the issue and  if you know the data design/mapping well for those tables well --> then you can use max(..) or min(..) or distinct which even you think will make those select queries in the case statements to return only one value.

Thanks,
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41877145
Even with the MAX, one of them is returning more than one row.  you need to dig into the data to figure out which one.

I think these queries will help but I cannot test then since I don't have your data:
select INVT_ACCESS, count(INVT_ACCESS) from E_ORD_D5
group by INVT_ACCESS, comp_code, ord_num ord_line_num
having count(*) > 1;

SELECT INVT_ACCESS, count(INVT_ACCESS) from E_RCPT_D5
group by INVT_ACCESS, comp_code, rcpt_num
having count(*) > 1;

Open in new window

0
 

Author Comment

by:JDCam
ID: 41877252
I ran the two queries suggested above, nether returned any results >1
I then edited the Case statement to return COUNT(INVT_ACCESS). All rows are 1 or null except 1 single row with a 0
I further edited the query to remove all the rows with null or 0 leaving only 1's .... same error persists.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41877351
I missed this one:
select INVT_QTY_BKD_FACT as IQBP
   FROM C_INVT
   WHERE C_INVT.Comp_Code = 'A1'
   AND C_INVT.Invt_access = INVT_ACCESS


see if it has more than one row coming back.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41877357
>>AND C_INVT.Invt_access = INVT_ACCESS

Was the second INVT_ACCESS meant to be a string literal?

If it is supposed to be a column from the outside query, you need to fully qualify it.  Otherwise it is using the column from C_INVT which is bringing back all columns.
0
 

Author Comment

by:JDCam
ID: 41877397
Yes, it is meant to be from the outside query.
How would I fully qualify this?
0
 

Author Comment

by:JDCam
ID: 41877404
I got it working. I simply gave a new name INVT_CODE to the value in the CASE then referenced that new name in the sub select join....
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 41877417
To answer the first part on how to alias it, give the from an alias:


SELECT CUST_CODE, OP_CODE, JOB_TP_DES, DOC_NUM, DOC_LINE_NUM, LAB_UNIT, INVT_ACCESS,
(select INVT_QTY_BKD_FACT as IQBP
   FROM C_INVT
   WHERE C_INVT.Comp_Code = 'A1'
   AND C_INVT.Invt_access = frank.INVT_ACCESS) IQBP2,
CASE
WHEN MINS < 0 THEN 1
ELSE MINS
END as MINS
FROM (
SELECT LAB.CUST_CODE,LAB.OP_CODE,JOB.JOB_TP_DES,LAB.DOC_NUM,
LAB.DOC_LINE_NUM,
...the rest of your select
order by LAB.OP_CODE,LAB_START_DATE
) frank
0
 

Author Comment

by:JDCam
ID: 41878737
Thank you very much
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question