please let me know how to convert this in HANA Model

[Former Member]

SELECT   coalesce(dims.IMS_ID, (select IMS_ID from DQR_OLAP.DIM_IMS where TEXT = 'N/A')) as DIM_IMS_ID,

                     coalesce(dco.COUNTRY_ID, (select COUNTRY_ID from DQR_OLAP.DIM_COUNTRY where COUNTRY_CODE = '00')) as DIM_COUNTRY_ID,

                     omr.ROLE_BITSET,

                     dcel.ACTIVE_CEL_STATUS_ID as DIM_ACTIVE_CEL_STATUS_ID,

                     coalesce(drev.REVENUE_ID, (select REVENUE_ID from DQR_OLAP.DIM_REVENUE where TEXT = 'Not Reported')) as DIM_REVENUE_ID,

                     dmas.MASTER_CODE_ID as DIM_MASTER_CODE_ID,

                         --SW,20111026

                     coalesce(isc.ISC_ID, (select ISC_ID from dqr_olap.dim_int_sales_classification where text = 'Unavailable')) as DIM_ISC_ID,

                     coalesce(rbc.RBC_ID, (select RBC_ID from dqr_olap.dim_reg_buying_classification where rbc_text = 'Unavailable')) as DIM_RBC_ID,

                     coalesce(blg.BLG_ID, (select BLG_ID from dqr_olap.dim_buying_lifecycle_global where blg_text = 'Unavailable')) as DIM_BLG_ID,

                         --

                         --SW,20120518

                         omr.HAS_ISE,

                         --NG,20120907

                         omr.DIM_ORG_SEGMENT_ID ,

                     count(*) as THE_COUNT

            FROM     DQR_OLAP.ORG_METRICS_RAW omr

                     left outer JOIN DQR_OLAP.DIM_IMS dims ON omr.IMS = dims.TEXT

                     left outer JOIN DQR_OLAP.DIM_COUNTRY dco ON omr.COUNTRY_CODE = dco.COUNTRY_CODE

                     left outer JOIN DQR_OLAP.DIM_ACTIVE_CEL_STATUS dcel ON omr.ACTIVE_CEL_STATUS = dcel.TEXT

                     left outer JOIN DQR_OLAP.DIM_MASTER_CODE dmas ON omr.SAP_MASTER_CODE = dmas.MASTER_CODE

                     left outer JOIN DQR_OLAP.DIM_REVENUE drev ON omr.REVENUE_USD = drev.REVENUE_ID

                         --SW,20111026

                         left outer JOIN DQR_OLAP.DIM_INT_SALES_CLASSIFICATION isc on isc.TEXT = omr.ISC_TEXT

                         left outer JOIN DQR_OLAP.DIM_REG_BUYING_CLASSIFICATION rbc on rbc.RBC_TEXT = omr.RBC_TEXT

                         left outer JOIN DQR_OLAP.DIM_BUYING_LIFECYCLE_GLOBAL blg on blg.BLG_TEXT = omr.BLG_TEXT

            group by dims.IMS_ID,

                     dco.COUNTRY_ID,

                     omr.ROLE_BITSET,

                     dcel.ACTIVE_CEL_STATUS_ID,

                     drev.REVENUE_ID,

                     dmas.MASTER_CODE_ID,

                         --SW,20111026

                     isc.ISC_ID,

                     rbc.RBC_ID,

                     blg.BLG_ID,

                         --SW,20120518

                         omr.HAS_ISE,

                         --NG,20120907

                         OMR.DIM_ORG_SEGMENT_ID;

I have created a Analytical view and its working fine ,but i have to incorporate the below like statements  from above query

coalesce(dims.IMS_ID, (select IMS_ID from DQR_OLAP.DIM_IMS where TEXT = 'N/A')) as DIM_IMS_ID, this statments whihc is stopping me to go forward

Please give me any ideas

[rindia]

Hi Asif,

One solution can be to use Calculated column for each null attribute.

For example : DIM_IMS_ID would be

if(isnull(IMS_ID),if(isnull(IMS_ID_OLAP),IMS_ID_OLAP)IMS_ID)

I not checked syntax but just to give you idea.

Make the original attributes hidden and use calculated attribute as output.

Regards

Raj