Help with Left Join and Null values

Help with Left Join and Null values

Post by Anni » Fri, 06 Jun 2003 21:46:47



I've been trying to figure out why the following syntax returns an error and
I gave up!  Could someone please look at the following and tell me what I'm
doing wrong?  The goal is to get a 0 returned when the value in
q.TimesheetStatus is Null.

Thanks a lot.

Following is the syntax.  The part that is causing the error is:
(IIF([q.TimesheetStatus]) Is Null,0,([q.TimesheetStatus]))
SQL = "SELECT DISTINCT u.UserID, q.WeekEnding, q.TimesheetStatus,
(IIF([q.TimesheetStatus]) Is Null,0,([q.TimesheetStatus])) FROM Users as u
LEFT JOIN (SELECT UserID,WeekEnding,TimesheetStatus from Timedetails where
WeekEnding='"&WeekEnd&"') as q ON u.UserID = q.UserID ORDER BY
q.TimesheetStatus DESC, u.UserID "

And the error I'm getting is:
Microsoft JET Database Engine error '80040e14'
Syntax error in query expression '(IIF([q.TimesheetStatus]) Is
Null,0,([q.TimesheetStatus]))'.

 
 
 

Help with Left Join and Null values

Post by Anith Se » Fri, 06 Jun 2003 22:32:21


Re-write as:

SELECT DISTINCT u.UserID, q.WeekEnding, q.TimesheetStatus,
       COALESCE(q.TimesheetStatus, 0)
  FROM Users as u
  LEFT JOIN (SELECT UserID,WeekEnding,TimesheetStatus
               FROM Timedetails
              WHERE WeekEnding = '"&WeekEnd&"') AS q
    ON u.UserID = q.UserID
 ORDER BY q.TimesheetStatus DESC, u.UserID

--
- Anith
( Please reply to newsgroups only )

 
 
 

Help with Left Join and Null values

Post by Anni » Fri, 06 Jun 2003 23:16:00


I have tried it and I'm getting the following error:

error '80004005'
Unspecified error

I'm using Microsoft Access as the back-end database would that be why?

Thanks,


Quote:> Re-write as:

> SELECT DISTINCT u.UserID, q.WeekEnding, q.TimesheetStatus,
>        COALESCE(q.TimesheetStatus, 0)
>   FROM Users as u
>   LEFT JOIN (SELECT UserID,WeekEnding,TimesheetStatus
>                FROM Timedetails
>               WHERE WeekEnding = '"&WeekEnd&"') AS q
>     ON u.UserID = q.UserID
>  ORDER BY q.TimesheetStatus DESC, u.UserID

> --
> - Anith
> ( Please reply to newsgroups only )

 
 
 

Help with Left Join and Null values

Post by Anith Se » Fri, 06 Jun 2003 23:30:46


Is this SQL Server or MS Access? Which interface are you using to execute
the query?

--
- Anith
( Please reply to newsgroups only )

 
 
 

Help with Left Join and Null values

Post by Anni » Fri, 06 Jun 2003 23:53:25


It's MS Access and I'm using ASP to execute the query.
Thanks.


Quote:> Is this SQL Server or MS Access? Which interface are you using to execute
> the query?

> --
> - Anith
> ( Please reply to newsgroups only )

 
 
 

Help with Left Join and Null values

Post by Anith Se » Sat, 07 Jun 2003 00:35:27


I am not much familiar with Access, you may get a much better response, if
you post this in MS Access Newsgroup. My response was based on the
assumption that you are using SQL Server since you posted on this Newsgroup.

--
- Anith
( Please reply to newsgroups only )

 
 
 

1. Export Null values to Excel cause left shifted values in XLS

With DTS, I export a table with some Null values.

In the resulting XLS file, for a Null value, instead to have the cell blank,
the cell is filled by the value of the just right column ; and all the next
values of the row are shifted to the left.

How to make correspond a Null value with a blank cell ?

Jacques.

2. Problem with release

3. Left join sql Request (double mixing left join)

4. SELECT

5. left join with join on left.column = criteria

6. HELP ! - DBTEMP - ONLINE 7.12

7. LEFT JOIN and LEFT OUTER JOIN

8. 17296-LA-NEW ORLEANS-ORACLE-Windows NT-VMS-Client/Server-DBA Skills-DATABASE ADMINISTRATOR

9. LEFT OUTER LOOP JOIN and LEFT OUTER JOIN

10. Stored Procedure handles LEFT OUTER JOIN nulls differently than the query

11. Left Join and Aggregate functions on right with nulls

12. Sql Server 6.0 and probelms with left join and null