I have 1 SQL Table and 1 SQL View as follows:
Table1 contains a column VendorId along with several other columns. The VendorId column in Table1 is formatted as varchar(255) and is typically storing a VendorId that consists of Alpha and Numeric values up to about 8 characters total. Ex. ATT0000, CEDA0678, SPIR0193, SPIR048, SPIR1433. There are several other Vendors in Table1 with data, etc.
View1 contains some of the Vendors above. My view is built using a Case Statement which parses the Vendor Id out of some text and I'm only returning the VendorId in View1.
When I attempt to return all Records from Table1 as:
Select * From Table1 Where VendorId in (Select VendorId from View1)
I get no results. however if I Select VendorId from View1 it returns the Vendors: ATT0000, CEDA0678, SPIR0193, SPIR048, SPIR1433. Also if I Select * from Table1 Where Vendorid in ('ATT0000', 'CEDA0678', 'SPIR0193', 'SPIR048', 'SPIR1433') I get results but the "in" does not work in the above script as I get no results. I've also tried wrapping ltrim(rtrim(VendorId)) around my Select statement with no results.
This returns values:
This returns values:
This returns nothing:
Select *
From _DSI_GobyPayableTransaction
Where ltrim(rtrim(GPVendorId)) in (Select ltrim(rtrim(GPInvalidRecord)) From _DSI_GobyErrorMessages A Where GPErrorMessage like '%Vendor%')
I use 'in' all the time to return records but now I'm getting nothing. Ideas? Suggestions? This shouldn't be this complicated.