October 24, 2008 at 12:52 pm
I am trying to create a query. Simplified, it looks like this
declare @wh int
set @wh=1;
select obitno, oaorsc
from hsdet inner join hshed on obhssq=oahssq
where oainvd>20050100
and convert(int,oaorsc)=@wh
Our oaorsc is our order source and the @wh is our warehouse ID. Before 2005, the order source was the salesman’s initials, such as ‘BL’. After 2005 they decided the order source should be the same as the warehouse. The problem is that when they set it up they made the order source 2 characters, ie 01,02,03 and the warehouse numbering system is 1,2,3 etc. So I thought I would convert the source to an integer and match them up that way. The problem I have is when I run the script above, even though I have the oainvd>20050100 filter, I still get an error referring to ‘omitted’ data.
‘SQL Server Database Error: Conversion failed when converting the nvarchar value 'BL' to data type int.’
So I thought I would create a temp table and apply the filter there instead and try it out, but still I get the same error. If I run the temp database by itself, I do not get any results with the value ‘BL’.
with hist(hitno, horsc ) as (select obitno, oaorsc
from hsdet inner join hshed on obhssq=oahssq
where oainvd>20050100)
select hitno, horsc
from hist
where convert(int,horsc)=@wh
order by horsc
I just do not understand how the rows with ‘BL’ are being referenced in the statement even with the filter to omit them.
Does anyone have any insight on this?
October 24, 2008 at 1:04 pm
This isn't necessarily the problem, but for arguments sake, run this:
SELECT *
FROM hsdet inner join hshed on obhssq=oahssq
where oainvd>20050100 AND NOT ISNUMERIC(oaorsc) = 1
October 24, 2008 at 1:23 pm
Gave it a shot with the isnumeric, but it still came back with the same error.
October 24, 2008 at 1:38 pm
I wasn't saying to put ISNUMERIC into your original query, only to check that all data above that breakpoint of 20050100 WAS numeric. (IE. Run that select and see if it returned anything)
October 24, 2008 at 1:44 pm
What is the result of this query (as asked earlier).
SELECT *
FROM hsdet inner join hshed on obhssq=oahssq
where oainvd>20050100 AND NOT ISNUMERIC(oaorsc) = 1
😎
October 24, 2008 at 1:49 pm
Gotcha. I ran the query like you have it. It comes back with 37,000+ rows. I modified it to show distinct oaorsc and it comes back with one result that is blank. Not null, but blank.
I tried to filter out the blank results, but I still get the same conversion error.
October 24, 2008 at 1:59 pm
Did you try adding the AND ISNUMERIC(oaorsc) = 1 to your CTE?
There are many ways of accomplishing this, the key is finding the most efficient.
eg. Selecting only numeric rows into a temp table first, STUFF'ing a varchar as @wh and comparing that instead of the int if your data is standardized in that way (possibly the most efficient)
October 24, 2008 at 2:15 pm
What are the data types for this two fields
obhssq
oahssq
😎
October 24, 2008 at 2:35 pm
Yes I added the isnumeric=1 to my CTE. If I ran this statement alone,
select obitno, convert(int,oaorsc) as orsc
from hsdet inner join hshed on obhssq=oahssq
where isnumeric(oaorsc)=1
I would get a series of rows with item numbers and source codes ranging from 1 to 14.
If I use that same script in my CTE and then reference it, I will get the conversion error. The only place the conversion is happening at is on the oaorsc column and the only column that 'BL' could exist is in the order source before the year 2005. I don't understand why it works when it stands alone, but doesn't when it becomes the CTE. It's just not making any sense to me.
Infact, I tried this as well and took out the variable and it works just fine.
declare @wh varchar(2)
set @wh='2';
with hist(hitno, horsc) as (select obitno, convert(int,oaorsc) as orsc
from hsdet inner join hshed on obhssq=oahssq
where isnumeric(oaorsc)=1)
select * from hist
but when I add the following line, I get the same conversion error.
where horsc=@wh
October 24, 2008 at 2:38 pm
Lynn Pettis (10/24/2008)
What are the data types for this two fieldsobhssq
oahssq
😎
that's the history sequence number to join up the order header file (hshed)with the order detail file(hsdet). They are both numeric primary keys.
October 24, 2008 at 2:42 pm
Just a guess, but try this:
select
obitno,
oaorsc
from
hsdet
inner join hshed
on (obhssq = oahssq
and oainvd > 20050100)
where
convert(int,oaorsc)=@wh
😎
October 24, 2008 at 2:51 pm
Lynn Pettis (10/24/2008)
Just a guess, but try this:
select
obitno,
oaorsc
from
hsdet
inner join hshed
on (obhssq = oahssq
and oainvd > 20050100)
where
convert(int,oaorsc)=@wh
😎
Tried it. Same conversion error.
October 24, 2008 at 3:05 pm
What are the results of this:
SELECT DISTINCT oaorsc
FROM hsdet inner join hshed on obhssq=oahssq
where oainvd>20050100
If the list is reasonably short, post the whole thing.
October 24, 2008 at 3:09 pm
Garadin (10/24/2008)
What are the results of this:
SELECT DISTINCT oaorscFROM hsdet inner join hshed on obhssq=oahssq
where oainvd>20050100
If the list is reasonably short, post the whole thing.
Here are the results. The first result above 01 is the '' result.
oaorsc
01
02
03
04
05
06
07
08
09
10
11
12
13
14
16
18
20
October 24, 2008 at 3:14 pm
Then I'd try this:
select
obitno,
oaorsc
from
hsdet
inner join hshed
on (obhssq = oahssq
and oainvd > 20050100)
where
oaorsc = right('0' + cast(@wh as varchar), 2)
😎
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply