Bizarre error message

Hello! I am new to SQL so bear with me.

I am trying to get the following code to work within a system that uses Oracle for back end data services:

Code:

---

SELECT Retail Tran DateTime, Product Department Name, Product Quantity, Product Retail Price
FROM Product Sales Detail
WHERE Product Department Name = 'Meter'
        AND Retail Tran DateTime = DATEADD(DAY, -1, CURRENT_TIMESTAMP)
ORDER BY Retail Tran DateTime;

---

But I keep getting the following error message but I don't understand how there could be any problem with my FROM statement:

Quote:

---

Oracle.DataAccess.Client.OracleException ORA-00923: FROM keyword not found where expected at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck) at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) at Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at Blackboard.ReportingSystem.XmlToSql.ParseXmlToSql.ExecuteSql()

---

Is there any way anyone could explain what this message is even telling me?

Code:

---

SELECT Retail Tran DateTime, Product Department Name, Product Quantity, Product Retail Price
FROM Product Sales Detail
WHERE Product Department Name = 'Meter'
        AND Retail Tran DateTime = DATEADD(DAY, -1, CURRENT_TIMESTAMP)
ORDER BY Retail Tran DateTime;

---

Is the code as it shows? with a table called "Product Department Name" separated in three words and column names like "Retail Tran DateTime" also separated in several words? Check names, please.

Is that what this seems to be telling you? I didn't think that was it because when I used underscores instead of spaces I got a table "doesn't exist" message and with this I don't.

The truth is, we are using a web based reporting system that has tools for building reports but they are quite limited so there is an option to put in "Custom SQL" code. The problem is, we have no idea what the table names or column names are (we don't have direct access to the Db) and I've had to guess based on the options in the report builder tools.

If that's what you think the problem is, I'll have to try to find a way to get that info but I was hoping it was something we could take care of on our end before going through the rigamarole of dealing with IT and the vendor... They suck!

Quote:

---

Originally Posted by notsirk

Is that what this seems to be telling you? I didn't think that was it because when I used underscores instead of spaces I got a table "doesn't exist" message and with this I don't.

---

Please do not take it the wrong way but that rationale is like saying that you drained all the blood out of your patient so he/she would stop bleeding :)

Quote:

---

Originally Posted by notsirk

The truth is, we are using a web based reporting system that has tools for building reports but they are quite limited so there is an option to put in "Custom SQL" code. The problem is, we have no idea what the table names or column names are (we don't have direct access to the Db) and I've had to guess based on the options in the report builder tools.

If that's what you think the problem is, I'll have to try to find a way to get that info but I was hoping it was something we could take care of on our end before going through the rigamarole of dealing with IT and the vendor... They suck!

---

Yes. I think the problem is that it is not easy to write a query if you know neither the name of the table nor the names of the columns. Once you get the table name, try to include the schema_name, fully qualifying it... that's most probably the reason why you got "table does not exist" error message, the alternatives are: 1) your user account has no privileges on the table and 2) the named table does not exist at all.

Looking forward, next steps are:
1- Get the right table names including schema name.
2- Be sure your account has privileges on such table/s
3- Log via sqlplus and do a "desc" of each table so to get a list of columns

Good luck.

Thanks for your help! I guess I'll just have to prepare for some serious prodding to get this information...

If you created tables with embedded spaces then you need double quotes to let Oracle know that there are embedded spaces in the table name and columns for example:

Code:

---

SELECT "Retail Tran DateTime", "Product Department Name",
      "Product Quantity", "Product Retail Price"
  from "Product Sales Detail"
 where product department name = 'Meter'
  and retail tran datetime = dateadd(day, -1, current_timestamp)
 order by retail tran datetime;

---

Here is the Access/SQL Server version of the above. Basically you can have select columnName columnAlias, columnName2 columnAlias2, etc

you can't have select column name aliasbecause Oracle would need to know that column is the name of the column and name is the alias or that column name is the name of the column with no alias. The as is optional.

Code:

---

SELECT [Retail Tran DateTime], [Product Department Name],
      [Product Quantity], [Product Retail Price]
  from [Product Sales Detail]
 where product department name = 'Meter'
  and retail tran datetime = dateadd(day, -1, current_timestamp)
 order by retail tran datetime
GO

---

Hence you use double quotes. Although you are better off not using double quotes in tables at all. It makes it much easier. Because if you do use double quotes, then you can do the following:

create table system."mYtAbLe" (
"column1" VARCHAR2(4000),
"Column1" VARCHAR2(4000),
"cOlumn1" VARCHAR2(4000),
"colLmn1" VARCHAR2(4000),
"coluMn1" VARCHAR2(4000),
"columN1" VARCHAR2(4000) );

create table system."MyTaBlE" (
"column1" VARCHAR2(4000),
"Column1" VARCHAR2(4000),
"cOlumn1" VARCHAR2(4000),
"colLmn1" VARCHAR2(4000),
"coluMn1" VARCHAR2(4000),
"columN1" VARCHAR2(4000) );


You can have both tabes in the same schema in the same database. You can even store almost any data type in any of the columns. But your database would be a mess.

Quote:

---

Originally Posted by notsirk

Hello! I am new to SQL so bear with me.

. . . E t c . . .
But I keep getting the following error message but I don't understand how there could be any problem with my FROM statement:


Is there any way anyone could explain what this message is even telling me?

---

That error is telling you the code has "syntax" errors.
Database objects (vg. table names / column names) normally do not have "spaces" in the name.
Many of these reporting tools have a "dictionary" which translates the database object names into "user friendly" names.
Look for this dictionary to locate the "real" database names being used.
Also, what may appear as a single table in the tool, may be in reality a complex "view".
As a developer you have the right to be provided with the structure and any other information necessary to do your job.
If you cannot obtain this information, you may need to escalate the issue with your manager.
Good luck!
;)

Quote:

---

Originally Posted by LKBrwn_DBA

That error is telling you the code has "syntax" errors.
Database objects (eg. table names / column names) normally do not have "spaces" in the name.

---

Both are correct statements. However, my guess is that the tables and column
names do have spaces, hence the OP needs to use double quotes.

Quote:

---

Originally Posted by gandolf989

. . . . However, my guess is that the tables and column
names do have spaces, hence the OP needs to use double quotes.

---

Yes, I myself had to deal with developers using Ms Access to create tables which ended having embedded blanks and mixed case in the table/column names.

On the other hand, the OP states that "we are using a web based reporting system that has tools for building reports". Many of these reporting tools have dictionaries that will allow users to give a more descriptive name to tables, views and even columns. which is the possibility I tried to address.
;)

Quote:

---

Originally Posted by LKBrwn_DBA

Yes, I myself had to deal with developers using Ms Access to create tables which ended having embedded blanks and mixed case in the table/column names.

On the other hand, the OP states that "we are using a web based reporting system that has tools for building reports". Many of these reporting tools have dictionaries that will allow users to give a more descriptive name to tables, views and even columns. which is the possibility I tried to address.
;)

---

This is where Mr. Hanky comes in and starts talking about shooting developers... :D
Oh Mr. Hanky, Can't you ever stay one step ahead of the law?!?!?! :D