asked on

MS SQL returns 1 row on no match found with null values

I have the following query, which returns 1 row with null segpax, because there is no matching data.

SELECT sum(segpax) segpax
FROM jismit_TCNdetail
WHERE
(issdate between '20080801' AND '20080831') AND
(fltdate between '20080801' AND '20090831') AND
(oddst = 'xxx') AND
(dmtseg = 'Y') ;

However this returns 0 row, which is correct (no sum);

SELECT segpax
FROM jismit_TCNdetail
WHERE
(issdate between '20080801' AND '20080831') AND
(fltdate between '20080801' AND '20090831') AND
(oddst = 'xxx') AND
(dmtseg = 'Y') ;

It seems if I am using sum I need group by, in which case I get 0 rows as well. If I have no values to group by and criteria matches, all is welll. Criteria does not match and no group by I get 1 null row....

Thanks

HainKurt

nice, but what is your question?

sammySeltzer

You really don't have to use group by based on what you showed above but you can if you want to and I don't think that's the reason you get null values.

My question is did you check to ensure there are actually values associated with segpax?

Also, can you post just one value of issdate?

I am assuming issdate is of datetime datatype no?

sammySeltzer

Nice catch there HainKurt!

HainKurt

put a group by

group by (case id when null then 0 else 1 end)

where id is a non null primary key of the table, it has no effect on result, just to make group by works

CPOINT2000

ASKER

HainKurt, question is based on what is happening between when there is matching data in DB and when there isn't why do I get what I get. As I explained if I have matching data in DB, 1 row is returned as it should. If there is no data found for the query in DB, 1 row returned with NULL under SEGPAX column. It did not find anything, yet result query has 1 row with values NULL. However if I display one of the fields and add it to GROUP BY line, then it will come back with 0 rows returned, as it should. Not sure how else I can explain.

I can force a group by if I have to, but I am trying to understand why the 1 row is returned with NULL when there is no match.

Thanks

CPOINT2000

ASKER

Sorry guys, question was assumed :)

CPOINT2000

ASKER

HainKurt, putting a group by resolves it, I know that. I was trying to understand is Group By required? In grand scheme of things, user will select various display items, based on this summmation fields will calculate and my logic will put Group By. So normally this works. I came accross this problem when my Tester happen to run a query without any display items (non computed fields), which I did not take into consideration at design time and did not build Group By. Maybe I need to, or force them to select at least one non-computed field...

CPOINT2000

ASKER

Sammy, yes data is all there, there is no issue with the data in DB. The issue is when you select a query that does not have matching data to it and it is being summed as null looks like.

Lowfatspread

it is the way aggregate functions work,,,

if you don't want the null row returned then use a having clause , without a group by to resolve your issue,,,

SELECT sum(segpax) segpax
FROM jismit_TCNdetail
WHERE
(issdate between '20080801' AND '20080831') AND
(fltdate between '20080801' AND '20090831') AND
(oddst = 'xxx') AND
(dmtseg = 'Y')
having sum(segpax) is not null

sammySeltzer

i will look at the value of issdate and see if it is the same as any of the hardcoded date values.

CPOINT2000

ASKER

I am attaching a screen print. Top images (1), is where there is data not found in DB with and Group By makes a difference on the output.
Bottom image (2) is when the query has data in DB, Group by does not matter whether used or not.

I can use Having, but would rather not. Sounds like I will force the user to select a display item that will force the Group By (already designed this way).

As I said above, I am trying to find out why image 1 and image 2 works the way they do (difference is one has matching data, other does not).

CPOINT2000

ASKER

sorry forgot the image :)
sqlexample.jpg

sammySeltzer

I will suggest you try casting the hard coded date values.

SELECT  segpax
FROM jismit_TCNdetail 
WHERE 
(issdate between cast('20080801' as datetime) AND cast('20080831' as datetime) AND 
(fltdate between cast('20080801' as datetime) AND cast('20090831' as datetime) AND 
(oddst = 'xxx') AND 
(dmtseg = 'Y') ;

Open in new window

You may do same fore issdate

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CPOINT2000

ASKER

I do not think it is a date issue. If I do the following where (simplest level) 3 characters defined, I get same result. If matching data, 1 line display, if no matching data 1 line null display

SELECT sum(passenger) passenger
FROM DBTABLE
WHERE DEST= 'XXX'

I think I have to use group by, that's pretty much is the answer.

Thanks all for suggestions.