BillyCode BillyCode - 1 month ago 9
SQL Question

Trouble with straightforward SQL query using Oracle

I am trying to self educate myself in SQL in order to better use databases at work. I am currently trying to code an SQL query in Oracle Application Express that will provide me with a report for management, however I am receiving the following error message:


ORA-01747: invalid user.table.column, table.column, or column specification


I have spent countless hours searching for the bug and doing research and I am getting nowhere, which is quite frustrating as I feel its probably something rather easy. My research has shown me that the probable cause of my error is that I have tried to reference a column name, but the column name used is a reserved word in Oracle. Unfortunately, I am having trouble pinpointing what exactly... Please see below for the code:

SELECT Channel.Channel_Number, Supplier.Supplier_Name, package.Package_Name, Program.Program_name, Rating.Rating_Code, Weekly_Guide.ShowTime
FROM Channel, Supplier, package, Program, Rating, weekly_guide, channel_package
Where weekly_guide.date = ''
AND channel.channel_number = weekly_guide.channel_number
AND weekly_guide.Program_ID = Program.Program_ID
AND channel.channel_number = channel_package.channel_number
AND channel_package.package_ID = package.package_ID
AND Program.rating_code = rating.rating_code
AND Program_list.Program_ID = Program.Program_ID
AND Program_list.list_ID = list.list_id
AND list.supplier_ID = Supplier.supplier_ID

Answer

It's most likely date from your Where clause. You'll need to wrap the reserved word in quotes. The name will then be case sensitive, so make sure you have the correct case for date in your query

SELECT Channel.Channel_Number, Supplier.Supplier_Name, package.Package_Name, Program.Program_name, Rating.Rating_Code, Weekly_Guide.ShowTime
FROM Channel, Supplier, package, Program, Rating, weekly_guide, channel_package
Where weekly_guide."date" = ''
    AND channel.channel_number = weekly_guide.channel_number
    AND weekly_guide.Program_ID = Program.Program_ID
    AND channel.channel_number = channel_package.channel_number
    AND channel_package.package_ID = package.package_ID
    AND Program.rating_code = rating.rating_code
    AND Program_list.Program_ID = Program.Program_ID
    AND Program_list.list_ID = list.list_id
    AND list.supplier_ID = Supplier.supplier_ID