October 8, 2024 at 7:32 pm
Hello, I'm trying to remove results from this sql statement where the custid like '%abc%' and company = 'abc', but think I'm getting the syntax wrong. Not including the entire thing, hopefully it's enough. the case statement begins at t2.custid.
Please help if you can, thank you!
t1.Company,
t2.CustID
(CASE
WHEN custid like '%abc%' and company = 'abc'
then null
ELSE custid
END),
from Dtl1 t1
left outer join Customer t2
on t1.Company = t2.Company
and t1.CustNum = t2.CustNum
join Reason t3
on t1.Company = t3.Company
and t1.ReasonCode= t3.ReasonCode
join Head1 t4
on t1.Company = t4.Company
and t1.ReturnNum = t4.ReturnNum
join Rcpt t5
on t4.Company = t5.Company
and t4.ReturnNum = t5.ReturnNum
and t1.returnline = t5.returnline
join ReturnDispose t7
on t5.Company = t7.Company
and t5.ReturnNum = t7.ReturnNum
and t5.ReturnLine = t7.ReturnLine
left outer join biPart p
on t1.Company = p.Company
and t1.PartNum = p.PartNum
left outer join Dtl2 t6
on t1.company = t6.company
and t1.ordernum = t6.ordernum
and t1.returnnum = t6.returnnum
and t1.returnline = t6.returnline
left outer join
(select t6.company,
sum(sellingshipqty) as Shipping
year(invoicedate) as InvoiceYear,
month(invoicedate) as InvoiceMonth,
from Dtl2 t6
join head2 t12
on t6.company = t12.company
and t6.InvoiceNum = t12.InvoiceNum
where t6.company = 'abc'
group by t6.company, year(invoicedate), month(invoicedate)) Sales
on t4.company = sales.company
and year (t4.returndate) = sales.InvoiceYear
and month(t4.returndate) = sales.InvoiceMonth
left outer join
(select t1.company,
sum(ReturnQty) as Returns,
year(returndate) as ReturnYear,
month(returndate) as ReturnMonth
from Dtl1 t1
join Head1 t4
on t1.company = t4.company
and t1.returnNum = t4.returnNum
where t1.company = 'abc'
group by t1.company, year(returndate), month(returndate)) Ret
on t4.company = ret.company
and year (t4.returndate) = ret.returnYear
and month(t4.returndate) = ret.returnMonth
where t4.ReturnDate >= DATEADD(yy,-2,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))
October 8, 2024 at 7:37 pm
Removal of results is done by including your condition in the WHERE clause, not in the SELECT.
WHERE custid not like '%abc%' or company <> 'abc'
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 8, 2024 at 7:47 pm
If you just want to change the result column in the SELECT, then do this:
...,
CustID =
(CASE
WHEN custid like '%abc%' and company = 'abc'
then null
ELSE custid
END),
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 8, 2024 at 7:55 pm
Thanks Phil, I'm not getting any errors, however my returndate restriction is no longer working,
(where t4.ReturnDate >= DATEADD(yy,-2,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))
it is pulling all results, instead of those just going back two years.
Current code: where t4.ReturnDate >= DATEADD(yy,-2,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) --Jan 1 of 2 yrs back to now
and t2.custid not like '%abc%' and t1.Company like 'abc'
October 8, 2024 at 8:29 pm
Your code is different from what I suggested. In particular, you changed <> to 'Like'.
SELECT ...
FROM sometables
WHERE t4.ReturnDate >= DATEADD (yy, -2, DATEADD (yy, DATEDIFF (yy, 0, GETDATE ()), 0))
AND t2.custid NOT LIKE '%abc%'
AND t1.Company <> 'abc';
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 9, 2024 at 1:59 pm
With those new characters, I get the following error message without displaying the change:
"An expression of non-boolean type specified in a context where a condition is expected."
October 9, 2024 at 2:13 pm
The code fragment I posted would not create that error. Can you post the entire text of the query which is giving that error?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 9, 2024 at 2:16 pm
t1.Company,
t2.CustID
(CASE
WHEN custid like '%abc%' and company = 'abc'
then null
ELSE custid
END),
from Dtl1 t1
left outer join Customer t2
on t1.Company = t2.Company
and t1.CustNum = t2.CustNum
join Reason t3
on t1.Company = t3.Company
and t1.ReasonCode= t3.ReasonCode
join Head1 t4
on t1.Company = t4.Company
and t1.ReturnNum = t4.ReturnNum
join Rcpt t5
on t4.Company = t5.Company
and t4.ReturnNum = t5.ReturnNum
and t1.returnline = t5.returnline
join ReturnDispose t7
on t5.Company = t7.Company
and t5.ReturnNum = t7.ReturnNum
and t5.ReturnLine = t7.ReturnLine
left outer join biPart p
on t1.Company = p.Company
and t1.PartNum = p.PartNum
left outer join Dtl2 t6
on t1.company = t6.company
and t1.ordernum = t6.ordernum
and t1.returnnum = t6.returnnum
and t1.returnline = t6.returnline
left outer join
(select t6.company,
sum(sellingshipqty) as Shipping
year(invoicedate) as InvoiceYear,
month(invoicedate) as InvoiceMonth,
from Dtl2 t6
join head2 t12
on t6.company = t12.company
and t6.InvoiceNum = t12.InvoiceNum
where t6.company = 'abc'
group by t6.company, year(invoicedate), month(invoicedate)) Sales
on t4.company = sales.company
and year (t4.returndate) = sales.InvoiceYear
and month(t4.returndate) = sales.InvoiceMonth
left outer join
(select t1.company,
sum(ReturnQty) as Returns,
year(returndate) as ReturnYear,
month(returndate) as ReturnMonth
from Dtl1 t1
join Head1 t4
on t1.company = t4.company
and t1.returnNum = t4.returnNum
where t1.company = 'abc'
group by t1.company, year(returndate), month(returndate)) Ret
on t4.company = ret.company
and year (t4.returndate) = ret.returnYear
and month(t4.returndate) = ret.returnMonth
where t4.ReturnDate >= DATEADD(yy,-2,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))
and t2.custid not like '%abc%' and t1.Company <> 'abc01'
October 9, 2024 at 2:27 pm
Not sure what that is, but try pressing 'Parse' (CTRL/F5) on it and you'll see that it doesn't.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 9, 2024 at 2:45 pm
Please use the {;}Code button when posting code.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 9, 2024 at 4:14 pm
Ed, yes, I was seriously confused about those characters, didn't realize they were supposed to be the less than/greater than symbols.
That code is now running without error, but the problem is the code is taking out ALL results where company = 'abc01' and eliminating all custids like '%abc%' from ALL companies. I tried to put the new where statement in parenthesis but that didn't seem to improve it.
where t4.ReturnDate >= DATEADD(yy,-2,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))
(and t2.custid not like '%abc%' and t1.Company <> 'abc01')
October 9, 2024 at 4:25 pm
That code is now running without error, but the problem is the code is taking out ALL results where company = 'abc01' and eliminating all custids like '%abc%' from ALL companies. I tried to put the new where statement in parenthesis but that didn't seem to improve it.
If you only want to exclude %abc% for company abc01 then maybe something like this. Without the tables I can't tell whether I have the brackets/parens right.
where t4.ReturnDate >= DATEADD(yy,-2,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))
and (
( t1.Company = 'abc01' and t2.custid not like '%abc%')
or
(t1.Company <> 'abc01')
)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply