Skip to Content
author's profile photo Former Member
Former Member

Case Statement in a Object of Universe Designer

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Jan 08, 2014 at 03:19 PM

    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..

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member 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))

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.