--創建視圖
create view ccc as
(select * from (
select CONVERT(varchar(100),a.Sub_ShipDate1,23) HS_Shipdate,a.TeamHandlingBy,a.JobNumber,a.Version,
a.Bill_to,a.Customer,a.JobName,a.Sub_Qty1 Quantity,
isnull(a.SetDeclareFactory,'待確認') SetDeclareFactory,
b.currency,b.unitprice_d,a.Sub_Qty1 Quantity1,a.Sub_Qty1*b.unitprice_d Total_Sales,
b.status,b.pcost,b.pcostratio from schedulingShipSchedule a, MailingPriceCalVH as b
where a.Sub_ShipDate1<>''and a.Sub_Qty1<>'' and a.JobNumber=b.ordernumber
and a.Version=b.versionno and a.TeamHandlingBy='Mailing'
union all
select CONVERT(varchar(100),a.Sub_ShipDate2,23) HS_Shipdate,a.TeamHandlingBy,a.JobNumber,a.Version,
a.Bill_to,a.Customer,a.JobName,a.Sub_Qty2 Quantity,
isnull(a.SetDeclareFactory,'待確認') SetDeclareFactory,
b.currency,b.unitprice_d,a.Sub_Qty2 Quantity1,a.Sub_Qty2*b.unitprice_d Total_Sales,
b.status,b.pcost,b.pcostratio from schedulingShipSchedule a, MailingPriceCalVH as b
where a.Sub_ShipDate2<>''and a.Sub_Qty2<>'' and a.JobNumber=b.ordernumber
and a.Version=b.versionno and a.TeamHandlingBy='Mailing'
)
as qqq order by JobNumber
)
--出錯信息
Msg 1033, Level 15, State 1, Procedure ccc, Line 22
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries,
and common table expressions, unless TOP, OFFSET or FOR XML is also specified.----如果不用 order by JobNumber 正常,請問怎樣可以排序
create view ccc as
(select * from (
select CONVERT(varchar(100),a.Sub_ShipDate1,23) HS_Shipdate,a.TeamHandlingBy,a.JobNumber,a.Version,
a.Bill_to,a.Customer,a.JobName,a.Sub_Qty1 Quantity,
isnull(a.SetDeclareFactory,'待確認') SetDeclareFactory,
b.currency,b.unitprice_d,a.Sub_Qty1 Quantity1,a.Sub_Qty1*b.unitprice_d Total_Sales,
b.status,b.pcost,b.pcostratio from schedulingShipSchedule a, MailingPriceCalVH as b
where a.Sub_ShipDate1<>''and a.Sub_Qty1<>'' and a.JobNumber=b.ordernumber
and a.Version=b.versionno and a.TeamHandlingBy='Mailing'
union all
select CONVERT(varchar(100),a.Sub_ShipDate2,23) HS_Shipdate,a.TeamHandlingBy,a.JobNumber,a.Version,
a.Bill_to,a.Customer,a.JobName,a.Sub_Qty2 Quantity,
isnull(a.SetDeclareFactory,'待確認') SetDeclareFactory,
b.currency,b.unitprice_d,a.Sub_Qty2 Quantity1,a.Sub_Qty2*b.unitprice_d Total_Sales,
b.status,b.pcost,b.pcostratio from schedulingShipSchedule a, MailingPriceCalVH as b
where a.Sub_ShipDate2<>''and a.Sub_Qty2<>'' and a.JobNumber=b.ordernumber
and a.Version=b.versionno and a.TeamHandlingBy='Mailing'
)
as qqq order by JobNumber
)
--出錯信息
Msg 1033, Level 15, State 1, Procedure ccc, Line 22
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries,
and common table expressions, unless TOP, OFFSET or FOR XML is also specified.----如果不用 order by JobNumber 正常,請問怎樣可以排序
(
with qq as
(
select CONVERT(varchar(100),a.Sub_ShipDate1,23) HS_Shipdate,a.TeamHandlingBy,a.JobNumber,a.Version,
a.Bill_to,a.Customer,a.JobName,a.Sub_Qty1 Quantity,
isnull(a.SetDeclareFactory,'待確認') SetDeclareFactory,
b.currency,b.unitprice_d,a.Sub_Qty1 Quantity1,a.Sub_Qty1*b.unitprice_d Total_Sales,
b.status,b.pcost,b.pcostratio from schedulingShipSchedule a, MailingPriceCalVH as b
where a.Sub_ShipDate1<>''and a.Sub_Qty1<>'' and a.JobNumber=b.ordernumber
and a.Version=b.versionno and a.TeamHandlingBy='Mailing'
union all
select CONVERT(varchar(100),a.Sub_ShipDate2,23) HS_Shipdate,a.TeamHandlingBy,a.JobNumber,a.Version,
a.Bill_to,a.Customer,a.JobName,a.Sub_Qty2 Quantity,
isnull(a.SetDeclareFactory,'待確認') SetDeclareFactory,
b.currency,b.unitprice_d,a.Sub_Qty2 Quantity1,a.Sub_Qty2*b.unitprice_d Total_Sales,
b.status,b.pcost,b.pcostratio from schedulingShipSchedule a, MailingPriceCalVH as b
where a.Sub_ShipDate2<>''and a.Sub_Qty2<>'' and a.JobNumber=b.ordernumber
and a.Version=b.versionno and a.TeamHandlingBy='Mailing'
)
select * from qq order by JobNumber
)--這樣也出錯
Msg 156, Level 15, State 1, Procedure ccc, Line 3
Incorrect syntax near the keyword 'with'.
Msg 319, Level 15, State 1, Procedure ccc, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Procedure ccc, Line 26
Incorrect syntax near ')'.
create view ccc as
(
select CONVERT(varchar(100),a.Sub_ShipDate1,23) HS_Shipdate,a.TeamHandlingBy,a.JobNumber,a.Version,
a.Bill_to,a.Customer,a.JobName,a.Sub_Qty1 Quantity,
isnull(a.SetDeclareFactory,'待確認') SetDeclareFactory,
b.currency,b.unitprice_d,a.Sub_Qty1 Quantity1,a.Sub_Qty1*b.unitprice_d Total_Sales,
b.status,b.pcost,b.pcostratio from schedulingShipSchedule a, MailingPriceCalVH as b
where a.Sub_ShipDate1<>''and a.Sub_Qty1<>'' and a.JobNumber=b.ordernumber
and a.Version=b.versionno and a.TeamHandlingBy='Mailing'
union all
select CONVERT(varchar(100),a.Sub_ShipDate2,23) HS_Shipdate,a.TeamHandlingBy,a.JobNumber,a.Version,
a.Bill_to,a.Customer,a.JobName,a.Sub_Qty2 Quantity,
isnull(a.SetDeclareFactory,'待確認') SetDeclareFactory,
b.currency,b.unitprice_d,a.Sub_Qty2 Quantity1,a.Sub_Qty2*b.unitprice_d Total_Sales,
b.status,b.pcost,b.pcostratio from schedulingShipSchedule a, MailingPriceCalVH as b
where a.Sub_ShipDate2<>''and a.Sub_Qty2<>'' and a.JobNumber=b.ordernumber
and a.Version=b.versionno and a.TeamHandlingBy='Mailing'
)--查詢視圖時再排序:
select * from ccc order by JobNumber
AS
SELECT TOP (100) PERCENT HS_Shipdate, TeamHandlingBy, JobNumber, Version, Bill_to, Customer, JobName, Quantity, SetDeclareFactory, currency,
unitprice_d, Quantity1, Total_Sales, status, pcost, pcostratio
FROM (SELECT CONVERT(varchar(100), a.Sub_ShipDate1, 23) AS HS_Shipdate, a.TeamHandlingBy, a.JobNumber, a.Version, a.Bill_to, a.Customer,
a.JobName, a.Sub_Qty1 AS Quantity, ISNULL(a.SetDeclareFactory, '待確認') AS SetDeclareFactory, b.currency, b.unitprice_d,
a.Sub_Qty1 AS Quantity1, a.Sub_Qty1 * b.unitprice_d AS Total_Sales, b.status, b.pcost, b.pcostratio
FROM dbo.schedulingShipSchedule AS a CROSS JOIN
dbo.MailingPriceCalVH AS b
WHERE (a.Sub_ShipDate1 <> '') AND (a.Sub_Qty1 <> '') AND (a.JobNumber = b.ordernumber) AND (a.Version = b.versionno) AND
(a.TeamHandlingBy = 'Mailing')
UNION ALL
SELECT CONVERT(varchar(100), a.Sub_ShipDate2, 23) AS HS_Shipdate, a.TeamHandlingBy, a.JobNumber, a.Version, a.Bill_to, a.Customer,
a.JobName, a.Sub_Qty2 AS Quantity, ISNULL(a.SetDeclareFactory, '待確認') AS SetDeclareFactory, b.currency, b.unitprice_d,
a.Sub_Qty2 AS Quantity1, a.Sub_Qty2 * b.unitprice_d AS Total_Sales, b.status, b.pcost, b.pcostratio
FROM dbo.schedulingShipSchedule AS a CROSS JOIN
dbo.MailingPriceCalVH AS b
WHERE (a.Sub_ShipDate2 <> '') AND (a.Sub_Qty2 <> '') AND (a.JobNumber = b.ordernumber) AND (a.Version = b.versionno) AND
(a.TeamHandlingBy = 'Mailing')) AS qqq
ORDER BY JobNumber
create view ccc as
(
SELECT TOP (100) PERCENT *
FROM
(
select CONVERT(varchar(100),a.Sub_ShipDate1,23) HS_Shipdate,a.TeamHandlingBy,a.JobNumber,a.Version,
a.Bill_to,a.Customer,a.JobName,a.Sub_Qty1 Quantity,
isnull(a.SetDeclareFactory,'待確認') SetDeclareFactory,
b.currency,b.unitprice_d,a.Sub_Qty1 Quantity1,a.Sub_Qty1*b.unitprice_d Total_Sales,
b.status,b.pcost,b.pcostratio from schedulingShipSchedule a, MailingPriceCalVH as b
where a.Sub_ShipDate1<>''and a.Sub_Qty1<>'' and a.JobNumber=b.ordernumber
and a.Version=b.versionno and a.TeamHandlingBy='Mailing'
union all
select CONVERT(varchar(100),a.Sub_ShipDate2,23) HS_Shipdate,a.TeamHandlingBy,a.JobNumber,a.Version,
a.Bill_to,a.Customer,a.JobName,a.Sub_Qty2 Quantity,
isnull(a.SetDeclareFactory,'待確認') SetDeclareFactory,
b.currency,b.unitprice_d,a.Sub_Qty2 Quantity1,a.Sub_Qty2*b.unitprice_d Total_Sales,
b.status,b.pcost,b.pcostratio from schedulingShipSchedule a, MailingPriceCalVH as b
where a.Sub_ShipDate2<>''and a.Sub_Qty2<>'' and a.JobNumber=b.ordernumber
and a.Version=b.versionno and a.TeamHandlingBy='Mailing'
) a
ORDER BY JobNumber
)