Avatar of shilpi2
shilpi2
 asked on

isnull

Hi Experts,

I am trying the following
Select
isnull((VRDStart.ValueDate), @Start_Date),
isnull((VRDEnd.ValueDate), @End_Date)       

The problem is that it does not return null . it just returns nothing something like space. So my select dosent work when it has nothing. How do i solve this.

Thanks
Microsoft SQL ServerMySQL ServerMicrosoft Access

Avatar of undefined
Last Comment
knightEknight

8/22/2022 - Mon
knightEknight

IsNull returns the second parameter if the first one is null.
If you want it to return null when the first param equals the second, use nullif:

select NULLIF( VRDStart.ValueDate,  @Start_Date)
Lee

Do you want a case statement then?


Select
case
    when VRDStart.ValueDate = ''= then, @Start_Date
end as VRDStart,
case
    when VRDEnd.ValueDate = '' then @End_Date
end as VRDEnd
from mytable
shilpi2

ASKER
i dont want to do that. I want to set VRDStart.ValueDate as @StartDate when VRDStart.ValueDate is empty.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Patrick Matthews

What data types are you using for those two ValueDate columns?

For example, if you are using a string type such as [n][var][char], if those are containing zero length strings then that is NOT the same thing as a null.
knightEknight

I'm not sure what you want ... do you WANT it to return null if VRDStart.ValueDate is null?  If so, then just remove the ISNULL altogether:

Select VRDStart.ValueDate, VRDEnd.ValueDate
Lee

Typo:

Select
case
    when VRDStart.ValueDate = '' then @Start_Date
end as VRDStart,
case
    when VRDEnd.ValueDate = '' then @End_Date
end as VRDEnd
from mytable
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
knightEknight

or do you want it to return @StartDate if the column is null or empty?  then do this:

select
   ISNULL( NULLIF(VRDStart.ValueDate,''), @Start_Date ),
   ISNULL( NULLIF(VRDEnd.ValueDate,''), @End_Date )
shilpi2

ASKER
Hi Experts,

do u know what is wrong with the following? It still returns empty start date and end date

CREATE TABLE #TEMP_All_Dates(StartDate DATETIME, EndDate DATETIME)
INSERT INTO #TEMP_All_Dates
Select
   ISNULL( NULLIF(VRDStart.ValueDate,''), @Start_Date ),
   ISNULL( NULLIF(VRDEnd.ValueDate,''), @End_Date )

--VRDStart.ValueDate AS StartDate,    
--VRDEnd.ValueDate AS EndDate
FROM

      (SELECT DISTINCT VRD.ValueDate
      FROM IDX_ValuationRiskDaily VRD
      WHERE
            VRD.ValueDate>=@Start_Date
      AND      VRD.VAlueDate< @End_Date
      AND      VRD.PortfolioID = @PortfolioID
      ) VRDStart,

      (SELECT DISTINCT VRD.ValueDate
      FROM IDX_ValuationRiskDaily VRD
      WHERE
            VRD.ValueDate>@Start_Date
      AND      VRD.VAlueDate<= @End_Date
      AND      VRD.PortfolioID = @PortfolioID
      ) VRDEnd
WHERE
      VRDEnd.ValueDate =      (SELECT MIN(VRD3.ValueDate)
                        FROM
                        IDX_ValuationRiskDaily VRD3
                        WHERE
                              VRD3.ValueDate>VRDStart.ValueDate
                        AND      VRD3.PortfolioID = @PortfolioID)
--ORDER by
--VRDStart.ValueDate
SELECT * from #TEMP_All_Dates
knightEknight

Where are  @Start_Date and @End_Date defined?  and where are their values being set?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
knightEknight

... before the insert into the #TEMP table, I would expect to see something like this:

  declare  @Start_Date datetime='2011-08-01',  @End_Date datetime='2011-08-31'

or, if this is in a stored procedure, then these two variables would be parameters to the procedure:

  exec myProc  @Start_Date='2011-08-01',  @End_Date datetime='2011-08-31'
knightEknight

or you might have something like this before the insert ...


  declare  @Start_Date datetime,  @End_Date datetime

  select  @Start_Date = '2011-08-01',  @End_Date = '2011-08-31'

shilpi2

ASKER
yup it is there.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
knightEknight

Can you show us please?

Also, you will not get a result if the query returns no rows ... does this count query return 0?


SELECT COUNT(*) as RecordCount FROM

      (SELECT DISTINCT VRD.ValueDate
      FROM IDX_ValuationRiskDaily VRD
      WHERE
            VRD.ValueDate>=@Start_Date
      AND      VRD.VAlueDate< @End_Date
      AND      VRD.PortfolioID = @PortfolioID
      ) VRDStart,

      (SELECT DISTINCT VRD.ValueDate
      FROM IDX_ValuationRiskDaily VRD
      WHERE
            VRD.ValueDate>@Start_Date
      AND      VRD.VAlueDate<= @End_Date
      AND      VRD.PortfolioID = @PortfolioID
      ) VRDEnd
