0

In a particular context, im writing a generic query that it will be executed in many different RDBMS (mainly Oracle and Teradata), to make this possible i want to write it in compliance with ANSI SQL, so which one of the following is a valid ANSI SQL query :

select tab.* from (...) tab where 1=1
/* or */
select tab.* from (...) as tab where 1=1
54l3d
  • 3,913
  • 4
  • 32
  • 58
  • 1
    Second won't work on Oracle. – Lukasz Szozda Aug 02 '17 at 12:53
  • 3
    Both are valid ANSI SQL, the `as` keyword is optional –  Aug 02 '17 at 12:58
  • @a_horse_with_no_name: So why it not works on Oracle as mentioned by lad2025 ? – 54l3d Aug 02 '17 at 13:00
  • @54l3d lad2025 is correct. the `as` keyword for table aliases is illegal in oracle ([see similar answer on this very site](https://stackoverflow.com/questions/21145028/how-to-use-the-as-keyword-to-alias-a-table-in-oracle)). Unless there's an implementation out there that has the `as` keyword _mandatory_ for table aliases, the first syntax would be the way to go. Otherwise, I'm afraid your fudged. – user2877959 Aug 02 '17 at 13:06
  • 3
    @54l3d: Because Oracle does not always follow the SQL standard. Just because a statement is valid according to the standard, doesn't mean it runs on all DBMS –  Aug 02 '17 at 13:09
  • @a_horse_with_no_name: ok thank you all, but does the first work on both oracle and teradata ? – 54l3d Aug 02 '17 at 13:14
  • @54l3d Yes the first will work on Teradata just fine. (As well as Postgres, SQL Server, and MySQL). And as ahorse mentioned, "ANSI SQL" is not like "POSIX" compliance with shell script. Every RDBMS deviates from standard and implements functionality that is not included in the standard. If your queries become sufficiently complex they will generally only work on one RDBMS and you may have to manage multiple statements for each box. – JNevill Aug 02 '17 at 13:26
  • ok thank you @JNevill, thats what i want to know ! – 54l3d Aug 02 '17 at 13:54

1 Answers1

-1

you have to use a field name for the required search

select tab.* from (...) tab where (..field name..)=1

Sanjeev Chauhan
  • 46
  • 2
  • 4
    `where 1=1` is valid even though useless in this situation. Obviously his question pertains to the use of the `AS` keyword in ANSI SQL, not whether his query is useful. – user2877959 Aug 02 '17 at 12:56