create table from query results

Hi everybody need help on the possibility creating a new table from the results of a view or query? below is my table named table1

ID col1 col2

1 a a
2 b d
3 c f

this would be my new table named table2

ID col1 col2 col3

1 a a aa
2 b d bd
3 c f cf

this new table has an additional column by concatenating col1+col2
tried this procedure but is not working

CREATE TABLE AS (SELECT ID, COL1, COL2, COL1+COL2) TABLE2

thanksare you sure you need to store the concatenation in a separate table?SELECT ID, COL1, COL2, COL1+COL2 AS COL3
INTO TABLE2
FROM TABLE1|||thanks Rudy

yes I'll be storing the concatenated field in a new table

will that procedure create automatically the table2 even if i don't use CREATE TABLE?

alex

Create table

Hi,
How would I create a table programmatically in a database. I am using C#
2005 Express edition.
For example I want to create a table named tblHours in a database
dbEmployees with the columns hours worked (integer), hourly pay (currency),
etc...
TIA
Roy
How about looking up "create table" in BOL?
ms-help://MS.SQLCC.v9/MS.SQLMobile.v3.en/SSMProg3/html/143cad25-5c1d-4c96-bd8b-6a95dbfb1b00.htm
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:5rEuf.56416$DQ4.1492398@.weber.videotron.net.. .
> Hi,
> How would I create a table programmatically in a database. I am using C#
> 2005 Express edition.
> For example I want to create a table named tblHours in a database
> dbEmployees with the columns hours worked (integer), hourly pay
> (currency),
> etc...
>
> TIA
> Roy
>
>

Create table

