Re: SQL bombing out on me, any ideas what is wrong

Interesting.

First, I don't know about that cast error. Types are usually in the
range of high 300's to 600 or in the 900 range.

So what is the type of the column VHUSR1? It can't be a time, date or
timestamp.

Second, are you sure it is the integer statement? The second statement
is "join less" and it may be that the implied join that is happening, is
between incompatible IREF or VEND fields?


"Mark A. Manske" wrote:
> 
> The following SQL does not work, until I pull the "integer" statement out
> 
> SELECT DISTINCT
>         VEVEND,
>         VENAME,
>         VHINVN,
>         VHIDT8,
>         INTEGER( VHUSR1 ) AS "Cig Count"
> FROM
>         PLDBFA.PLPVD PLPVD,
>         PLDBFA.PLPVH PLPVH,
>         PLDBFA.PLPVE PLPVE
> WHERE
>         PLPVD.VDIREF = PLPVH.VHIREF
>         AND PLPVD.VDVEND = PLPVE.VEVEND
> 
> The message is CAST from C01060001 to C01980005 not supported.
> 
> However; if I do the following SQL, I get the desired conversion of the
> alpha field to a numeric field.
> 
> SELECT
>         VHVEND,
>         VHIDT8,
>         VHIREF,
>         INTEGER( VHUSR1 ) AS "Cig Count"
> FROM
>         PLDBFA.PLPVH PLPVH
> 
> We are now on V4R4 as of two weeks ago, the SQL worked fine on V4R2,
> our current cum PTF level is C9229440.
> 
> Thank you for the help; this list has been a life saver more than once.
> 
> Mark A. Manske     Sr. Project Lead     Minter-Weisman
> 
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator: david@midrange.com
> +---

-- 
===========================================================
R. Bruce Hoffman, Jr.
 -- IBM Certified AS/400 Professional System Administrator
 -- IBM Certified AS/400 Professional Network Administrator

"The sum of all human knowledge is a fixed constant.
    It's the population that keeps growing!"
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---