Re: Compare Character to numberic

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

AUTH1 AREA DOOR REC DATE REC TIME COST SLIP1 ORDER ID
4986 03 3807 10/13/05 18:36:00 $32.30 384052 G2834
2752 08 1404 10/13/05 17:24:00 $113.20 934856 F3948
2130 06 208 10/13/05 12:54:00 $3.90 192857 K3948
2132 06 210 10/13/05 12:42:00 $4.30 384956 L3049
2752 08 1404 10/13/05 18:38:00 $42.00 192784 G2834
4986 03 3807 10/13/05 18:41:00 $1.24 49581 F3948
2765 08 1417 10/13/05 16:44:00 $4.33 792734 K3948
4438 06 212 10/13/05 3:17:00 $2.44 395867 L3049
5170 05 3603 10/13/05 5:31:00 $129.35 374856 K4856
Auth1 and SLIP1 are defined as numberic


STST2(PRN2 is defined as 132 character)

AUTH2 PRN2
2132 ORDERI 384956 10/14/05 L3049
2752 ORDERI 192784 10/15/05 G2834

Final Line

AUTH1 AREA DOOR REC TIME COST SLIP1 ORDER ID CONFIRMED
2132 06 210 12:42:00 $4.30 384956 L3049 ORDERI 384956 L3049
2752 08 1404 18:38:00 $42.00 192784 G2834 ORDERI 192784 G2834



"Hugo Kornelis" wrote:

> 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)
>
.

Relevant Pages

  • Re: Trouble with multiple tables and linked relationships
    ... "Hugo Kornelis" wrote: ... > Hi Dave, ... > I fail to see how the query you posted could return more than one row for ... as based on your sample data. ...
    (microsoft.public.sqlserver.programming)
  • Re: Compare Character to numberic
    ... >I have to join two table based on the matching of two fields. ... >STST2.PRN2 is defined as CHARACTER ... The query you posted will work. ... few rows of sample data to illustrate the problem (posted as INSERT ...
    (microsoft.public.sqlserver.mseq)
  • Re: WHERE EXISTS query help
    ... The query also seems ... redundant to me but this is what I ended up with to get the desired (for the ... I'll put together the table structure and sample data and post it again. ... "Hugo Kornelis" wrote: ...
    (microsoft.public.sqlserver.programming)
  • Re: Search text field for all 1s
    ... Hugo Kornelis wrote: ... > That would match any string containing at least one character '1'. ... > original poster wanted a query to find strings that contain ONLY the ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL*PLUS select statment
    ... single column; Here is an example: ... Another bad habit is to store dates as character strings (rely as well ... with the query and modify it to the way I need it and I could not get ... dynamic data back with that said how could I turn this into a dynamic ...
    (comp.databases.oracle.misc)