Re: Error in select
Date: Fri, 9 Nov 2012 06:28:04 -0800 (PST)
Message-ID: <3d915a95-8cb0-4f2d-baf8-6f618e76e0f3_at_googlegroups.com>
On Friday, November 9, 2012 4:17:19 AM UTC-5, Q-IK Q-IK wrote:
> 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.
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.
Received on Fri Nov 09 2012 - 15:28:04 CET