WHERE
      VRDEnd.ValueDate =      (SELECT MIN(VRD3.ValueDate)
                        FROM
                        IDX_ValuationRiskDaily VRD3
                        WHERE
                              VRD3.ValueDate>VRDStart.ValueDate
                        AND      VRD3.PortfolioID = @PortfolioID)
shilpi2

ASKER

/*	
--SELECT * FROM IDX_Securities
--DELETE FROM IDX_ValuationRiskDaily
--SELECT * FROM IDX_ValuationRiskDaily
--SELECT * FROM IDX_Transaction
DECLARE @Start_Date DateTime
DECLARE @End_Date DateTime
DECLARE @PortfolioID INT
SET @Start_Date = '2011-08-08'
SET @End_Date  = '2011-08-09'
SET @PortfolioID = 1
IF OBJECT_ID('tempdb..#TEMP_All_Dates') IS NOT NULL 
	DROP TABLE  #TEMP_All_Dates
IF OBJECT_ID('tempdb..#Temp_All_Sec_Dates') IS NOT NULL 
	DROP TABLE  #Temp_All_Sec_Dates
IF OBJECT_ID('tempdb..#Temp_Valuation') IS NOT NULL 
	DROP TABLE  #Temp_Valuation
IF OBJECT_ID('tempdb..#Temp_Transaction') IS NOT NULL 
	DROP TABLE  #Temp_Transaction
--*/
--First Determine for this portfolio, what the appropriate start date and end dates are... 
DECLARE @RealStartDate DATETIME
DECLARE @RealEndDate DATETIME
SET @RealStartDate = (
	SELECT isnull(MIN(VRD.ValueDate), @Start_Date)
	FROM IDX_ValuationRiskDaily VRD 
	WHERE 
		VRD.ValueDate>= @Start_Date 
	AND 	VRD.ValueDate< @End_Date
	AND 	VRD.PortfolioID = @PortfolioID)


        SET @RealEndDate = (
	SELECT MAX(VRD.ValueDate) 
	FROM IDX_ValuationRiskDaily VRD 
	WHERE 
		VRD.ValueDate<= @End_Date 
	AND 	VRD.ValueDate> @Start_Date 
	AND 	VRD.PortfolioID = @PortfolioID)

--select @RealStartDate as 'Real Start Date' ,@RealEndDate as 'Real End Date'
--Next Summarize the Daily Valuations into sequential date pairs within the calculated start and end dates


CREATE TABLE #TEMP_All_Dates(StartDate DATETIME, EndDate DATETIME)
INSERT INTO #TEMP_All_Dates
Select
VRDStart.ValueDate AS StartDate,     
VRDEnd.ValueDate AS EndDate
FROM

	(SELECT DISTINCT VRD.ValueDate
	FROM IDX_ValuationRiskDaily VRD
	WHERE
		VRD.ValueDate>=@Start_Date
	AND	VRD.VAlueDate< @End_Date
	AND	VRD.PortfolioID = @PortfolioID
	) VRDStart,

	(SELECT DISTINCT VRD.ValueDate 
	FROM IDX_ValuationRiskDaily VRD
	WHERE
		VRD.ValueDate>@Start_Date
	AND	VRD.VAlueDate<= @End_Date
	AND	VRD.PortfolioID = @PortfolioID
	) VRDEnd
WHERE
	VRDEnd.ValueDate =	(SELECT MIN(VRD3.ValueDate)
				FROM
				IDX_ValuationRiskDaily VRD3
				WHERE
					VRD3.ValueDate>VRDStart.ValueDate
				AND	VRD3.PortfolioID = @PortfolioID)
--ORDER by
--VRDStart.ValueDate
if VRDStart.ValueDate = ''
begin
INSERT INTO #TEMP_All_Dates
end
SELECT * from #TEMP_All_Dates

Open in new window

knightEknight

okay - I have to ask ... it appears the declaration and setting of the two variables @Start_Date and @End_Date are commented out above (via lines 1 and 20), in which case I would not expect the query to work at all, but rather it would throw an error of some kind.  Are you getting an error, or are you just seeing empty columns for ValueDate ?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
shilpi2

ASKER
well what i do is select the text starting from declare line and ending till where i want and press f5.
ASKER CERTIFIED SOLUTION
knightEknight

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
knightEknight

... although I don't know why this would work any different than what you were doing with ISNULL( NULLIF(VRDStart.ValueDate,''), @Start_Date ), etc
knightEknight

oh, and if ValueDate can be a string, then you should still use nullif:

   ISNULL( nullif(VRD.ValueDate,''), @Start_Date ) as ValueDate
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
shilpi2

ASKER
Hi Knight,

This will not work either couz the output does not come as 'NULL' . PFA the screenshot.
image.bmp
knightEknight

right, if the query returns no rows then you will not get null or any other result.  What is the result of the count query in post 36351715 ?