Case Statement in a Object of Universe Designer

[Former Member]

Hi All,

I have created a object "Changed Value" in universe designer with a Case Statement as below:

CASE WHEN dbo.MASTER_CHECK.MASTER_CHECK_ID = 1 THEN dbo.SITE_SALES_SUMMARY.MASTER_OPERATOR_ID

    WHEN dbo.MASTER_CHECK.MASTER_CHECK_ID = 2 THEN dbo.ASSOC_SITE_OPERATOR.MASTER_OPERATOR_ID

    WHEN dbo.MASTER_CHECK.MASTER_CHECK_ID = 3 THEN  dbo.PRODUCT_GROUP_SALES_SUMMARY.MASTER_PRODUCT_GROUP_ID

   WHEN dbo.MASTER_CHECK.MASTER_CHECK_ID = 4 THEN dbo.PRODUCT_GROUP_PAYMENT_SALES_SUMMARY.MASTER_PAYMENT_METHOD_ID

   WHEN dbo.MASTER_CHECK.MASTER_CHECK_ID = 5 THEN dbo.PRODUCT_GROUP_SALES_SUMMARY.SALES_QUANTITY

   WHEN dbo.MASTER_CHECK.MASTER_CHECK_ID =6 THEN dbo.PRODUCT_GROUP_PAYMENT_SALES_SUMMARY.PAYMENT_AMOUNT

   WHEN dbo.MASTER_CHECK.MASTER_CHECK_ID = 7 THEN dbo.PRODUCT_GROUP_SALES_SUMMARY.MARGIN

WHEN dbo.MASTER_CHECK.MASTER_CHECK_ID = 8 THEN dbo.PRODUCT_GROUP_SALES_SUMMARY.MARGIN

WHEN dbo.MASTER_CHECK.MASTER_CHECK_ID = 9 THEN dbo.SITE_SALES_SUMMARY.SALES_TOTAL_EPAYMENT

WHEN dbo.MASTER_CHECK.MASTER_CHECK_ID =10 THEN dbo.PRODUCT_GROUP_SALES_SUMMARY.MARGIN

WHEN dbo.MASTER_CHECK.MASTER_CHECK_ID =11 THEN dbo.PRODUCT_GROUP_SALES_SUMMARY.STOCK_READING

WHEN dbo.MASTER_CHECK.MASTER_DSR_CHECK_ID = 12 THEN dbo.PRODUCT_GROUP_SALES_SUMMARY.STOCK_READING

WHEN dbo.MASTER_CHECK.MASTER_CHECK_ID = 13 THEN dbo.PRODUCT_GROUP_SALES_SUMMARY.PRICE_PER_UNIT

WHEN dbo.MASTER_CHECK.MASTER_CHECK_ID =14 THEN dbo.PRODUCT_GROUP_SALES_SUMMARY.COMMISSION

WHEN dbo.MASTER_CHECK.MASTER_CHECK_ID = 15 THEN dbo.SITE_SALES_SUMMARY.MASTER_SITE_ID

WHEN dbo.MASTER_CHECK.MASTER_CHECK_ID = 16 THEN dbo.SITE_SALES_SUMMARY.CLOSE_TIME1

WHEN dbo.MASTER_CHECK.MASTER_CHECK_ID =17 THEN dbo.PRODUCT_GROUP_SALES_SUMMARY.MARGIN

WHEN dbo.MASTER_CHECK.MASTER_CHECK_ID = 18 THEN dbo.SITE_SALES_SUMMARY.MASTER_STATUS_ID

    ELSE NULL

  END

In this Case Staement the objects have Date Data type, Number Data Type and Character Date Type. The Date Object is Close time for which I created a new Object Close Time1 and changed it to Number Data type by using formula Convert(int,Convert(varchar(10),@Select(Dbo Site Sales Summary\Close Time),112))  to be used in the above Case Statement. When I use this in the above Case Statement in the "Changed Value" Object, where the data type is number it parses fine but when I go to display in the properties it gives SQL Excecution error. So when I go to invidiual objects and check the values evrything shows numeric values but Master_site_id shows alpha numeric values. Master_Status_ID shows Character values and evrything else is numeric. How do I make this work? How do I chnage the character to numeric or numeric to Character so that I dont get SQL Excecution error?

Thanks,

Nisha

[Former Member]

You will have to make sure your output object data type is same for all the cases... So in your case try to convert all the number to strings..

[Former Member]

Thanks for your response. So for all the already existing objects that I am using in the CASE Statement I am creating new objects with the formula as:

"convert(varchar(10),dbo.table.field)" and change the data type from number to character. and the objects thats are already character I dont change them and they remain the same.

So for one field "sales quantity" when I recreate the object with the same formula it gives me an error as: "Arithmetic overflow error converting numeric to data type varchar.State: 22003" this object parses fine but when I go to display it gives me a sql excecution error. Please let me know where am I going wrong.

Thanks,

Nisha

[Former Member]

This error is occurring as you have a set small limitation of 10 for the length of var char field. Basically your numbers are more than 10 digit long in some cased so it does not fit in the set length of 10 while converting to varchar.

Try making the varchar length to a higher number may be 50 to accommodate all the length of the value..

[Former Member]

all objects parse fine and they display the values when checked individually but when I use all of them in CASE statement, eventhough I have chnaged all the objects to character data type and I chnage the CASE statement to Character it shows its not compatible with the data type and it parses fine with number data type. and when trying to execute the sql it gives me error as :

Exception: DBD, [Microsoft SQL Server Native Client 10.0] : Cannot convert a char value to money. The char value has incorrect syntax.State: 22018

How do I solve this?

Thanks,

Nisha

[Former Member]

Nisha,

Please try to understand ; when you have a object independently the output is always the same data type so they work independently. However when you are including all of them into one statement using a case then the output for all the cases has to be in a same and consistent data type. That is the reason I suggest you to convert all the output to string as any number can be converted to string.

Now looking into your error ; you have two error actually ...you are trying to convert a char to number and there is a syntax error..

If your database is just SQL server then just use case to convert number to string and the ones with string type you can just leave them alone

Here is a sample syntax

CAST(table.field AS varchar(50))