Parameterised queries and UNION clause 
Author Message
 Parameterised queries and UNION clause

Hello,

I am having problems with an SQL statement I'm trying to execute.

The SQL statement is along the lines of

SELECT ID FROM Table1 WHERE Table1.ID = :Parameter1
UNION
SELECT ID FROM Table2 WHERE Table2.ID = :Parameter2

Parameters 1 & 2 are valid column names in another table to which is
attached a datasource which is selected in the DataSource property of my
query.

The SQL statement won't execute.  I get an access violation at 4C5F2E19 in
'idsql32.dll', reading address 00000009.

If I replace the parameters with constants (i.e. ...WHERE Table1.ID =
'1'...), the SQL statement executes fine.

Does anybody have any suggestions?  I don't have that much experience of
SQL.  Maybe I'm not able to use a UNION clause with parameterised queries?

Thanks for any help received...

--
Conor
   (Remove garbage from email address to activate)



Wed, 18 Jun 1902 08:00:00 GMT
 Parameterised queries and UNION clause

You're not going to get any result unless the two parameters are IDENTICAL.
 A UNION means that ONLY those values that are IDENTICAL in both tables
will be included in the result set. Also, I would reference the ID in the
first part of the SELECT clause specifically i.e.: Table1.ID instead of
just ID.  Finally, make sure that the datatype of your parameters are
compatible with the datatype of the field to be compared.

Hope this has helped! :)



Quote:
> Hello,

> I am having problems with an SQL statement I'm trying to execute.

> The SQL statement is along the lines of

> SELECT ID FROM Table1 WHERE Table1.ID = :Parameter1
> UNION
> SELECT ID FROM Table2 WHERE Table2.ID = :Parameter2

> Parameters 1 & 2 are valid column names in another table to which is
> attached a datasource which is selected in the DataSource property of my
> query.

> The SQL statement won't execute.  I get an access violation at 4C5F2E19
in
> 'idsql32.dll', reading address 00000009.

> If I replace the parameters with constants (i.e. ...WHERE Table1.ID =
> '1'...), the SQL statement executes fine.

> Does anybody have any suggestions?  I don't have that much experience of
> SQL.  Maybe I'm not able to use a UNION clause with parameterised
queries?

> Thanks for any help received...

> --
> Conor
>    (Remove garbage from email address to activate)



Wed, 18 Jun 1902 08:00:00 GMT
 Parameterised queries and UNION clause



Quote:
> You're not going to get any result unless the two parameters are
IDENTICAL.
>  A UNION means that ONLY those values that are IDENTICAL in both tables
> will be included in the result set. Also, I would reference the ID in the
> first part of the SELECT clause specifically i.e.: Table1.ID instead of
> just ID.  Finally, make sure that the datatype of your parameters are
> compatible with the datatype of the field to be compared.

> > The SQL statement is along the lines of

> > SELECT ID FROM Table1 WHERE Table1.ID = :Parameter1
> > UNION
> > SELECT ID FROM Table2 WHERE Table2.ID = :Parameter2

Thanks for your reply.  However, I don't see why the two parameters have to
be identical.  Surely your second point should be, that a UNION will only
work if the two result sets you're trying to join have an identical format?
 Which, in my case, they do (ID).  The fact that my query works with
constants in the WHERE clause would support that?  Yeah, I've tried
explicitly refering to e.g. Table1.ID as well.  I'm not creating the
parameters either; they are column names in a table attached via a
TDataSource to the DataSource property of my query.

Once again, thanks for your, and any other, suggestions!

--
Conor
   (Remove garbage from email address to activate)



Wed, 18 Jun 1902 08:00:00 GMT
 Parameterised queries and UNION clause

Quote:

> You're not going to get any result unless the two parameters are IDENTICAL.
>  A UNION means that ONLY those values that are IDENTICAL in both tables
> will be included in the result set.

No, only their types have to be identical.

Otherwise, it wouldn't be UNION but INTERSECTION.

        Arne.



Wed, 18 Jun 1902 08:00:00 GMT
 Parameterised queries and UNION clause

Hmmm... as a matter of fact, you're right.  It is by type rather than
value.  By the definition I'm going by, the UNION will eliminate duplicates
too unless the ALL keyword follows.



Quote:

> > You're not going to get any result unless the two parameters are
IDENTICAL.
> >  A UNION means that ONLY those values that are IDENTICAL in both tables
> > will be included in the result set.

> No, only their types have to be identical.

> Otherwise, it wouldn't be UNION but INTERSECTION.

>    Arne.



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Write a parameterised query to a temp table based on a dynamic where clause

2. Weird error with Parameterised Union joins with Oracle

3. Parameterised WHERE Clause

4. ADO cannot open SQL2000 Stored Procecedures with parameterised FROM clause

5. Parameterised WHERE Clause

6. Parameterised WHERE Clause

7. ADO Parameterised queries

8. Opening a Parameterised Access Query as CRecordSet

9. Simple question about parameterised queries

10. Calling parameterised MS Access query from VB

11. Oracle PO8, MS Excel, Parameterised queries & dates

12. Parameterised Queries


 
Powered by phpBB® Forum Software