Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

New Named Calculation Expression for Case Statement? Expand / Collapse
Author
Message
Posted Thursday, September 12, 2013 3:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 4, 2013 10:34 AM
Points: 2, Visits: 28
Hi,

Can any one help with the case statement?

The following CASE STATEMENT is the result in my SQL Management.

select
Column1 = case when Column1 = 'true' then 'Y'
ELSE 'N'
END ,
Column2 = case when Column1 = 'true' then 'Y'
ELSE 'N'
END ,
SHARED = case when Column1='true' AND Column1='true' THEN 'SHARED' ELSE 'NOTSHARED'
END
FROM Table

RESULT:
Column1 Column2 SHARED
Y N NOTSHARED
N N NOTSHARED
Y N NOTSHARED
N N NOTSHARED
N N NOTSHARED
Y N NOTSHARED
Y N NOTSHARED
Y N NOTSHARED
N N NOTSHARED
Y N NOTSHARED
N N NOTSHARED
Y N NOTSHARED
Y N NOTSHARED
N N NOTSHARED

I have to create a new named calculation using the above statement. How to do it?

When I try this it is displaying the error:

Deferred prepare could not be completed.
Statement(s) could not be prepared.
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Post #1494381
Posted Friday, September 27, 2013 7:59 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:39 AM
Points: 3,184, Visits: 1,388

The following CASE STATEMENT is the result in my SQL Management.

select
Column1 = case when Column1 = 'true' then 'Y'
ELSE 'N'
END ,
Column2 = case when Column1 = 'true' then 'Y'
ELSE 'N'
END ,
SHARED = case when Column1='true' AND Column1='true' THEN 'SHARED' ELSE 'NOTSHARED'
END
FROM Table

RESULT:
Column1 Column2 SHARED
Y N NOTSHARED
N N NOTSHARED
Y N NOTSHARED
N N NOTSHARED
N N NOTSHARED
Y N NOTSHARED
Y N NOTSHARED
Y N NOTSHARED
N N NOTSHARED
Y N NOTSHARED
N N NOTSHARED
Y N NOTSHARED
Y N NOTSHARED
N N NOTSHARED

So this is a TSQL query. Do you want to produce the equivalent in SSAS? If so is this a line-by-line detail query (i.e. within the data source view) or are you attempting with within the cube as a calculation.

Assuming you are producing a new named calculation within a data source view then either :

a) produce a new named calculation for each new column (Column1, Column2 and Shared). Each calculation can only return a single value per row hence 3 calculations. Each one would look like below. No need to include SELECT:
Name : Column1
Formula : case when Column1 = 'true' then 'Y' ELSE 'N' END

b) choose the option of replacing the source table with a new named query and then you should be able to specify the complete TSQL query as the source.


I have to create a new named calculation using the above statement. How to do it?

When I try this it is displaying the error:

Deferred prepare could not be completed.
Statement(s) could not be prepared.
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.


The errors above is due to the calculation being included within the TSQL source. Example below:

Table : Table1
Original table columns: ColumnA, ColumnB, ColumnC
Query used by
SELECT ColumnA, ColumnB, ColumnC 
FROM Table1

When you introduced the named calculation using the original query.

Name : Column1
Formula :
select
Column1 = case when Column1 = 'true' then 'Y'
ELSE 'N'
END ,
Column2 = case when Column1 = 'true' then 'Y'
ELSE 'N'
END ,
SHARED = case when Column1='true' AND Column1='true' THEN 'SHARED' ELSE 'NOTSHARED'
END
FROM Table

then the TSQL looks like below. Notice the query calculation is entered as the formula on the select clause. This is the reason for the error message. The second side issue is that the name Column1 is used as the result and the input.

SELECT ColumnA, ColumnB, ColumnC , select
Column1 = case when Column1 = 'true' then 'Y'
ELSE 'N'
END ,
Column2 = case when Column1 = 'true' then 'Y'
ELSE 'N'
END ,
SHARED = case when Column1='true' AND Column1='true' THEN 'SHARED' ELSE 'NOTSHARED'
END
FROM Table AS Column1
FROM Table1

Best process create 3 calculations as below:

Name : Column1Calc
Formula : case when Column1 = 'true' then 'Y' ELSE 'N' END

Name : Column2Calc
Formula : case when Column2 = 'true' then 'Y' ELSE 'N' END

Name : ColumnShared
Formula : case when Column1 = 'true' and Column2 = 'true' then 'Y' ELSE 'N' END

The resulting DSV query would be :
SELECT <<other columns>>, ,
case when Column1 = 'true' then 'Y' ELSE 'N' END AS Column1Calc,
case when Column2 = 'true' then 'Y' ELSE 'N' END AS Column2Calc,
case when Column1 = 'true' and Column2 = 'true' then 'Y' ELSE 'N' END AS ColumnShared
FROM <<tablename>>

Fitz
Post #1499394
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse