Link to home
Start Free TrialLog in
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?
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

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%')
Avatar of pepps11976
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()))
I think you are selecting value based on 'AND' condition ,it may filter some values.

if possible provide data in excel format.
ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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 ')'.
ok sussed it thanks for your help