-3

I'm trying to build a SQL query to cross checks a couple of columns. My issue is with the calcontvalue column. The query works fine until I want to include a Where clause containing it AND calcontvalue = job.contvalue. 

SELECT 
job.accountno, 
job.groupno, 
job.contvalue, 
job.z1total, 
job.z2equip, 
job.z3freight, 
job.z4pack, 
job.z5ancil, 
job.z6roy, 
job.zrpay, 
job.z7paid, 
job.z8invtype,
IIF(job.z8invtype <> 0, job.z2equip+job.z5ancil, 
IIF(job.z8invtype <> 1, job.z2equip+job.z3freight+job.z4pack+job.z5ancil, 0)) AS calcontvalue, 
group.groupno, 
group.grouptype, 
group.title  FROM job

LEFT JOIN group  
ON job.groupno = group.groupno 
WHERE grouptype = 'J' AND calcontvalue = job.contvalue

The I'm presented with this error:

SQL:Column 'CALCONTVALUE' is not found.

Not sure what to try next.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Dean
  • 11
  • 1
  • 1
  • 4
  • The WHERE grouptype = 'J' condition makes the LEFT JOIN execute as a regular INNER JOIN. If you really want a LEFT JOIN result, move that condition to the ON clause. (Otherwise switch to INNER JOIN...) – jarlh Oct 22 '15 at 11:51

1 Answers1

4

You cannot use alias in WHERE, use full expression:

SELECT 
  job.accountno, 
  job.groupno, 
  job.contvalue, 
  job.z1total, 
  job.z2equip, 
  job.z3freight, 
  job.z4pack, 
  job.z5ancil, 
  job.z6roy, 
  job.zrpay, 
  job.z7paid, 
  job.z8invtype,
  IIF(job.z8invtype <> 0, job.z2equip+job.z5ancil, 
  IIF(job.z8invtype <> 1, job.z2equip+job.z3freight+job.z4pack+job.z5ancil, 0)) AS calcontvalue, 
  group.groupno, 
  group.grouptype, 
  group.title  
FROM job
LEFT JOIN group  
  ON job.groupno = group.groupno 
WHERE grouptype = 'J' 
  AND IIF(job.z8invtype <> 0, job.z2equip+job.z5ancil, 
      IIF(job.z8invtype <> 1, job.z2equip+job.z3freight+job.z4pack+job.z5ancil, 0)) = job.contvalue

Also naming table group is bad practise because GROUP is keyword GROUP BY.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275