Create table?

Hi All
I want to create a table with a name as a varible. That variable contains the table name by doing some string operations.
say: create table @.var
@.var contains the table name that is generated.
how could i do this?
plz help me .You will need to use dynamic sql to build your CREATE TABLE command, and then EXEC your dynamic sql.|||Why on earth would you ever want to do that? You are opening yourself up for a complete world of hurt. Whatever you are hoping to accomplish with this, you are most certainly solving the problem completely wrong.
Instead of asking fora horrible hack, you need to ask for advise on how to come up with an effective solution.|||Thanks a lot......... it solved my query.

create stored procedures in every new database

Hi. Is there a way to ensure that every database created on a sql
server contains a specific stored procedure? I have a set of stored
procedures that need to exist in every database on the server. Rather
than constantly checking to see if each database has what's necessary,
I was hoping there was a way to setup a template database that would
contains these sp's, and force every new database to use that as a
starting point. Is anything like this possible? Thanks.
On 9 Sep 2004 13:45:10 -0700, Michael Bosco wrote:

>Hi. Is there a way to ensure that every database created on a sql
>server contains a specific stored procedure? I have a set of stored
>procedures that need to exist in every database on the server. Rather
>than constantly checking to see if each database has what's necessary,
>I was hoping there was a way to setup a template database that would
>contains these sp's, and force every new database to use that as a
>starting point. Is anything like this possible? Thanks.
Hi Michael,
Just create the stored procedure(s) in the model database. That is the
template that will be used for all future new databases.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

create stored procedures in every new database

Hi. Is there a way to ensure that every database created on a sql
server contains a specific stored procedure? I have a set of stored
procedures that need to exist in every database on the server. Rather
than constantly checking to see if each database has what's necessary,
I was hoping there was a way to setup a template database that would
contains these sp's, and force every new database to use that as a
starting point. Is anything like this possible? Thanks.On 9 Sep 2004 13:45:10 -0700, Michael Bosco wrote:
>Hi. Is there a way to ensure that every database created on a sql
>server contains a specific stored procedure? I have a set of stored
>procedures that need to exist in every database on the server. Rather
>than constantly checking to see if each database has what's necessary,
>I was hoping there was a way to setup a template database that would
>contains these sp's, and force every new database to use that as a
>starting point. Is anything like this possible? Thanks.
Hi Michael,
Just create the stored procedure(s) in the model database. That is the
template that will be used for all future new databases.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Create new dimension member based on other members

I have a dimension named category which contains the following members:

O, A, B, C, Cd

Now I want to create a new dimension category (not necessarily in the same hierarchy named LowCaring and which should contain both O and A. How do I do this?

I already tried creating a calculated member with parent hierarchy the category but it doens't do what I need it to do.

Thanks in advance for any help or pointers;

Stijn Verrept.

dear rept,

Try to create a view in your SQL to get only the O and A, and add this view into your datasourceview of Analysis Services.

When create the cube add this object as dimension!

Helped?

regards!!

|||

Hi Pedro, thanks for your reply.

When I would do it in SQL then I need to calculate all the measures connected to that dimension as well?

I have for example a fact table occupation with

OC_Date

OC_Category

OC_Number

The OC_Date is connected to a time dimension and the OC_Category to the category. That table is already filled with facts so SSAS should be able to sum the OC_Number for O and A automatically.

Is that possible?

|||

Yes, It works...

the view you create works in Analysis Services as as other object.. you can define it as a dimension a works as the others.

In your case as you described it works! Try it!

Helped?

Regards!!

|||

Hi Pedro,

I'm trying as you suggested:

I created a new query which lists only the O and A, I added this one to the DSV. Then I created a new dimension which also deploys fine.

The trouble starts when I add this dimension to the cube, then I get this error:

Processing Measure Group 'Occupation' failed.

Start time: 21/07/2007 12:18:29; End time: 21/07/2007 12:18:30; Duration: 0:00:01
Processing Partition 'Occupation' failed. 1 rows have been read.
Start time: 21/07/2007 12:18:30; End time: 21/07/2007 12:18:30; Duration: 0:00:00
SQL queries 1
SELECT [dbo_Occupation].[OC_Number] AS [dbo_OccupationOC_Number0_0],[dbo_Occupation].[OC_HOID] AS [dbo_OccupationOC_HOID0_1],[dbo_Occupation].[OC_CAID] AS [dbo_OccupationOC_CAID0_2],[dbo_Occupation].[OC_Date] AS [dbo_OccupationOC_Date0_3]
FROM [dbo].[Occupation] AS [dbo_Occupation]
Error Messages 2
Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Occupation, Column: OC_CAID, Value: 3. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Categorie of Dimension: Laagzorgbehoevenden from Database: WZM, Cube: Dossier DW, Measure Group: Occupation, Partition: Occupation, Record: 3.

Occupation is the fact table, Laagzorgbehoevenden is the new dimension with only O and A.

What did I do wrong?

|||

Occupation is your dimension or Fact?!

Regards

|||Occupation is my fact. Category is the dimension table with (O, A, B, C, CD, ...) and Laagzorgbehoevenden is the dimension table with only O and A, based on the query I make like you said.|||

And what is the factTable?

Link the Laagzorgbehoevenden to your factTable with uniqueID in datasourceview... did it?

Regards

|||

The fact table is occupation. When I link the laagzorgbehoevenden to occupation in DSV and try to deploy I get:

Warning 2 Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Occupation, Column: OC_CAID, Value: 3. 0 0

Which is normal because the fact table (occupation) contains values that aren't in laagzorgbehoevenden, because laagzorgbehoevenden only contains O and A. Categories contains them all.

I think this is the wrong way to go. Isn't there another easier way of getting 2 dimension members and creating a third that contains them both and sums the occupation values of those both?

|||

There are several ways to do this. Following PedroCGD's advice, create another column in the view you are using to build the Category dimension. You can do this by adding a derived column to the UDM or by modifying the underlying table. The definition for the new column would look something like this:

Code Snippet

CASE category

WHEN 'O' THEN 'LowCaring'

WHEN 'A' THEN 'LowCaring'

ELSE 'Other'

END AS CategoryType

Add the CategoryType as a new attribute hierarchy in the Category dimension. The two attribute hierarchies will act independently.

I hope I've interpreted the question correctly.

|||

Dear Friend,

Martin Mason
was more rapid than me! :-)

