时间戳

首页 > 解决方案 > 使用 SQL Server 的列名无效

sql - 使用 SQL Server 的列名无效

问题描述

我想减去 to 的值POSquantity，iPay88quantity但出现错误

列名无效

我的代码：

select distinct
    tblCurrTrxIPAY88.strAmount as IPay88,
    tblCurrTrxIPAY88.strPOSOnlineRefNo,
    count(tblCurrTrxIPAY88.strPOSOnlineRefNo) as iPay88quantity,
    b.strCostCentreID,
    b.strPOSOnlineRefNo, 
    count(b.strPOSOnlineRefNo) as POSquantity,
    strPayTypeCode, b.strRemarks as Agency,
    tblMachine.strDesc As KioskName, 
    SUM(b.dblPaidAmt) as POS24,
    SUM(b.dblPaidAmt) - tblCurrTrxIPAY88.strAmount as RMVarince,
    POSquantity - iPay88quantity as Varince
from 
    tblCurrTrxMaster as b
inner join
    tblMachine on b.strMachID = tblMachine.strMachID 
inner join
    tblCurrTrxIPAY88 on b.strPOSOnlineRefNo = tblCurrTrxIPAY88.strPOSOnlineRefNo
where
    strPaymentMethod = '02' 
    and b.dtmCreated >= '1/23/2020' 
    and b.strTransStatus = '01'
group by 
    b.strCostCentreID , b.strPOSOnlineRefNo, b.strPayTypeCode, b.strRemarks, 
    tblMachine.strDesc, tblCurrTrxIPAY88.strAmount, 
    tblCurrTrxIPAY88.strPOSOnlineRefNo

标签： sqlsql-server

解决方案

iPay88Quantity 不是表中的列名，它是一个计算字段 - 您需要对基础字段进行数学运算，而不是计算字段，即：

POSquantity - count(tblCurrTrxIPAY88.strPOSOnlineRefNo) as Variance

推荐阅读