cancel
Showing results for 
Search instead for 
Did you mean: 

Q51: How to divide 'RDR1.Price' by 'RDR1.Rate' when blank

Former Member
0 Kudos

Dear All,

It appears my report as below is reporting values in £ or $ so I assume I need to introduce the 'Rate' field to bring all values back to Sterling.

However, when a sales order is in sterling the 'Rate' field is left blank and I get an error message whenever I try to introduce the 'Rate' field - Query does not like dividing by 'blank'

Any ideas?

SELECT

t0.cardcode as 'Customer Code',

t3.slpname as 'Sales Person',

sum(((T1.OpenQty)*(T1.Price))-t0.discsum) as 'Sales Value'

FROM

ordr t0 inner join rdr1 t1 on t0.docentry = t1.docentry

inner join ocrd t2 on t0.cardcode = t2.cardcode

inner join oslp t3 on t2.slpcode = t3.slpcode

WHERE

t0.docduedate between and and

t1.linestatus = 'O' and

isnull (t0.u_forecast,'N') !='Y'

GROUP BY

t0.cardcode,

t3.slpname,

t0.discsum

ORDER BY t0.cardcode

Robin

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Robin,

Try:

SELECT

t0.cardcode as 'Customer Code',

t3.slpname as 'Sales Person',

sum(T1.OpenSum) as 'Sales Value'

FROM

ordr t0 inner join rdr1 t1 on t0.docentry = t1.docentry

inner join ocrd t2 on t0.cardcode = t2.cardcode

inner join oslp t3 on t2.slpcode = t3.slpcode

WHERE

t0.docduedate between \[%0\] and \[%1\] and

t1.linestatus = 'O' and

isnull (t0.u_forecast,'N') !='Y'

GROUP BY

t0.cardcode,

t3.slpname,

t0.discsum

ORDER BY t0.cardcode

You have OpenSum and OpenSumFC so hoepuflly OpenSum will always return it using your local currency.

Regards,

Adrian

Former Member
0 Kudos

Thanks Adrian..........spot on!

Answers (0)