Framework Manager Query. (Urgent) :(

[sujitgouda]

I am using the below select/case statement to over-write the value of the Sub Channel field coming from database.
While validating the query, I am getting below issues;

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.
UDA-SQL-0358 Line 2: Syntax error near "=".

I am not sure, if this is the right way to approach, but I need this field value to display new values as per clients suggestion and I do not wish to touch database.


Select
   sv_trans_summary."Sub Channel" as "Sub Channel" =
CASE

WHEN sv_trans_summary.Territory = 'MID AMERICA' THEN 'WH-WEST'
WHEN sv_trans_summary.Territory = 'NORTH CENTRAL' THEN 'WH-WEST'
WHEN sv_trans_summary.Territory = 'CHICAGOLAND' THEN 'WH-WEST'
WHEN sv_trans_summary.Territory = 'MICHIGAN - INDIANA' THEN 'WH-WEST'
WHEN sv_trans_summary.Territory = 'SOUTH' THEN 'WH-WEST'
WHEN sv_trans_summary.Territory = 'MOUNTAIN' THEN 'WH-WEST'
WHEN sv_trans_summary.Territory = 'NORTH TX' THEN 'WH-WEST'
WHEN sv_trans_summary.Territory = 'SOUTH TX' THEN 'WH-WEST'
WHEN sv_trans_summary.Territory = 'PACIFIC NW' THEN 'WH-WEST'
WHEN sv_trans_summary.Territory = 'L.A. SOUTH' THEN 'WH-WEST'
WHEN sv_trans_summary.Territory = 'SOUTHWEST' THEN 'WH-WEST'
WHEN sv_trans_summary.Territory = 'L.A. NORTH' THEN 'WH-WEST'
WHEN sv_trans_summary.Territory = 'NORTH CA' THEN 'WH-WEST'
WHEN sv_trans_summary.Territory = 'SAN FRANCISCO' THEN 'WH-WEST'

WHEN sv_trans_summary.Territory = 'MID EAST' THEN 'WH-EAST'
WHEN sv_trans_summary.Territory = 'RUST BELT' THEN 'WH-EAST'
WHEN sv_trans_summary.Territory = 'NJ' THEN 'WH-EAST'
WHEN sv_trans_summary.Territory = 'PHILLY METRO' THEN 'WH-EAST'
WHEN sv_trans_summary.Territory = 'MID ATLANTIC' THEN 'WH-EAST'
WHEN sv_trans_summary.Territory = 'NY' THEN 'WH-EAST'
WHEN sv_trans_summary.Territory = 'BOSTON' THEN 'WH-EAST'
WHEN sv_trans_summary.Territory = 'CT - LONG ISLAND' THEN 'WH-EAST'
WHEN sv_trans_summary.Territory = 'NY METRO' THEN 'WH-EAST'
WHEN sv_trans_summary.Territory = 'NEW ENGLAND' THEN 'WH-EAST'
WHEN sv_trans_summary.Territory = 'GEORGIA' THEN 'WH-EAST'
WHEN sv_trans_summary.Territory = 'CAROLINAS' THEN 'WH-EAST'
WHEN sv_trans_summary.Territory = 'SE FLORIDA' THEN 'WH-EAST'
WHEN sv_trans_summary.Territory = 'VIRGINIA' THEN 'WH-EAST'
WHEN sv_trans_summary.Territory = 'NW FLORIDA' THEN 'WH-EAST'

WHEN sv_trans_summary.Territory = 'U.S. / OFFSHORE' THEN 'WH-INTL'

ELSE 'OTHERS'

END
From
   [Sales Reporting].sv_trans_summary sv_trans_summary

[gosoccer]

Within FM Model for a Relational, I'm using the following without any errors,

when
([DATA].[LIST_OF_VALUES].[S_CD]  in ('6','O') )
then 'O'
when
([DATA].[LIST_OF_VALUES].[S_CD]  in ('7','H') )
then 'H'
when
([DATA].[LIST_OF_VALUES].[S_CD]  in ('8','C') )
then 'C'
else 'X'
end

I have never mixed the Select and Case together in a subject Query.
You could have the Subject Query and within the subject query, you could have the Case Statement established as a calculation/filter for
your data item in this case sv_trans_summary.Territory.

Hope this help!!! :) :)

[globalbear]

My approach is normally a select * from a table and then you get all columns automatically.
Then in the next semantic layer (business view) you can do whatever calculations you wish.

If you do not have different logical layers you could bring in all columns with
select * from [Sales Reporting].sv_trans_summary

This will import all columns including territory.
Then go to the calculations tab and add a new query item with a case statement such as this:

CASE

WHEN sv_trans_summary.Territory = 'MID AMERICA' THEN 'WH-WEST'
WHEN sv_trans_summary.Territory = 'NORTH CENTRAL' THEN 'WH-WEST'
WHEN sv_trans_summary.Territory = 'CHICAGOLAND' THEN 'WH-WEST'
WHEN sv_trans_summary.Territory = 'MICHIGAN - INDIANA' THEN 'WH-WEST'
ELSE 'YADA YADA YADA'

END

Call the new query item sub_channel.

//globalbear

[bdbits]

Select
CASE
WHEN sv_trans_summary.Territory = 'MID AMERICA' THEN 'WH-WEST'
... {rest of your cases here} ...
WHEN sv_trans_summary.Territory = 'U.S. / OFFSHORE' THEN 'WH-INTL'
ELSE 'OTHERS'
END
AS "Sub Channel"
From
   [Sales Reporting].sv_trans_summary sv_trans_summary

[sujitgouda]

Thanku  so much globalbear. Used your technique. Resolved this.