?
Solved

Oracle Subquery bad Join

Posted on 2016-11-07
11
Medium Priority
?
88 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
[X]
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
  • 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
 
LVL 1

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
Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

 
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
 
LVL 1

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
 
LVL 1

Author Comment

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

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 2000 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
 
LVL 1

Author Comment

by:JDCam
ID: 41878737
Thank you very much
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

765 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