Обсуждение: select query of mysql to postgres

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;

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;