SQLTeam.com | Weblogs | Forums

Nested SQL Script using in Not Returning any data

#1

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:
image

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.

#2

Is it possible you have unprintable characters (carriage return, linefeed etc) in the version coming out of your view? If you try:

SELECT GPInvalidRecord, LEN(GPInvalidRecord) FROM _DSI_GobyErrorMessages

do the lengths look correct?

#3

there is an extra space at the End of the column in the view so even when I use ltrim(rtrim(GPInvalidRecord)) it shows as 8 or 9 yet the number of characters visible only show 7 or 8. I thought ltrim(rtrim(column)) would strip out any extra space or as you point out a possible carriage return, line feed. how do I get rid of that?

image

I would normally just do a something like:
left(ltrim(rtrim(columnname)), len(columnname)-1) to get the cleaned up version of that column but as you can see in the result set sometimes the len is off by 1 and other times its off by 2.

Ideas/suggestions? Is there a charindex to find a carriage return or some value that I don't see?

#4

You need to identify what those extra characters are...to do that use the ASCII function: ASCII(RIGHT(column, 1))

Once you have identified the bad characters - use a replace to get rid of them:

REPLACE(column, char(13), '') --removes carriage return
REPLACE(REPLACE(column, char(13), ''), char(10), '') --remove carriage return and line feed

#5

Jeff,

just stumbled across that same syntax, updated my view and am good to go.

Thanks for the help!