Thread: select query of mysql to postgres

select query of mysql to postgres

From
Ankur Kaushik
Date:
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;

Re: select query of mysql to postgres

From
"Burgholzer, Robert (DEQ)"
Date:
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;

Re: select query of mysql to postgres

From
Ankur Kaushik
Date:

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;


Re: select query of mysql to postgres

From
"Burgholzer, Robert (DEQ)"
Date:
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




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;


Re: select query of mysql to postgres

From
Date:

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;

 

Re: select query of mysql to postgres

From
Matheus de Oliveira
Date:

On Fri, Jun 26, 2015 at 9:06 AM, <Holger.Friedrich-Fa-Trivadis@it.nrw.de> wrote:
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.)

Neither exists. There is no concept of variables in plain SQL (there is in procedural languages though).


--
Matheus de Oliveira


Re: select query of mysql to postgres

From
Jan Lentfer
Date:
@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



Re: select query of mysql to postgres

From
Ankur Kaushik
Date:
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;

 


Re: select query of mysql to postgres

From
"David G. Johnston"
Date:
On Fri, Jun 26, 2015 at 7:58 AM, Burgholzer, Robert (DEQ) <Robert.Burgholzer@deq.virginia.gov> wrote:

The function that performs equivalently is "COALESCE"

David J.​