Trouble with straightforward SQL query using Oracle

Question

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

You refer to list.list_id and list.supplier_ID, but you don't have list in the FROM clause. — Barmar
– Barmar, Commented Dec 19, 2013 at 3:24
You should learn ANSI JOIN syntax instead of the old syntax of listing multiple tables in the FROM clause. — Barmar
– Barmar, Commented Dec 19, 2013 at 3:25
Apex is for developing web applications, but for testing SQL and PL/SQL I recommend using a tool like Oracle SQL Developer. — Jeffrey Kemp
– Jeffrey Kemp, Commented Dec 19, 2013 at 5:09

Matt Busche · Accepted Answer · 2013-12-19 03:26:13Z

2

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

answered Dec 19, 2013 at 3:26

Matt Busche

14.4k5 gold badges38 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BillyCode Over a year ago

Thank you for your comment. Your solution successfully got rid of the error! However, I am still receiving "ORA-00942: table or view does not exist" message. Working on it as we speak.

Barmar Over a year ago

@BillyCode Fixing the list of tables in FROM should solve that new error.

Collectives™ on Stack Overflow

Trouble with straightforward SQL query using Oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related