FMS - SQL Query

[Former Member]

Dear Experts,

I want to have sum from items before inserting the lines in database. I wrote a SQL query in FMS but this query works if the lines are already in database.

SELECT SUM(CAST([RDR1.U_H1] AS numeric(17,2)) + CAST([RDR1.U_H2] AS numeric(17,2))) FROM RDR1

INNER JOIN ORDR

ON RDR1.DOCENTRY = ORDR.DOCENTRY

where RDR1.DOCNUM = $[ORDR.DOCNUM]

I need to have sum in the bottom line while adding new lines which is not already in database.

Main idea is that : The customer wants to have some calculation in the bottom line of table.

Is there any solution for this situation?

If you have that kind of experience, I would be appreciated.

Thanks in advance.

Kind Regard,

Onur

[Former Member]

Dear Onur,

FMS may work on row level to get header info, but not vice versa.

Thanks,

Gordon

[kothandaraman_nagarajan]

Hi,

Try this:

SELECT SUM(CAST([RDR1.U_H1] AS numeric(17,2)) + CAST([RDR1.U_H2] AS numeric(17,2))) FROM RDR1

INNER JOIN ORDR

ON RDR1.DOCENTRY = ORDR.DOCENTRY

where RDR1.DOCNUM = $[ORDR.DOCENTRY]

Thanks & Regards,

Nagarajan

[Former Member]

Hi,

There isn't DocNum column in RDR1 table.
Also this query tries to find the values in database. But I want to calculate the values which is not already in database. After I use "add" button and then database will have the values.

I am thinking of using While or Loop expression but I am not sure and have no idea how to do that.

Thanks,

Onur

[kothandaraman_nagarajan]

Hi,

Try this:

SELECT SUM(CAST([RDR1.U_H1] AS numeric(17,2)) + CAST([RDR1.U_H2] AS numeric(17,2))) FROM RDR1

INNER JOIN ORDR

ON RDR1.DOCENTRY = ORDR.DOCENTRY

where RDR1.DOCENTRY = $[ORDR.DOCENTRY]

Thanks & Regards,

Nagarajan