SQL Anywhere database - Dynamic Columns

Author

Dynamic Columns

Chris Ceniza

2005-06-16, 3:24 am

Well this problem is quite old... I hope you guys can now
assist me :)

The problem is to create a query the would yield a result
set like this

itemcode warehouse 1 warehouse 2 warehouse 3 ....
.... ....
00001 10 5
6 .... .... ....
00002 12 7
8 .... .... ....

the ".... .... ...." represents that the columns are DYNAMIC
which means that every time I add a new warehouse it should
automatically register on above result set.

I can always do a query like

select a.itemcode,
warehouse_1 = ( select b.qty from
item_location b where
b.itemcode =
a.itemcode )
.... so on an so forth
but this would make me update my query everytime I add a new
warehouse, so it doesn't help.

Is this a GOOD PROBLEM?

More Power,

Chris

Mark Culp

2005-06-16, 3:24 am

I've done this type of thing a number of times... the basic
principle is to construct your query in a string, and then
execute the query using execute immediate or by opening a
cursor on the query.

Example (taken from my show_table.sql webservice example included in 9.0.x):
declare query long varchar;
set query = 'select list( ''<th>''||html_encode(c.column_name)||''</th>'',
'''' )'
|| ' from sys.syscolumn c, sys.systable t'
|| ' where t.table_id = c.table_id'
|| and_user
|| ' and t.table_name = ''' || table_name || '''';
execute immediate 'insert into #res(s) ' || query;

Using this method, you can create as complicated a query as you like
using any other information that is available to you - in your case the
number of warehouses.
--
Mark Culp
SQLAnywhere Research and Development
-------------------------------------------------------------------------
** Whitepapers, TechDocs, bug fixes are all available through the **
** iAnywhere Developer Community at http://www.ianywhere.com/developer **
-------------------------------------------------------------------------
* <<<<< Please always include the ASA version and build number >>>>> *
* <<<<< _and_ the operating system version that you are using >>>>> *
-------------------------------------------------------------------------

Chris, Ceniza wrote:
>
> Well this problem is quite old... I hope you guys can now
> assist me :)
>
> The problem is to create a query the would yield a result
> set like this
>
> itemcode warehouse 1 warehouse 2 warehouse 3 ....
> ... ....
> 00001 10 5
> 6 .... .... ....
> 00002 12 7
> 8 .... .... ....
>
> the ".... .... ...." represents that the columns are DYNAMIC
> which means that every time I add a new warehouse it should
> automatically register on above result set.
>
> I can always do a query like
>
> select a.itemcode,
> warehouse_1 = ( select b.qty from
> item_location b where
> b.itemcode =
> a.itemcode )
> .... so on an so forth
> but this would make me update my query everytime I add a new
> warehouse, so it doesn't help.
>
> Is this a GOOD PROBLEM?
>
> More Power,
>
> Chris

Chris Ceniza

2005-06-16, 3:24 am

Thanks I'll give it a try ... (btw, I'm using ASA 8.0.3 on
windows server 2003)

[color=darkred]
> I've done this type of thing a number of times... the
> basic principle is to construct your query in a string,
> and then execute the query using execute immediate or by
> opening a cursor on the query.
>
> Example (taken from my show_table.sql webservice example
> included in 9.0.x):
> declare query long varchar;
> set query = 'select list(
> ''<th>''||html_encode(c.column_name)||''</th>'', '''' )'
> || ' from sys.syscolumn c, sys.systable t'
> || ' where t.table_id = c.table_id'
> || and_user
> || ' and t.table_name = ''' || table_name || '''';
> execute immediate 'insert into #res(s) ' || query;
>
> Using this method, you can create as complicated a query
> as you like using any other information that is available
> to you - in your case the number of warehouses.
> --
> Mark Culp
> SQLAnywhere Research and Development
> ----------------------------------------------------------
> --------------- ** Whitepapers, TechDocs, bug fixes are
> all available through the ** ** iAnywhere Developer
> Community at http://www.ianywhere.com/developer **
> ----------------------------------------------------------
> --------------- * <<<<< Please always include the ASA
> version and build number >>>>> * * <<<<< _and_ the
> operating system version that you are using >>>>> *
> ----------------------------------------------------------
> ---------------
>
> Chris, Ceniza wrote:

Sponsored Links