Avatar of mburk1968
mburk1968Flag for United States of America asked on

SQL Syntax increment a number each time I execute the query

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

SQL

Avatar of undefined
Last Comment
mburk1968

8/22/2022 - Mon
Pawan Kumar

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 Kumar

Edited by last comment. Added comma.
ASKER
mburk1968

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Pawan Kumar

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

ASKER
mburk1968

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 Kumar

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
mburk1968

See results attached
Book1.xlsx
Pawan Kumar

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

ASKER
mburk1968

Same results.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Pawan Kumar

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

ASKER
mburk1968

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

FYI I need to step away from desk for about an hour. Thank You
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Pawan Kumar

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

ASKER
mburk1968

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 Kumar

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
mburk1968

I get 50001 for all of the SalesOrders in the database
Pawan Kumar

Wait I will create a sample for u
ASKER CERTIFIED SOLUTION
Pawan Kumar

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
mburk1968

See my resiults
Book1.xlsx
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Pawan Kumar

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.
ASKER
mburk1968

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

I was able to get the results that I needed by trimming the results.  Thank You
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23