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.
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.
;WITH cte AS
(
SELECT fields, 'my_calculated_field' AS calculatedField
FROM table
WHERE conditions
),
cte2 AS
(
SELECT fields, DENSE_RANK() OVER (ORDER BY calculatedField) as rowNum
FROM cte
)
SELECT fields
FROM cte2
ORDER BY rowNum