Select data where keyfield not in select statement failure

Question

Post reply

Select data where keyfield not in select statement failure

fireball

SSC Enthusiast

Points: 112
More actions
April 14, 2016 at 3:56 am

#325640

This is a really basic query .. which seems to be failing
I have two tables - SalesDeliverynote (SDN) and SalesInvoice (SI)
SalesInvoice is generally created from SalesDeliverynote - where that happens the SI field "DELIVERYNOTE" is populated with the SDN PK field
ID and DELIVERYNOTE are both bigint
the query
select id, number
from SALESDELIVERYNOTE
where id not in (select DELIVERYNOTE from SALESINVOICE)
I know I've got a salesdeliverynote ID that is not in salesinvoice yet it's not being returned in the above query.
Is there an issue with BIGINT's and not in select queries?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Do the missing rows from SALESINVOICE have NULL DELIVERYNOTE, by any chance?

John

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 2

You said that DeliveryNote is poulated from SDN's ID...

So, there are no records in SDN which are not in SI.

Use the following query for your test,

select SDN.id, SDN.number ,SI.DELIVERYNOTE

from SALESDELIVERYNOTE SDN

LEFT JOIN SALESINVOICE SI on SI.DELIVERYNOTE =SDN.ID

Or

Select ID from SALESDELIVERYNOTE SDN

EXCEPT

select DELIVERYNOTE from SALESINVOICE

fireball SSC Enthusiast Points: 112 More actions · Answer 3

there are some null deliverynote entries in SI - where an invoice has been raised directly. that shouldn't be the case here ..

I've got a deliverynote that says it's been invoiced, but I can't find the invoice!

fireball SSC Enthusiast Points: 112 More actions · Answer 4

ta - I added where SI.Deliverynote is null to that top query and I've got some records back.

I would expect to get at least a few as we're constantly raising deliverynotes but invoices are generally only run once per day - so that's good.

What's also good (sort of!) is the record that I can't find the invoice for is in the results.

Edit: The Select / Except query returned the same results - so that works too... who knows why the "not in" doesn't though ...

said it was simple - just so simple I couldn't think around it ...

Thank you!

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

OK, please provide table DDL in the form of CREATE TABLE statements, sample data in the form of INSERT statements, and expected results. We should be able to help you after that.

Thanks

John

fireball SSC Enthusiast Points: 112 More actions · Answer 6

thanks John - it's already been resolved ... ta 🙂