Error when using union SQL statement as a source in D-Link.

[John 27182]

Hi all.

I am trying to build a D-Link. For my source I have an "ODBC (SQL) Database". I'm trying to create a union query for my source table. The query is logically OK, when I click the "Fetch" button I can see the table in the "Preview of columns" window. However when I click OK and try to use it in the D-Link the following error message appears:

"Cannot retrieve distinct data from source. All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists".

Has anyone else experienced this? Any suggestions? Thanks.

[ericlfg]

Hey John,

Quick google search of your error reveals 2 technotes that might help you figure out the problem or come up with a workaround:

www-01.ibm.com/support/docview.wss?uid=swg21371356
www-01.ibm.com/support/docview.wss?uid=swg21367352

Cheers

[John 27182]

Hi Eric,

I've tried deleting spaces, and it still doesn't work. The SQL Server workaround is a possibility, but it would be annoying.

Are you able to do union statements in your D-Links? A rather typical type a query I would write is as below. Can you think why this may fail? Thanks for any help.

Select [Field1], [Field2] from [Table1]
UNION
Select [Field1], [Field2] from [Table1]