CODEWITHSUNDEEP
sql-serversql-server-2008stored-proceduressqlexception
Amrit
Amrit

Reputation: 421

Use OuterQuery Column in Subquery

Hi I am doing a subquery to get value of cTL column But i need value of outer column in the subquery for right response but i get error that it doesn't recognize the SC and RC varaibles. ANy way out?

SELECT DISTINCT(cStoreCode) as SC
    ,cRetailerCode as RC
    , cStoreCode + ' - ' + cStoreName AS Store,
    x.TeamLead 
FROM
(
    SELECT cTL as TeamLead 
    FROM tblEmpSchedule 
    WHERE   cRepCode LIKE @repCode AND
        cClientCode LIKE @gCode AND
        cProjectCode LIKE @pCode AND
        cYear LIKE @year AND
        cCycleNo LIKE @cycleNo AND
        cRetailerCode LIKE @retailerCode AND
        cStoreCode = SC AND
        cRetailerCode = RC
 )x
,tblEmpSchedule 
WHERE cRepCode LIKE @repCode AND
    cClientCode LIKE @gCode AND
    cProjectCode LIKE @pCode AND
    cYear LIKE @year AND
    cCycleNo LIKE @cycleNo AND
    cRetailerCode LIKE @retailerCode AND
    cStoreCode <> '' AND cStoreCode is Not Null
ORDER BY cStoreCode

Upvotes: 0

Views: 314

Answers (2)

spencer7593
spencer7593

Reputation: 108380

No, you can't reference an alias for an expression like that, in a WHERE clause. An alias for an expression can be referenced in a WHERE clause ONLY if it's from an inline view or CTE (referenced in the FROM clause.)

The use of the DISTINCT keyword looks peculiar. There's no need to wrap the first expression in the SELECT list in parenthesis. Your construct makes it almost look like it were a function call. (It isn't, that DISTINCT keyword applies to ALL of the expressions in your SELECT list).

It's not at all clear why you need a JOIN operation here.

I hate to answer a question with a question, but... What are you trying to gain by using an inline view to get a value from the cTL column aliased as TeamLead? What problem is that supposed to be solving? Why does the table need to be referenced twice?

To ask the same question another way...

What's "wrong" with the result set returned by a query on the tblEmpSchedule table, like this one?

SELECT DISTINCT s.cStoreCode             AS sc
     , s.cRetailerCode                   AS rc
     , s.cStoreCode + ' - ' + cStoreName AS Store
     , s.cTL                             AS TeamLead
  FROM tblEmpSchedule s
 WHERE s.cRepCode LIKE @repCode AND
       s.cClientCode LIKE @gCode AND
       s.cProjectCode LIKE @pCode AND
       s.cYear LIKE @year AND
       s.cCycleNo LIKE @cycleNo AND
       s.cRetailerCode LIKE @retailerCode AND
       s.cStoreCode <> ''
 ORDER BY s.cStoreCode

If you can explain why the result set returned by this query differs from what you need, then I (or someone else) may be able to help. As your question stands, it's not clear what you are trying to achieve.

NOTE: the "cStoreCode IS NOT NULL" predicate in your query is redundant. We already know it can't be NULL if the "cStoreCode <> ''" predicate returns TRUE.

Upvotes: 1

Taryn
Taryn

Reputation: 247680

Looks like you need single quotes around the SC/RC:

cStoreCode = 'SC' AND
cRetailerCode = 'RC'

EDIT:

Please check the following to see if it would work:

SELECT cTL as TeamLead 
FROM tblEmpSchedule t1
INNER JOIN 
(
  SELECT DISTINCT(cStoreCode) SC
    , cRetailerCode RC
    , cStoreCode + ' - ' + cStoreName AS Store
  FROM tblEmpSchedule
)t2

  on t1.cTL = t2.cTL
WHERE t1.cRepCode LIKE @repCode 
  AND t1.cClientCode LIKE @gCode 
  AND t1.cProjectCode LIKE @pCode 
  AND t1.cYear LIKE @year 
  AND t1.cCycleNo LIKE @cycleNo 
  AND t1.cRetailerCode LIKE @retailerCode 
  AND (t1.cStoreCode <> '' AND t1.cStoreCode is Not Null)
  AND t1.cStoreCode = t2.SC
  AND t1.cRetailerCode = t2.RC
ORDER BY  t1.cStoreCode

Upvotes: 1

Related Questions