Ask The Oracle Expert: Questions & Answers

How to check SQL query construction with the Mimer Validator

How to check SQL query construction with the Mimer Validator

By  Rudy Limeback, SQL Consultant, r937.com

SearchOracle.com

Suppose I have a table with columns as (a,b,c,d) and (a,b) form the composite key. Can I write the query with values of the key in a list of values? For example, like this:

select a,c,d from mytable 
where (a,b) in ((1,2),(1,4),(1,5))

Yes.

Should you ever be in doubt as to whether some SQL construction is valid, run it through the Mimer Validator. In this instance, your query—entered into the validator exactly as you wrote it—produces the following results:

  • In SQL-92, it is invalid

  • In SQL-99 and SQL_2003, it is valid, with these comments:

    The following features outside Core SQL-99, or Core SQL-200x (draft), are used:

    F641, "Row and table constructors"
    T051, "Row types"
    F561, "Full value expressions"

What this means is that if your particular database system does not support those optional features, then you will still get an error message.

You might want to try the following alternative query:

SELECT a,c,d 
  FROM mytable 
 WHERE a = 1 AND b = 2
    OR a = 1 AND b = 4
    OR a = 1 AND b = 5

This works quite well because (a,b) is a key, so index lookups will be efficient.