RE: selecting data with 2 different data types

Giri,

FWIW:

I have not had a consistant experience with cast in SQL on the as400.
Sometimes it would work and sometimes not.  So I always move or eval the
data into a workfield, then use the workfield in the SQL statement.

Phil

> -----Original Message-----
> From: rpg400-l-admin@midrange.com [mailto:rpg400-l-admin@midrange.com]On
> Behalf Of Giri Gopal
> Sent: Wednesday, March 13, 2002 4:17 PM
> To: rpg400-l@midrange.com
> Subject: RE: selecting data with 2 different data types
>
>
> I get the following error when I use cast within the sql:
>
> Selection error involving field TLYNBR.
>
> TLYNBR is the character field
>
>
> Thanks
> GG
>
> -----Original Message-----
> From: rpg400-l-admin@midrange.com [mailto:rpg400-l-admin@midrange.com]On
> Behalf Of DeLong, Eric
> Sent: Wednesday, March 13, 2002 12:58 PM
> To: 'rpg400-l@midrange.com'
> Subject: RE: selecting data with 2 different data types
>
>
> It really depends on your data.  If your 10a field was filled
> with a move op
> (move, movel), then the character representation of the numeric data would
> have leading zeros, which will mess you up.  SQL can convert numeric to
> char, but it trims off the leading zeros.  Perhaps you'd have better luck
> casting the char field into a decimal type for the compare.....
> See example
> of a cast:
>
> SELECT *
>       FROM table1 a, table2 b
>       WHERE cast(a.CHRVND# as decimal(5,0)) = b.NUMVND#
>
> hth
>
> Eric DeLong
> Sally Beauty Company
> MIS-Sr. Programmer/Analyst
> 940-898-7863 or ext. 1863
>
>
>
> -----Original Message-----
> From: Giri Gopal [mailto:GiriGopal@bcginK.com]
> Sent: Wednesday, March 13, 2002 11:48 AM
> To: rpg400-l@midrange.com
> Subject: selecting data with 2 different data types
>
>
>  Hello:
> I am select data from 2 different files. The where condition is comparing
> a.field with b.field.  A.Field is character 10 long and B.Field
> is Numeric 6
> byte long. Can I convert B.field to a character before comparing
> to A.field
> with a substring 1 to 6 bytes?
>
>
> Thanks for your help in advance
> GG
>
> _______________________________________________
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
> or email: RPG400-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>
> _______________________________________________
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
> or email: RPG400-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>
> _______________________________________________
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
> or email: RPG400-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com