Solved

Oracle Subquery bad Join

Posted on 2016-11-07
11
65 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 76

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 76

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 76

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 76

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 76

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

831 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