Create table from Multiple tables (unknown number)

[Former Member]

Hi,

Is it possible to create a overview table in Sybase from multiple other tables?

The number of tables to create this overview table.. depends on number of partitions.

Is there a way where create table statement can have a where clause to know number of partitions and then loop through it.

I know we can use Stored Procedure.. but what if the number of partitions gets changed? How can this be adapted.

Appreciate any help/suggestions. Thanks.

[markmumy]

Do you want to move data from one table (or set of tables) to another?  Or do you want to present a logical view of the data?

From your initial question, it sounds like a view over the other tables would be best.

Mark

[Former Member]

Hi Mark, I want to create a table with fewer columns and which contains data from multiple other partitioned table.

[Former Member]

Just thinking what if a Stored Procedure is created to create a table as union all of multiple tables?

I am new to Stored Procedures so not sure if this is possible and if so how to write SP?

[markmumy]

OK.  You still have two choices.  You could just create a logical table (VIEW) on the data.  That's the easiest and most dynamic.  It does not involve any data movement on your part.  But it can be a bit slower as we have to stitch the data together at runtime.  Depending on the type of view and queries performance may be great.

The second choice would be to physically create a table.  You could do this with just straight SQL or in a stored procedure.

What I would recommend is that you write the SELECT statement that will get your data out of the tables.  Once you have the SELECT you can easily change it to an INSERT (to add data to an existing table) or a SELECT INTO (if you want to create a new table).

Once you have that we can walk through putting it into a stored procedure.  Let's get the SQL first, then work on putting parameters into it and making it a procedure.

Mark

[Former Member]

Thanks Mark.

So, for 1st option - view

If I create a view on top of existing view by adding group by would it show any performance improvement in Webi reports?

For 2nd option I tried to create a Stored Procedure but getting few errors. Can you spot what is going wrong here?

create procedure summary_table()

begin

declare @cnt varchar(20)

declare @i varchar(20)

declare @sql clob

declare @part_tname varchar(250)

set @sql= ' '

select @cnt=count(*) from systable where table_name like 'SALES_%'

set @sql='select * into SALES_SUMMARY from ('

while @i <= @cnt

begin

if (@i < 10)

  set part_tname = 'SALES_0' + i

else

  set part_tname = 'SALES_' + i

set @sql= @sql + 'SELECT date,time,no,avg(counter1),avg(counter2)

  from

  @part_tname

  group by

  date,time,no union all'

end

SET @sql = LEFT(@sql, LEN(@sql) - 10);

set @sql=@sql+ ')';

execute immediate   @sql;

end

[markmumy]

On the surface, I see that you are mixing syntax.  The lines that end with ';' are the Watcom SQL dialect (the SET statements at the bottom).  Those lines that don't have a ';' are T-SQL (like your DECLARE lines).  You cannot mix the two syntaxes.  That would be like writing a C program with both C and Java code.  Can't be done.

I would recommend that you rewrite this for Watcom SQL and make sure that every line terminates with a ';'.

Mark

[Former Member]

Thanks Mark.

I tried to add semicolon to all lines except begin,end,if,else. But I am getting following error -

ERROR: 12:01:16  [CREATE - 0 row(s), 0.000 secs] [Error Code: 102, SQL State: 42W04]  SQL Anywhere Error -131: Syntax error near '(end of line)' on line 3

WARNING: 12:01:17  [DECLARE - 0 row(s), 0.178 secs] Command processed. No rows were affected

WARNING: 12:01:17  [DECLARE - 0 row(s), 0.173 secs] Command processed. No rows were affected

WARNING: 12:01:18  [DECLARE - 0 row(s), 0.181 secs] Command processed. No rows were affected

ERROR: 12:01:18  [SET - 0 row(s), 0.000 secs] [Error Code: 137, SQL State: 42W14]  SQL Anywhere Error -260: Variable '@sql' not found

ERROR: 12:01:18  [SELECT - 0 row(s), 0.000 secs] [Error Code: 137, SQL State: 42W14]  SQL Anywhere Error -260: Variable '@cnt' not found

