4 Replies - 365 Views - Last Post: 03 April 2019 - 12:09 PM

#1 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,218
  • Joined: 12-January 10

Value returning null even if there is data

Posted 03 April 2019 - 07:38 AM

so in my query it checks for null and if it is it should grab another value
for some reason it returns null even if the value exists:

broken part
CASE WHEN  dbo.aaa_tuptimefeedstock.BLK_Tons_Added IS NULL OR dbo.aaa_tuptimefeedstock.BLK_Tons_Added = 0 THEN (dbo.aaa_tuptimefeedstock.BLK_Tons_Added + (SELECT TOP 1 dbo.aaa_tuptimefeedstock.BLK_RemainingOnHand from dbo.aaa_tuptimecategory
			       inner join dbo.aaa_tUptimeCategorydivision
				   on dbo.aaa_tuptimecategory.Categorykey = dbo.aaa_tUptimeCategorydivision.Categorykey
				   left outer join dbo.aaa_tuptimefeedstock
				   on dbo.aaa_tuptimefeedstock.divisionkey = dbo.aaa_tUptimeCategorydivision.DivisionKey
				   and dbo.aaa_tuptimefeedstock.Categorykey =  dbo.aaa_tUptimeCategorydivision.Categorykey
				   and  dbo.aaa_tUptimeFeedstock.ProdDate< @[email protected]    
				   where
				   dbo.aaa_tUptimeCategorydivision.divisionkey = @DivisionKey [email protected]
				   and dbo.aaa_tUptimeCategorydivision.active = 1 order by ProdDate desc )) ELSE BLK_Tons_Added END AS BLK_Tons_Added






full query:

@DivisionID	varchar(50),
		@ProdDate	datetime 
	)
