Error: SQL Window

[ScottMattes]

PLSD 10.0.5

given the query:

select *
  from emp e
 where e.

union

select *
  from elections e
 where e.

after typing the 'e.' in the second select, PLSD gives you the same fields as after typing the 'e.' in the first select.

 

[Marco Kalter]

We'll check it out.

 

[Sgnao]

The statement you posted is definitely ONE query.

My guess is the program takes the first one alias as "e" and ignores the second. For good function of the query itself I advice you to use different aliases...

And I have to say that in my opinion that's the way it has to work.

In fact if you remove the UNION and separate the queries by a semi-colon it will work fine using the correct table for drop-down list even if you use the same alias for the two queries.

Marco

 

[Hilarion]

The fact that the code assistant works OK when the query is split does not mean that there's something wrong with the original query.
As Oracle allows alias reuse in a query, there's nothing wrong with it being reused when a query is written using PL/SQL Developer.

It certainly is a complication for PL/SQL Developer to be able to distinguish to which object the alias is referring to in a specified query context and it gets much more complicated from the fact that the query is not yet complete, so it's syntactically incorrect. I can agree that PL/SQL Developer might have to be limited in this case and NOT be able to handle alias reuse correctly but I can't agree to stating that such query is incorrect or will function incorrectly.

 

[Sgnao]

The fact that the code assistant works OK when the query is split does not mean that there's something wrong with the original query.

I didn't say that! That's proof my guess is right on how PL/SQL Developer interprets the query as written by ScottMates.

As Oracle allows alias reuse in a query, there's nothing wrong with it being reused when a query is written using PL/SQL Developer. It certainly is a complication for PL/SQL Developer to be able to distinguish to which object the alias is referring to in a specified query context and it gets much more complicated from the fact that the query is not yet complete, so it's syntactically incorrect. I can agree that PL/SQL Developer might have to be limited in this case and NOT be able to handle alias reuse correctly but I can't agree to stating that such query is incorrect or will function incorrectly.

Sure it's a complication, but not only for PLSQLD, also for who is writing the query and more that that for who will have to read it! In my opinion using the same alias, even for the same table or view, in two sides of a union is simply crazy for a developer. You can give infinite different aliases, why do you have to use the same?

In my opinion the program works fine, but as fine as it can, on this. I wouldn't change a thing.

 

[Hilarion]

I have to disagree on the fact that reusing the same alias in a query is "crazy" or a bad practice in every case. I'd say "it depends".
I do not "have to" use the same alias, but I can, as sometimes it may actually improve readability. For example if two parts of a union have similar structure and at least some of the tables/views are used in both in the same way and for the same purpose, I'd go for using the same alias for those. Even in case of different tables/views being used for the same purpose in two union parts, I might consider showing this by using the same alias for both.
I will not try to force you to share my opinion on this, but if you'd like to discuss that, I'll gladly consider your arguments and share mine.

I agree that the program works quite fine but I can't agree that there's no room for improvement. I'd say that in this particular case if the program can distinguish the aliases (as Oracle does), then it eventually should. It's not a critical feature - there are many missing or bugged that should be implemented/fixed earlier - but if it does not break the program, then it should eventually be implemented.
This is also a matter of opinion, but this functionality would not cause any problems for anyone sharing your approach to using aliases and would help those that have different one, so I see no reason for objecting it.

 

[Sgnao]

I'm not objecting anything.

You are definitely right, "It depends" on what you define "readable" or "not readable". In my opinion using the same alias in two parts of a UNION query makes it unreadable or readable with difficulties.

I repeat, this is my humble opinion.

For sure there's room for improvements, but this is probably not even in the top 1000.

It's not about arguments to have or not to have, it's about how you are used to work, in our office we share my point of view.

I respect your opinion. Thanks for sharing it.