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@xxxxxxxxx]
SENT: Friday, June 26, 2015 7:32 AM
TO: Burgholzer, Robert (DEQ)
CC: pgsql-admin@xxxxxxxxxxxxxx [2]
SUBJECT: 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.Burgholzer@xxxxxxxxxxxxxxxx [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@xxxxxxxxxxxxxx [4]
[pgsql-admin-owner@xxxxxxxxxxxxxx [5]] on behalf of Ankur Kaushik
[ankurkaushik@xxxxxxxxx [6]]
SENT: Friday, June 26, 2015 4:13 AM
TO: pgsql-admin@xxxxxxxxxxxxxx [7]
SUBJECT: 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@xxxxxxxxxxxxxx
[3] mailto:Robert.Burgholzer@xxxxxxxxxxxxxxxx
[4] mailto:pgsql-admin-owner@xxxxxxxxxxxxxx
[5] mailto:pgsql-admin-owner@xxxxxxxxxxxxxx
[6] mailto:ankurkaushik@xxxxxxxxx
[7] mailto:pgsql-admin@xxxxxxxxxxxxxx