AS
BEGIN
SET NOCOUNT ON;

	declare @DivisionKey int
	select @DivisionKey = DivisionKey from dbo.aaa_tUptimeDivision where DivisionID = @DivisionID

	
    SELECT Alias

               ,dbo.aaa_tUptimeCategorydivision.WorkCenter 
               ,dbo.aaa_tUptimeCategorydivision.[Categorykey]
               ,dbo.aaa_tuptimecategory.CategoryID 
               ,dbo.aaa_tUptimeFeedstock.ProdDate  
			   ,CASE WHEN  dbo.aaa_tuptimefeedstock.BLK_Tons_Added IS NULL OR dbo.aaa_tuptimefeedstock.BLK_Tons_Added = 0 THEN (dbo.aaa_tuptimefeedstock.BLK_Tons_Added + (SELECT TOP 1 dbo.aaa_tuptimefeedstock.BLK_RemainingOnHand from dbo.aaa_tuptimecategory
			       inner join dbo.aaa_tUptimeCategorydivision
				   on dbo.aaa_tuptimecategory.Categorykey = dbo.aaa_tUptimeCategorydivision.Categorykey
				   left outer join dbo.aaa_tuptimefeedstock
				   on dbo.aaa_tuptimefeedstock.divisionkey = dbo.aaa_tUptimeCategorydivision.DivisionKey
				   and dbo.aaa_tuptimefeedstock.Categorykey =  dbo.aaa_tUptimeCategorydivision.Categorykey
				   and  dbo.aaa_tUptimeFeedstock.ProdDate< @[email protected]    
				   where
				   dbo.aaa_tUptimeCategorydivision.divisionkey = @DivisionKey [email protected]
				   and dbo.aaa_tUptimeCategorydivision.active = 1 order by ProdDate desc )) ELSE BLK_Tons_Added END AS BLK_Tons_Added
			   ,dbo.aaa_tUptimeFeedstock.[BLK_Consumed_Tons]
		       ,dbo.aaa_tUptimeFeedstock.[BLK_RemainingOnHand]
			   ,CASE WHEN  BLKWT_Tons_Added IS NULL OR BLK_Tons_Added = 0 THEN (BLKWT_Tons_Added + (SELECT TOP 1 BLKWT_RemainingOnHand from dbo.aaa_tuptimecategory
				   inner join dbo.aaa_tUptimeCategorydivision
				   on dbo.aaa_tuptimecategory.Categorykey = dbo.aaa_tUptimeCategorydivision.Categorykey
				   left outer join dbo.aaa_tuptimefeedstock
				   on dbo.aaa_tuptimefeedstock.divisionkey = dbo.aaa_tUptimeCategorydivision.DivisionKey
				   and dbo.aaa_tuptimefeedstock.Categorykey =  dbo.aaa_tUptimeCategorydivision.Categorykey
				   and  dbo.aaa_tUptimeFeedstock.ProdDate< @ProdDate    
				   where
				   dbo.aaa_tUptimeCategorydivision.divisionkey = @DivisionKey
				   and dbo.aaa_tUptimeCategorydivision.active = 1 order by ProdDate desc)) ELSE BLKWT_Tons_Added END AS BLKWT_Tons_Added
			   ,dbo.aaa_tUptimeFeedstock.[BLKWT_Consumed_Tons]
			   ,dbo.aaa_tUptimeFeedstock.[BLKWT_RemainingOnHand]
			   ,dbo.aaa_tUptimeFeedstock.[status]

    into    #tmpGrade

    from dbo.aaa_tuptimecategory
    inner join dbo.aaa_tUptimeCategorydivision
    on dbo.aaa_tuptimecategory.Categorykey = dbo.aaa_tUptimeCategorydivision.Categorykey
    left outer join dbo.aaa_tuptimefeedstock
    on dbo.aaa_tuptimefeedstock.divisionkey = dbo.aaa_tUptimeCategorydivision.DivisionKey
    and dbo.aaa_tuptimefeedstock.Categorykey =  dbo.aaa_tUptimeCategorydivision.Categorykey
    and  dbo.aaa_tUptimeFeedstock.ProdDate = @ProdDate
  --  and dbo.aaa_tUptimeFeedstock.status = 1
    where
	dbo.aaa_tUptimeCategorydivision.divisionkey = @DivisionKey
    and dbo.aaa_tUptimeCategorydivision.active = 1

	declare @tempBlk_added as integer
	select @tempBlk_added =  BLK_Tons_Added from #tmpGrade 
	inner join dbo.aaa_tuptimecategory
	on #tmpGrade.Categorykey = dbo.aaa_tuptimecategory.Categorykey

	  if @tempBlk_added is null  	   
	  update  #tmpGrade	 
	  set BLK_Tons_Added =	  
	  (
		  SELECT TOP 1 dbo.aaa_tuptimefeedstock.BLK_RemainingOnHand
		  from  dbo.aaa_tuptimecategory
			inner join dbo.aaa_tUptimeCategorydivision
			on dbo.aaa_tuptimecategory.Categorykey = dbo.aaa_tUptimeCategorydivision.Categorykey
			left outer join dbo.aaa_tuptimefeedstock
			on dbo.aaa_tuptimefeedstock.divisionkey = dbo.aaa_tUptimeCategorydivision.DivisionKey  
			 and dbo.aaa_tuptimefeedstock.Categorykey =  dbo.aaa_tUptimeCategorydivision.Categorykey
			and  dbo.aaa_tUptimeFeedstock.ProdDate< @ProdDate    
			where
			dbo.aaa_tUptimeCategorydivision.divisionkey = @DivisionKey
			and dbo.aaa_tUptimeCategorydivision.active = 1 
			and #tmpGrade.Categorykey = dbo.aaa_tuptimefeedstock.Categorykey
			order by ProdDate desc
	 )
	
	declare @tempBlkWT_added as integer
	select @tempBlkWT_added =  BLKWT_Tons_Added from #tmpGrade 
	inner join dbo.aaa_tuptimecategory
	on #tmpGrade.Categorykey = dbo.aaa_tuptimecategory.Categorykey
	 
	  if @tempBlkWT_added is null  	   
	  update  #tmpGrade	 
	  set BLKWT_Tons_Added =	  
	  (
		  SELECT TOP 1 dbo.aaa_tuptimefeedstock.BLKWT_RemainingOnHand
		  from  dbo.aaa_tuptimecategory
			inner join dbo.aaa_tUptimeCategorydivision
			on dbo.aaa_tuptimecategory.Categorykey = dbo.aaa_tUptimeCategorydivision.Categorykey
			left outer join dbo.aaa_tuptimefeedstock
			on dbo.aaa_tuptimefeedstock.divisionkey = dbo.aaa_tUptimeCategorydivision.DivisionKey  
			 and dbo.aaa_tuptimefeedstock.Categorykey =  dbo.aaa_tUptimeCategorydivision.Categorykey
			and  dbo.aaa_tUptimeFeedstock.ProdDate< @ProdDate    
			where
			dbo.aaa_tUptimeCategorydivision.divisionkey = @DivisionKey
			and dbo.aaa_tUptimeCategorydivision.active = 1 
			and #tmpGrade.Categorykey = dbo.aaa_tuptimefeedstock.Categorykey
			order by ProdDate desc
	 )
	 	 
   select * from #tmpGrade
   drop table #tmpGrade

 





