Avatar of pepps11976
pepps11976
 asked on

Like Statement

Hi i have the following view
 
SELECT     TOP (100) PERCENT CAST(dbo.itran.it_exdate AS datetime) AS Month, dbo.ihead.ih_sorder AS Sales_Order, dbo.ihead.ih_doc, dbo.ihead.ih_sprojid AS Opportunity_No, 
                      dbo.ihead.ih_custref AS Customer_Reference, dbo.itran.it_stock AS Stock_Reference, dbo.itran.it_quan AS Quantity, dbo.ihead.ih_account AS Account, 
                      dbo.ihead.ih_name AS Name, dbo.itran.it_exdate AS Customer_Date, CAST(dbo.itran.it_due AS datetime) AS Our_Date, CAST(DATEDIFF(dd, dbo.itran.it_exdate, 
                      dbo.itran.it_due) AS varchar(20)) AS Date_Difference, CAST(DATEDIFF(dd, dbo.itran.it_exdate, { fn NOW() }) AS varchar(20)) AS Past_Due, 
                      dbo.itran.it_doc AS [Document], dbo.ihead.ih_quotat AS Quotation, dbo.ihead.ih_proform AS Proforma, dbo.itran.it_anal AS Anaylysis_Code, 
                      CAST(dbo.ihead.ih_orddate AS datetime) AS Order_Date, CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' THEN NULL ELSE dbo.itran.it_dtedelv END AS Delivery_Date, 
                      CASE WHEN dbo.fnGetBusinessDays(dbo.itran.it_dtedelv, it_exdate) BETWEEN 0 AND 5 THEN 'Pass' ELSE NULL END AS Passed_Delivery, 
                      CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' OR
                      dbo.fnGetBusinessDays(dbo.itran.it_dtedelv, it_exdate) BETWEEN 0 AND 5 THEN NULLIF (' ', ' ') ELSE 'Fail' END AS Failed_Delivery, 
                      dbo.fnGetBusinessDays(dbo.itran.it_dtedelv, dbo.itran.it_exdate) AS Delivery_Days, DATEPART(mm, dbo.itran.it_exdate) AS Month_No
FROM         dbo.ihead LEFT OUTER JOIN
                      dbo.itran ON dbo.ihead.ih_doc = dbo.itran.it_doc
WHERE     (dbo.itran.it_status = 'A') AND (dbo.ihead.ih_orddate >= '2009-01-01') AND (dbo.itran.it_anal NOT LIKE '%zz%') AND (dbo.itran.it_anal NOT LIKE '%LIN%') AND 
                      (DATEPART(yyyy, dbo.itran.it_exdate) = DATEPART(yyyy, GETDATE()))

Open in new window


i am trying to add to the end of it

where ih_name = 'Selex Galileo Limited' or ih_name like '%Thales%'

however i am being returned thousends of rows instead of expecting 84 rows. the where statement does not seem to be taking into account the datepart that i have before i try to narrow it down by names.

can anybody help?
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
pepps11976

8/22/2022 - Mon
Vitor Montalvão

Please post the entire select with last criteria that you added.
Want to check if you add parentesis. You need to be very careful with OR operator. In this case you need to add with parentesis: (ih_name = 'Selex Galileo Limited' or ih_name like '%Thales%')
pepps11976

ASKER
this is the entire select before adding the bit i want

SELECT     TOP (100) PERCENT CAST(dbo.itran.it_exdate AS datetime) AS Month, dbo.ihead.ih_sorder AS Sales_Order, dbo.ihead.ih_doc, dbo.ihead.ih_sprojid AS Opportunity_No,
                      dbo.ihead.ih_custref AS Customer_Reference, dbo.itran.it_stock AS Stock_Reference, dbo.itran.it_quan AS Quantity, dbo.ihead.ih_account AS Account,
                      dbo.ihead.ih_name AS Name, dbo.itran.it_exdate AS Customer_Date, CAST(dbo.itran.it_due AS datetime) AS Our_Date, CAST(DATEDIFF(dd, dbo.itran.it_exdate,
                      dbo.itran.it_due) AS varchar(20)) AS Date_Difference, CAST(DATEDIFF(dd, dbo.itran.it_exdate, { fn NOW() }) AS varchar(20)) AS Past_Due,
                      dbo.itran.it_doc AS [Document], dbo.ihead.ih_quotat AS Quotation, dbo.ihead.ih_proform AS Proforma, dbo.itran.it_anal AS Anaylysis_Code,
                      CAST(dbo.ihead.ih_orddate AS datetime) AS Order_Date, CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' THEN NULL ELSE dbo.itran.it_dtedelv END AS Delivery_Date,
                      CASE WHEN dbo.fnGetBusinessDays(dbo.itran.it_dtedelv, it_exdate) BETWEEN 0 AND 5 THEN 'Pass' ELSE NULL END AS Passed_Delivery,
                      CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' OR
                      dbo.fnGetBusinessDays(dbo.itran.it_dtedelv, it_exdate) BETWEEN 0 AND 5 THEN NULLIF (' ', ' ') ELSE 'Fail' END AS Failed_Delivery,
                      dbo.fnGetBusinessDays(dbo.itran.it_dtedelv, dbo.itran.it_exdate) AS Delivery_Days, DATEPART(mm, dbo.itran.it_exdate) AS Month_No
FROM         dbo.ihead LEFT OUTER JOIN
                      dbo.itran ON dbo.ihead.ih_doc = dbo.itran.it_doc
WHERE     (dbo.itran.it_status = 'A') AND (dbo.ihead.ih_orddate >= '2009-01-01') AND (dbo.itran.it_anal NOT LIKE '%zz%') AND (dbo.itran.it_anal NOT LIKE '%LIN%') AND
                      (DATEPART(yyyy, dbo.itran.it_exdate) = DATEPART(yyyy, GETDATE()))
anandarajpandian

I think you are selecting value based on 'AND' condition ,it may filter some values.

if possible provide data in excel format.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Lowfatspread

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
Lowfatspread

if you hadn't bracketed this

AND ( ih_name = 'Selex Galileo Limited' or ih_name like '%Thales%')

correctly the OR could have been taken as a condition on its own without respect to the other conditions.
pepps11976

ASKER
hi lowfatspread i get the following error with that code

Msg 4145, Level 15, State 1, Line 29
An expression of non-boolean type specified in a context where a condition is expected, near ')'.
pepps11976

ASKER
ok sussed it thanks for your help
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.