Solved

Oracle Subquery bad Join

Posted on 2016-11-07
11
40 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)
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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)
Comment Utility
>>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
Comment Utility
Yes, it is meant to be from the outside query.
How would I fully qualify this?
0
 

Author Comment

by:JDCam
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you very much
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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 recover a database from a user managed backup

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now