Solved

Add another "Having" clause?

Posted on 2016-07-18
9
46 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
  • 6
  • 3
9 Comments
 
LVL 48

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 48

Accepted Solution

by:
PortletPaul earned 500 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 48

Expert Comment

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

Author Comment

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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

838 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