We help IT Professionals succeed at work.

Why am I getting a return for an item, but not if I provide the item_id specifically?

The issue: I am querying items, using a WHERE clause to filter out rows if there is a value for "a.item_sales_tax_class"
If I run the query as below, I get a list of  item_id's returned,  including item_id  "66253364053" even though there IS a value for a.item_sales_tax_class, in the inv_mast  table
for item_id  "66253364053".
If I run the 2nd query and specify the "item_id" in the WHERE clause, I DON'T get a value returned.
item_sales_tax_class is a varchar(8) type
What am I missing??

This query, returns a list of  item_id's, with NULL for "item_sales_tax_class"  (including item_id  "66253364053"):

SELECT DISTINCT   a.item_id as "Item ID",a.inv_mast_uid as "INV MAST UID", b.tax_group_id as "Tax Group",b.product_group_id as "Product Group",a.item_sales_tax_class as "Avalara_Tax_Code",a.date_created as "Date Created",a.delete_flag as "Inv_Mast Delete Flag",b.delete_flag as "LOC Delete Flag"  ,a.requisition,a.created_by
from inv_mast a
INNER JOIN inv_loc b
ON a.inv_mast_uid = b.inv_mast_uid
where a.item_sales_tax_class is NULL
AND a.delete_flag = 'N'
AND a.requisition = 'N'

Open in new window


This query does NOT return any rows:

SELECT DISTINCT   a.item_id as "Item ID",a.inv_mast_uid as "INV MAST UID", b.tax_group_id as "Tax Group",b.product_group_id as "Product Group",a.item_sales_tax_class as "Avalara_Tax_Code",a.date_created as "Date Created",a.delete_flag as "Inv_Mast Delete Flag",b.delete_flag as "LOC Delete Flag"  ,a.requisition,a.created_by
from inv_mast a
INNER JOIN inv_loc b
ON a.inv_mast_uid = b.inv_mast_uid
where a.item_sales_tax_class is NULL
AND a.delete_flag = 'N'
AND a.requisition = 'N'
AND a.item_id = '66253364053'

Open in new window

Comment
Watch Question

What data type is item_id column?

You may try
SELECT DISTINCT   a.item_id as "Item ID",a.inv_mast_uid as "INV MAST UID", b.tax_group_id as "Tax Group",b.product_group_id as "Product Group",a.item_sales_tax_class as "Avalara_Tax_Code",a.date_created as "Date Created",a.delete_flag as "Inv_Mast Delete Flag",b.delete_flag as "LOC Delete Flag"  ,a.requisition,a.created_by
from inv_mast a
INNER JOIN inv_loc b
ON a.inv_mast_uid = b.inv_mast_uid
where a.item_sales_tax_class is NULL
AND a.delete_flag = 'N'
AND a.requisition = 'N'
AND CAST(a.item_id AS varchar(20)) = '66253364053'

Open in new window

Richard GouetteIT Manager

Author

Commented:
VarChar(40)
OK, then the item_id could contain some invisible characters or zeros replaced by O..
Please change the last line to

AND LEFT(a.item_id, 11) = '66253364053'

Or you could even display the Item_ID converted to varbinary:

SELECT DISTINCT  CAST(a.item_id as varbinary(40)) AS Item_ID_BIN, a.item_id as "Item ID",a.inv_ ...
Richard GouetteIT Manager

Author

Commented:
ok, using that, I get a return for that item_id.

I looked at the item in our ERP where it appears, and copied/pasted into notepad++ etc..
I cannot find any instance of any extra characters..??
Also, if I'm specifying '66253364053' , how is that different than taking it from a  LEFT function?
Surely there's something I just don't understand..?

Thanks much,
Rich
Did you check the value in varbinary format? What it contains?

If your column contains e.g. '66253364053' + CHAR(160)  value then you don't see the hard space at the end. but to compare it against  '66253364053' returns no match.
Richard GouetteIT Manager

Author

Commented:
CAST(a.item_id as varbinary(40))   returns:
varbinary value
Yes,  09 means (invisible) TAB character and it explains everything.

To Display all rows containing TAB character:
SELECT * FROM inv_mast WHERE CHARINDEX(CHAR(9), item_id) > 0

To remove such character from the column you may issue:
UPDATE inv_mast  SET item_id = REPLACE(item_id, CHAR(9), '') WHERE CHARINDEX(CHAR(9), item_id) > 0
Richard GouetteIT Manager

Author

Commented:
ok, I'll investigate.
Many thanks!!