Re: select query of mysql to postgres - Mailing list pgsql-admin

From Ankur Kaushik
Subject Re: select query of mysql to postgres
Date
Msg-id CALXoLqyv3JJazg92MNhpJ+fKePtwCfB4YdsrArkbaVp=0jTtdg@mail.gmail.com
Whole thread Raw
In response to Re: select query of mysql to postgres  (<Holger.Friedrich-Fa-Trivadis@it.nrw.de>)
List pgsql-admin
Please check this is in readable format

select 
bs.toll_zone,
bs.toll_fee,
concat(r.route_number,if(r.route_direction='UP','UP','DN')) route_number,
route_order,
r.route_id,
ifnull(bs.bus_stop_code, bs.bus_stop_id) bus_stop_code,
ifnull(bs.bus_stop_name, '') bus_stop_name,
(ifnull(bs.bus_stop_name_nudi, '')) bus_stop_name_nudi,
bs.bus_stop_id, ifnull(bs.alias1, '') alias1,
IF( if(ifnull(rm.schedule_distance, 0)='0',
ifnull(rm.distance, 0),
ifnull(rm.schedule_distance, 0)) IS NULL,
@tot_dur := @tot_dur,
@tot_dur := @tot_dur + if(ifnull(rm.schedule_distance, 0)='0',
ifnull(rm.distance, 0),ifnull(rm.schedule_distance, 0)) )/1000 AS tot_dist,
ifnull(rp.sub_stage,'N') sub_stage, ifnull(rp.fare_stage,'N') fare_stage,
a.stage_count,
rmd.adult,
rmd.children,
rmd.senior_citizen
from route r
JOIN (SELECT @tot_dur := 0) d
inner join route_point rp on rp.route_id = r.route_id 
inner join bus_stop bs on bs.bus_stop_id=rp.bus_stop_id and bs.point_type_id!=2 
left join route_map rm on rm.start_bus_stop_id=rp.bus_stop_id and rp.route_id='3004' and rm.route_id='3004' 
INNER JOIN (select route_id, route_points_id, if(if(rp.fare_stage='Y',1,0) IS NULL,
@stage_count := @stage_count,@stage_count := @stage_count+if(rp.fare_stage='Y',1,0)) as stage_count from route_point rp 
JOIN (SELECT @stage_count := 0) e 
where rp.route_id='3004' ) a ON rp.route_points_id = a.route_points_id 
INNER JOIN rate_master_details rmd ON a.stage_count = rmd.stage_no AND rmd.rate_master_id='12' 
where r.route_id='3004' AND bs.point_type_id NOT IN ('2', '13')  order by r.route_id;

On Fri, Jun 26, 2015 at 5:36 PM, <Holger.Friedrich-Fa-Trivadis@it.nrw.de> wrote:

Burgholzer, Robert (DEQ) wrote on Friday, June 26, 2015 1:59 PM:

Ø  So this seems to be saying that postgres does not have the ifnull function: http://www.postgresql.org/docs/9.2/static/functions-conditional.htmlhttp://www.postgresql.org/docs/9.2/static/functions-conditional.html

 

No, it seems to be saying that PostgreSQL did not understand the @tot_dur := @tot_dur part.

 

If you format the error output with a fixed-pitch font, the ^ points exactly to the := operator.  (It seems to me that neither @ nor := exists in PostgreSQL.)

 

LINE 8: ...fnull(rm.schedule_distance, 0)) IS NULL, @tot_dur := @tot_du...

                                                             ^

 

                Best regards,

Holger Friedrich

 


From: Ankur Kaushik [ankurkaushik@gmail.com]
Sent: Friday, June 26, 2015 7:32 AM
To: Burgholzer, Robert (DEQ)
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] select query of mysql to postgres

 

Below is the error while executing the query.

 

ERROR:  syntax error at or near ":="

LINE 8: ...fnull(rm.schedule_distance, 0)) IS NULL, @tot_dur := @tot_du...

                                                             ^

 

********** Error **********

 

ERROR: syntax error at or near ":="

SQL state: 42601

Character: 474

 

On Fri, Jun 26, 2015 at 4:33 PM, Burgholzer, Robert (DEQ) <Robert.Burgholzer@deq.virginia.gov> wrote:

Two things - 1) Off the top of my head I would guess that the "@" symbols refer to something that is mysql specific (or in my limited postgresql experience, I have not seen/used it), 2) you should include an error message, cause that will tell you what type of error and approximate location, 3) to get help on this it may help other people if you formatted your query in a way that was more legible.  For example:

 

