September 12, 2013 at 3:11 pm
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.
September 27, 2013 at 7:59 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply