Re: Compare Character to numberic

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: Instr() problem?
    ... If you only want to test starting with the sixth character: ... > My stumbling point *seems* to be matching StrName to a portion of MlgAddr. ... > Instead of trying to do all this in just one query, I've created several, ...
    (microsoft.public.access.queries)
  • Re: Compare Character to numberic
    ... STST2(PRN2 is defined as 132 character) ... "Hugo Kornelis" wrote: ... > The query you posted will work. ... > few rows of sample data to illustrate the problem (posted as INSERT ...
    (microsoft.public.sqlserver.mseq)
  • 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)
  • Re: Newbie performance/design question
    ... > character. ... just need to speed up this particular query, ... Pro SQL Server 2000 Database Design - ... > My first thought is to create a new field composed of the above,> concatenated together and separated by perhaps a '|' vertical bar ...
    (microsoft.public.sqlserver.programming)
  • Re: How to trim leading spaces that are not removed by Trim
    ... Just create a new query that is only used to determine the ... imported table and adding a field with the funny character ... as its first character to the field list. ... Then try running the Update query using the value from the ...
    (microsoft.public.access.queries)