[SQL] PostgreSQL - ERROR: Cannot cast type character varying to integer

[Mauricio Fernandez]

Hello I´m new in the list, and I want to learn a lot about postgresql, now
I´m working in a database and I have this problem:

I have a table called historicoventas as follows (just the structure)

>  historicoventas
> (
>   codigopedido                varchar(20) 
>   codigohospital              varchar(20) 
>   fecha                       varchar(15) 
>   producto            varchar(20) 
>   unidades            varchar(10) 
>   costo                       varchar(20)   
> ) 
> 
And I want to create a view based on that table whit this instruction:

> CREATE OR REPLACE VIEW vw_temphistoricoventas 
> (Pedido, Fecha, Hospital, Producto, Unidades, Costo) 
> AS 
> SELECT codigoPedido , (substring( fecha from 1 for 4 ) || '-'  
> || substring( fecha from 5 for 2 ) || '-' 
> || substring( fecha from 7 for 2 ) ) AS fecha,
> codigoHospital , producto , sum( unidades::int4 ) AS unidades, sum(
> costo::float4 ) AS costo 
> FROM historicoventas 
> WHERE ((producto = '759928') OR (producto = '759936') OR 
> (producto = '4002382') OR (producto = '4002580')) 
> GROUP BY codigoPedido, fecha, codigoHospital, producto 
> ORDER BY fecha;
But I always get this message:
> ERROR:  Cannot cast type character varying to integer
My developer workstation is under win xp and postgresql 8.0 but in
production server (external web hosting partner) I have a Linux Red Hat with
kernel 2.4.21-27.0.2.ELsmp y PostgreSQL 7.3.
In local machine all castings between data types works fine but in server it
doesn´t.
convertions like sum(unidades::int4)  works fine and reading the postgresql
7.3 docs I can see this type of convertions are enabled by the system
Can somebody help me with this topic?

Thanks a lot

>  
> Mauricio Fernández A.
> Ingeniero de Sistemas
> Universidad Autónoma de Manizales
> [EMAIL PROTECTED]
> movil: 607365968
>  
> P.D.  Some historicoventas table rows can be:
> codPedido     codHospital     fecha           producto        unidades
> costo
> 0001001299    100058          20050103        759936          1
> 2375 
> 0001001299    100058          20050103        759928          1
> 2375 
> 0001001300    100078          20050103        759936          1
> 2375
> 0001003044    100086          20050614        759936          1
> 2275.25 
> 0001003128    100062          20050623        759936          2
> 4550.5 
> 0001003129    100069          20050623        759936          2
> 4550.5 
> 0001003131    100019          20050623        759928          5
> 11376.25 
> 0001003131    100019          20050623        759936          3
> 6825.75 
> 0001003132    100075          20050623        759936          4
> 9101 
> 0001003133    100106          20050623        759936          2
> 4550.5 
> 0001003134    100031          20050623        759936          2
> 4550.5 
> 0001003135    100037          20050623        759936          3
> 6825.75 
> 0001003136    100009          20050623        759936          5
> 11376.25 
> 0001003137    100009          20050623        759936          1
> 2275.25 
> 0001003138    100024          20050623        759936          4
> 9101 

<<attachment: winmail.dat>>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly