Help please with TD avg function

Database
Enthusiast

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_NBR
FROM EMPLOYEE_POSITION A, EMPLOYEE_POSITION_CTRL B
WHERE (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.
4 REPLIES
Teradata Employee

Re: Help please with TD avg function

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,
Enthusiast

Re: Help please with TD avg function

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.
Teradata Employee

Re: Help please with TD avg function

So what you want is something like this?

SELECT 'OUT OF RANGE'
FROM (SELECT AVG(SALARY) AS AVG_SAL FROM EMPLOYEE_POSITION) A
CROSS JOIN
(SELECT AVG(SALARY) AS AVG_CTRL FROM EMPLOYEE_POSITION_CTRL) B
WHERE 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...
Enthusiast

Re: Help please with TD avg function

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