Skip site navigation (1) Skip section navigation (2)

MySQL -> PostgreSQL conversion issue

From: Scott Newton <scott(dot)newton(at)vadacom(dot)co(dot)nz>
To: pgsql-general(at)postgresql(dot)org
Subject: MySQL -> PostgreSQL conversion issue
Date: 2010-10-29 00:44:26
Message-ID: 201010291344.26932.scott.newton@vadacom.co.nz (view raw or flat)
Thread:
Lists: pgsql-general
Hi

I have the following rather complicated SQL which works under MySQL but fails 
under PostgreSQL 8.3. The SQL is generated by A2Billing 
(http://www.asterisk2billing.org/cgi-bin/trac.cgi). The issue is the following 
part of the SQL:

as tf on tf.dnid=substr(cdr.dnid,1,length(tf.dnid))

where tf.dnid is a bigint and cdr.dnid is varchar(40). The error returned is 
ERROR:  function length(bigint) does not exist at character 2521
2010-10-29 13:34:27 NZDT HINT:  No function matches the given name and 
argument types. You might need to add explicit type casts.

I have tried adding explicit casts:

tf.dnid = substr(cdr.dnid, 1, length(cast(td.dnid) as varchar(40)))
which complains with
ERROR:  operator does not exist: bigint = text at character 2502

and

tf.dnid = cast(substr(cdr.dnid, 1, length(cast(td.dnid) as varchar(40))) as 
bigint)
which complains with
ERROR:  invalid input syntax for integer: "dnid"

What would be the best way to convert this to work under PostgreSQL?

The complete log entry is given below:

Thanks

2010-10-29 13:34:27 NZDT ERROR:  function length(bigint) does not exist at 
character 2521
2010-10-29 13:34:27 NZDT HINT:  No function matches the given name and 
argument types. You might need to add explicit type casts.
2010-10-29 13:34:27 NZDT STATEMENT:  create temporary table pnl_report  as 
        select  
id,name,call_count,time_minutes,tall_free_buy_cost,pay_phone_buy_cost,orig_only,credits,orig_total,
                tall_free_sell_cost,pay_phone_sell_cost,term_only,charges,term_total,   
first_use,discount,
                net_revenue,(net_revenue-orig_total) as profit, (net_revenue-
orig_total)/net_revenue*100 as  margin
        from(
        select main_id as id, 
name,call_count,time_minutes,tall_free_buy_cost,pay_phone_buy_cost,orig_only,credits,orig_cost+credits 
as orig_total,
                tall_free_sell_cost,pay_phone_sell_cost,term_only,charges,term_cost+charges 
as term_total,
                first_use,discount,
                ((term_cost+charges))*( 1-discount/100)            as 
net_revenue
        from(
         select  t1.id_group as 
main_id,cg.name,call_count,time_minutes,tall_free_buy_cost,pay_phone_buy_cost,
                orig_cost-tall_free_buy_cost-pay_phone_buy_cost  as 
orig_only,orig_cost,
                case when credits is null then 0 else credits end as credits,0 
as total,
                tall_free_sell_cost,pay_phone_sell_cost,term_cost-
tall_free_sell_cost-pay_phone_sell_cost as term_only,term_cost,
                case when charges is null then 0 else  charges end as  
charges,
                first_use,discount

         from
         (
          select  id_group,count(*) as call_count ,sum(sessiontime) / 60 as 
time_minutes,
                sum( case when tall_free=0 then 0 else 
real_sessiontime/60*tf_cost end) as tall_free_buy_cost,
                sum( case when pay_phone=0 then 0 else 
real_sessiontime/60*tf_cost end) as pay_phone_buy_cost,
                sum(buycost) as orig_cost,
                sum( case when tall_free=0 then 0 else 
real_sessiontime/60*tf_sell_cost end) as tall_free_sell_cost,
                sum( case when pay_phone=0 then 0 else 
real_sessiontime/60*tf_sell_cost end) as pay_phone_sell_cost,
                sum(sessionbill) as term_cost,
                sum(discount*sessionbill)/sum(sessionbill) as discount
          from (
           select cc.id_group,
               cdr.sessiontime,cdr.dnid,cdr.real_sessiontime,sessionbill,buycost,cc.discount,
                   case when tf.cost is null then 0 else tf.cost end as 
tf_cost,
                    case when tf.sell_cost is null then 0 else tf.sell_cost 
end as tf_sell_cost,
                        case when tf.dnid_type is null then 0 when 
tf.dnid_type=1 then 1 else 0 end as tall_free,
                        case when tf.dnid_type is null then 0 when 
tf.dnid_type=2 then 1 else 0 end as pay_phone
                    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=substr(cdr.dnid,1,length(tf.dnid))
          where
            sessiontime>0 and CURRENT_TIMESTAMP - interval '6 hours' <= 
cdr.starttime 
            order by cdr.starttime desc
           ) as a group by id_group
         ) as t1  left join cc_card_group as cg on cg.id=id_group left join 
pnl_report_sub1 as t2 on t1.id_group=t2.id_group 
                   left join pnl_report_sub2  as t3 on t1.id_group=t3.id_group
                   left join  pnl_report_sub3 as t4 on t1.id_group=t4.id_group
                 )as result
                )as final
         
2010-10-29 13:34:27 NZDT ERROR:  relation "pnl_report" does not exist
2010-10-29 13:34:27 NZDT STATEMENT:  SELECT  
name,call_count,time_minutes,tall_free_buy_cost,pay_phone_buy_cost,orig_only,credits,orig_total,
                tall_free_sell_cost,pay_phone_sell_cost,term_only,charges,term_total,   
first_use,discount,
                net_revenue,  margin, profit, id, id FROM pnl_report ORDER BY 
id ASC LIMIT 10 OFFSET 0
2010-10-29 13:34:27 NZDT ERROR:  relation "pnl_report" does not exist
2010-10-29 13:34:27 NZDT STATEMENT:  SELECT count(*) FROM pnl_report
2010-10-29 13:34:27 NZDT ERROR:  relation "pnl_report" does not exist
2010-10-29 13:34:27 NZDT STATEMENT:  select 
'TOTAL',sum(call_count),sum(time_minutes),sum(tall_free_buy_cost),sum(pay_phone_buy_cost),sum(orig_only),sum(credits),sum(orig_total),sum(
        tall_free_sell_cost),sum(pay_phone_sell_cost),sum(term_only),sum(charges),sum(term_total),sum(first_use),
(1-sum(net_revenue)/sum(term_total))*100,sum(
        net_revenue),sum(profit)/sum(net_revenue)*100,sum(profit),sum(id) from 
pnl_report

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

Responses

pgsql-general by date

Next:From: Scott MarloweDate: 2010-10-29 01:31:26
Subject: Re: MySQL -> PostgreSQL conversion issue
Previous:From: Gregory MachinDate: 2010-10-28 22:42:10
Subject: Adivice on master - master replication.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group