OraFAQ Forum: SQL & PL/SQL » outer query returning more than one row

Home » SQL & PL/SQL » SQL & PL/SQL » outer query returning more than one row (MS SQL)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

outer query returning more than one row [message #681882]

Sat, 05 September 2020 07:54

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

Report message to a moderator

Re: outer query returning more than one row [message #681885 is a reply to message #681882]

Sat, 05 September 2020 10:13

EdStevens
Messages: 1375
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.

Report message to a moderator

Re: outer query returning more than one row [message #681886 is a reply to message #681882]

Sat, 05 September 2020 10:24

Michel Cadot
Messages: 68418
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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Mar 25 16:29:46 CDT 2023