Not able to display data with LEFT OUTER JOIN

[Former Member]

Hi

  I am joining two tabels using CE_LEFT_OUTER_JOIN , as of now there is no common data bwetween both tables,I expected at least data from left table should be displayed and right table data would be NULL .But the output does not show any data,below is my code.Am I doing some thing wrong.Request your ideas.

pn_cases = ce_calc_view ("_SYS_BIC"."cms-c5185606/CL_IS_CMS_AT_GPO",

    ["ORDER_ID", "IS_CHANGE_TIME", "IS_STATUS_ID"]);

    orders_filter_eval = ce_projection (:pn_cases, 

    ["ORDER_ID" , "IS_CHANGE_TIME" , "IS_STATUS_ID" as "IS_STATUS_A",

    ce_calc('string("IS_CHANGE_TIME")',varchar(18))as "IS_CHANGE_TIME_EVAL"],

    '"IS_STATUS_A" = 10' );

    orders_filter_create = ce_projection (:pn_cases, 

    ["ORDER_ID" , "IS_CHANGE_TIME", "IS_STATUS_ID" as "IS_STATUS_B" ,

    ce_calc('string("IS_CHANGE_TIME")',varchar(18))as "IS_CHANGE_TIME_CREATE"],

    '"IS_STATUS_B" = 20' );  

     join_orders = CE_LEFT_OUTER_JOIN(:cases_filter_eval, :cases_filter_create, ["ORDER_ID"],

    ["ORDER_ID","IS_STATUS_A", "IS_STATUS_B", "IS_CHANGE_TIME_EVAL", "IS_CHANGE_TIME_CREATE"]

     );   

   var_out=  ce_projection (:join_orders,["ORDER_ID","IS_STATUS_A", "IS_STATUS_B",

   "IS_CHANGE_TIME_EVAL", "IS_CHANGE_TIME_CREATE",

   ce_calc('if (isnull("IS_STATUS_B"), ''STILL EVAL PHASE'', 

   string(daysbetween(date("IS_CHANGE_TIME_EVAL"), date("IS_CHANGE_TIME_CREATE")))) ',NVARCHAR(35))

   as "IS_DAYS_EVAL"]);

Thanks

Santosh

[former_member184768]

Hi Santosh,

I am not sure about your model or data, but could you get some output from each of the views like

pn_cases

orders_filter_eval

orders_filter_create

before you could perform the join_orders. Please also validate if there is any issue with the filter applied in the final output.

Regards,

Ravi

[Former Member]

Hi Ravi

  I tested at each point , only when I apply LEFT OUTER JOIN there it is not giving any output, till then the filers are working fine, I tested that, Could it be some thing wrog with my syntax.

Thanks

Santosh

[Former Member]

Hi All

  Any body faced similar kind of issue before.

     join_orders = CE_LEFT_OUTER_JOIN(:cases_filter_eval, :cases_filter_create, ["ORDER_ID"],

    ["ORDER_ID","IS_STATUS_A", "IS_STATUS_B", "IS_CHANGE_TIME_EVAL", "IS_CHANGE_TIME_CREATE"]

     );  

I am thinking it could be due to "ORDER_ID",because this field is part of both tables,incase of SQl we have to give the table name before the field as it is part of both tables otherwise it would throw column ambigously defined error. Please share your views.

Thanks

Santosh

[Former Member]

Hi All

Below is the example data

Left Table:-

-------------

ORDER_ID	STATUS_A	CHANGE_TIME_EVAL
111	10	20120404
112	10	20120405
113	10	20120406
114	10	20120407
115	10	20120408
116	10	20120409
117	10	20120410

Right Table:-

-----------------

ORDER_ID	STATUS_B	CHANGE_TIME_CREATE
118	20	20120410

When I do left outer Join my understanding at least data from left table should be displayed. but as of now nothing is being displayed.

Thanks

Santosh

[former_member184768]

Hi Santosh,

I don't think there is any issue with the syntax. I'd suspect the last projection statement, but as you mentioned that you already could see the output till the LEFT OUTER JOIN, I am not sure what could be the issue.

Can I request you to build the same model graphically and check if it works.

Regards,

Ravi

[Former Member]

Hi Ravi

I have tried the belwo, the only difference between the two is I am usingtwo  column tables here order_eval and order_create. This works exactly how I wanted.But still could not figure out why the prior one does not fetch any rows. may be this would give any hint, can you check

pn_order_eval = ce_column_table("order_EVAL",["order_ID",
    "STATUS_A","CHANGE_TIME","CHANGE_TIME_EVAL"]);

    order_eval = ce_projection(:pn_order_eval,["order_ID",
     "STATUS_A","CHANGE_TIME_EVAL" ],
    '"STATUS_A" = 10');
    
   
    pn_order_create = ce_column_table("order_CREATE",["order_ID",
    "STATUS_B","CHANGE_TIME","CHANGE_TIME_CREATE"]);

    order_create = ce_projection(:pn_order_create,["order_ID",
    "STATUS_B","CHANGE_TIME_CREATE" ],
    '"STATUS_B" = 20');

  join_order = ce_left_outer_join(:order_eval,:order_create,["order_ID"],
   ["order_ID","STATUS_A","STATUS_B","CHANGE_TIME_EVAL","CHANGE_TIME_CREATE"]);
  
  
   var_out=  ce_projection(:join_order,["order_ID","STATUS_A","STATUS_B","CHANGE_TIME_EVAL",
   "CHANGE_TIME_CREATE",ce_calc('if (isnull("STATUS_B"), ''STILL EVAL PHASE'', 
   string(daysbetween(date("CHANGE_TIME_EVAL"), date("CHANGE_TIME_CREATE")))) ',NVARCHAR(35))
   as "IS_DAYS_EVAL"]);

Thanks

Santosh

[Former Member]

Hi Ravi

  I tried that, but it seems , left outer join is not available in Grpahical Calcview.

Thanks

Santosh

[former_member184768]

Hi Santosh,

Are you sure Left outer join is not available. I am on revision 52 and I can see the option of Left outer join in Graphical Calc view.

Can you please confirm your HANA DB and studio revision.

Regards,

Ravi

[Former Member]

Hi Ravi

   I am on studio revision 38, that could be a problem.can you do me a favour and try with the data models I posted earlier  and provide the result wheter it is working or not.

Thanks

Santosh

[Former Member]

Hi Ravi

  It seems that  CE_LEFT_OUTER_JOIN , does not work in the context of calculation views meaning when the source data is from calcviews, can you try  a simple calcview  as as source from yourside and check whether it works for you.

Thanks

Santosh

[former_member184768]

Hi Santosh,

It works even in Calc View. I tried it just now and it works as expected. As mentioned, I am on revision 52. Please send me your mail address and I can send you the screenshots.

Regards,

Ravi

[Former Member]

Hi Ravi

  Sorry for delay, Ok did you try the Graphical approach, if it is successful, can you also try the CE script approach to test the Left Outer Join functionality, taking calculation view as a source.

santosh_aravindmca@yahoo.co.in

Thanks

Santosh

[Former Member]

Hi All

   I tried RIGHT OUTER JOIN as well ,even the right outer join is not displaying anything  in output, even if there is no common data it should display data from right table incase of right outer join and data from left table incase of left outer join,please correct me if my understanding is worng.

or is it something to do with my syntax.

Thanks

Santosh

[Former Member]

Hi

I tried the below query and working fine, the same logic I have applied in CE_LEFT_OUTER_JOIN

function could not understand why it is not fetching rows.

select * from order_eval  LEFT JOIN order_create

  ON order_eval.order_id = order_create.order_id

Thanks

Santosh