使用以下查询(该查询不包含日期和/或时间转换的字符串):
SELECT
Date,
RangeFam,
QTY,
Country
FROM (
SELECT Q_QTM as Qty,
Q_OFDFIN as Date,
Q_PAYS as Country,
CASE Q_DTRI05
WHEN 'eBALTIC - EBB' THEN 'EBAB'
WHEN 'Baltic -' THEN 'BALT'
WHEN 'eBALTIC - EBF' THEN 'EBAF'
WHEN 'Roof-tops - ENERGY' THEN 'ENER'
WHEN 'Roof-tops - FLEXAIR' THEN 'FLER'
END as RangeFam
FROM [sales].[dbo].[CARNET2_D]
WHERE Q_OFET='D'
and Q_TYPE='M'
UNION ALL
SELECT Qty,
Range_Code as RangeFam,
ShippingDate as Date,
CASE Country collate DATABASE_DEFAULT
WHEN 'France' THEN 'FRANCE'
WHEN 'Netherlands' THEN 'PAYS BAS'
WHEN 'Belgium' THEN 'BELGIQUE'
WHEN 'Portugal' THEN 'PORTUGAL'
WHEN 'Spain' THEN 'ESPAGNE'
END AS Country
FROM [crmv2].[dbo].[View_Forecastdata_Extended]
WHERE BaseUnit='1'
and (Project_status='Detection / Faisability' or Project_status='Execution' or Project_status='Quotation' or Project_status='Specification stage')
and [Report_S3A/B_group]='Rooftop'
and Sop='1'
and SopValid='1'
and (Sourcing='L&B' or Sourcing='LON' or Sourcing='BUR')
) as T1
ORDER BY Date
我知道这个错误:
Msg 241,16级,状态1,第1行
从字符串转换日期和/或时间时,转换失败。
奇怪的是,如果我删除了所有与字段RangeFam相关的代码,那么查询就可以了,所以我想我错过了一些关于它的东西。
谢谢
当我说删除有关RangeFam的代码时,我的意思是以下查询没有问题:
SELECT
Date,
--RangeFam,
QTY,
Country
FROM (
SELECT Q_QTM as Qty,
Q_OFDFIN as Date,
Q_PAYS as Country
--CASE Q_DTRI05
-- WHEN 'eBALTIC - EBB' THEN 'EBAB'
-- WHEN 'Baltic -' THEN 'BALT'
-- WHEN 'eBALTIC - EBF' THEN 'EBAF'
-- WHEN 'Roof-tops - ENERGY' THEN 'ENER'
-- WHEN 'Roof-tops - FLEXAIR' THEN 'FLER'
--END as RangeFam
FROM [sales].[dbo].[CARNET2_D]
WHERE Q_OFET='D'
and Q_TYPE='M'
UNION ALL
SELECT Qty,
--Range_Code as RangeFam,
ShippingDate as Date,
CASE Country collate DATABASE_DEFAULT
WHEN 'France' THEN 'FRANCE'
WHEN 'Netherlands' THEN 'PAYS BAS'
WHEN 'Belgium' THEN 'BELGIQUE'
WHEN 'Portugal' THEN 'PORTUGAL'
WHEN 'Spain' THEN 'ESPAGNE'
END AS Country
FROM [crmv2].[dbo].[View_Forecastdata_Extended]
WHERE BaseUnit='1'
and (Project_status='Detection / Faisability' or Project_status='Execution' or Project_status='Quotation' or Project_status='Specification stage')
and [Report_S3A/B_group]='Rooftop'
and Sop='1'
and SopValid='1'
and (Sourcing='L&B' or Sourcing='LON' or Sourcing='BUR')
) as T1
ORDER BY Date
发布于 2022-12-02 11:19:43
写查询的方式第一个子查询中的Q_OFDFIN
内容是UNIONed,第二个子查询的内容是Range_Code
。因为其中一个似乎是Date
,而另一个似乎是字符串,所以服务器必须将它们转换为公共类型。
UNION和UNION都按位置处理结果字段,而不是子查询别名。
若要修复此问题,请确保列以正确的顺序显示:
FROM (
SELECT Q_QTM as Qty,
Q_OFDFIN as Date,
Q_PAYS as Country,
CASE Q_DTRI05
WHEN 'eBALTIC - EBB' THEN 'EBAB'
WHEN 'Baltic -' THEN 'BALT'
WHEN 'eBALTIC - EBF' THEN 'EBAF'
WHEN 'Roof-tops - ENERGY' THEN 'ENER'
WHEN 'Roof-tops - FLEXAIR' THEN 'FLER'
END as RangeFam
FROM [sales].[dbo].[CARNET2_D]
WHERE Q_OFET='D'
and Q_TYPE='M'
UNION ALL
SELECT Qty,
ShippingDate as Date,
CASE Country collate DATABASE_DEFAULT
WHEN 'France' THEN 'FRANCE'
WHEN 'Netherlands' THEN 'PAYS BAS'
WHEN 'Belgium' THEN 'BELGIQUE'
WHEN 'Portugal' THEN 'PORTUGAL'
WHEN 'Spain' THEN 'ESPAGNE'
END AS Country,
Range_Code as RangeFam
FROM [crmv2].[dbo].[View_Forecastdata_Extended]
...
https://stackoverflow.com/questions/74654635
复制相似问题