select Max and null value

[sql]

if my select statement have like
select ReceiveDate,InterviewDate, max(CompleteDate), max(ApprovalDate),
OfficerName,SecondOfficer, DATEDIFF( max(ApprovalDate), ReceiveDate) as diff from Pos as P, Status as A , Records as P2
where P.UserId=A.UserId and P.UserId=P2.userId
and ApprovalDate<>'Null'
and ReceiveDate>='2010-07-02' and ApprovalDate<='2010-09-02'
if not result on this range , I still get one row that contain Null value in the whole row?
Thank you
________
Marijuana strain green crack

[SDas]

sql, an Aggregate Function (i.e. Min, Max, Avg, Count) will always return at least 1 row. If there are no rows in the table(s) to satisfy the Where clause, you will get 1 row with a NULL (except for Count which will return 0).

If you can deal with this in your process, you can try making it a Nested / Derived table:

SELECT *
FROM (SELECT MAX(col-name) AS MAX_VALUE
      FROM table-name
      WHERE 1=0
     ) AS A
WHERE A.MAX_VALUE IS NOT NULL

[sql]

Thank you for the reply,
but why have double from , and what is the mean of 1=0 , I try

select
 from( max(CompleteDate) as MAX_VALUE
 from PaymentRecords
 where 1=0 )as A
WHERE A.MAX_VALUE IS NOT NULL

________
BMW R 1150 GS

[sql]

and I get error Error code 1064, SQL state 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from( max(CompleteDate) as MAX_VALUE
from PaymentRecords
where 1=0 )as A
WHERE' at line 2
Line 1, column 1
________
HEALTH STORE

[SDas]

You left a Select out of your copy of my SQL.

SELECT *
FROM (SELECT MAX(col-name) AS MAX_VALUE
      FROM table-name
      WHERE 1=0
     ) AS A
WHERE A.MAX_VALUE IS NOT NULL

select
 from( max(CompleteDate) as MAX_VALUE
 from PaymentRecords
 where 1=0 )as A
WHERE A.MAX_VALUE IS NOT NULL

As for WHERE 1=0, I was trying to show a situation where a row would NOT be returned (since 1 does NOT equal 0). You can replace the Where clause with your Where clause that does not return any row.

[sql]

I try your suggestion on one table , it did get away the null value , but I don't how to put the ideal on my code yet , let me think about it , may ask more question
Thankyou!
________
Mercedes-benz w215

[sql]

I still not ideal how to put your ideal into my code ,

may I ask can the Null value is cause by not row get select ,
if I did get one row or more then one row , I odn't need to worry the
null value !

there for can I have something like
select count(*) as found
from Pos as P, Status as A , Records as P2
where P.UserId=A.UserId and P.UserId=P2.userId
and ApprovalDate<>'Null'
and ReceiveDate>='2010-07-02' and ApprovalDate<='2010-09-02'
if found is not 0 run the code I post ?
________
Dx1

[sql]

just add
having count(1) >0
________
NSR500

[sql]

just add having count(1)>0 at the end of my select , then the Null row will not show!!
________
HAWAII MARIJUANA DISPENSARY