ERROR: 12:01:19  [SET - 0 row(s), 0.000 secs] [Error Code: 137, SQL State: 42W14]  SQL Anywhere Error -260: Variable '@sql' not found

ERROR: 12:01:19  [WHILE - 0 row(s), 0.000 secs] [Error Code: 102, SQL State: 42W04]  SQL Anywhere Error -131: Syntax error near '(end of line)' on line 4

ERROR: 12:01:19  [ELSE - 0 row(s), 0.000 secs] [Error Code: 102, SQL State: 42W04]  SQL Anywhere Error -131: Syntax error near 'else' on line 1

ERROR: 12:01:20  [SET - 0 row(s), 0.000 secs] [Error Code: 137, SQL State: 42W14]  SQL Anywhere Error -260: Variable '@sql' not found

ERROR: 12:01:20  [END - 0 row(s), 0.000 secs] [Error Code: 102, SQL State: 42W04]  SQL Anywhere Error -131: Syntax error near 'end' on line 2

ERROR: 12:01:20  [SET - 0 row(s), 0.000 secs] [Error Code: 137, SQL State: 42W14]  SQL Anywhere Error -260: Variable '@sql' not found

ERROR: 12:01:21  [EXECUTE - 0 row(s), 0.000 secs] [Error Code: 504, SQL State: 52W09]  SQL Anywhere Error -265: Procedure 'immediate' not found

ERROR: 12:01:21  [END - 0 row(s), 0.000 secs] [Error Code: 102, SQL State: 42W04]  SQL Anywhere Error -131: Syntax error near 'end' on line 1

[markmumy]

Post your new version and I will look at it.  Likely it is not just the ';' addition, but a syntax change as some commands are TSQL and some are WatcomSQL.

Mark

[markmumy]

Here's a version that I made from the original post. In some parts you had variables but didn't have the '@' symbol.  the IF statement needed to be converted to Watcom SQL.  The While loop needed to be converted to Watcom SQL.

You didn't initialize @i to it always had NULL which prevented you from going into the while loop.

I also converted your @i and @cnt to int variables rather than characters.

You didn't increment @i in the while loop meaning that this was an infinite loop.

I added the 'message' statement to the end to print the @sql contents to the client output.  You can comment it out using two dashes '--' or just remove it.

drop table if exists SALES_01;

drop table if exists SALES_02;

drop table if exists SALES_03;

drop table if exists SALES_04;

drop table if exists SALES_05;

create table SALES_01 ( a1 int );

create table SALES_02 ( a1 int );

create table SALES_03 ( a1 int );

create table SALES_04 ( a1 int );

create table SALES_05 ( a1 int );

drop procedure if exists summary_table

;

create procedure summary_table()

begin

--declare @cnt varchar(20);

--declare @i varchar(20);

declare @cnt int;

declare @i int;

declare @sql clob;

declare @part_tname varchar(250);

set @sql= ' ';

set @i= 1;

select count(*) into @cnt from systable where table_name like 'SALES_%';

set @sql='select * into SALES_SUMMARY from (';

while @i <= @cnt loop

  if (@i < 10) then

    set @part_tname = 'SALES_0' || @i;

  else

    set @part_tname = 'SALES_' || @i;

  end if;

  set @sql= @sql + 'SELECT date,time,no,avg(counter1),avg(counter2)

    from

    @part_tname

    group by

    date,time,no union all ';

    set @i = @i+1;

end loop;

SET @sql = LEFT(@sql, LEN(@sql) - 10);

set @sql=@sql+ ')';

message @sql to client;

execute immediate   @sql;

end

;

CALL summary_table();

[Former Member]

Thanks Mark.

I corrected the code as you mentioned above.

But still facing following errors -

11:07:11  [DROP - 0 row(s), 0.000 secs]  Command processed. No rows were affected

