CODEWITHSUNDEEP
sqlsql-servert-sql
FrenkyB
FrenkyB

Reputation: 7197

Variable in subquery from main query

I would like to get USER_FNM (user's first name) from table BAUSER for each USER_KEY. Return result from my query however shows only USER_FNM from the first in the table - Superadministrator. I don't know what is wrong - is USER_KEY iterating in subquery or not?

Here is query:

    SELECT DISTINCT USER_KEY, (SELECT TOP(1) USER_FNM FROM BAUSER WHERE BAUSER.USER_KEY = USER_KEY), SUM([110000003]),SUM([120000002]),SUM([120000003])
FROM CAUSDE_TAS CA
PIVOT
(
SUM(USDE_HSU)
FOR DEPA_KEY IN ([110000003],[120000002],[120000003]
) PIVOT_LOCATIONS
WHERE USDE_DAT >= '01.12.2016' AND USDE_DAT <= '03.02.2017' 
AND USER_KEY IN (100000002,100000004,100000006,100000008,100000011)
GROUP BY USER_KEY

And here are results:

enter image description here

Upvotes: 0

Views: 65

Answers (1)

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

Use PIVOT_LOCATIONS.USER_KEY in your subquery:

SELECT DISTINCT USER_KEY, (SELECT TOP(1) USER_FNM FROM BAUSER WHERE BAUSER.USER_KEY = PIVOT_LOCATIONS.USER_KEY), SUM([110000003]),SUM([120000002]),SUM([120000003])
    FROM CAUSDE_TAS CA
    PIVOT
    (
    SUM(USDE_HSU)
    FOR DEPA_KEY IN ([110000003],[120000002],[120000003]
    ) PIVOT_LOCATIONS
    WHERE USDE_DAT >= '01.12.2016' AND USDE_DAT <= '03.02.2017' 
    AND USER_KEY IN (100000002,100000004,100000006,100000008,100000011)
    GROUP BY USER_KEY

Upvotes: 2

Related Questions