select bs.toll_zone, bs.toll_fee, 

  concat(r.route_number,if(r.route_direction='UP','UP','DN')) route_number, 

  route_order, r.route_id, 

  ifnull(bs.bus_stop_code, bs.bus_stop_id) bus_stop_code, 

  ifnull(bs.bus_stop_name, '') bus_stop_name, 

  (ifnull(bs.bus_stop_name_nudi, '')) bus_stop_name_nudi, 

  bs.bus_stop_id, ifnull(bs.alias1, '') alias1, 

  IF( if(ifnull(rm.schedule_distance, 0)='0',ifnull(rm.distance, 0),ifnull(rm.schedule_distance, 0)) IS NULL, @tot_dur := @tot_dur, @tot_dur := @tot_dur + if(ifnull(rm.schedule_distance, 0)='0',ifnull(rm.distance, 0),ifnull(rm.schedule_distance, 0)) )/1000 AS tot_dist, ifnull(rp.sub_stage,'N') sub_stage, ifnull(rp.fare_stage,'N') fare_stage,a.stage_count, rmd.adult, rmd.children, rmd.senior_citizenfrom route r JOIN (SELECT @tot_dur := 0) d inner join route_point rp on rp.route_id = r.route_id inner join bus_stop bs on bs.bus_stop_id=rp.bus_stop_id and bs.point_type_id!=2 left join route_map rm on rm.start_bus_stop_id=rp.bus_stop_id and rp.route_id='3004' and rm.route_id='3004' INNER JOIN (select route_id, route_points_id, if(if(rp.fare_stage='Y',1,0) IS NULL,@stage_count := @stage_count,@stage_count := @stage_count+if(rp.fare_stage='Y',1,0)) as stage_count from route_point rp JOIN (SELECT @stage_count := 0) e where rp.route_id='3004' ) aON rp.route_points_id = a.route_points_id INNER JOIN rate_master_details rmd ON a.stage_count = rmd.stage_no AND rmd.rate_master_id='12'where r.route_id='3004' AND bs.point_type_id NOT IN ('2', '13')  order by r.route_id;

 


From: pgsql-admin-owner@postgresql.org [pgsql-admin-owner@postgresql.org] on behalf of Ankur Kaushik [ankurkaushik@gmail.com]
Sent: Friday, June 26, 2015 4:13 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] select query of mysql to postgres

I Have below query of mysql which is not executing in postgresql , What changes need to do to execute in postgressql database

 

select bs.toll_zone, bs.toll_fee, concat(r.route_number,if(r.route_direction='UP','UP','DN')) route_number, route_order, r.route_id, ifnull(bs.bus_stop_code, bs.bus_stop_id) bus_stop_code, ifnull(bs.bus_stop_name, '') bus_stop_name, (ifnull(bs.bus_stop_name_nudi, '')) bus_stop_name_nudi, bs.bus_stop_id, ifnull(bs.alias1, '') alias1, IF( if(ifnull(rm.schedule_distance, 0)='0',ifnull(rm.distance, 0),ifnull(rm.schedule_distance, 0)) IS NULL, @tot_dur := @tot_dur, @tot_dur := @tot_dur + if(ifnull(rm.schedule_distance, 0)='0',ifnull(rm.distance, 0),ifnull(rm.schedule_distance, 0)) )/1000 AS tot_dist, ifnull(rp.sub_stage,'N') sub_stage, ifnull(rp.fare_stage,'N') fare_stage,a.stage_count, rmd.adult, rmd.children, rmd.senior_citizenfrom route r JOIN (SELECT @tot_dur := 0) d inner join route_point rp on rp.route_id = r.route_id inner join bus_stop bs on bs.bus_stop_id=rp.bus_stop_id and bs.point_type_id!=2 left join route_map rm on rm.start_bus_stop_id=rp.bus_stop_id and rp.route_id='3004' and rm.route_id='3004' INNER JOIN (select route_id, route_points_id, if(if(rp.fare_stage='Y',1,0) IS NULL,@stage_count := @stage_count,@stage_count := @stage_count+if(rp.fare_stage='Y',1,0)) as stage_count from route_point rp JOIN (SELECT @stage_count := 0) e where rp.route_id='3004' ) aON rp.route_points_id = a.route_points_id INNER JOIN rate_master_details rmd ON a.stage_count = rmd.stage_no AND rmd.rate_master_id='12'where r.route_id='3004' AND bs.point_type_id NOT IN ('2', '13')  order by r.route_id;

 


pgsql-admin by date:

Previous
From: Jan Lentfer
Date:
Subject: Re: select query of mysql to postgres
Next
From: "David G. Johnston"
Date:
Subject: Re: select query of mysql to postgres