Thread: SQL query problem

SQL query problem

From

"Marek Lewczuk"

Date:

09 October 2003, 18:53:45

Hello,
I'm in the middle of the migration process from MySQL to PostgreSQL and
I cannot understand why this query isn't working (in MySQL it's working
fine). PG returns: ERROR:  Relation "_con" does not exist

This is my query:

SELECT
  _CON.con_id,
  _MOD.mod_ty,
  _VER.version,
  _YEA.year,
  _CON.dri_id,
  _CON.man_cod,
  _ENG.eng_pow
FROM
  db_data.mda_mod _MOD,
  db_data.mda_mod_con _CON,
  db_data.mda_mak_eng _ENG,
  db_data.set_mda_fue _FUE
  LEFT JOIN db_data.mda_mod_ver _VER ON _VER.ver_id = _CON.ver_id
  LEFT JOIN db_data.mda_mod_yea _YEA ON _YEA.yea_id = _CON.yea_id
WHERE
  _MOD.mod_id = '283' AND
  _CON.mod_id = _MOD.mod_id AND
  _CON.psd <= NOW() AND
  _CON.ped > NOW() AND
  _ENG.eng_id = _CON.eng_id AND
  _ENG.eng_fue = _FUE.fue_id


I will be appreciated for you help.

ML

Re: SQL query problem

From

Bruno Wolff III

Date:

09 October 2003, 18:58:54

On Thu, Oct 09, 2003 at 20:52:58 +0100,
  Marek Lewczuk <newsy@lewczuk.com> wrote:
> Hello,
> I'm in the middle of the migration process from MySQL to PostgreSQL and
> I cannot understand why this query isn't working (in MySQL it's working
> fine). PG returns: ERROR:  Relation "_con" does not exist

Probably you quoted the table name when you created it so that it is named
_CON. However when you don't quote the name in later references _CON
is converted to _con which is a different name that doesn't match any
existing table.

This behavior is contrary to the spec (lowercasing instead of uppercasing),
but isn't going to change since uppercase names look ugly.

A good general rule to follow is to either never quote names or always
quote names.

>
> This is my query:
>
> SELECT
>   _CON.con_id,
>   _MOD.mod_ty,
>   _VER.version,
>   _YEA.year,
>   _CON.dri_id,
>   _CON.man_cod,
>   _ENG.eng_pow
> FROM
>   db_data.mda_mod _MOD,
>   db_data.mda_mod_con _CON,
>   db_data.mda_mak_eng _ENG,
>   db_data.set_mda_fue _FUE
>   LEFT JOIN db_data.mda_mod_ver _VER ON _VER.ver_id = _CON.ver_id
>   LEFT JOIN db_data.mda_mod_yea _YEA ON _YEA.yea_id = _CON.yea_id
> WHERE
>   _MOD.mod_id = '283' AND
>   _CON.mod_id = _MOD.mod_id AND
>   _CON.psd <= NOW() AND
>   _CON.ped > NOW() AND
>   _ENG.eng_id = _CON.eng_id AND
>   _ENG.eng_fue = _FUE.fue_id
>
>
> I will be appreciated for you help.
>
> ML
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

Re: SQL query problem

From

Stuart Barbee

Date:

10 October 2003, 03:52:18

Marek,

Not sure but, try switching the lines
 db_data.mda_mod_con _CON,  db_data.set_mda_fue _FUE 

with 
 db_data.set_mda_fue _FUE,  db_data.mda_mod_con _CON 

so there query is:

SELECT _CON.con_id, _MOD.mod_ty, _VER.version, _YEA.year, _CON.dri_id, _CON.man_cod, _ENG.eng_pow
FROM db_data.mda_mod _MOD, db_data.mda_mak_eng _ENG, db_data.set_mda_fue _FUE, db_data.mda_mod_con _CON LEFT JOIN
db_data.mda_mod_ver_VER ON _VER.ver_id =
 
_CON.ver_id LEFT JOIN db_data.mda_mod_yea _YEA ON _YEA.yea_id =
_CON.yea_id
WHERE _MOD.mod_id = '283' AND _CON.mod_id = _MOD.mod_id AND _CON.psd <= NOW() AND _CON.ped > NOW() AND _ENG.eng_id =
_CON.eng_idAND _ENG.eng_fue = _FUE.fue_id
 



--- Marek Lewczuk <newsy@lewczuk.com> wrote:
> Hello,
> I'm in the middle of the migration process from
> MySQL to PostgreSQL and
> I cannot understand why this query isn't working (in
> MySQL it's working
> fine). PG returns: ERROR:  Relation "_con" does not
> exist
> 
> This is my query:



__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com