results:

Attached image(s)

  • Attached Image
  • Attached Image


Is This A Good Question/Topic? 0
  • +

Replies To: Value returning null even if there is data

#2 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15359
  • View blog
  • Posts: 61,583
  • Joined: 12-June 08

Re: Value returning null even if there is data

Posted 03 April 2019 - 07:41 AM

Pull that subquery out.. 18-27.. start plugging in data to see what's up.
Was This Post Helpful? 0
  • +
  • -

#3 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,218
  • Joined: 12-January 10

Re: Value returning null even if there is data

Posted 03 April 2019 - 09:32 AM

i was wrong this is the broken part:

declare @tempBlk_added as integer
	select @tempBlk_added =  BLK_Tons_Added from #tmpGrade 
	inner join dbo.aaa_tuptimecategory
	on #tmpGrade.Categorykey = dbo.aaa_tuptimecategory.Categorykey

	  if @tempBlk_added is null  	  
	  
	  select  BLK_Tons_Added from #tmpGrade   --- shows the values 
	  update  #tmpGrade	 
	  set BLK_Tons_Added =	  
	  (
		  SELECT TOP 1 dbo.aaa_tuptimefeedstock.BLK_RemainingOnHand
		  from  dbo.aaa_tuptimecategory
			inner join dbo.aaa_tUptimeCategorydivision
			on dbo.aaa_tuptimecategory.Categorykey = dbo.aaa_tUptimeCategorydivision.Categorykey
			left outer join dbo.aaa_tuptimefeedstock
			on dbo.aaa_tuptimefeedstock.divisionkey = dbo.aaa_tUptimeCategorydivision.DivisionKey  
			 and dbo.aaa_tuptimefeedstock.Categorykey =  dbo.aaa_tUptimeCategorydivision.Categorykey
			and  dbo.aaa_tUptimeFeedstock.ProdDate< @ProdDate    
			where
			dbo.aaa_tUptimeCategorydivision.divisionkey = @DivisionKey
			and dbo.aaa_tUptimeCategorydivision.active = 1 
			and #tmpGrade.Categorykey = dbo.aaa_tuptimefeedstock.Categorykey
			order by ProdDate desc
	 )


select  BLK_Tons_Added from #tmpGrade   ---  values are gone 

This post has been edited by DarenR: 03 April 2019 - 09:33 AM

Was This Post Helpful? 0
  • +
  • -

#4 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,218
  • Joined: 12-January 10

Re: Value returning null even if there is data

Posted 03 April 2019 - 09:38 AM

x is what the return should be

yellow are the fields that represent eachother

Attached image(s)

  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#5 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,218
  • Joined: 12-January 10

Re: Value returning null even if there is data

Posted 03 April 2019 - 12:09 PM

the fix:

declare @tempWIPBLK_added as integer
	select @tempWIPBLK_added =  WIPBLK_Tons_Added from #tmpWIP 
	inner join dbo.aaa_tuptimecategory
	on #tmpWIP.Categorykey = dbo.aaa_tuptimecategory.Categorykey	
	
	  if @tempWIPBLK_added is null or  @tempWIPBLK_added <> 0
	  begin
	  
	  update  #tmpWIP	 
	  set WIPBLK_Tons_Added =	  
	  (
	  	 ( 
			SELECT TOP 1 dbo.aaa_tuptimeWIP.WIPBLK_RemainingOnHand
			from  dbo.aaa_tuptimecategory
			inner join dbo.aaa_tUptimeCategorydivision
			on dbo.aaa_tuptimecategory.Categorykey = dbo.aaa_tUptimeCategorydivision.Categorykey
			left outer join dbo.aaa_tuptimeWIP
			on dbo.aaa_tuptimeWIP.divisionkey = dbo.aaa_tUptimeCategorydivision.DivisionKey  
			 and dbo.aaa_tuptimeWIP.Categorykey =  dbo.aaa_tUptimeCategorydivision.Categorykey
			and  dbo.aaa_tuptimeWIP.ProdDate< @ProdDate 
			where
			dbo.aaa_tUptimeCategorydivision.divisionkey =  @DivisionKey
			and dbo.aaa_tUptimeCategorydivision.active = 1 
			and #tmpWIP.Categorykey =  dbo.aaa_tUptimeCategorydivision.Categorykey
			order by ProdDate desc 
		 ) 				
	   )
	 end


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1