AnsweredAssumed Answered

SQL Query

Question asked by mklimow on Jun 27, 2014
Latest reply on Jul 2, 2014 by wimdecorte

Hello,

I am trying to run a sql script which would take a sum of all of the values that have a certain month and year listed and has a certain condition, and by a certain person.

 

I have 3 tables, called Tool, FYEngineer, and Users.

 

I have it setup so it is:

 

year, Engineer, jan, feb, mar, april, may, jun, ..., dec, total for engineer

 

inside tool, holds a month, year, budget, and a condition.

 

and im using the engineer to pull from the tool that they are in.

 

 

I am trying to pull the budget out when the engineers match, and the condition is a 5, while the month and the year both match what is inside of report. then i want to sum together all of the budgets they have pulled out.

 

I am trying to use this script,

 

Let ( [

 

 

~sql = "

SELECT Sum(A.~field1)

FROM ~table1 AS A, ~table2 AS B

WHERE (A.~field3 = B.~field4) AND (A.~field5 = 5) AND (A.~field6 = B.~field7) AND (A.~field2 = ?)

";

 

 

~sqlQuery = Substitute ( ~sql ;

[ "~table1" ; _fnSQLTableFieldNames (TOOL::BUDescriptionCapitalTab; "table" ) ];

[ "~table2" ; _fnSQLTableFieldNames (FYEngineer::April; "table" ) ];

[ "~field1" ; _fnSQLTableFieldNames ( TOOL::Line1BudgetCapitalTab ; "field" ) ];

[ "~field2" ; _fnSQLTableFieldNames (TOOL::Line1MonthCapitalTab; "field" ) ];

[ "~field3" ; _fnSQLTableFieldNames (TOOL::Line1YearCapitalTab; "field" ) ];

[ "~field4" ; _fnSQLTableFieldNames (FYEngineer::FiscalYear; "field" ) ];

[ "~field5" ; _fnSQLTableFieldNames (TOOL::ToolConditionRating; "field" ) ];

[ "~field6" ; _fnSQLTableFieldNames (TOOL::ID_MFMProjectEngineerCapitalTab; "field" ) ];

[ "~field7" ; _fnSQLTableFieldNames (FYEngineer::ID_Engineer; "field" ) ]

) ;

 

 

~sqlResult = _fnSQL.debug (

ExecuteSQL (

~sqlQuery ; "" ; "" ;

GetValue($months;$count)

)

)

];

 

 

//If ( ~sqlResult = "?" ; False ; True )

~sqlResult

 

 

)

 

I have been looking, and i cannot seem to find why i am receiving an empty value.

 

Thanks,

Outcomes