1

Oracle gives an error for the following code: 

SELECT actor_name, 
       char_name
  FROM zActor AS zAct, 
       zCharacter AS zChar
 WHERE zChar.char_num IN
       (SELECT COUNT(zP.char_num) AS charCount
          FROM zPlay AS zP, 
               zActor AS zAct, 
               zCharacter AS zChar
         WHERE zAct.actor_num = zP.actor_num
           AND zChar.char_num = zP.char_num
      GROUP BY zAct.actor_name
  HAVING COUNT(zP.char_num) > 1);

I cannot figure out why because I am somewhat new to oracle but have a good grasp on SQL. Any help is appreciated.

E_net4
  • 27,810
  • 13
  • 101
  • 139
koder42
  • 27
  • 3
  • 2
    You should learn how to use modern join syntax. Your query is hard to follow. – Tim Biegeleisen Oct 29 '18 at 06:09
  • we are not allowed to so i have to steer away from using it. thanks. – koder42 Oct 29 '18 at 06:11
  • that worked, but now i'm discovering my query, as a whole, might be incorrect, because the output is blank, which is incorrect. – koder42 Oct 29 '18 at 06:17
  • In that case, please update your question with posting your expected result and what is the exact result. Or Post a new question. It will be better if you post a new question – YLG Oct 29 '18 at 06:19
  • @motto, I have updated my comment as an answer, If you find useful, please upvote it. – YLG Oct 29 '18 at 06:33
  • being that im new to stackoverflow, and that i cant post another question for 90 more minutes, is it frowned upon to erase my original question and replace it with a new one? is that even possible? – koder42 Oct 29 '18 at 06:35
  • https://meta.stackexchange.com/questions/106249/update-a-question-or-post-a-new-question It may help. – YLG Oct 29 '18 at 06:48

2 Answers2

0

It is because your subquery and outer query are using the same alias name zAct and aChar. Just change the names, it should work

codeLover
  • 2,571
  • 1
  • 11
  • 27
  • After using the following code: 'SELECT actor_name, char_name FROM zActor AS zAct, zCharacter AS zChar WHERE zChar.char_num IN (SELECT COUNT(zP.char_num) AS charCount FROM zPlay AS zP, zActor AS zA, zCharacter AS zC WHERE zA.actor_num = zP.actor_num AND zC.char_num = zP.char_num GROUP BY zA.actor_name HAVING COUNT(zP.char_num) > 1);' I still received the same error message. – koder42 Oct 29 '18 at 06:13
  • can you share the columns in both the tables? – codeLover Oct 29 '18 at 06:16
0

Try removing As in table alias. Give zActor zAct instead of zActor AS zAct. Do this with all the table alias.

 SELECT actor_name, 
        char_name
  FROM zActor  zAct, 
       zCharacter  zChar
 WHERE zChar.char_num IN
      (SELECT COUNT(zP.char_num) AS charCount
         FROM zPlay  zP, 
              zActor  zAct, 
              zCharacter  zChar
        WHERE zAct.actor_num = zP.actor_num
          AND zChar.char_num = zP.char_num
     GROUP BY zAct.actor_name
 HAVING COUNT(zP.char_num) > 1);

FYR,
techonthenet.com/oracle/alias.php

YLG
  • 855
  • 2
  • 14
  • 36