11:07:11  [CREATE - 0 row(s), 0.000 secs]  [Error Code: 102, SQL State: 42W04]  SQL Anywhere Error -131: Syntax error near '(end of line)' on line 3

11:07:11  [DECLARE - 0 row(s), 0.000 secs]  Command processed. No rows were affected

11:07:11  [DECLARE - 0 row(s), 0.000 secs]  Command processed. No rows were affected

11:07:11  [DECLARE - 0 row(s), 0.000 secs]  Command processed. No rows were affected

11:07:11  [SET - 0 row(s), 0.000 secs]  [Error Code: 137, SQL State: 42W14]  SQL Anywhere Error -260: Variable '@sql' not found

11:07:11  [SET - 0 row(s), 0.000 secs]  [Error Code: 137, SQL State: 42W14]  SQL Anywhere Error -260: Variable '@i' not found

11:07:11  [SELECT - 1 row(s), 0.032 secs]  [Error Code: 137, SQL State: 42W14]  SQL Anywhere Error -260: Variable '@cnt' not found

11:07:11  [SET - 0 row(s), 0.000 secs]  [Error Code: 137, SQL State: 42W14]  SQL Anywhere Error -260: Variable '@sql' not found

11:07:11  [WHILE - 0 row(s), 0.000 secs]  [Error Code: 102, SQL State: 42W04]  SQL Anywhere Error -131: Syntax error near '(end of line)' on line 3

11:07:11  [ELSE - 0 row(s), 0.000 secs]  [Error Code: 102, SQL State: 42W04]  SQL Anywhere Error -131: Syntax error near 'else' on line 1

11:07:11  [END - 0 row(s), 0.000 secs]  [Error Code: 102, SQL State: 42W04]  SQL Anywhere Error -131: Syntax error near 'end' on line 1

11:07:11  [SET - 0 row(s), 0.000 secs]  [Error Code: 137, SQL State: 42W14]  SQL Anywhere Error -260: Variable '@sql' not found

11:07:11  [END - 0 row(s), 0.000 secs]  [Error Code: 102, SQL State: 42W04]  SQL Anywhere Error -131: Syntax error near 'end' on line 1

11:07:11  [SET - 0 row(s), 0.000 secs]  [Error Code: 137, SQL State: 42W14]  SQL Anywhere Error -260: Variable '@sql' not found

11:07:11  [SET - 0 row(s), 0.000 secs]  [Error Code: 137, SQL State: 42W14]  SQL Anywhere Error -260: Variable '@sql' not found

11:07:11  [EXECUTE - 0 row(s), 0.000 secs]  [Error Code: 504, SQL State: 52W09]  SQL Anywhere Error -265: Procedure 'immediate' not found

11:07:11  [END - 0 row(s), 0.000 secs]  [Error Code: 102, SQL State: 42W04]  SQL Anywhere Error -131: Syntax error near 'end' on line 1

... 18 statement(s) executed, 1 row(s) affected, exec/fetch time: 0.032/0.000 sec  [0 successful, 4 warnings, 14 errors]

[markmumy]

How are you executing the stored procedure?  The output that you show is not familiar to me.  It almost looks as if you are running this through a debugger?

When I run the code above, as is, via Interactive SQL (dbisql), it completes with no errors and returns the UNION ALL string.

Can you try running this through dbisql?  Based on your output it would seem that something is getting garbled in the variable declarations via the debugger or profiler that you are using.

Also, which version of IQ are you using?  It shouldn't affect this, but it is good to know.

Mark

[Former Member]

Hi Mark,

I am using Dbvisualizer to run this.

Thanks

[markmumy]

II've not run DBvisualizer in a while. Can you run this through our tools?  Use Interactive SQL.  I think you are fighting what DBvisualizer can and cannot do.

Mark

[Former Member]

Thanks Mark.

As you mentioned I tried to run this using Interactive SQL.

It did create the Stored Procedure.

But when I run - CALL summary_table();

I dont see the table created and also in the results it says -

1 row affected

Procedure completed

But something is going wrong which I am unable to figure out.