Q
Manage Learn to apply best practices and optimize your operations.

How to check SQL query construction with the Mimer Validator

Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.

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.

This was last published in April 2009

Dig Deeper on Oracle development languages

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close