Обсуждение: select query of mysql to postgres
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;
Sent: Friday, June 26, 2015 4:13 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] select query of mysql to postgres
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;
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 postgresI Have below query of mysql which is not executing in postgresql , What changes need to do to execute in postgressql databaseselect 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;
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
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 postgresI Have below query of mysql which is not executing in postgresql , What changes need to do to execute in postgressql databaseselect 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;
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.)
--
@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
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 postgresI 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;
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