null comparison

Question

Post reply

null comparison

JanM-66

Ten Centuries

Points: 1247
More actions
July 29, 2007 at 4:11 am

#372987

Hi
I have two table to join to find the same entries. Some equal rows does habe null values, they are not found by an outer join.
Tried also to set ansi null but it doesn't help. It worked only when null is set to empty.
Is there a way to get the records by comparing null values?
Thanks for help
Jan

Viewing 14 posts - 1 through 13 (of 13 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I need some sample data and the required output to be sure to understand what you want.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

In the mean time you can check out this fact

where null = null doesn't work

you need something like where Col1 IS NULL AND Col2 IS NULL

JanM-66 Ten Centuries Points: 1247 More actions · Answer 3

Two tables dbo.flightinfo and #duplicaterow with the same columns to eliminate duplicate rows.

The query looks like:

select b.company, b.airline_nr, b.awb, b.part_shipment, b.nr_1, b.from_destcode, b.to_destcode, b.mode, b.flight_1, b.flight_2, b.start_date, b.end_date, b.end_destcode, b.statuscode, b.pieces, b.nr_2, b.weight

from dbo.flightinfo full outer join #duplicaterow as b

on dbo.flightinfo.company = b.company

where dbo.flightinfo.airline_nr = b.airline_nr

and dbo.flightinfo.awb = b.awb

and dbo.flightinfo.part_shipment = b.part_shipment

and dbo.flightinfo.nr_1 = b.nr_1

and dbo.flightinfo.from_destcode = b.from_destcode

and dbo.flightinfo.to_destcode = b.to_destcode

and dbo.flightinfo.mode = b.mode

and dbo.flightinfo.flight_1 = b.flight_1

and dbo.flightinfo.flight_2 = b.flight_2

and dbo.flightinfo.start_date = b.start_date

--and dbo.flightinfo.end_date = b.end_date

and dbo.flightinfo.end_destcode = b.end_destcode

and dbo.flightinfo.statuscode = b.statuscode

and dbo.flightinfo.pieces = b.pieces

and dbo.flightinfo.nr_2 = b.nr_2

and dbo.flightinfo.weight = b.weight

and dbo.flightinfo.awb = 50255483

With the result:

AMS France 757 50255483 1 30000 LBV POG Flight SMJ 554 30.01.07 NULL POG DEP 5 1.000 306

As you can see the end_date is null. So if this column is not commented out in the query there are no rows comming back.

dbo.flightinfo.end_date = b.end_date, both are null. Is it that what not works?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

That's certainly one reason.

I've never actually tried this but I think it can work. Thanks for letting me know.

You can do this to correct the problem :

(

dbo.flightinfo.end_date = b.end_date

OR

dbo.flightinfo.end_date IS NULL AND b.end_date IS NULL

)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Also there are easier ways to clean out data.

If you want, we can check for another solution, but we need the table definition (with keys) and what determines the row to keep (identity, date, somethign else)...

JanM-66 Ten Centuries Points: 1247 More actions · Answer 6

Your solution works.

The table does not have keys because of the origin data, not a good thing but I can't change it.

So first I group the data having count(*) > 1 an insert that in the temp table.

Second I use a delete query with the joint above and third I insert the rows back from the temptable into the

dbo.flightinfo table.

May you know a better way?

Thanks, Jan

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

You can always add an identity column and use that as uniqueidentifier to delete the tuples, then drop the column.

Create perm table (exact match)

insert ValidData into newtable

drop old table

rename new table

rebuild index/constraints

Sergiy SSC Guru Points: 110208 More actions · Answer 8

I use it everywhere and it works perfect.

Even better than I expected from it.

The only thing: there is an error in your code:

dbo.flightinfo.end_date = b.end_date

OR

(

dbo.flightinfo.end_date IS NULL AND b.end_date IS NULL

)

_____________
Code for TallyGenerator

sairam-257016 SSC Journeyman Points: 95 More actions · Answer 9

You can also use ISNULL check for the datecolumns :

ISNULL(dbo.flightinfo.end_date,'') = ISNULL(b.end_date,'')

thereby avoiding an additional condition as

OR

(

dbo.flightinfo.end_date IS NULL AND b.end_date IS NULL

)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Ninja's_RGR'us

SSC Guru

Points: 294069

July 30, 2007 at 1:18 pm

#723398

Thanks for the assist .

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

So you want to cast a date to a string to do comparaisons (useless work)!!!

Or even worse, case the date to 1900/01/01 which is a valid data, which can return false results!!

Sergyi, fell free to assist on this one... I don't have any access to a server right now and I'm not 100% of what I just said.

Sergiy SSC Guru Points: 110208 More actions · Answer 12

Don't want even comment ISNULL approach.

To avoid accusations in harsh wording...

- False results;

- poor performance;

...

_____________
Code for TallyGenerator

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Come on... make a Joe Celko out of you .

Thanks for the quick feed back.