Stephen Forte's Weblog
A complete waste of time…

 



Subscribe to "Stephen Forte's Weblog" in Radio UserLand.

Click to see the XML version of this web page.

Click here to send an email to the editor of this weblog.

 

 

  Friday, June 13, 2003


New York, NY

Long Live TSQL

Why can I never remember the IsNull(foo,0) function in SQL Server. It is an ISNULL or NZ type of function that converts a null value to the item specified, which in this case is a zero. I don't know why I forget this code all the time is that I go back to my Access roots and try to do an NZ() or the Null to Zero function specific to the Access object model. (Old habits die hard!) Well I can usually IM my good friend Al for help, which I did today, but now here is an example of using IsNull in a TSQL string:

update tblSummaryByLocation_WebSublocation
set LocalNewsPaper=IsNUll(LocalNewsPaper1,0)+IsNUll(LocalNewsPaper2,0)+IsNUll(LocalNewsPaper3,0)
WHERE Weekending_ID= @Weekending_ID

 

I am also sick of using the CASE statement, this code requires me to know something about my data, can' t wait for the Yukon PIVOT keyword:

SELECT     dbo.tlkpWeekEnding.WeekEnding_ID, dbo.tlkpWeekEnding.WeekEnding_DT, dbo.tlkpLocation.Location_ID, dbo.tlkpLocation.Location_NM,
                      dbo.tblLocationSub_WebMapping.WebSublocation_ID,
                      dbo.tblLocationSub_WebMapping.WebSublocation_DS,                       SUM(TotalListings) AS GrandTotal, SUM(CASE tlkpServiceProviders.Service_NM WHEN 'Hot Jobs' THEN TotalListings ELSE 0 END) AS HotJobsTotal,
                      SUM(CASE tlkpServiceProviders.Service_NM WHEN 'Monster' THEN TotalListings ELSE 0 END) AS MonsterTotal,
                      SUM(CASE tlkpServiceProviders.Service_NM WHEN 'Career Builder' THEN TotalListings ELSE 0 END) AS CareerBuilderTotal

FROM         dbo.tblData INNER JOIN
                      dbo.trelServiceURL ON dbo.tblData.URL_ID = dbo.trelServiceURL.URL_ID INNER JOIN
                      dbo.tlkpLocation ON dbo.trelServiceURL.Location_ID = dbo.tlkpLocation.Location_ID INNER JOIN
                      dbo.tlkpServiceProviders ON dbo.trelServiceURL.Service_ID = dbo.tlkpServiceProviders.Service_ID INNER JOIN
                      dbo.tlkpWeekEnding ON dbo.tblData.WeekEnding_ID = dbo.tlkpWeekEnding.WeekEnding_ID INNER JOIN
                      dbo.tblLocationSub_WebMapping ON dbo.tlkpLocation.Location_ID = dbo.tblLocationSub_WebMapping.Location_ID INNER JOIN
                      dbo.tblLocationSub_WebMapping_Detail ON
                      dbo.tblLocationSub_WebMapping.WebSublocation_ID = dbo.tblLocationSub_WebMapping_Detail.WebSublocation_ID INNER JOIN
                      dbo.tblLocationSub ON dbo.trelServiceURL.SubLocation_ID = dbo.tblLocationSub.SubLocation_ID AND
                      dbo.tlkpLocation.Location_ID = dbo.tblLocationSub.Location_ID AND
                      dbo.tblLocationSub_WebMapping_Detail.Sublocation_ID = dbo.tblLocationSub.SubLocation_ID

Where tlkpWeekEnding.weekending_id= @Weekending_ID

GROUP BY dbo.tlkpWeekEnding.WeekEnding_ID, dbo.tlkpWeekEnding.WeekEnding_DT, dbo.tlkpLocation.Location_ID, dbo.tlkpLocation.Location_NM,
                      dbo.tblLocationSub_WebMapping.WebSublocation_ID, dbo.tblLocationSub_WebMapping.WebSublocation_DS, dbo.tblLocationSub.SubLocation_ID,
                      dbo.tblLocationSub.SubLocation_DS

 

 


3:25:20 PM    


Click here to visit the Radio UserLand website. © Copyright 2003 Stephen Forte.
Last update: 7/30/2003; 1:40:35 PM.

June 2003
Sun Mon Tue Wed Thu Fri Sat
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30          
May   Jul