DECLARE
-- Comment out this line for Procedure
@ordOrderNo INT = 2716,
@ordCommtAbrv VARCHAR(30),
@salesOrderNo VARCHAR(10),
@trailingIdent VARCHAR(1) = 'S';
-- Drop the temp table in case for some reason it did not get dropped after the last stored procedure.
IF OBJECT_ID('tempdb..#abrvTable') IS NOT NULL
BEGIN
DROP TABLE #abrvTable;
END
-- Create abbreviation temp table
CREATE TABLE #abrvTable
(
fullTerm VARCHAR(100),
abrvTerm VARCHAR(15)
);
-- Insert data into data table
INSERT INTO #abrvTable (fullTerm, abrvTerm)
VALUES
-- Color
('Amber', 'ASH'), ('Ash', 'BISC'),
-- Species
('Alder', 'ALD'), ('Beech', 'BEE'),
-- Door Style
('Bridgeport', 'BRPT'), ('Grandview', 'GV'),
-- Drawer front Style
('5PC_TOP', '5TOP'), ('Matching', '5PCS'),
-- Product line
('FFO', 'FF'), ('FLO', 'FL');
-- Concat Order Comment Abbrevation based on feature options
SET @ordCommtAbrv = 'NULL';
SET @salesOrderNo = RIGHT('00000' + CAST(@ordOrderNo AS VARCHAR(5)), 5);
SELECT
-- Where the magic happens with trailing letter with break
-- Right now it's ordered by door style, it should be switch to abrv_comment
@salesOrderNo + CHAR(ASCII('A') + DENSE_RANK() OVER (ORDER BY 'Abrv_Comment') -1) + @trailingIdent AS SalesOrderNo,
-- Leave Empty
'' AS [ARDivision No],
vwolni.[venCode] AS [Customer#],
[pdCode] AS [Item Code],
-- olnpd.olnpdEngModel AS [Eng Model],
[olniQty] AS [QTY],
'--TODOs: Concat feature' AS [ftr_Comment],
vwolni.olnpdNetPrice AS [Unit Price],
[bomtCode] AS [Item Type],
-- Control Value | Make sure Sage value is matched
olnopt_Finish.olnoValue AS [Color],
-- Control Value | Make sure Sage value is matched
olnopt_Species.olnoValue AS [Species],
-- Control Value | Make sure Sage value is matched
olnopt_Door_Style.olnoValue AS "Door Style",
-- Control Value | Make sure Sage value is matched
olnopt_Drawer_Head.olnoValue AS [Drawer Head],
'--Report Only' AS [Custom Finish],
'--Report Only' AS [Collection / Order Form Type],
[olniQty] AS [Number of cabinets],
'--Report Only'/*--Subtotal unit price(vwolni.olnpdNetPrice*olniQty)*/ AS [Orderline Total],
ord.ordPONumber as [Customer PO],
(SELECT ISNULL((SELECT abrvTerm
FROM #abrvTable
WHERE fullTerm = olnopt_Finish.olnoValue), 'NULL'))
+('/')
+(SELECT ISNULL((SELECT abrvTerm FROM #abrvTable WHERE fullTerm = olnopt_Species.olnoValue), 'NULL'))
+('/')
+(SELECT ISNULL((SELECT abrvTerm FROM #abrvTable WHERE fullTerm = olnopt_Door_Style.olnoValue), 'NULL'))
+('/')
+(SELECT ISNULL((SELECT abrvTerm FROM #abrvTable WHERE fullTerm = olnopt_Drawer_Head.olnoValue), 'NULL'))
+('/')
AS 'Abrv_Comment',
'--Report Only' AS [Factor],
'--Report Only' AS [Multiplier],
OrgCom.octValue AS [E-mail],
'--Report Only' AS [Contact person],
-- Control Value | Make sure Sage value is matched
shmCode AS [Ship Via],
-- Control Value | Make sure Sage value is matched
subquery_att.SalesPersonCode AS [Salesperson],
OrdPr.ordpValue AS [Freight],
subquery_att.[Job Number] AS [JobNo],
'--Report Only' AS [ShopSQFT],
'--Report Only' AS [Fin Shop SQFT],
olnopt_Drawer_Box.olnoValue AS [DrawerBox],
optCode AS [Cabinet Interior-Exterior]
FROM
...
I tried using just olnopt_Door_Style.olnoValue
to test and the DENSE RANK
line for sales order Number works. But when I switch it to Abrv_comment
, it doesn't work with the error code:
Msg 5309, Level 16, State 1, Line 63
Windowed functions, aggregates and NEXT VALUE FOR functions do not support constants as ORDER BY clause expressions.
1条答案
按热度按时间46scxncf1#
The usual way to solve this problem is to use Common Table Expressions (CTEs). Note the semicolon before the
WITH
statement - it's necessary if the CTE isn't the first line of your code.