We help IT Professionals succeed at work.

SQL Syntax increment a number each time I execute the query

mburk1968
mburk1968 asked
on
56 Views
Last Modified: 2017-11-13
I am creating a Sales Report that exports to CSV to be imported into an EDI system. The query pulls detail records using @SalesOrder as the parameter. I have a need to increment a shipment number and Container ID for each SalesOrder # the query executes against. This is just a temporary solution to move inventory. Once completed I'll have no need for this. So I'm looking for the easiest solution.

DECLARE @SalesOrder INT;
SET @SalesOrder = '830901';
SELECT  DTL = 'DTL' ,
        ShipmentNumber = 'Begin with 50000 and increment' ,
		ContainerID = 'Begin with PORTCONTAINER1 and increment',
        ORD.style ,
        ORD.color_code ,
        ORD.dimension
FROM    [dataKLL].[dbo].[KLL Sales Order Summary] ORD
WHERE   ord_num = @SalesOrder;

Open in new window

Comment
Watch Question

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please try this-

DECLARE @SalesOrder INT;
SET @SalesOrder = '830901';

SELECT  DTL = 'DTL' ,
        ShipmentNumber = 50000 +  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) ,
		ContainerID =  ROW_NUMBER() OVER (ORDER BY ContainerID ASC),
        ORD.style ,
        ORD.color_code ,
        ORD.dimension
FROM    [dataKLL].[dbo].[KLL Sales Order Summary] ORD
WHERE   ord_num = @SalesOrder;

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Edited by last comment. Added comma.

Author

Commented:
Sorry I should have be a bit more clear in my request. the ShipmentNumber for the 18 detail records returned for SalesOrder# 830901 should all be 50000. What I am getting is 50000, 50001, 50002, etc.

So for example if I execute for SalesOrder# 830901 I get 50000 for all the DTL Records returned. If I run for SalesOrder# 830902 I would get 50001, etc. Does that make sense?

Also I'm getting an error for ContainerID 'Invalid column name.  and the Container ID should work similar to the ShipmentNumber only it should be PORTCONTAINER1 for SalesOrder# 830902 and if I were to run for another SalesOrder it would be PORTCONTAINER2 for all DTL records returned.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Updated ..
DECLARE @SalesOrder INT;
SET @SalesOrder = '830901';

SELECT  DTL = 'DTL' ,
        ShipmentNumber = 50000 +  DENSE_RANK() OVER (ORDER BY (SELECT NULL)) ,
		ContainerID =  'PORTCONTAINER' + CAST(DENSE_RANK() OVER  (ORDER BY (SELECT NULL)) AS VARCHAR(100)) ,
        ORD.style ,
        ORD.color_code ,
        ORD.dimension
FROM    [dataKLL].[dbo].[KLL Sales Order Summary] ORD
WHERE   ord_num = @SalesOrder

Open in new window

Author

Commented:
I get the format that I'm looking for however I get the same values if I run for a different sales order. I need them to be unique for each SalesOrder that I execute the query against.

So I'm thinking this will need to be put into a table right?
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please run this and see what you are getting..

SELECT  DTL = 'DTL' ,
        ShipmentNumber = 50000 +  DENSE_RANK() OVER (ORDER BY (SELECT NULL)) ,
		ContainerID =  'PORTCONTAINER' + CAST(DENSE_RANK() OVER  (ORDER BY (SELECT NULL)) AS VARCHAR(100)) ,
        ORD.style ,
        ORD.color_code ,
        ORD.dimension
FROM    [dataKLL].[dbo].[KLL Sales Order Summary] ORD

Open in new window

Author

Commented:
See results attached
Book1.xlsx
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Please try this-

DECLARE @SalesOrder INT;
SET @SalesOrder = '830901';

;WITH CTE AS
(
	SELECT  DTL = 'DTL' ,
			ShipmentNumber = 50000 +  DENSE_RANK() OVER (ORDER BY (SELECT NULL)) ,
			ContainerID =  'PORTCONTAINER' + CAST(DENSE_RANK() OVER  (ORDER BY (SELECT NULL)) AS VARCHAR(100)) ,
			ORD.style ,
			ORD.color_code ,
			ORD.dimension,
			ord_num
	FROM    [dataKLL].[dbo].[KLL Sales Order Summary] ORD	
)
SELECT * FROM CTE
WHERE ord_num = @SalesOrder

Open in new window

Author

Commented:
Same results.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Ok if you are running query below are you getting 50002 with SalesOrder = 830902 ??            
                                                                        
SELECT  DTL = 'DTL' ,
			ShipmentNumber = 50000 +  DENSE_RANK() OVER (ORDER BY (SELECT NULL)) ,
			ContainerID =  'PORTCONTAINER' + CAST(DENSE_RANK() OVER  (ORDER BY (SELECT NULL)) AS VARCHAR(100)) ,
			ORD.style ,
			ORD.color_code ,
			ORD.dimension,
			ord_num
	FROM    [dataKLL].[dbo].[KLL Sales Order Summary] ORD

Open in new window

Author

Commented:
I do not. Also it's probably important to mention that I am running SalesOrders one at a time.

Author

Commented:
FYI I need to step away from desk for about an hour. Thank You
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Pls do not use any filter. Please run as it is..Also if possible pls provide that data to excel.

;WITH CTE AS
(
	SELECT  DTL = 'DTL' ,
			ShipmentNumber = 50000 +  DENSE_RANK() OVER (ORDER BY (SELECT NULL)) ,
			ContainerID =  'PORTCONTAINER' + CAST(DENSE_RANK() OVER  (ORDER BY (SELECT NULL)) AS VARCHAR(100)) ,
			ORD.style ,
			ORD.color_code ,
			ORD.dimension,
			ord_num
	FROM    [dataKLL].[dbo].[KLL Sales Order Summary] ORD	
)
SELECT * FROM CTE

Open in new window

Author

Commented:
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '.'.

When I click the error it take me here         SELECT * FROM CTE
        ORD.style ,


If I remove the ORD it gives me incorrect Column Color_Code
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
pls run this -

SELECT  DTL = 'DTL' ,
			ShipmentNumber = 50000 +  DENSE_RANK() OVER (ORDER BY (SELECT NULL)) ,
			ContainerID =  'PORTCONTAINER' + CAST(DENSE_RANK() OVER  (ORDER BY (SELECT NULL)) AS VARCHAR(100)) ,
			ORD.style ,
			ORD.color_code ,
			ORD.dimension,
			ord_num
	FROM    [dataKLL].[dbo].[KLL Sales Order Summary] ORD

Open in new window

Author

Commented:
I get 50001 for all of the SalesOrders in the database
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Wait I will create a sample for u
Database Expert
Awarded 2016
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
See my resiults
Book1.xlsx
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Do we have  starting from SalesOrder = 830901 or do we have records before this also ?

Can you send the entire in excel to me ..so that i can write the tested query. thanks.

Author

Commented:
We have records before this. The 50000 was simply a made up number. Perhaps I just need to trim the output?

Author

Commented:
I was able to get the results that I needed by trimming the results.  Thank You
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.