I hope I get it!!

If not post here, I will try to help until you get it!

Regards!!

|||

Thanks Martin,

This works great! A followup question, is it somehow possible to hide the Other value for the users (in the browser for example)?

Create index on col with data and without data, any time differenc

Hello:
In terms of the time it takes to create an index (non-clustered) in a
column, would it be faster if the column contains only null? Or the time it
takes to create an index will be the same whether this column has data or not.
Thanks,
Q
Hi
I don't think there will be a significant difference in the time taken to
create the index, but if you then significantly change the data in the
column, the index may be fragmented and the statistics out of date, therefore
it would be better to build the index once populated.
John
"Q" wrote:

> Hello:
> In terms of the time it takes to create an index (non-clustered) in a
> column, would it be faster if the column contains only null? Or the time it
> takes to create an index will be the same whether this column has data or not.
> Thanks,
> Q
|||No rows are omitted from an index. Not even rows where the indexed
column is NULL. So the time to index the column would be the same,
regardless of any value (or NULL) (assuming fixed size data type).
Gert-Jan
Q wrote:
> Hello:
> In terms of the time it takes to create an index (non-clustered) in a
> column, would it be faster if the column contains only null? Or the time it
> takes to create an index will be the same whether this column has data or not.
> Thanks,
> Q

Create index on col with data and without data, any time differenc

Hello:
In terms of the time it takes to create an index (non-clustered) in a
column, would it be faster if the column contains only null? Or the time it
takes to create an index will be the same whether this column has data or no
t.
Thanks,
QHi
I don't think there will be a significant difference in the time taken to
create the index, but if you then significantly change the data in the
column, the index may be fragmented and the statistics out of date, therefor
e
it would be better to build the index once populated.
John
"Q" wrote:

> Hello:
> In terms of the time it takes to create an index (non-clustered) in a
> column, would it be faster if the column contains only null? Or the time
it
> takes to create an index will be the same whether this column has data or
not.
> Thanks,
> Q|||No rows are omitted from an index. Not even rows where the indexed
column is NULL. So the time to index the column would be the same,
regardless of any value (or NULL) (assuming fixed size data type).
Gert-Jan
Q wrote:
> Hello:
> In terms of the time it takes to create an index (non-clustered) in a
> column, would it be faster if the column contains only null? Or the time
it
> takes to create an index will be the same whether this column has data or
not.
> Thanks,
> Q

Create index on col with data and without data, any time differenc

Hello:
In terms of the time it takes to create an index (non-clustered) in a
column, would it be faster if the column contains only null? Or the time it
takes to create an index will be the same whether this column has data or not.
Thanks,
QHi
I don't think there will be a significant difference in the time taken to
create the index, but if you then significantly change the data in the
column, the index may be fragmented and the statistics out of date, therefore
it would be better to build the index once populated.
John
"Q" wrote:
> Hello:
> In terms of the time it takes to create an index (non-clustered) in a
> column, would it be faster if the column contains only null? Or the time it
> takes to create an index will be the same whether this column has data or not.
> Thanks,
> Q|||No rows are omitted from an index. Not even rows where the indexed
column is NULL. So the time to index the column would be the same,
regardless of any value (or NULL) (assuming fixed size data type).
Gert-Jan
Q wrote:
> Hello:
> In terms of the time it takes to create an index (non-clustered) in a
> column, would it be faster if the column contains only null? Or the time it
> takes to create an index will be the same whether this column has data or not.
> Thanks,
> Q

Create Cursor with Dynamic SQL

I am attempting to create a store procedure that contains a cursor. I would like to Dynamically create the cursor and I am having difficulty.

Code:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
create or replace procedure allocation_rewrite2(p_as_of_date IN DATE, p_gl_accnt_id IN NUMBER) is

<Define Variables>

CURSOR cur_gl_bucket
IS
SELECT <column name >
FROM ledger_stat
WHERE <statement>

BEGIN

Select <column name> from table;

OPEN Cursor cur_gl_bucket with retrieved column name.

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

The <column name> is selected from a table based on the parameters passed into the procedure. I would then like to OPEN the cursor with the retrieved column name dynamically imbedded in the Select statment for the cursor.

I successfully use DBMS_SQL.PARSE and EXECUTE later in the procedure but have been unable to get it to work when I open the cursor. I guess the question is can this be done, and if so what is the syntax for doing this.....

Regards
Patrick McCarthy
mailto: patrick_mccarthy@.canmail.comLooks like you are on Oracle?

create or replace procedure allocation_rewrite2(p_as_of_date IN DATE, p_gl_accnt_id IN NUMBER) is

<Define Variables>

TYPE refcur IS REF CURSOR;
cur_gl_bucket refcur;

v_column_name VARCHAR2(30);

BEGIN

Select <column name> INTO v_column_name from table;

OPEN cur_gl_bucket FOR 'SELECT '||v_column_name||' FROM ledger_stat WHERE <statement>';