September 2, 2008 at 6:29 pm
I have a fairly complicted view that has a field for customer ID. Some of our customer IDs have a '-' and this view fails on those ones because it looks like it is trying to convert the customerID to an integer.so i want to not see those ones in my view. I wrote the following criteria and it still returns the odd one
NOT LIKE '%-%'
Does anyone know of a reason for this?
Here is the SQl statement
SELECT CAST(b.FISCYR AS varchar(4)) AS FiscYr, CAST(b.FISCPER AS varchar(2)) AS FiscPer, CAST(b.FISCYR AS varchar(4))
+ '-' + CAST(b.FISCPER AS varchar(2)) AS YrPer, a.IDCUST + '-' + CAST(b.FISCYR AS varchar(4)) + '-' + CAST(b.FISCPER AS varchar(2)) AS ConYrPer,
CASE WHEN d .Value = 999999 THEN a.IDCust ELSE d .Value END AS Coordinator, a.IDCUST AS ConsultantID, a.NAMECUST AS Consultant,
b.DOCNUM, CAST(LEFT(LTRIM(STR(b.INVDATE)), 4) + '-' + SUBSTRING(LTRIM(STR(b.INVDATE)), 5, 2) + '-' + SUBSTRING(LTRIM(STR(b.INVDATE)), 7, 2)
+ 'T00:00:00' AS datetime) AS InvDate, b.ORDNUMBER, b.INVNUMBER, SUM(CASE WHEN b.TransType = 2 OR
b.TransType = 4 THEN - c.ExtInvMisc ELSE c.ExtInvMisc END) AS InvoiceAmount, SUM(CASE WHEN LEFT(b.DocNum, 1)
= 'C' THEN c.TAmount2 ELSE - c.TAmount2 END) AS Commission
FROM dbo.ARCUS AS a INNER JOIN
dbo.OEAUDH AS b ON a.IDCUST = b.CUSTOMER INNER JOIN
dbo.OEAUDD AS c ON b.TRANSTYPE = c.TRANSTYPE AND b.DAYENDNUM = c.DAYENDNUM AND b.ENTRYNUM = c.ENTRYNUM INNER JOIN
dbo.ARCUSO AS D ON a.IDCUST = D.IDCUST AND D.OPTFIELD = 'RECRUITER'
WHERE (c.MISCCHARGE <> 'FRT') AND (b.INVDATE <> 0) AND (c.ITEM NOT LIKE '%START%') AND (c.ITEM NOT LIKE 'S9%')
GROUP BY b.FISCYR, b.FISCPER, a.IDCUST, a.NAMECUST, b.DOCNUM, b.INVDATE, b.ORDNUMBER, b.INVNUMBER, D.VALUE
HAVING (NOT (a.IDCUST LIKE '%-%'))
September 2, 2008 at 10:01 pm
Try putting the statement in the WHERE part.
where CustID not like '%-%'
September 3, 2008 at 8:47 pm
I tried that and it doesent work either
September 3, 2008 at 8:56 pm
What is the data type of the column?
Just to clarify, your data looks like this in the ID column.
1234567
234-567
September 3, 2008 at 9:01 pm
The ID is char(12)... The two examples you gave are what you could expect in this database.
September 3, 2008 at 9:14 pm
try running a query directly against the table itself with just the where clause from above and see if it returns the desired results.
select top 20 percent CustomerID from Table where CustomerID not like '%-%'
That should tell us if it is something with the query or the view.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply