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

@VAR is T-SQL, Postgres does not support this. You will need to rewrite
the whole thing.

https://en.wikipedia.org/wiki/Transact-SQL

Regards

Jan


Am 2015-06-26 14:06, schrieb Holger.Friedrich-Fa-Trivadis@it.nrw.de:
> 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
> [1]
>
> 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 [2]
> 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 [3]> 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 [4]
> [pgsql-admin-owner@postgresql.org [5]] on behalf of Ankur Kaushik
> [ankurkaushik@gmail.com [6]]
> SENT: Friday, June 26, 2015 4:13 AM
> TO: pgsql-admin@postgresql.org [7]
> 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;
>
>
>
> Links:
> ------
> [1]
> http://www.postgresql.org/docs/9.2/static/functions-conditional.html
> [2] mailto:pgsql-admin@postgresql.org
> [3] mailto:Robert.Burgholzer@deq.virginia.gov
> [4] mailto:pgsql-admin-owner@postgresql.org
> [5] mailto:pgsql-admin-owner@postgresql.org
> [6] mailto:ankurkaushik@gmail.com
> [7] mailto:pgsql-admin@postgresql.org