Sql Syntax To Select A Value From A Lookup Table

[Elena]

Hello,

 

I am trying to select a value out of a look up table separate from my main table.

 

I have a tabular report.

 

The SQL syntax that I have (below) works, but it selects only those with zero (0) value on the "RP" column. Anything more than zero, I get blanks. 

 

I actually thought this could help in showing specific values conditionally from a lookup table.

 

SELECT Percent_Matrix
FROM MatrixPayOut
WHERE YOS = [@calcfield:3] AND RP = [@calcfield:6]

 

 

Can you please help me rewrite the syntax? I would like to know if an inner join method is a better approach?

 

Thank you and Happy Holidays.

 

Elena

[iren]

Hello Elena,

 

Could you please provide your formulas of calculated fields?

 

Thanks,

IREN

[Elena]

Hi Iren,

 

Thank you for responding.

 

The formula for my calculated fields:

 

Calculated Field 3:

Datediff(year, [@field:Date_Hire],GetUTCDate())

 

 

Calculated Field 6:

[@field:Current_Salary] - ([@calcfield:8])) / (([@calcfield:4]) - ([@calcfield:8]))

 

 

 

The calculated fields 8 and 4, are both SQL SELECT syntax from a separate lookup table.

[Elena]

I must add that the lookup table (MatrixPayOut) with columns, "YOS" and "RP" have the same exact matching values with the main table that I am using for my tabular report.

 

As you can surmise the values from my main table (Census) are generated using calculations.

I checked the rounding off format to see if that is causing the problem. But as far as I can see, the values generated out of the calculated fields from the table "Census", generate the same exact values and rounding off as the lookup table,"MatrixPayOut".

 

I hope I am making sense in this explanation.

 

Thank you,

 

Elena

[Elena]

I solved this issue. My problem was that the precision on my calculated field 3, was not set to the same decimal and rounding setup as my look up table.

 

I fixed this accordingly by proper formatting.

 

Thank you.