All Forums Database
mior66 7 posts Joined 03/08
06 Mar 2008
Help please with TD avg function

Hi all,I am trying to do a statement equivalent to the following one from Oracle;SELECT DISTINCT A.EMPLOYEE_NBRFROM EMPLOYEE_POSITION A, EMPLOYEE_POSITION_CTRL BWHERE (SELECT AVG(SALARY) FROM EMPLOYEE_POSITION) > ((SELECT AVG(SALARY) FROM EMPLOYEE_POSITION_CTRL) * .05)+ (SELECT AVG(SALARY) FROM EMPLOYEE_POSITION_CTRL)OR (SELECT AVG(SALARY) FROM EMPLOYEE_POSITION) < ((SELECT AVG(SALARY) FROM EMPLOYEE_POSITION_CTRL)-(SELECT AVG(SALARY) FROM EMPLOYEE_POSITION_CTRL) * .05)Basically I am trying to determine if the AVG of salary in table A is within 5 percent of table B. It works for all other db's but in TD i get errors and i've tried different things with no luck. The datatype is Float. If somebody could help me i would sincerely appreciate it, were turning our wheels here. For more info or to contact me directly please respond to this post.

Fred 1096 posts Joined 08/04
06 Mar 2008

Even as an Oracle query, this does not make sense as posted - because there appears to be no correlation and no join predicate. The WHERE clause is either always FALSE (so the query returns an empty set) or always TRUE (in which case you first generate an implicit cartesian product of the tables, then remove the duplicates with DISTINCT, and are left after much unnecessary overhead with all the EMPLOYEE_NBRs in A).If you figure out what the actual query should be, you probably can solve it using one or more derived table expressions in the FROM clause, e.g.FROM (SELECT AVG(SALARY) AS AVG_SAL FROM EMPLOYEE_POSITION_CTRL) B,

mior66 7 posts Joined 03/08
07 Mar 2008

hey thanks for replying. The query does not need a join as you mentioned because you only need the averages from both tables, so why would you need to relate the two tables? The query basically takes the avg of table 1 and multiplies it by 1.05 and then does the same only subtracting .05 from the avg. So if it is not withign 5 percent then it should return anything, dosn't matter what. When i tried what you suggested, i keep getting error similiar to;Syntax error: expected something between ')' and '>'.It appears to not like the comparision. Not sure if you have any other suggestions or now that you know what im tryign to do, mabye another method. thansk again.

Fred 1096 posts Joined 08/04
07 Mar 2008

So what you want is something like this?SELECT 'OUT OF RANGE'FROM (SELECT AVG(SALARY) AS AVG_SAL FROM EMPLOYEE_POSITION) ACROSS JOIN(SELECT AVG(SALARY) AS AVG_CTRL FROM EMPLOYEE_POSITION_CTRL) BWHERE A.AVG_SAL > (B.AVG_CTRL * 1.05)OR A.AVG_SAL < (B.AVG_CTRL * 0.95);If so, this sort of approach is a better solution in "all the other DBs" also...

mior66 7 posts Joined 03/08
07 Mar 2008

seems to be working with that statement man, i appreciate it. thanks.

You must sign in to leave a comment.