Re: MySQL -> PostgreSQL conversion issue

On Fri, 29 Oct 2010 14:40:51 Tom Lane wrote:
> [ blanch... ] That's not the worst SQL code I've ever seen, but it
> might be in the top ten. They're apparently trying to see whether
> tf.dnid, taken as a string, matches a prefix of cdr.dnid --- but what if
> you have say 123 versus "1234foo"? This will match, but most likely
> it shouldn't. They need to acquire a clue, and a better data
> representation.

Agreed - it's not very pretty.

> Anyway, you've got two different typing violations there, so you need
> two casts to fix it:
>
> as tf on tf.dnid::text = substr(cdr.dnid,1,length(tf.dnid::text))

Unfortunately not:
from cc_call cdr left join cc_card cc on cdr.card_id=cc.id left join
(select 'dnid' as dnid, 0.1 as sell_cost,0.1 as cost,0
as dnid_type union select 8887798764,0.02,0.06 ,1
) as tf on
tf.dnid::text=substr(cdr.dnid,1,length(tf.dnid::text))

still gives

2010-10-29 14:58:09 NZDT ERROR: invalid input syntax for integer: "dnid"

Thanks

--
Regards
Scott Newton
Software Engineer @ Vadacom Limited
Ph: (09) 9690600 x 280