Home » SQL & PL/SQL » SQL & PL/SQL » outer query returning more than one row (MS SQL)
outer query returning more than one row [message #681882] Sat, 05 September 2020 07:54 Go to next message
kumarravik
Messages: 32
Registered: January 2016
Location: delhi
Member
There are four table joined together.
new requirement have this condition - if note column has any value 'great' then only display the data of this column else rest data will be displayed as usual and note column will have null. We have to join the ord_note table where an order can have multiple nottxt rows.

expectation

Boieng_code Order Note
727 8001 1234567
728 8002
729 8003 3453455


(select 'SHIPment' Boieng_code,

(select case when ord_note.nottxt like 'GREAT%' then SUBSTR(ord_note.nottxt, INSTR(ord_note.nottxt, 'GREAT') + 4) end

from ord_note
where ord_note.nottxt like 'GREAT%'
and ord_note.ordnum = shipment.ship_id
and shipment.carcod = 'DEALS') Note,..........



i have added this inner query which return exactly the record i want but when i run the whole query i receive multiple rows and that does not
satisfy the condition

while Below are coming up

Boieng_code Order Note
727 8001 1234567
727 8001 1234567
727 8001 1234567


Re: outer query returning more than one row [message #681885 is a reply to message #681882] Sat, 05 September 2020 10:13 Go to previous messageGo to next message
EdStevens
Messages: 1317
Registered: September 2013
Senior Member
Please help us to help you. Format your code and wrap it in 'code' tags. Provide script to create subject tables and populate them with representative data. Show the expected output and explain the logic.
Re: outer query returning more than one row [message #681886 is a reply to message #681882] Sat, 05 September 2020 10:24 Go to previous message
Michel Cadot
Messages: 67851
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Previous Topic: Foreign key
Next Topic: Sending email MS Word attachment containing Arabic letters
Goto Forum:
  


Current Time: Tue May 18 00:38:33 CDT 2021