Multiple Select commands in a Collector query - This statement type is not supported

[ShlomoKatz]

Hello

G&L version 7.1.1.p1 an p2

I was supplied a complex sql query to gather information from a SQL database.

In order to get the information it requires two select (in-line?) statements.

Example:

   Select a.email, a.user, a.group from user_account UA

   (SELECT a.user_account_key, MIN(b.entry_time) FIRST_LOGON, MAX(b.entry_time) LAST_LOGON, COUNT(*)    NUM_LOGONS ......
   FROM user_account A)

When I run the test it responds with This statement type is not supported

There are a lot of inner joins as well, but the issue seems to only occur when the second query is added.

The full query works from in SQL Developer.

[KristianNordman · Accepted solution]

If the application requires a complex SQL to collect data, I would ask the DBA to create a view for it. This has a few advantages for the IGL team:

• Simpler SQL
  In IGL you can keep a simple SQL to fetch what you need from the view without joins or multiple selects.

• Shift in maintenance
  The DBA for the application, who probably knows the application fairly well, can update and optimize the view over time without heavy coordination with the IGL team, provied the same data is still presented in the view.
• Less Administration
  In case of a version upgrade, IGL would not need to test as much as long as a new view is created which delivers the same data as before.

If you still want to go with the multiple Select way, look into UNION. It should be available on most dialects.

[EdwardBertsch2]

I also get the "This statement type is not supported" error when trying a fairly simple SQL, 

Version 7.1.1.166242 P02_HF01

Select distinct
bpuser.USERNAME
,bpuser.LASTLOGINTIME
FROM [BPMAINDB].[dbo].[BPUSER] bpuser
INNER JOIN EMPLOYEEAM e on e.id= bpuser.employeeid
WHERE e.ENDTIME is null or getdate() <= e.endtime

The error display is not very helpful. Yes I can ask my DBA to create a view but it's not clear why we should have to do so. I consider this issue a defect, please email me with a JIRA if you agree.

Also once again, we see "No one else had this question" only because it has been marked as answered. That's not good.

Thanks for posting this, Shlomo Katz

[IanStaines]

I see you have opened a RSA Customer Support case for this.  I will update this post when a solution is provided.