Skip to Content
0
Jul 02, 2019 at 05:46 PM

New field for local table in SQL Command

87 Views

Crystal Report version: 14.1.2.1121

Hi,

I'm trying to create a new field in a local table using SQL command by getting fields from four other tables and perform some logic and calculations to get the final data populated into the new field.

I can't do it in the report itself because I need to do left join of the local table with another table after filtering out the new field from the local table. I also can't create a table in the database itself because the DBA doesn't want to change the database.

For example,

Table1.Activity, Table2.Factor, Table3.Weight, Table3.Volume, Table4.Location are fields I need and then:

if {Table4.Location} = 5
then
(
if {Table3.Weight} <> 0 OR NOT(ISNULL({Table3.Weight})) 
then
({Table1.Activity} * {Table2.Factor}) / {Table3.Weight}
)

else
if {Table4.Location} = 4
then
(
if {Table3.Volume} <> 0 OR NOT(ISNULL({Table3.Volume})) then 
({Table1.Activity} * {Table2.Factor}) / {Table3.Volume}
)

else 
({Table1.Activity} * {Table2.Factor}) 

After all the calculations, the results are stored into the new field in a local table.

What is the best way to do this? Can SQL command able to do this?

Thank you.