[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Oracle Subquery bad Join

Posted on 2016-11-07
11
Medium Priority
?
97 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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 configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

656 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