Re: select query of mysql to postgres

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(dot)Friedrich-Fa-Trivadis(at)it(dot)nrw(dot)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.html
> http://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(at)gmail(dot)com]
> *Sent:* Friday, June 26, 2015 7:32 AM
> *To:* Burgholzer, Robert (DEQ)
> *Cc:* pgsql-admin(at)postgresql(dot)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(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;
>
>
>