cancel
Showing results for 
Search instead for 
Did you mean: 

CREATE TABLE ... AS SELECT query with placeholders fails

Former Member
0 Kudos

Connecting to SAP HANA via pyodbc, using libodbcHDB32.so driver on 32bit Ubuntu.

I'm trying to create a table from a subquery, which has some placeholders:


>>> cursor.execute('CREATE TABLE temporary_table AS (SELECT ? AS a FROM DUMMY) WITH DATA', [1])

Traceback (most recent call last):

  File "<stdin>", line 1, in <module>

pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000')

My query clearly has a single placeholder, and I'm passing a single value for this single placeholder.

If I change my query to a simple select from a subquery instead of a CREATE TABLE, it works fine:


>>> cursor.execute('SELECT * FROM (SELECT ? AS a FROM DUMMY) AS sub', [1])

<pyodbc.Cursor object at 0xb70f0b80>

If I change my query to select a constant value instead of a value passed via a placeholder, it also works fine:


>>> cursor.execute('CREATE TABLE temporary_table AS (SELECT 1 AS a FROM DUMMY) WITH DATA')

<pyodbc.Cursor object at 0xb71bcb80>

The only scenario that fails is a CREATE TABLE query with placeholders. I'm completely baffled and I have no idea what else to try. I need to create temporary tables with data from selects that need values passed via placeholders.

Any help would be appreciated.

Accepted Solutions (0)

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

Hi Mihail,

from what you write I understand that you want to be able to dynamically define the column or the list of columns that should be used in the create table as select sub-select.

Correct?

But this is not working with the ? (query parameter). Query parameter values are always used as constant values in queries during execution time. They are never treated as identifiers or key words.

That's standard SQL and not a HANA specific limitation.

For a dynamic table creation you might want to look into dynamic SQL instead.

- Lars

p.s. and yes, the first error message is misleading, since a parameter value actually had been supplied.

However, the WITH DATA clause doesn't make any sense, because your statement wouldn't even touch the DUMMY table data - it would simply insert your constant.

Also: WITH DATA is only valid for the CREATE TABLE ... LIKE command.

Former Member
0 Kudos

No, this is just a minimal example that triggers the problem. In my actual query I'm selecting data from other tables, using WHERE clauses that require parameters. I'm not trying to pass column names as parameters, I'm just selecting constant data just to have something for the CREATE statement.

As for the FROM DUMMY, this seems to be a weird requirement of every SELECT query in HANA to refer to tables, even when no data from the table is actually referenced. A simple "SELECT 'constant'" query with no FROM clause fails.

And no, WITH DATA also works with CREATE TABLE ... AS SELECT queries as per CREATE TABLE - SQL Reference - SAP Library. All query examples included in my question are tested, and the example with no placeholder still uses the WITH DATA clause, and it does not result in error.

lbreddemann
Active Contributor
0 Kudos

Please show your actual problem in order to get a proper solution.

I still don't understand why you don't use CREATE TABLE LIKE if you want to have the same structure in the new table.

The DUMMY table is similar to Oracles/IBMs DUAL table. Unlike MS SQL SERVER where you can just select out of the blue, HANA (and the other DBMS) require a row source to produce a non-empty result set.

DUMMY (DUAL) is always filled with exactly one record. So we can always select from it to create records from scratch or to evaluate functions and the like.

- Lars

Former Member
0 Kudos

I can't use CREATE TABLE LIKE, because I do not want to create a table with the same structure as another table — I want to create a temporary table holding the actual result from a SELECT statement, so that I can reuse that data set in subsequent queries.

My actual query will be completely useless to you because you do not have the actual tables I'm referring to. I've made the actual effort to isolate the problem to the simplest possible query that reproduces the issue, which is straightforward to reproduce without building the entire structure of my database. I am demonstrating my problem — I am showing you a query that should succeed, but fails without any reason instead.

Still, here's my actual query. Let's see if it will be more useful to you:


CREATE LOCAL TEMPORARY TABLE #feed_activity_recipients AS (

     SELECT prof2_id AS profile_id FROM datastore_relation

     WHERE prof1_id = ? AND distance = 1

     UNION DISTINCT

     SELECT profile_id FROM datastore_position

     WHERE is_current = 1 AND company_id IN (

          SELECT company_id FROM datastore_position WHERE is_current = 1 AND profile_id = ?

     )

     UNION DISTINCT

     SELECT profile_id FROM datastore_groupmembership WHERE li_group_id IN (

          SELECT li_group_id FROM datastore_groupmembership WHERE profile_id = ?

     )

) WITH DATA;

Executed with parameters (927331, 927331, 927331)

lbreddemann
Active Contributor
0 Kudos

Well, again, the WITH DATA clause is wrong in your code.

It belongs to the CREATE TABLE LIKE command - not to the CREATE TABLE AS command.

Anyhow, I tested this with correct syntax and it still fails with the wrong number of parameters error. For now you have to create the table structure first and insert the data in a second step.

The other thing is: if you just want to refer to the result set later on, it's much better to use table variables instead of temp. tables.

- Lars

former_member182302
Active Contributor
0 Kudos

Hi Lars,


Lars Breddemann wrote:

The other thing is: if you just want to refer to the result set later on, it's much better to use table variables instead of temp. tables.

Have a question, can you throw some more light on the above statement on why using Table variables are better than temp. tables?

My observations on using Temp Tables below:

1) Locking may happen when multiple users use that procedure, but as it is a session specific structure i did not see it happen  ( need your inputs whether my understanding was correct )

2) When users increase i saw performance degrading a considerable rate for Temporary tables i was thinking it to be because of "insert" operation cost is it correct?

3) Only advantage with temp tables i saw is when you want to perform some logic instead of doing on entire data set, you can use a limited scope data which you can insert into temporary table ( if dynamic filters we can use Exec ) and further use it.

Even in the implementation we did we are asked to remove Temp tables as much as possible and then it is where we used EXEC IMMEDIATE. ( We need to use because we had a lot of filters close to 30 and multiple if else blocks ) to remove the temporary tables we used in our procedures.

Could you please help me to understand the differences better and why table variables are preferred over to Temp Tables? And why Temp tables have should not be used ?

Regards,

Krishna Tangudu

lbreddemann
Active Contributor
0 Kudos

Hey Krishna,

table variables cannot be used for data modification operations (INSERT/UPDATE/DELETE), but solely for SELECTs. Based on the description given before, I was under the impression, that for this scenario one result set is created that will then be used a couple of times in the procedure.

For that kind of scenarios table variables are beneficial, because they don't force the materialization of the result set.

Instead every use of the table variable is part of a data flow graph through the procedure and can be optimized so that only relevant parts of it are actually used.

Also the table variables are just that - variables, so they hand over the contents by reference, where a temp. table is literally building an independent table in memory with no compression whatsoever.

Alright, as your remaining follow-up questions are rather broad, I think they deserve their own thread

- Lars

former_member182302
Active Contributor
0 Kudos

Thanks Lars for the reply . I understand now what you meant by your statement

I shall open another thread for other questions as suggested.

Regards.

Krishna Tangudu