Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Add another "Having" clause?

Posted on 2016-07-18
9
Medium Priority
?
53 Views
Last Modified: 2016-07-19
I had this question after viewing How can I add this SQL to the existing SQL I have?.

Paul helped me with the the solution in the link above and it works but I just realized I need to have another clause. I tried it in the "where" clause but that won't work.

1. I need to have this
LoadTypeId not in (2, 3,6,10)

Open in new window

2. We actually have a "view" to read the enums so instead of #1, I want to use the view

I have it to here but how can I add another having clause to it?

This is SQL 2014

SELECT
      c.CommSalesOrderId
    , a.Code
	, count(CASE WHEN (lv.Name = 'Settled') THEN l.Loadid END) AS SettledLoads
	,count(CASE WHEN ( lv.Name = 'Waived') THEN l.Loadid END) AS WavedLoads

FROM (
        select CommSalesOrder_CommSalesOrderId
        from loads
		left join dbo.LoadStatusER as V
                  on V.enumId = loadStatus 
        left join [dbo].[LoadTypeTypesER] ltv on ltv.EnumId = loadtypeId  ---*** I added this but now how do I exclude the values?
		 where shipdate  >= (@shipdateFrom) AND ShipDate < (@shipdateTo +1)
		 --and LoadTypeId not in (2, 3,6,10)  --*** this won't work
		    
        group by CommSalesOrder_CommSalesOrderId
		having  sum(case when v.enumId is not null then 0 else 1 end) = 0 

		
      ) AS lgroup
INNER JOIN CommSalesOrders AS c ON c.CommSalesOrderId = lgroup.CommSalesOrder_CommSalesOrderId
inner join loads l  ON c.CommSalesOrderId = l.CommSalesOrder_CommSalesOrderId
inner join accounts a on a.AccountId = c.Seller_AccountId
--inner join Addresses ad on ad.AccountId = a.AccountId
inner join [dbo].[LoadStatusER] lv on l.LoadStatus = lv.enumId

group by
      c.CommSalesOrderId
    , a.Code

Open in new window

0
Comment
Question by:Camillia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41717896
You can include a subquery for NOT IN like this:

             where shipdate  >= (@shipdateFrom) AND ShipDate < (@shipdateTo +1)
             and LoadTypeId not in (select LoadTypeId from that_view)  

but be careful with NULL values when using NOT IN this way.
But I'm afraid I don't fully understand this question. All I do understand is that line 14 does not work and that you have a view.

What is the name of that view?
What columns does it contains? (does it have LoadTypeId?)
What column of the subquery will you compare it to? (is it still LoadTypeId ?)
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 41717969
By the way, using a subquery inside a NOT IN () construct can lead to problems if a NULL is returned by the subquery, so please do take care, e.g.


     where shipdate  >= (@shipdateFrom) AND ShipDate < (@shipdateTo +1)
     and LoadTypeId not in (select v.LoadTypeId from that_view v where v.LoadTypeId IS NOT NULL)
0
 
LVL 7

Author Comment

by:Camillia
ID: 41717979
Thanks, Paul. Let me try it in the morning. I'll post back.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 7

Author Comment

by:Camillia
ID: 41719216
No, that won't do it. It has to be in "Having" clause. This is the related question above.

If I add "not in"...it won't bring back any rows. There are 3 rows. One has to be brought back.

It needs to be in "Having" like the inner SQL. You see I have the left-join and now somehow I need to have "Having" just like the other "having" clause you helped me with.
 left join [dbo].[LoadTypeTypesER] ltv on ltv.EnumId = loadtypeId

Open in new window


   select CommSalesOrder_CommSalesOrderId
        from loads
		left join dbo.LoadStatusER as V
                  on V.enumId = loadStatus 
        left join [dbo].[LoadTypeTypesER] ltv on ltv.EnumId = loadtypeId  ---*** I added this but now how do I exclude the values?
		 where shipdate  >= (@shipdateFrom) AND ShipDate < (@shipdateTo +1)
		 --and LoadTypeId not in (2, 3,6,10)  --*** this won't work
		    
        group by CommSalesOrder_CommSalesOrderId
		having  sum(case when v.enumId is not null then 0 else 1 end) = 0 

Open in new window

0
 
LVL 7

Author Comment

by:Camillia
ID: 41719240
I did this and for one row, I get the correct result but not sure if I will run into issues...

I added to the 'Having' clause on the outer SQL. I've noted the section below

SELECT
      c.CommSalesOrderId
    , a.Code
	, count(CASE WHEN (lv.Name = 'Settled') THEN l.Loadid END) AS SettledLoads
	, count(CASE WHEN ( lv.Name = 'Waived') THEN l.Loadid END) AS WavedLoads
	,l.loadid

FROM (
        select CommSalesOrder_CommSalesOrderId, loadid
        from loads
		left join dbo.LoadStatusER as V
                  on V.enumId = loadStatus 
      --  left join [dbo].[LoadTypeTypesER] ltv on ltv.EnumId = loadtypeId
		 where shipdate  >= (@shipdateFrom) AND ShipDate < (@shipdateTo +1)
		--and LoadTypeId not in (select v.EnumId from [dbo].[LoadTypeTypesER] v where v.EnumId IS NOT NULL)
		       and CommSalesOrder_CommSalesOrderId = 81972 ---------------------------*************** remove this
        group by CommSalesOrder_CommSalesOrderId, loadid
		having  sum(case when v.enumId is not null then 0 else 1 end) = 0 

		
      ) AS lgroup
