Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Error in select

138 views
Skip to first unread message

erich...@gmail.com

unread,
Nov 9, 2012, 4:17:19 AM11/9/12
to
Hello,
I have such select:

SELECT * FROM ifsinfo.q_ial_wysylki_planowane where "Planowana_data" < to_date('09/11/2012','DD/MM/YYYY')

and it's work good, but select:

SELECT * FROM ifsinfo.q_ial_wysylki_planowane where 'Planowana_data' < to_date('09/11/2012','DD/MM/YYYY')
generate error:
01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"
*Cause: Illegal year entered
*Action: Input year in the specified range

Why?
I must put such select in php - and i have problem with "" and ' '.

Thanks in advance for help.
Q.

Noons

unread,
Nov 9, 2012, 4:23:04 AM11/9/12
to
Why are you using quotes around the column name?
It's not needed, unless you have a very strange way of defining the tables...

Q-IK Q-IK

unread,
Nov 9, 2012, 4:47:38 AM11/9/12
to wizo...@yahoo.com.au
When i dont using quotes i have such error:

ORA-00904: "PLANOWANA_DATA": niepoprawny identyfikator
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 1 Column: 55

Charles Hooper

unread,
Nov 9, 2012, 9:28:04 AM11/9/12
to
The answer is simply that when the table was created, the Planowana_data column name was placed in quotes. As such, the column name becomes case sensitive - if the column name is not placed in quotes, the column name is automatically handled as if it were capitalized when the table is created and when the query is optimized.

Here is a quick demonstration, a table with four columns:
CREATE TABLE T1(
My_Data NUMBER,
"My_Data" NUMBER,
"my_data" NUMBER,
"MY_DATA" NUMBER);

When the table creation script is executed in SQL*Plus, an error message is returned, indicating that the column name on line 5 is a duplicate:
SQL> CREATE TABLE T1(
2 My_Data NUMBER,
3 "My_Data" NUMBER,
4 "my_data" NUMBER,
5 "MY_DATA" NUMBER);
"MY_DATA" NUMBER)
*
ERROR at line 5:
ORA-00957: duplicate column name

Let's fix that problem and try again:
CREATE TABLE T1(
My_Data NUMBER,
"My_Data" NUMBER,
"my_data" NUMBER,
"MY_DATa" NUMBER);

Table created.

So, the Oracle Database was able to create the table this time with four very similar columns with case sensitive column names. Let's insert a row into the table:
INSERT INTO T1 VALUES(
1,
2,
3,
4);

Now, let's try selecting a row:
SELECT
*
FROM
T1
WHERE
MY_DATA=1;

MY_DATA My_Data my_data MY_DATa
---------- ---------- ---------- ----------
1 2 3 4

Notice that a row was returned, even though the upper/lower case did not match the first column name when the T1 table was created (the column name was created using uppercase letters).

Let's see if a row is returned if we try the following (failing to query using the fourth column of the table):
SELECT
*
FROM
T1
WHERE
MY_DATa=1;

MY_DATA My_Data my_data MY_DATa
---------- ---------- ---------- ----------
1 2 3 4

The query optimizer understood that the first column in the table was being referenced.

Let's try again, this time wrapping the column name in quotes:
SELECT
*
FROM
T1
WHERE
"MY_DATA"=1;

MY_DATA My_Data my_data MY_DATa
---------- ---------- ---------- ----------
1 2 3 4

A row was still returned (as expected). So then, how do we determine if the fourth column contains the value 1? We need to place the column name in quotes as follows:
SELECT
*
FROM
T1
WHERE
"MY_DATa"=1;

no rows selected

Notice that this time no rows were returned. So, the short answer is that you must wrap the column name in quotes if you want to select that particular column. In PHP, is there a way to append an ASCII 34 character to a string? When building the SQL statement, you could then append the ASCII 34 character before and after the column name.

You will need to recreate the table without including the quotes in the column names if you want to fix this problem - then you will need to fix all of the SQL statements that reference the column name wrapped in quotes.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

joel garry

unread,
Nov 9, 2012, 11:39:15 AM11/9/12
to
The single quotes mean you are referencing a value rather than a
column name, so it is trying to implicitly convert Planowana_data to a
date that can compare to the to_date value.

jg
--
@home.com is bogus.
http://www.businessinsider.com/hp-shows-off-new-itanium-servers-2012-11
http://en.wikipedia.org/wiki/Pat_Paulsen#Quotes

Noons

unread,
Nov 9, 2012, 11:36:56 PM11/9/12
to
Q-IK Q-IK wrote,on my timestamp of 9/11/2012 8:47 PM:

>>
>> Why are you using quotes around the column name?
>>
>> It's not needed, unless you have a very strange way of defining the tables...
>
> When i dont using quotes i have such error:
>
> ORA-00904: "PLANOWANA_DATA": niepoprawny identyfikator
> 00904. 00000 - "%s: invalid identifier"
> *Cause:
> *Action:
> Error at Line: 1 Column: 55
>

Well, like I said: someone used a strange way of creating the table, with mixed
upper and lower case characters for column names.
NO ONE who has been coding in the database space for a while does even bother
doing such. Mostly because it invariably creates a lot of problems down the
line. Proof? This very problem you are having.

No, you do NOT need to have column names in mixed case nor is there ANY business
or development reason to do such.

So:
1- Stop using that planowano nonsense table in its native form, it was created
by nimcompoops with less brain than a constipated fly.
2- Create a view over that table that includes ALL columns, no predicates and
simply defines all column names in upper case and maps them to the original
nonsense names.
3- Then, simply use the view name and its columns in your php code as if it was
the plano-whatever table.

Noons

unread,
Nov 9, 2012, 11:37:39 PM11/9/12
to
Q-IK Q-IK wrote,on my timestamp of 9/11/2012 8:47 PM:
>>
>> Why are you using quotes around the column name?
>>
>> It's not needed, unless you have a very strange way of defining the tables...
>
> When i dont using quotes i have such error:
>
> ORA-00904: "PLANOWANA_DATA": niepoprawny identyfikator
> 00904. 00000 - "%s: invalid identifier"
> *Cause:
> *Action:
> Error at Line: 1 Column: 55
>

Q-IK Q-IK

unread,
Nov 12, 2012, 5:16:38 AM11/12/12
to wizo...@yahoo.com.au
Super, thanks for your support - everything works as it should after appropriate changes.

Noons

unread,
Nov 12, 2012, 6:37:35 AM11/12/12
to
Q-IK Q-IK wrote,on my timestamp of 12/11/2012 9:16 PM:
> Super, thanks for your support - everything works as it should after appropriate changes.
>

No problem, glad it all worked out for you.
0 new messages