morning

Morning

Morning

Morning

buenos dias

ƃuᴉuɹoɯ

@PeterVandivier I would expect Paul to say that but not you

i've been working in NoSQL lately, my whole world is upside down ¯\_(ツ)_/¯

Moo

む

@PeterVandivier Could be worse. Could be working on old legacy AS/400 systems.

I might be a little salty. The system updates a premium figure in-situ, but has another table with a list of adjustments. Fortunately it has an initial premium entry in the amendments table - except when it doesn't. I <3 crusty old legacy systems.

The legacy ETL tries to interpret a stream of consciousness from the amendments and then takes the updated value. There are 11 sprocs in the ETL and 6 consumers in the ETL that populated the DDS.

My poor little brain hurts.

technical bits start at section 4

@PeterVandivier That's fascinating.

Anyone?

One any

Is it due to the collation?

Does TABLE2 actually have a code column?

yes, and SELECT code FROM TABLE2 WHERE <something> returns about 20 codes

and to confirm SELECT F1,F2,F3 FROM TABLE WHERE code NOT IN (SELECT code FROM TABLE2 WHERE <something>) yields nothing?

it returns all rows from TABLE

Or more appropriately, SELECT DISTINCT code FROM TABLE has at least one value that isn't generated in the sub select?

The NOT IN option returns all rows as well?

@AndriyM nothing returned

SQL-Server 2014

SP3-CU4

And you're certain there is at least one code in table that isn't in the list of 20?

All right, but just to be on the safe side, can you also try it like this: SELECT F1,F2,F2 FROM TABLE WHERE code IN (SELECT TABLE2.code FROM TABLE2 WHERE <something>)? To make sure the subquery really takes the column from TABLE2

And what Bill said

We're both tilting at the same conclusion. Team high five

@billinkc Strictly you'd need AND code IS NOT NULL in the subquery (aliased ideally)

Pipe down you'll get none of my chicken

SELECT code FROM dbo.table INTERSECT SELECT code FROM dbo.table2;
SELECT code FROM dbo.table EXCEPT SELECT code FROM dbo.table2;

@AndriyM bang, I've added an alias and now works fine

Always use aliases

@PaulWhitesaysGoFundMonica 👍

SQL Prompt has been invaluable for this rule (alias everything) alone

@PaulWhitesaysGoFundMonica Yes, Monica might agree with that too

@billinkc Agreed

This issue comes up a lot e.g.

Schema prefixes as well

@PaulWhitesaysGoFundMonica ... unless you really need to not use them ;)

@ypercubeᵀᴹ is there such a case?

there might be, but I can't think of it

oh yeah

bountied that one and still didn't remember it

@PaulWhitesaysGoFundMonica what would make you remember an answer?...if it had a lot of unrelated comments that weren't deleted?

heh maybe