INNER JOIN CommSalesOrders AS c ON c.CommSalesOrderId = lgroup.CommSalesOrder_CommSalesOrderId
inner join loads l  ON c.CommSalesOrderId = l.CommSalesOrder_CommSalesOrderId
inner join accounts a on a.AccountId = c.Seller_AccountId
--inner join Addresses ad on ad.AccountId = a.AccountId
inner join [dbo].[LoadStatusER] lv on l.LoadStatus = lv.enumId
inner join [dbo].[LoadTypeTypesER] ltv on ltv.EnumId = loadtypeId ---**** Added it here

group by
      c.CommSalesOrderId
    , a.Code
	,l.LoadId
having ( (count(CASE WHEN (lv.Name = 'Settled') THEN l.Loadid END) > 0  or  count(CASE WHEN ( lv.Name = 'Waived') THEN l.Loadid END) > 0 ) 
           AND  ---***** added these here *****
            ( count(CASE WHEN ( ltv.Name = 'PC') THEN l.Loadid END) = 0  or count(CASE WHEN ( ltv.Name = 'DL') THEN l.Loadid END) = 0 
			  or count(CASE WHEN ( ltv.Name = 'RJ') THEN l.Loadid END) = 0  or count(CASE WHEN ( ltv.Name = 'SC') THEN l.Loadid END) = 0  --*****
			
			)
	     )

Open in new window

0
 
LVL 7

Author Comment

by:Camillia
ID: 41719248
This one works for one test row as well. In this one, I put it in the "Having" of the inner SQL. I get the same one row but what's the difference between the one I have above (in outer SQL) and this one (inner sql). Would I get unexpected results?

SELECT
      c.CommSalesOrderId
    , a.Code
	, count(CASE WHEN (lv.Name = 'Settled') THEN l.Loadid END) AS SettledLoads
	, count(CASE WHEN ( lv.Name = 'Waived') THEN l.Loadid END) AS WavedLoads
	,l.loadid

FROM (
        select CommSalesOrder_CommSalesOrderId, loadid
        from loads
		left join dbo.LoadStatusER as V
                  on V.enumId = loadStatus 
               inner join [dbo].[LoadTypeTypesER] ltv on ltv.EnumId = loadtypeId ---***added it here
		 where shipdate  >= (@shipdateFrom) AND ShipDate < (@shipdateTo +1)
		
		       and CommSalesOrder_CommSalesOrderId = 81972 ---------------------------*************** remove this
        group by CommSalesOrder_CommSalesOrderId, loadid
		having  ( sum(case when v.enumId is not null then 0 else 1 end) = 0 
		
		         AND ( ----***** I added it here
				 
				  ( count(CASE WHEN ( ltv.Name = 'PC') THEN Loadid END) = 0  or count(CASE WHEN ( ltv.Name = 'DL') THEN Loadid END) = 0 
		        	  or count(CASE WHEN ( ltv.Name = 'RJ') THEN Loadid END) = 0  or count(CASE WHEN ( ltv.Name = 'SC') THEN Loadid END) = 0 
			
		     	)
				 ) 
		
		          )

		
      ) AS lgroup
INNER JOIN CommSalesOrders AS c ON c.CommSalesOrderId = lgroup.CommSalesOrder_CommSalesOrderId
inner join loads l  ON c.CommSalesOrderId = l.CommSalesOrder_CommSalesOrderId
inner join accounts a on a.AccountId = c.Seller_AccountId
--inner join Addresses ad on ad.AccountId = a.AccountId
inner join [dbo].[LoadStatusER] lv on l.LoadStatus = lv.enumId
inner join [dbo].[LoadTypeTypesER] ltv on ltv.EnumId = loadtypeId

group by
      c.CommSalesOrderId
    , a.Code
	,l.LoadId
having ( (count(CASE WHEN (lv.Name = 'Settled') THEN l.Loadid END) > 0  or  count(CASE WHEN ( lv.Name = 'Waived') THEN l.Loadid END) > 0 ) 
          -- AND 
          --  ( count(CASE WHEN ( ltv.Name = 'PC') THEN l.Loadid END) = 0  or count(CASE WHEN ( ltv.Name = 'DL') THEN l.Loadid END) = 0 
			--  or count(CASE WHEN ( ltv.Name = 'RJ') THEN l.Loadid END) = 0  or count(CASE WHEN ( ltv.Name = 'SC') THEN l.Loadid END) = 0 
			
			--)
	     )

Open in new window

0
 
LVL 7

Author Closing Comment

by:Camillia
ID: 41719305
Going to close this and post another one with some sample data. Thanks for your help
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41719932
Ok. Thanks.
0
 
LVL 7

Author Comment

by:Camillia
ID: 41719938
Thanks, again, Paul.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question