Jump to content

IsNull Function


Recommended Posts

So I have an SQL SELECT query where some of the values are null. Here is a sample of one field: ISNULL(S.TOTAL_RELE,0) AS TOTAL_SMALL_RELEASEPretty simple... What I want is instead of replacing a null by 0, I want to replace with a -. I tried ISNULL(S.TOTAL_RELE,'-')... But I get an error (Error converting data type varchar to float.)Complete query (ColdFusion environment):

SELECT C.CAS_NUMBER, C.CHEM_F AS SUBSTANCE, S.LESS_1_TON AS LESS_1_TON, ISNULL(S.TOTAL_RELE,0)AS TOTAL_SMALL_RELEASE, ISNULL(S.TOTAL_RELE,0) AS TOTAL_RELEASE, ISNULL(D.Total_ON_S,0)AS TOTAL_ON_SITE_DISPOSAL, (ISNULL(d.total_disp,0) - ISNULL(d.total_on_s,0))AS TOTAL_OFF_SITE_DISPOSAL, ISNULL(D.TOTAL_DISP,0) AS TOTAL_DISPOSAL, ISNULL(D.TOTAL_RECY,0)AS TOTAL_RECYCLE, S.UNITS_STOR AS UNITS, W.LINK_F AS LINKFROM QM3_SubsRele S INNER JOIN QM3_CHEMcode C ON S.CAS_NUMBER = C.CAS_NUMBER AND S.REPORTYEAR = C.REPORTYEAR INNER JOIN QM3_SubsDisp D ON C.CAS_NUMBER = D.CAS_NUMBER AND S.NPRI_ID = D.NPRI_ID AND S.REPORTYEAR = D.REPORTYEAR INNER JOINQS3_Chem_Web WON C.CAS_NUMBER = W.CAS_NUMBERWHERE (C.CAC<>'Y' OR C.CAC IS NULL) AND (S.NPRI_ID = '#opt_npri_id#')AND (S.REPORTYEAR = '#opt_report_year#') AND C.NPRI ='Y' ORDER BY UNITS DESC, SUBSTANCE ASC

Link to post
Share on other sites

Just found out a problem related to this topic. In one page there is a data table with the following column: Total Disposal, Total On Site Disposal and Total Off Site Disposal. Only Total Off Site Disposal is not a field in the DB, it is calculated from the 2 others like so (ISNULL(d.total_disp,-1) - ISNULL(d.total_on_s,-1)) AS TOTAL_OFF_SITE_DISPOSAL. The problem with the previous calculation is the -1 if null, so basically it's something like this when null: -1 - -1 = 0 but it's really null (-) not 0. Is there a way I could have the - showing when both are null and when only 1 is it should not be incremented or decremented by 1.

Link to post
Share on other sites

I'm assuming you're using SQL Server by the way nulls are handled. T-SQL contains an if statement, you might need to add some logic to the query and test values before you calculate fields. There's a reference here:http://manuals.sybase.com/onlinebooks/grou.../54345;pt=54430Also, this sounds like the perfect candidate for being in a stored procedure. If you haven't looked into SPs yet, this might be a good opportunity.

Link to post
Share on other sites

So hmmm... how would I integrate an IF statement in my huge query? I just need to verify if d.total_disp and d.total_on_s is null. If they are both they shouldn't be added and TOTAL_OFF_SITE_DISPOSAL should be -1. If d.total_on_s is null then it should simply be ignored in the calculation and TOTAL_OFF_SITE_DISPOSAL should be equal to d.total_disp.

Link to post
Share on other sites

This is the only example I have of using logic in SQL Server, and this is in a stored procedure.

CREATE PROCEDURE [dbo].[usp_ugroup_report](  @cid_list nvarchar(500),  @comp varchar(6), -- all | comp | incomp  @range varchar(4), -- all | date  @datestart smalldatetime,  @dateend smalldatetime,  @type varchar(4) -- all | comp)ASCREATE TABLE #ugroup_table1(  ugid nvarchar(10) NOT NULL,  uid nvarchar(50) NOT NULL,  ugadmin varchar(6))CREATE TABLE #ugroup_table2(  id int IDENTITY PRIMARY KEY,  ugid nvarchar(10),  uid nvarchar(50),  ugadmin varchar(6),  fname nvarchar(100),  lname nvarchar(100),  total_score int,  completed int,  counter int,  last_date_taken smalldatetime,  cid nvarchar(8),  title nvarchar(100))SET NOCOUNT ONINSERT INTO #ugroup_table1 SELECT ugid, uid, 'Y' AS ugadmin FROM usergroup_adminsINSERT INTO #ugroup_table2 SELECT r.ugid, r.uid, r.ugadmin, u.fname, u.lname, s.total_score, s.completed, s.counter, s.last_date_taken, s.cid, null FROM #ugroup_table1 AS r INNER JOIN users AS u ON r.uid=u.uid LEFT OUTER JOIN content_session AS s ON s.uid=r.uid JOIN dbo.udf_SplitIDs(@cid_list) AS i ON s.cid = i.cidINSERT INTO #ugroup_table2 SELECT ug.ugid, ug.uid, ' ' AS ugadmin, u.fname, u.lname, s.total_score, s.completed, s.counter, s.last_date_taken, s.cid, null FROM usergroup_users AS ug INNER JOIN users AS u ON ug.uid=u.uid AND ug.uid NOT IN (SELECT uid FROM #ugroup_table1) RIGHT OUTER JOIN content_session AS s ON s.uid=ug.uid AND s.completed=1 JOIN dbo.udf_SplitIDs(@cid_list) AS i ON s.cid = i.cid WHERE ug.uid NOT IN (SELECT uid FROM #ugroup_table1)IF @comp = 'comp'  BEGIN  DELETE FROM #ugroup_table2 WHERE ISNULL(completed,0) = 0  ENDELSE  BEGIN  INSERT INTO #ugroup_table2 SELECT ugid, null, null, null, null, null, null, null, null, null, null FROM usergroups WHERE ugid NOT IN (SELECT ugid FROM #ugroup_table2)  IF @comp = 'incomp'	BEGIN	DELETE FROM #ugroup_table2 WHERE completed = 1	END  ENDIF @range = 'date'  BEGIN  DELETE FROM #ugroup_table2 WHERE (last_date_taken NOT BETWEEN @datestart AND @dateend) OR (ISNULL(last_date_taken, CAST('1980-01-01 00:00' AS smalldatetime)) = CAST('1980-01-01 00:00' AS smalldatetime))  ENDIF @type = 'comp'  BEGIN  SELECT ugid, cid, COUNT(uid) AS nr_users FROM #ugroup_table2 WHERE completed=1 GROUP BY ugid, cid ORDER BY ugid, cid  ENDELSE  BEGIN  SELECT * FROM #ugroup_table2 ORDER BY ugid, uid, cid  ENDGO

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...