[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
> (
>   codigopedidovarchar(20) 
>   codigohospital  varchar(20) 
>   fecha   varchar(15) 
>   productovarchar(20) 
>   unidadesvarchar(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   productounidades
> costo
> 0001001299100058  20050103759936  1
> 2375 
> 0001001299100058  20050103759928  1
> 2375 
> 0001001300100078  20050103759936  1
> 2375
> 0001003044100086  20050614759936  1
> 2275.25 
> 0001003128100062  20050623759936  2
> 4550.5 
> 0001003129100069  20050623759936  2
> 4550.5 
> 0001003131100019  20050623759928  5
> 11376.25 
> 0001003131100019  20050623759936  3
> 6825.75 
> 0001003132100075  20050623759936  4
> 9101 
> 0001003133100106  20050623759936  2
> 4550.5 
> 0001003134100031  20050623759936  2
> 4550.5 
> 0001003135100037  20050623759936  3
> 6825.75 
> 000100313619  20050623759936  5
> 11376.25 
> 000100313719  20050623759936  1
> 2275.25 
> 0001003138100024  20050623759936  4
> 9101 
<>
---(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

Re: [SQL] Clustering problem

[CG]

Why would you then drop the index? Performance and storage issues? I imagine
that I would cluster the table at regular intervals to maintain the ordering,
so I'd need to to keep the index around, yes?

--- PFC <[EMAIL PROTECTED]> wrote:

> 
> 
> > Is it even possible to cluster a table based on the clustering scheme  
> > (which is
> > not the link_id ...) from the master table?
> >
> > Can you gurus think of a better strategy? :) (Please??) :)
> 
>   You can create a functional index on a function which returns the 
> desired  
> order by looking in the main table, cluster it, then drop the index...
> 
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>choose an index scan if your joining column's datatypes do not
>match
> 





Sell on Yahoo! Auctions – no fees. Bid on great items.  
http://auctions.yahoo.com/

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings