Avatar of pmnox
pmnox asked on

Does Microsoft JET Database Engine have a table that always exists? (like dual from mysql)

Does Microsoft JET Database Engine have a table that always exists? (like dual from mysql)
DatabasesMicrosoft SQL ServerMySQL Server

Avatar of undefined
Last Comment
Steve Bink

8/22/2022 - Mon
graye

What are you trying to do?
Steve Bink

AFAIK, no.  

The dual 'table' from MySQL does not really exist.  It is merely a place-holder keyword for selecting when no tables need to be referenced.  It is unnecessary within MySQL, and is provided only to support compatibility with servers that require that syntax (such as Oracle, I believe).
ASKER
pmnox

I'm trying to create an expression that returns an array with two values something like

(SELECT 1 FROM DUAL UNION ALL SELECT 2 FROM DUAL)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Steve Bink

In Access, you do a SELECT with no table reference:

SELECT 1
UNION
SELECT 2

If you all want is an array, though, using VBA to populate a user variable would be much easier to deal with.  With the SELECT, you'll need to handle a recordset.
ASKER
pmnox

Can I use select like:
SELECT 1
UNION
SELECT 2 WHERE '1' = '0'

or


SELECT 1
UNION
SELECT 2 WHERE '1' = '1'
ASKER CERTIFIED SOLUTION
Steve Bink

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
pmnox

well. I want to write an sql expression that returns an an error if certain condition is met.

I need to write an expression like this one:
SELECT * FROM USERS WHERE 1 = (SELECT 1 UNION SELECT 2 WHERE username REGEXP '[a-z][a-z][a-z][a-z]'  )

The easiest way to return an error is to return 2 elements in subexpression instead of one.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Steve Bink

To use a sub-query in that manner, you need to return a single record with a single field.  In your example, though, I do not see how this would differ from using the sub-query just as a WHERE clause.

SELECT * FROM USERS WHERE username REGEXP '[a-z][a-z][a-z][a-z]'

If you need to generate an error if there is no return, do so at the application level by checking the number of rows.

Can you explain further why you need an "error" recordset?
ASKER
pmnox

Sometimes there is an error in database. I want to raise an exception instead of downloading a few hundred mb of data and then checking for an exception.
Steve Bink

What kind of error?  Please explain as thoroughly as you can.  The database service will return an error or warning when an error or warning at the database level is merited.  If you want to artificially raise an error, you would probably be better off using a stored procedure rather than a query.  Better yet, perhaps you can check for the error state FIRST, and handle it in code.  
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
pmnox

Do you know where can I find JET database syntax and manual? I'm not allowed to used stored procedures at server (administrator rules).  It doesn't matter what kind of error I get. I just want it to be generated at runtime level. It can be division by zero for example, etc.
Steve Bink

If you're dealing with MSSQL, then the "Books Online" is what you need.  Search Microsoft's site for "SQL Server Book Online", and it should point you in the right direction.