MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8;
 format=flowed
Date: Fri, 26 Jun 2015 14:15:51 +0200
From: Jan Lentfer <Jan.Lentfer@web.de>
To: <pgsql-admin@postgresql.org>
Subject: Re: select query of mysql to postgres
In-Reply-To: 
 <C5DBACC6DCC7604C9E4875FD9C7968B11A14E0E32B@ITXS01EVS.service.it.nrw.de>
References: 
 <CALXoLqz0=1_hy6XpX3QriPgFLPtW8r8YC=yCQw3WSSr9Z0dQuw@mail.gmail.com>
 <77F5F8FA4B407F43BFCFD3FC3478969108EC9201@COVMSGCES-MBX01>,
 <CALXoLqy+sVx1Rj-O2PHc9jn=Oo-q83LwBS5_8SeFOKrx6Q=Z2w@mail.gmail.com>
 <77F5F8FA4B407F43BFCFD3FC3478969108EC9246@COVMSGCES-MBX01>
 <C5DBACC6DCC7604C9E4875FD9C7968B11A14E0E32B@ITXS01EVS.service.it.nrw.de>
Message-ID: <54d0aa5c08a565170ee35bf762f4ebc7@imap.lan.net>
User-Agent: Roundcube Webmail/0.7.2
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

@VAR is T-SQL, Postgres does not support this. You will need to rewrite=20
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:
>
> =C3=98 So this seems to be saying that postgres does not have the ifnull
> function:
>=20
> 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 :=3D @tot_dur part.
>
> If you format the error output with a fixed-pitch font, the ^ points
> exactly to the :=3D operator.=C2=A0 (It seems to me that neither @ nor :=
=3D
> exists in PostgreSQL.)
>
> LINE 8: ...fnull(rm.schedule_distance, 0)) IS NULL, @tot_dur :=3D
> @tot_du...
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0
> ^
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0 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 ":=3D"
>
> LINE 8: ...fnull(rm.schedule_distance, 0)) IS NULL, @tot_dur :=3D
> @tot_du...
>
>  ^
>
> ********** Error **********
>
> ERROR: syntax error at or near ":=3D"
>
> 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=20
> 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=3D'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)=3D'0',ifnull(rm.distance,
> 0),ifnull(rm.schedule_distance, 0)) IS NULL, @tot_dur :=3D @tot_dur,
> @tot_dur :=3D @tot_dur + if(ifnull(rm.schedule_distance,
> 0)=3D'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 :=3D
> 0) d inner join route_point rp on rp.route_id =3D r.route_id inner join
> bus_stop bs on bs.bus_stop_id=3Drp.bus_stop_id and bs.point_type_id!=3D2
> left join route_map rm on rm.start_bus_stop_id=3Drp.bus_stop_id and
> rp.route_id=3D'3004' and rm.route_id=3D'3004' INNER JOIN (select=20
> route_id,
> route_points_id, if(if(rp.fare_stage=3D'Y',1,0) IS NULL,@stage_count :=3D
> @stage_count,@stage_count :=3D @stage_count+if(rp.fare_stage=3D'Y',1,0))
> as stage_count from route_point rp JOIN (SELECT @stage_count :=3D 0) e
> where rp.route_id=3D'3004' ) aON rp.route_points_id =3D a.route_points_id
> INNER JOIN rate_master_details rmd ON a.stage_count =3D rmd.stage_no=20
> AND
> rmd.rate_master_id=3D'12'where r.route_id=3D'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=3D'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, ''))=20
> bus_stop_name_nudi,
> bs.bus_stop_id, ifnull(bs.alias1, '') alias1, IF(
> if(ifnull(rm.schedule_distance, 0)=3D'0',ifnull(rm.distance,
> 0),ifnull(rm.schedule_distance, 0)) IS NULL, @tot_dur :=3D @tot_dur,
> @tot_dur :=3D @tot_dur + if(ifnull(rm.schedule_distance,
> 0)=3D'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 :=3D
> 0) d inner join route_point rp on rp.route_id =3D r.route_id inner join
> bus_stop bs on bs.bus_stop_id=3Drp.bus_stop_id and bs.point_type_id!=3D2
> left join route_map rm on rm.start_bus_stop_id=3Drp.bus_stop_id and
> rp.route_id=3D'3004' and rm.route_id=3D'3004' INNER JOIN (select=20
> route_id,
> route_points_id, if(if(rp.fare_stage=3D'Y',1,0) IS NULL,@stage_count :=3D
> @stage_count,@stage_count :=3D @stage_count+if(rp.fare_stage=3D'Y',1,0))
> as stage_count from route_point rp JOIN (SELECT @stage_count :=3D 0) e
> where rp.route_id=3D'3004' ) aON rp.route_points_id =3D a.route_points_id
> INNER JOIN rate_master_details rmd ON a.stage_count =3D rmd.stage_no=20
> AND
> rmd.rate_master_id=3D'12'where r.route_id=3D'3004' AND bs.point_type_id
> NOT IN ('2', '13') order by r.route_id;
>
>
>
> Links:
> ------
> [1]=20
> 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


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin