Select cast(0 as Int) as FSelected,
M.FDate,
M.FNo,
tblStock.FName As FStockName,
tblStock.FShortCode,
tblStock.FCode,
tblStock.FBarCode,
tblStock.FMeasurement,
M.FValidDateBegin,
M.FValidDateEnd,
tblDepart.FName As FDepartName,
tblFlowType.FName As FFlowTypeName,
INPRICETYPE.FName As FInPriceTypeName,
tblPriceType.FName As FSalePriceTypeName,
D.FStockID,
D.FSalePrice,
D.FOldSalePrice,
tblStock.FSalePrice As FDefaultSalePrice,
D.FSalePrice0,
D.FSalePrice1,
D.FLowPrice,
Coalesce(D.FSalePrice, 0) - Coalesce(D.FFavorDisAmount, 0) As FSaleMark,
D.FInType,
D.FInTPrice,
D.FDiscountRate * 100 As FDiscountRate,
D.FPerAmount,
D.FPerProfit,
D.FProfitRate * 100 As FProfitRate,
D.FBonusCostType,
D.FBonusLimit,
D.FBonus,
D.FQuantyLimit,
D.FSelledQuanty,
M.FVersionNo,
M.FPolicytype,
tblStock.FProducingArea,
tblStock.FValidDays,
tblStock.FWeighable,
M.FMaker,
M.FConfirmer,
Case
When (D.FLowPrice = 0) Then
0
When (D.FLowPrice <> 0 And D.FIntype in ('0', '1')) Then
Round((D.FLowPrice - D.FIntPrice) / D.FLowPrice, 4) * 100
When (D.FLowPrice <> 0 And D.FIntype in ('2', '3', '4')) Then
Round(((D.FLowPrice - (D.FLowPrice * (1 - D.FDisCountRate) -
Coalesce(D.FPerAmount, 0))) / D.FLowPrice),
4) * 100
Else
NULL
End As FMinProfitRate,
M.FAbstract,
M.FLowRate * 100 As FLowRate,
M.FRate1 * 100 As FRate1,
M.FRate0 * 100 As FRate0,
M.FStdRate * 100 As FStdRate,
Case
When Coalesce(M.FCanReserve, 0) = 1 Then
'v'
Else
Null
End as FCanReserve,
Case
When Coalesce(M.FChecked, 0) = 1 Then
'v'
Else
''
End As FChecked,
M.FID As FBillID,
D.FID As FDetailID,
tblMark.FName as FMarkName,
tblStockClass.FName As FStockClassName,
D.FPriceTagPrintCount,
Case
when Coalesce(D.FPriceTagPrintCount, 0) > 0 then
'???'
else
''
end As FPriceTag,
Case
When Coalesce(D.FStopped, 0) = 1 Then
'v'
Else
Null
End as FStopped,
D.FStopDate,
tblPerson.FName as FStopPersonName,
Cast(1 As Int) As FPrintCurr,
tblStock.FModel || RPAD(' ', 1) || tblStockClass.FSpecFix As FUnionModel,
tblStock.FSpecification,
tblStock.FComment,
cast(tblStock.FExplain as varchar(500)) as FExplain,
tblMark.FName || RPAD(' ', 1) || tblStockClass.FShorten As FUnionMarkName,
M.FPolicyType,
D.FFavorDisAmount,
D.FFavorSupRate,
D.FGiftStockName
FROM tblSalePolicyDetail D
Inner Join tblSalePolicy M on D.FHeaderID = M.FID
Inner JOin tblSalePolicyFlowType on m.FID =
tblSalePolicyFlowType.FHeaderID
Inner Join((TblStock
Inner Join TblStockClass On TblStock.FStockClassID = TblStockClass.FID)
inner join tblMark on tblStock.FMarkID = tblMark.FID) On D.FStockID = tblStock.FId
Inner Join tblSalePolicyDepart on M.FID = tblSalePolicyDepart.FHeaderID
Inner Join tblDepart On tblSalePolicyDepart.FDepartID = tblDepart.FID
Inner Join tblFlowType On tblSalePolicyFlowType.FFlowtype =
tblFlowType.FID
inner Join tblPriceType On D.FSalePriceTypeID = tblPriceType.FID
Left Join tblPricetype INPRICETYPE On M.FPriceTypeID = INPRICETYPE.FID
left join tblPerson on D.FStopPersonID = tblPerson.FID
WHERE M.FDate >= TO_DATE('2013-03-23', 'YYYY-MM-DD')
And M.FDate <= TO_DATE('2013-03-23', 'YYYY-MM-DD')
ORDER BY tblStock.FName NULLS FIRST,
Case
When Coalesce(m.FPolicyType, 0) > 0 then
0
else
1
End NULLS FIRST,
M.FDate Desc NULLS LAST,
D.FID Desc NULLS LAST
在数据库里面执行就可以,但到程序里面执行就报错。ora-00918 未明确定义列 数据库
M.FDate,
M.FNo,
tblStock.FName As FStockName,
tblStock.FShortCode,
tblStock.FCode,
tblStock.FBarCode,
tblStock.FMeasurement,
M.FValidDateBegin,
M.FValidDateEnd,
tblDepart.FName As FDepartName,
tblFlowType.FName As FFlowTypeName,
INPRICETYPE.FName As FInPriceTypeName,
tblPriceType.FName As FSalePriceTypeName,
D.FStockID,
D.FSalePrice,
D.FOldSalePrice,
tblStock.FSalePrice As FDefaultSalePrice,
D.FSalePrice0,
D.FSalePrice1,
D.FLowPrice,
Coalesce(D.FSalePrice, 0) - Coalesce(D.FFavorDisAmount, 0) As FSaleMark,
D.FInType,
D.FInTPrice,
D.FDiscountRate * 100 As FDiscountRate,
D.FPerAmount,
D.FPerProfit,
D.FProfitRate * 100 As FProfitRate,
D.FBonusCostType,
D.FBonusLimit,
D.FBonus,
D.FQuantyLimit,
D.FSelledQuanty,
M.FVersionNo,
M.FPolicytype,
tblStock.FProducingArea,
tblStock.FValidDays,
tblStock.FWeighable,
M.FMaker,
M.FConfirmer,
Case
When (D.FLowPrice = 0) Then
0
When (D.FLowPrice <> 0 And D.FIntype in ('0', '1')) Then
Round((D.FLowPrice - D.FIntPrice) / D.FLowPrice, 4) * 100
When (D.FLowPrice <> 0 And D.FIntype in ('2', '3', '4')) Then
Round(((D.FLowPrice - (D.FLowPrice * (1 - D.FDisCountRate) -
Coalesce(D.FPerAmount, 0))) / D.FLowPrice),
4) * 100
Else
NULL
End As FMinProfitRate,
M.FAbstract,
M.FLowRate * 100 As FLowRate,
M.FRate1 * 100 As FRate1,
M.FRate0 * 100 As FRate0,
M.FStdRate * 100 As FStdRate,
Case
When Coalesce(M.FCanReserve, 0) = 1 Then
'v'
Else
Null
End as FCanReserve,
Case
When Coalesce(M.FChecked, 0) = 1 Then
'v'
Else
''
End As FChecked,
M.FID As FBillID,
D.FID As FDetailID,
tblMark.FName as FMarkName,
tblStockClass.FName As FStockClassName,
D.FPriceTagPrintCount,
Case
when Coalesce(D.FPriceTagPrintCount, 0) > 0 then
'???'
else
''
end As FPriceTag,
Case
When Coalesce(D.FStopped, 0) = 1 Then
'v'
Else
Null
End as FStopped,
D.FStopDate,
tblPerson.FName as FStopPersonName,
Cast(1 As Int) As FPrintCurr,
tblStock.FModel || RPAD(' ', 1) || tblStockClass.FSpecFix As FUnionModel,
tblStock.FSpecification,
tblStock.FComment,
cast(tblStock.FExplain as varchar(500)) as FExplain,
tblMark.FName || RPAD(' ', 1) || tblStockClass.FShorten As FUnionMarkName,
M.FPolicyType,
D.FFavorDisAmount,
D.FFavorSupRate,
D.FGiftStockName
FROM tblSalePolicyDetail D
Inner Join tblSalePolicy M on D.FHeaderID = M.FID
Inner JOin tblSalePolicyFlowType on m.FID =
tblSalePolicyFlowType.FHeaderID
Inner Join((TblStock
Inner Join TblStockClass On TblStock.FStockClassID = TblStockClass.FID)
inner join tblMark on tblStock.FMarkID = tblMark.FID) On D.FStockID = tblStock.FId
Inner Join tblSalePolicyDepart on M.FID = tblSalePolicyDepart.FHeaderID
Inner Join tblDepart On tblSalePolicyDepart.FDepartID = tblDepart.FID
Inner Join tblFlowType On tblSalePolicyFlowType.FFlowtype =
tblFlowType.FID
inner Join tblPriceType On D.FSalePriceTypeID = tblPriceType.FID
Left Join tblPricetype INPRICETYPE On M.FPriceTypeID = INPRICETYPE.FID
left join tblPerson on D.FStopPersonID = tblPerson.FID
WHERE M.FDate >= TO_DATE('2013-03-23', 'YYYY-MM-DD')
And M.FDate <= TO_DATE('2013-03-23', 'YYYY-MM-DD')
ORDER BY tblStock.FName NULLS FIRST,
Case
When Coalesce(m.FPolicyType, 0) > 0 then
0
else
1
End NULLS FIRST,
M.FDate Desc NULLS LAST,
D.FID Desc NULLS LAST
在数据库里面执行就可以,但到程序里面执行就报错。ora-00918 未明确定义列 数据库
这里有问题
一般程序分页会出现这个问题。字段名相同报错,你这太多了,仔细看看吧SELECT *
FROM (SELECT A.*, ROWNUM RN
FROM (
select 1 a,2 a from dual
) A
WHERE ROWNUM <= 25)
WHERE RN >= 1