Hi,
How would I create a table programmatically in a database. I am using C#
2005 Express edition.
For example I want to create a table named tblHours in a database
dbEmployees with the columns hours worked (integer), hourly pay (currency),
etc...
TIA
Roy>> create a table programmatically in a database.. <<
You do not do that at all. First, you write a data model of your real
world, then you implement in a schema. Your real world does not change
on the fly. If the model needs to add, alter or remove tables , you do
it with other tools, such as QA. Never in a front end program.
You might also want stop putting silly prefixes in front of data
element names (see ISO-11179).|||What do you mean "programatically?" If you mean permanently then you need
to get the Management Studio Express from
(http://msdn.microsoft.com/sql/express/)
If you mean you want to formulate a table based on some input (hopefully for
temporary usage) you just simply formulate a CREATE TABLE statement and
execute it in ADO.NET. Do you know how to execute a SQL Statement?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:snEuf.56318$DQ4.1489316@.weber.videotron.net...
> Hi,
> How would I create a table programmatically in a database. I am using C#
> 2005 Express edition.
> For example I want to create a table named tblHours in a database
> dbEmployees with the columns hours worked (integer), hourly pay
> (currency), etc...
>
> TIA
> Roy
>|||What's wrong with prefixes? If you have dozens or hundreds of tables and
views, it is useful to distinguish them at a glance in stored procedure for
example.
Peter|||Nothing, the guy doesn't understand the term KISS (Keep It Simple Sweet).
Table prefixes are useful for segmenting code, personally, i don't use tbl_
type prefixes, but i do use vw_ because if and when I use views in code I
can quickly see that I am using a view in a query and then can more quickly
understand that there is further logic underneath that might be causing a
performance problem.
I prefix stored procedures using logical segmentation, for instance
events_sel; research_sel etc... groups objects together in the database to
make life (and development/maintanence) easier.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Rogas69" <rogas69@.no_spamers.o2.ie> wrote in message
news:%23ZSEWRSEGHA.916@.TK2MSFTNGP10.phx.gbl...
> What's wrong with prefixes? If you have dozens or hundreds of tables and
> views, it is useful to distinguish them at a glance in stored procedure
> for example.
> Peter
>|||"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com>:
news:eGy3wlTEGHA.3200@.tk2msftngp13.phx.gbl...
> Nothing, the guy doesn't understand the term KISS (Keep It Simple Sweet).
> Table prefixes are useful for segmenting code, personally, i don't use
> tbl_ type prefixes, but i do use vw_ because if and when I use views in
> code I can quickly see that I am using a view in a query and then can more
> quickly understand that there is further logic underneath that might be
> causing a performance problem.
> I prefix stored procedures using logical segmentation, for instance
> events_sel; research_sel etc... groups objects together in the database to
> make life (and development/maintanence) easier.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Rogas69" <rogas69@.no_spamers.o2.ie> wrote in message
> news:%23ZSEWRSEGHA.916@.TK2MSFTNGP10.phx.gbl...
>
This is something newer
For me KISS stands for "keep it simple, stupid"
--
Andrey Odegov
avodeGOV@.yandex.ru
(remove GOV to respond)|||Celko has strong opinions and rarely is gentle with his advice (look past
that for some good advice much of the time, but as with all advice, there
are plenty of opinions.)
Personally, I don't like any type based prefixes on anything :) I do
something like this with stored procedures, functions etc (seperated by a $
(dollar sign) like table$action or purchaseOrder$delete, or something along
these lines.
On programming objects, I don't think either way is a big deal. I don't
prefix stuff with an indication if it is a procedure or a view, etc., but
either way is fine. I do feel that prefixes on tables, views and columns
look bad because the user is often presented these names, and they don't
need to know which are tables and which are views, and a programmer can tell
based on context. And since I always look at lists of objects in the
context of what they are:
select table_schema, table_name, table_type
from information_schema.tables
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Rogas69" <rogas69@.no_spamers.o2.ie> wrote in message
news:%23ZSEWRSEGHA.916@.TK2MSFTNGP10.phx.gbl...
> What's wrong with prefixes? If you have dozens or hundreds of tables and
> views, it is useful to distinguish them at a glance in stored procedure
> for example.
> Peter
>

Create table

Hi,
How would I create a table programmatically in a database. I am using C#
2005 Express edition.
For example I want to create a table named tblHours in a database
dbEmployees with the columns hours worked (integer), hourly pay (currency),
etc...
TIA
RoyHow about looking up "create table" in BOL?
ms-help://MS.SQLCC.v9/MS.SQLMobile.v3.en/SSMProg3/html/143cad25-5c1d-4c96-bd8b-6a95dbfb1b00.htm
--
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:5rEuf.56416$DQ4.1492398@.weber.videotron.net...
> Hi,
> How would I create a table programmatically in a database. I am using C#
> 2005 Express edition.
> For example I want to create a table named tblHours in a database
> dbEmployees with the columns hours worked (integer), hourly pay
> (currency),
> etc...
>
> TIA
> Roy
>
>

Create table

Hi,
How would I create a table programmatically in a database. I am using C#
2005 Express edition.
For example I want to create a table named tblHours in a database
dbEmployees with the columns hours worked (integer), hourly pay (currency),
etc...
TIA
RoyHow about looking up "create table" in BOL?
ms-help://MS.SQLCC.v9/MS.SQLMobile.v3.en/SSMProg3/html/143cad25-5c1d-4c96-bd
8b-6a95dbfb1b00.htm
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:5rEuf.56416$DQ4.1492398@.weber.videotron.net...
> Hi,
> How would I create a table programmatically in a database. I am using C#
> 2005 Express edition.
> For example I want to create a table named tblHours in a database
> dbEmployees with the columns hours worked (integer), hourly pay
> (currency),
> etc...
>
> TIA
> Roy
>
>

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 NamedCalculation in DSV based on other NamedCalculation

Dear Friends,

How can I create a namedcalculation based on another named calculation?

The first named calculatoion has in expression textarea:

CASE
WHEN [D_INST_TIPO_ID]=1 THEN
CASE
WHEN [Price/NPV] = 0 THEN [Avg Flash]
ELSE [Price/NPV]
END
END

And I need to create a second named calculation based on the first!!

If it's not possible, can I set the expression of first named calculation to a variable? To use only one named calculation? example:

xx=(CASE
WHEN [D_INST_TIPO_ID]=1 THEN
CASE
WHEN [Price/NPV] = 0 THEN [Avg Flash]
ELSE [Price/NPV]
END
END)

xx/(Field3)+(Field4)*(Field5)

?

Thanks!!

I don't believe this is possible. You may need to repeat the first calculation in the context of the second one.|||Someone knows any solution?|||

Hi,

you can't use a named calculation inside another.

If you have formulae that are going to be used one inside another,

then you can use 'calculated measures'.

These are created from the 'calculation' tab of the BI studio.

and they are evaluated at run time.

Check this link for more details of creating calculations,

http://msdn2.microsoft.com/en-us/library/ms181224.aspx

if you have specific reasons not to use this,

then you have to expand all your formulae to the lowest level.

That is to elements that are already avaliable in the dsv table.

Regards

Vijay R

Create NamedCalculation in DSV based on other NamedCalculation

Dear Friends,

How can I create a namedcalculation based on another named calculation?

The first named calculatoion has in expression textarea:

CASE
WHEN [D_INST_TIPO_ID]=1 THEN
CASE
WHEN [Price/NPV] = 0 THEN [Avg Flash]
ELSE [Price/NPV]
END
END

And I need to create a second named calculation based on the first!!

If it's not possible, can I set the expression of first named calculation to a variable? To use only one named calculation? example:

xx=(CASE
WHEN [D_INST_TIPO_ID]=1 THEN
CASE
WHEN [Price/NPV] = 0 THEN [Avg Flash]
ELSE [Price/NPV]
END
END)

xx/(Field3)+(Field4)*(Field5)

?

Thanks!!

I don't believe this is possible. You may need to repeat the first calculation in the context of the second one.|||Someone knows any solution?|||

Hi,

you can't use a named calculation inside another.

If you have formulae that are going to be used one inside another,

then you can use 'calculated measures'.

These are created from the 'calculation' tab of the BI studio.

and they are evaluated at run time.

Check this link for more details of creating calculations,

http://msdn2.microsoft.com/en-us/library/ms181224.aspx

if you have specific reasons not to use this,

then you have to expand all your formulae to the lowest level.

That is to elements that are already avaliable in the dsv table.

Regards

Vijay R