CODEWITHSUNDEEP
sqloracle-database
chloe
chloe

Reputation: 13

How can I fix 'ORA-00904' error in Oracle?

Hi I'm trying to make a code in SQL server, but the server says there is 'ORA-00904' error. I have to show the name and city of customers who don't live in Harrison and Woodside and who have more than 500 in their accounts using SELECT.

This is ACCOUNT TABLE.

ACCOUNT_NUMBER   BRANCH_NAME   BALANCE
         A-101      Downtown       500
         A-102    Perryridge       400
         A-201      Brighton       900
         A-215        Mianus       700
         A-217      Brighton       750
         A-222       Redwood       700
         A-305    Round Hill       350

This is DEPOSITOR TABLE.

CUSTOMER_NAME   ACCOUNT_NUMBER
        Hayes            A-102 
      Johnson            A-101
      Johnson            A-201
        Jones            A-217
      Lindsay            A-222
        Smith            A-215
       Turner            A-305

This is CUSTOMER TABLE.

CUSTOMER_NAME   CUSTOMER_STREET   CUSTOMER_CITY
        Adams            Spring      Pittsfield
       Brooks           Senator        Brooklyn
        Curry             North             Rye
        Glenn         Sand Hill        Woodside
        Green            Walnut        Stamford
        Hayes              Main        Harrison
      Johnson              Alma       Palo Alto
        Jones              Main        Harrison
      Lindsay              Park      Pittsfield
        Smith             North             Rye
       Turner            Putnam        Stamford
     Williams            Nassau       Princeton

I already tried the code below and I guess there is problem with alias but I don't know exactly why. 

SELECT customer_name, 
       customer_city 
FROM   (SELECT * 
        FROM   (SELECT * 
                FROM   depositor X 
                       full OUTER JOIN (SELECT * 
                                        FROM   customer Y 
                                        WHERE  NOT customer_city = 'Harrison' 
                                               AND NOT customer_city = 
                                                       'Woodside') 
                                    ON X.customer_name = y.customer_name) Z 
               full OUTER JOIN (SELECT * 
                                FROM   account W) 
                            ON Z.account_num = w.account_num) 
WHERE  balance >= 500;

Upvotes: 0

Views: 1552

Answers (3)

Himanshu
Himanshu

Reputation: 3970

Idk why your query is so complex with joins. Can try with the below query

     select c.customer_name,c.customer_city from customer c
      where 
       c.customer_city not in 
      ( 'Woodside','Harrison' )
       and c.customer_name in 
     (Select d.customer_name
       from depositor d where 
     d.account_number in
    (Select a.account_number from account a where
     a.balance >=500)
     )

The above query might help as its short and includes no joins

Upvotes: 0

Raymond Nijland
Raymond Nijland

Reputation: 11602

I already tried the code below and I guess there is problem with alias but I don't know exactly why.

Correct geuss a alias is indeed missing.

SELECT customer_name, 
       customer_city 
FROM   (SELECT * 
        FROM   (SELECT * 
                FROM   depositor X 
                       full OUTER JOIN (SELECT * 
                                        FROM   customer Y 
                                        WHERE  NOT customer_city = 'Harrison' 
                                               AND NOT customer_city = 
                                                       'Woodside') 
                                    ON X.customer_name = y.customer_name) Z 
               full OUTER JOIN (SELECT * 
                                FROM   account W) AS alias # here it was missing
               ON Z.account_num = w.account_num) AS alias # here it was missing
WHERE  balance >= 500;

Upvotes: 1

sgeddes
sgeddes

Reputation: 62861

If I'm understanding your question correctly, there's no need for all those subqueries with full outer joins. You can just use regular joins and aggregate on the sum:

select c.customer_name, c.customer_city
from customer c
    join depositor d on c.customer_name = d.customer_name
    join account a on d.account_number = a.account_number
where c.customer_city not in ('Harrison','Woodside')
group by c.customer_name, c.customer_city
having sum(a.balance) > 500

A couple notes: I'd recommend using a customer_id as your primary key instead of customer_name. Duplicate names like johnson can cause issues and false results. Also it looks like the depositor table is a 1-n table with the customer table. That's why this uses a having statement with sum instead of where criteria.

Upvotes: 2

Related Questions