Compare Character to numberic

I have to join two table based on the matching of two fields. The bad part
is one field is numeric but defined as Character and the other field is
decmial. This is my SQL.
FROM
SLTEST1.STST1 STST1
LEFT OUTER JOIN SLTEST1.STST2 STST2
ON
STST1.AUTH1 = STST2.AUTH2 AND
STST1.SLIP1 = SUBSTRING( STST2.PRN2, 7, 7 )
STST1.SLIP1 is defined as NUMERIC
STST2.PRN2 is defined as CHARACTER
Any help will be appreciated.
On Tue, 25 Oct 2005 16:11:01 -0700, Daniell wrote:

>I have to join two table based on the matching of two fields. The bad part
>is one field is numeric but defined as Character and the other field is
>decmial. This is my SQL.
>FROM
>SLTEST1.STST1 STST1
>LEFT OUTER JOIN SLTEST1.STST2 STST2
>ON
>STST1.AUTH1 = STST2.AUTH2 AND
>STST1.SLIP1 = SUBSTRING( STST2.PRN2, 7, 7 )
>STST1.SLIP1 is defined as NUMERIC
>STST2.PRN2 is defined as CHARACTER
>Any help will be appreciated.
Hi Daniell,
I'll gladly help, but it's not clear fto me what the question is.
The query you posted will work. That is, it will
1. Take positions 7 up to and including 13 of the character string in
STST2.PRN2;
2. Attempt to convert these 7 positions to decimal - if this fails, the
query will be terminated with an error condition;
3. Check if the same value is in STST1.SLIP1.
Is this what you want? Is the query executing as expected?
If you need further help, you'll have to provide more detailed
information. The most important is the structure of your tables (posted
as CREATE TABLE statements, including all constraints and properties), a
few rows of sample data to illustrate the problem (posted as INSERT
statements), and the output you expect from the given sample data.
Check out www.aspfaq.com/5006 as well.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||I am new at this and am not sure I am doing it correct. I have attempted to
convert the fields to numeric but receive the same error:
Character in cast argument not valid
This is sample data:
STST1
AUTH1AREADOORREC DATEREC TIMECOSTSLIP1ORDER ID
498603380710/13/0518:36:00$32.30 384052G2834
275208140410/13/0517:24:00$113.20 934856F3948
21300620810/13/0512:54:00$3.90 192857K3948
21320621010/13/0512:42:00$4.30 384956L3049
275208140410/13/0518:38:00$42.00 192784G2834
498603380710/13/0518:41:00$1.24 49581F3948
276508141710/13/0516:44:00$4.33 792734K3948
44380621210/13/053:17:00$2.44 395867L3049
517005360310/13/055:31:00$129.35 374856K4856
Auth1 and SLIP1 are defined as numberic
STST2(PRN2 is defined as 132 character)
AUTH2PRN2
2132ORDERI 384956 10/14/05 L3049
2752ORDERI 192784 10/15/05 G2834
Final Line
AUTH1AREADOORREC TIME COSTSLIP1ORDER ID CONFIRMED
21320621012:42:00 $4.30 384956L3049 ORDERI 384956 L3049
275208140418:38:00 $42.00 192784G2834 ORDERI 192784 G2834
"Hugo Kornelis" wrote:

> On Tue, 25 Oct 2005 16:11:01 -0700, Daniell wrote:
>
> Hi Daniell,
> I'll gladly help, but it's not clear fto me what the question is.
> The query you posted will work. That is, it will
> 1. Take positions 7 up to and including 13 of the character string in
> STST2.PRN2;
> 2. Attempt to convert these 7 positions to decimal - if this fails, the
> query will be terminated with an error condition;
> 3. Check if the same value is in STST1.SLIP1.
> Is this what you want? Is the query executing as expected?
> If you need further help, you'll have to provide more detailed
> information. The most important is the structure of your tables (posted
> as CREATE TABLE statements, including all constraints and properties), a
> few rows of sample data to illustrate the problem (posted as INSERT
> statements), and the output you expect from the given sample data.
> Check out www.aspfaq.com/5006 as well.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Fri, 28 Oct 2005 11:35:03 -0700, Daniell wrote:

