Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a simple table.
Issuer number, cost
ABC , 100
DEF , 300
I want to create a third field that is Cost/total cost,
ABC would be 100/400
DEF would be 300/400
How do I get my 400?
My script as it stands, but obviously not working...
sum(VAL_RPT.COST) as R_OPPQC_00020_COST,
Sum(Total (VAL_RPT.COST)) as R_OPPQC_00020_Total,
VAL_RPT.ISSUER_NO
resident VAL_RPT_R230A
where VAL_RPT.VAL_DATE = '$(vDate_Validation)'
and Match (VAL_RPT.PORT_NO, 'P0000000101','P0000000136' )
group by VAL_RPT.ISSUER_NO
;
Here's how I did it:
load
sum(if(Match (VAL_RPT.PORT_TYPE, 'MI','ME'),VAL_RPT.COST)) as R_OPPQC_00020_COST,
VAL_RPT.ISSUER_NO as R_OPPQC_00020.ISSUER_NO
resident VAL_RPT_R230A
where VAL_RPT.VAL_DATE = '$(vDate_Validation)'
and Match (VAL_RPT.PORT_NO, 'P0000000101','P0000000136' )
group by VAL_RPT.ISSUER_NO
;
R_OPPQC_00020_total:
left join (R_OPPQC_00020)
load
sum(VAL_RPT.COST) as R_OPPQC_00020
resident VAL_RPT_R230A
where VAL_RPT.VAL_DATE = '$(vDate_Validation)'
and Match (VAL_RPT.PORT_NO, 'P0000000101','P0000000136' )
;
Here's how I did it:
load
sum(if(Match (VAL_RPT.PORT_TYPE, 'MI','ME'),VAL_RPT.COST)) as R_OPPQC_00020_COST,
VAL_RPT.ISSUER_NO as R_OPPQC_00020.ISSUER_NO
resident VAL_RPT_R230A
where VAL_RPT.VAL_DATE = '$(vDate_Validation)'
and Match (VAL_RPT.PORT_NO, 'P0000000101','P0000000136' )
group by VAL_RPT.ISSUER_NO
;
R_OPPQC_00020_total:
left join (R_OPPQC_00020)
load
sum(VAL_RPT.COST) as R_OPPQC_00020
resident VAL_RPT_R230A
where VAL_RPT.VAL_DATE = '$(vDate_Validation)'
and Match (VAL_RPT.PORT_NO, 'P0000000101','P0000000136' )
;
And YES, I mark my responses as correct if I come up with a valid solution, lol!!!