Typecasting string as a table

[Former Member]

Hi,

I had been trying to get user input for schema name(X) and table name(Y) . Then concatenate it to a string "X"."Y". Then select * from "X"."Y"

I am getting an error saying its not possible to select from a string.

Any thoughts on how i can proceed?

Thank you for your time .

Regards

Meenu

[lbreddemann · Accepted solution]

Hi Meenu

I cannot look into the future, so I may be wrong, in fact I will probably be wrong.

But I believe what will happen now is this:

1. You'll somehow hear about dynamic SQL and get all excited about it as it seems to solve your problem.

2. You stop looking for a solution, because you think you found the perfect answer at step 1. And when the problems with this approach start to surface you likely won't be there (as you are the developer of the system, not the user, administrator or maintainer).

Please carefully think about the requirement here.

You have a query that you want to run against a table in a schema of which both is unknown at the time you design the query.

What kind of super-clever-flexible data design might that be?

- Lars

[former_member182302 · Accepted solution]

Adding to the Lars comments, If you still feel it is unavoidable and looking to frame your query dynamically then you got to use EXEC or EXECUTE IMMEDIATE

You can find a sample here in the below link:

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

Hi Lars,

Yes you got it wrong .. those are similar resultant tables from one algorithm , written back as hana tables. gonna sip this as a solution to customer . So i dont want them to be restricted to which schema they write on the results. so results give answers for different questions. I am trying to put it into an explorer and do same analysis for may be 7-8 different resultant tables.

Hope that clears the doubt ..

Thank You

Meenu

[Former Member · Accepted solution]

Hi Krisna ,

I tried EXEC'select * from "SCHEMA"."TABLE"';

where "SCHEMA"."TABLE" is a string from some selection in an UI .

This will return the same typecasting issue as i mentioned

Any further ideas appreciated.

Thank You

Meenu

[former_member182302 · Accepted solution]

Did you keep the EXEC statement in the procedure  as shown in that document and then called the procedure? And you landed up with that error?

Can you share the error screenshot?

On the side note-- You are running some algorithim for which you will get results in multiple tables and then you want to use BO Explorer on top of the views created on this table? Some how i got lost in requirement and the solution you are trying to work on. Can you explain a bit further?

Regards,

Krishna Tangudu

[lbreddemann · Accepted solution]

Meenu

Up to you - I am not there to convince you about that what you're doing there is probably not the best idea. Just don't say afterwards that nobody told you

Cheers, Lars

[Former Member · Accepted solution]

Lars,

That was no way helping a type casting Question ,

But good spirit of proving me wrong. Your message received, i may be wrong in the thought process. I will surely reconsider the way i am doing .

But question left unanswered.But I would like to know how typecasting work here

Thank You

Meenu

[lbreddemann · Accepted solution]

Meenu

I told you how this would work.dynamic SQL.

Krishna told you how this would work: EXEC (IMMEDIATE) which is, guess what: dynamic SQL (the same I told you about).

I even pointed to the fact that dynamic SQL is the wrong tool 99% of the time just to make sure you don't waste your time with something that sound alluring to most SQL developers first and that turns out to be not the right thing afterwards.

That what not about proving you wrong but providing help.

If you don't want to take advise - fine with me.

Be happy with just the correct use of syntax instead:

create schema S1;

create schema S2;

create column table s1.tab1 (id integer, name varchar(20));
insert into s1.tab1 values (1, 'LARS');

create column table s2.tab1 (id integer, name varchar(20));
insert into s2.tab1 values (1, 'PAUL');

create procedure sel_switch (IN schema_name varchar, IN table_name varchar)
LANGUAGE SQLSCRIPT
as

BEGIN

    EXECUTE IMMEDIATE ( 'SELECT * FROM ' || :schema_name  || '.' || :table_name );

END;

call sel_switch ('S1', 'TAB1');

/*
ID  NAME
1   LARS
*/

call sel_switch ('S2', 'TAB1');

/*
ID  NAME
1   PAUL
*/

Works on my Rev 70 instance without any "type cast" issue...

- Lars

[Former Member · Accepted solution]

Hi Lars,

Sorry for misinterpreting . I appreciate  your help

I was doing

EXEC('select * from ' || :full_qualifier); which apparently did not work .

Somehow I overlook Krishna saying execute immediate .

Your suggestion works.EXECUTE IMMEDIATE('select * from ' || :full_qualifier) ;

I am rethinking options, The main concern in my project was to give the end user an interactive GUI experience. I will send you a detailed mail on what I was thinking to achieve ,May be you can guide me in the thought process. I am sure there will be some issues in thought process.

Thank You for helping out .

Have a good day!

Regards

Meenu