Subqueries not supported error

[Former Member]

Good day,

I'm trying to do a simple nested select statement in a procedure but keep getting errors. In the code below I delete from a table where its VERSIONID falls within a range returned by a select-from-where statement. The code as it is below returns the error: "feature not supported: subqueries are not supported in where condition".

DELETE FROM "EPM_PCM"."IQ_PP_ACTIVITYDRIVERVALUE"

WHERE "EPM_PCM"."IQ_PP_ACTIVITYDRIVERVALUE"."VERSIONID" IN (

SELECT "VERSIONID" FROM "EPM_PCM"."SDA_PP_ACTIVITYDRIVERVALUE"

WHERE "PERIOD_NAME" = "MARCH_YTD")

Replacing the SELECT statement in the IN clause with the values I'm expecting from the SELECT produces the correct result. I've tried table variables but that also returns an error.

Any ideas would be appreciated

[former_member1334]

Hi,

Can you try the below steps:

1) Select the inner query into a temp table

lt_del = SELECT "VERSIONID" FROM "EPM_PCM"."SDA_PP_ACTIVITYDRIVERVALUE"

WHERE "PERIOD_NAME" = "MARCH_YTD";

2) Delete from the actual table by using the temp table id's

DELETE FROM "EPM_PCM"."IQ_PP_ACTIVITYDRIVERVALUE"

WHERE "EPM_PCM"."IQ_PP_ACTIVITYDRIVERVALUE"."VERSIONID" IN (SELECT VERSIONID FROM :lt_del);

Hope this helps.

Regards,

Charles

[marco_glaser]

Hi Charles,

2) Delete from the actual table by using the temp table id's 
DELETE FROM "EPM_PCM"."IQ_PP_ACTIVITYDRIVERVALUE"
WHERE "EPM_PCM"."IQ_PP_ACTIVITYDRIVERVALUE"."VERSIONID" IN (SELECT VERSIONID FROM :lt_del);

As it seems that the subquery is not supported, I guess it will not make a difference if you do a SELECT on a temp table. That's why I suggested to Loop over the results of the inner query and concatenate the version IDs to a String which then can be used for the outer statement.

First call the inner select:
SELECT "VERSIONID" FROM "EPM_PCM"."SDA_PP_ACTIVITYDRIVERVALUE"
WHERE "PERIOD_NAME" = "MARCH_YTD"
Then use the results to concatenate them into the delete statement where condition. Then it should look like this at the end:
                    DELETE FROM "EPM_PCM"."IQ_PP_ACTIVITYDRIVERVALUE"
                    WHERE "EPM_PCM"."IQ_PP_ACTIVITYDRIVERVALUE"."VERSIONID" IN (xxx, yyy, zzz)

This delete statement should work because it does not use a subquery.

[marco_glaser]

Are you trying to delete from a virtual table which is provided by Smart Data Access?

I am currently facing the same problem when trying to use a subquery in delete statement for virtual tables. It seems like this is not supported by Smart Data Access.

[Former Member]

Hi Marco,

Those are tables in my archiving system (Sybase IQ). I don't remember if they were called virtual tables

[marco_glaser]

So how do you access them from your HANA system?

Did you use HANA Smart Data Access?

If yes, it seems this is the problem. To solve it, I did a workaround:

First call the inner select:

SELECT "VERSIONID" FROM "EPM_PCM"."SDA_PP_ACTIVITYDRIVERVALUE"

WHERE "PERIOD_NAME" = "MARCH_YTD"

Then use the results to concatenate them into the delete statement where condition. Then it should look like this at the end:

                    DELETE FROM "EPM_PCM"."IQ_PP_ACTIVITYDRIVERVALUE"

                    WHERE "EPM_PCM"."IQ_PP_ACTIVITYDRIVERVALUE"."VERSIONID" IN (xxx, yyy, zzz)

This delete statement should work because it does not use a subquery.

[Former Member]

Hi Cobus.

A golden rule for sub query is that the sub query that return more than one row can only be used with multiple value operators, such as the IN operator.

In your case i suspect PERIOD_NAME" = "MARCH_YTD  will return only one row.

Check in schema if you have only one active row for the condition.In that case you can directly use it in the main query.

Hope it helps.

Regards,

Kannan

[Former Member]

Hi Kannan,

My bad - the code used to specify IN. I've just played around with the so many different options and have left it at '=' but nothing works. I am expecting at least 2 values to be returned...

[I051505]

Hello,

Please check if the name of field is correct. Eg. PERIODID instead of PERIOD_NAME.

Take care

thierrY