Implement the query

[arulsettu]

Hi i am trying to implement the following query

select pdr_prod_code, count(1),

sum(decode(pdr_status, 'R', 1, 0)) from t_pol_due_for_ren

where trunc(pdr_pol_to_dt) between '01-DEC-2014' and '31-DEC-2014'

group by pdr_prod_code

in line chart. month and year as dimensions

please suggest me something its urgent...

thank you

[PrashantSangle]

Hi,

In script try like

Load pdr_prod_code,

         sum(if(pdr_status='R',1,0)) as Sum_pdr_Status,

from t_pol_due_for_ren

where Date(pdr_pol_to_dt,'DD-MMM-YYYY')>='01-Dec-2014' and Date(pdr_pol_to_dt,'DD-MMM-YYYY')<='31-Dec-2014'

group by pdr_prod_code;

or

If you want to write set analysis using above condition then try like

sum(if(pdr_status='R' and pdr_pol_to_dt>='01-Dec-2014' and pdr_pol_to_dt<='31-Dec-2014',1,0))

Regards

[arulsettu]

Hi

max i used below code in script now what to write in chart

Load pdr_prod_code,

         sum(if(pdr_status='R',1,0)) as Sum_pdr_Status,

from t_pol_due_for_ren

where Date(pdr_pol_to_dt,'DD-MMM-YYYY')>='01-Dec-2014' and Date(pdr_pol_to_dt,'DD-MMM-YYYY')<='31-Dec-2014'

group by pdr_prod_code;

thank you

[PrashantSangle]

Hi,

As you said you have month and Year as dimension then you have dimension and

in expression what do you want to show??

if you want to show Sum_pdr_Status then use it as expression.

Regards

[arulsettu]

hi max i have to show like this

In y axis have to show in ratio

thanks

[arulsettu]

any ideas?

[arulsettu]

hi max

can you suggest based on the image

thanks

[krishna20]

Hi Arul,

If you are struggling to write an expression for this. Simply follow these steps you can achieve what you want

Step1 :

Prod_Code_Cal:

select pdr_prod_code, count(1),

sum(decode(pdr_status, 'R', 1, 0)) Prod_sum from t_pol_due_for_ren

where trunc(pdr_pol_to_dt) between '01-DEC-2014' and '31-DEC-2014'

group by pdr_prod_code

Store Prod_Code_Cal into [../QVD/Prod_Code_Cal.qvd];

        drop table Prod_Code_Cal;

Step2 :

Upload this QVD and associate to t_pol_due_for_ren and Prod_Code table.

Step3:

Take Line chart with your dimensions and  expression .

Note : Please notice on Prod_sum.you need to store this result in a column.Then, only you can use this value in expression on front End.

Regards

Krishna

[arulsettu]

max it showing invalid expression plz suggest

[arulsettu]

not getting the result