Re: 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(dot)Burgholzer(at)deq(dot)virginia(dot)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(at)postgresql(dot)org [pgsql-admin-owner(at)postgresql(dot)org]
> on behalf of Ankur Kaushik [ankurkaushik(at)gmail(dot)com]
> *Sent:* Friday, June 26, 2015 4:13 AM
> *To:* pgsql-admin(at)postgresql(dot)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;
>