>I am new at this and am not sure I am doing it correct. I have attempted to
>convert the fields to numeric but receive the same error:
> Character in cast argument not valid
Hi Daniell,
If you try to convert the complete STST2.PRN2 column to numeric, than
this error is to be expected, since there's non-numeric data in that
column. If you try to convert SUBSTRING(STST2.PRN2, 7, 7) to numeric,
than I don't understand - based on the data below, this should run
without error.
(snip)
>STST2(PRN2 is defined as 132 character)
>AUTH2PRN2
>2132ORDERI 384956 10/14/05 L3049
>2752ORDERI 192784 10/15/05 G2834
This might explain why you're not getting the desired results. If I
count characters in PRN2, the 7th character is the first space after
ORDERI. Taking 7 characters from there, I get 4 spaces and "384" for the
first row, or four spaces and "192" for the second row.
Maybe you should change the substring expression to
SUBSTRING(STST2.PRN2, 7, 10)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,
Maybe I am doing to conversion wrong or maybe at the wrong place. I am
attempting to do it on the join command using the cast verb.
FROM
SLTEST1.STST1 STST1
LEFT OUTER JOIN SLTEST1.STST2 STST2
ON
STST1.AUTH1 = STST2.AUTH2 AND
STST1.SLIP1 = CAST( SUBSTRING( STST2.PRN2, 7, 7 ) as numeric )
"Hugo Kornelis" wrote:

> On Fri, 28 Oct 2005 11:35:03 -0700, Daniell wrote:
>
> Hi Daniell,
> If you try to convert the complete STST2.PRN2 column to numeric, than
> this error is to be expected, since there's non-numeric data in that
> column. If you try to convert SUBSTRING(STST2.PRN2, 7, 7) to numeric,
> than I don't understand - based on the data below, this should run
> without error.
> (snip)
> This might explain why you're not getting the desired results. If I
> count characters in PRN2, the 7th character is the first space after
> ORDERI. Taking 7 characters from there, I get 4 spaces and "384" for the
> first row, or four spaces and "192" for the second row.
> Maybe you should change the substring expression to
> SUBSTRING(STST2.PRN2, 7, 10)
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Mon, 31 Oct 2005 09:51:37 -0800, Daniell wrote:

>Hugo,
>Maybe I am doing to conversion wrong or maybe at the wrong place. I am
>attempting to do it on the join command using the cast verb.
>FROM
>SLTEST1.STST1 STST1
>LEFT OUTER JOIN SLTEST1.STST2 STST2
>ON
>STST1.AUTH1 = STST2.AUTH2 AND
>STST1.SLIP1 = CAST( SUBSTRING( STST2.PRN2, 7, 7 ) as numeric )
Hi Daniell,
If there is no bad data in the table, then this should work. With the
data you posted in an earlier message, this should produce no error
(though you wouldn't get any matches on the join condition, as I
explained in my previous message).
If you want me to help you investigate this further, then please do the
following:
1. Copy and paste the output of SELECT @.@.VERSION in your reply.
2. Also copy and paste the EXACT and COMPLETE text of the error message
you get when running your query from Query Analyzer (if any).
3. Post the complete CREATE TABLE statements for the tables used in the
query. Check www.aspfaq.com/5006 for how you can get the table
definition scripted.
4. Post INSERT statements with some sample data. If the amount of data
in your test database is not prohibitively large, post the complete set
of data (check the link to Vyas' script in www.aspfaq.com/5006).
5. If the query doesn't result in an error but in incorrect output, then
include the expected output from the query as well.
Without the information above, I really can't help you any further than
I've already done.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo, I am going to look at it a little closer and maybe find a few
books to help me understand the working of SQL a little better because the
table it a large one. I appreciate the help.
"Hugo Kornelis" wrote:

> On Mon, 31 Oct 2005 09:51:37 -0800, Daniell wrote:
>
> Hi Daniell,
> If there is no bad data in the table, then this should work. With the
> data you posted in an earlier message, this should produce no error
> (though you wouldn't get any matches on the join condition, as I
> explained in my previous message).
> If you want me to help you investigate this further, then please do the
> following:
> 1. Copy and paste the output of SELECT @.@.VERSION in your reply.
> 2. Also copy and paste the EXACT and COMPLETE text of the error message
> you get when running your query from Query Analyzer (if any).
> 3. Post the complete CREATE TABLE statements for the tables used in the
> query. Check www.aspfaq.com/5006 for how you can get the table
> definition scripted.
> 4. Post INSERT statements with some sample data. If the amount of data
> in your test database is not prohibitively large, post the complete set
> of data (check the link to Vyas' script in www.aspfaq.com/5006).
> 5. If the query doesn't result in an error but in incorrect output, then
> include the expected output from the query as well.
> Without the information above, I really can't help you any further than
> I've already done.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>