DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Bizarre error message

Hybrid View

  1. #1
    Join Date
    Feb 2013
    Posts
    3

    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:
    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?

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Feb 2013
    Posts
    3
    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!

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by notsirk View Post
    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 View Post
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Feb 2013
    Posts
    3
    Thanks for your help! I guess I'll just have to prepare for some serious prodding to get this information...

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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.

  7. #7
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool

    Quote Originally Posted by notsirk View Post
    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!
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by LKBrwn_DBA View Post
    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.

  9. #9
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool

    Quote Originally Posted by gandolf989 View Post
    . . . . 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.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  10. #10
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by LKBrwn_DBA View Post
    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...
    Oh Mr. Hanky, Can't you ever stay one step ahead of the law?!?!?!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width