need some help to update a table 1

[chamilz]

I would greatly appriciate your suggestions and advice to find a sulution to following,

I have a table which is maintain by current application comprising "pack code/style/vendor/size/qty".
The pack code is automatically assign for the cobination of style/vendor/color/size/quantity.(exisitng application)

Now there is a manual process running where user create "style/vendor/color/size/qty" text file and i am importing these records into following working table. Then i am assigning PACK_CODE from existing table (Application)
base on the style/Vendor/Color/size/qty.(used inner join with application table to update my work table)


Question:
If there is no pack_code exist for the cobination of style/vendor/color/size/qty, then i have to generate a pack code seperately (sequential number used diffrent range).
I used Identity table to generate pack_code. But, i have probelem in generating and assigning pack code back to my working
table? i am quite new to T-SQL and never used a cursor befor.

working table:

Pack Style Vendor color size qty

02365 455511262 12398 002 920 1
02365 455511262 12398 002 930 2
02365 455511262 12398 002 950 2
02365 455511262 12398 002 960 1
455511262 12398 101 920 1
455511262 12398 101 930 2
455511262 12398 101 950 3
455511262 12398 101 960 2
02372 455511262 12398 404 920 1
02372 455511262 12398 404 930 2
02372 455511262 12398 404 950 2
02372 455511262 12398 500 960 1
455511262 12398 500 920 1
455511262 12398 500 930 2
455511262 12398 500 950 2
455511262 12398 500 960 1

Pack_code
Seq_id Pack_code (Identity/ seed 1)
1 40000


Thank you.

 

[Catadmin]

I don't think a cursor is the answer to your problem.

What code is being used to auto generate the PACKCODE? Is this a VB application or a stored procedure?

I'm thinking you could just use a trigger and the temporary tables to fix this problem.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[chamilz]

There is no link with other application.Only T-SQL

Pack code is predefined sequence number like below.Always assign next available pack code.( i dont have code for this at this moment)
Pack Code status need to be updated each time after updating working table.

ID PACK CODE Status
1 40000 1 -Used
2 40001 0
.
.
N NNNN

I need to develop code for following logic;

IF WORKING_TABLE Pack_Code = NULL
--> Assign next available pack code from PACK_TABLE
(style/color/size/qty)
--> update PACK_CODE status
End IF

If this is not a proper solution i can change the logic. But i need to keep a track for newly generated PACK_CODE.
Thanks.

 

[Catadmin]

Such a shame default value in table design wants literals and doesn't allow expressions (AFAIK).

I'm split on this one. On one hand, I'm tempted to recommend a FOR UPDATE trigger which occurs after you've inserted the table, but you say in some instances, this is already getting inserted when the user does this one way.

(More detail on when this actually works correctly would be helpful if you're going the Trigger route. You say it's T-SQL, but how is the user invoking this? In Query Analyzer? In Ent. Manager? Do the users even see this code or is something they are doing somewhere else activating this T-SQL?)

My other thought is to write another SP that goes into the table, looks for NULL PackCodes, then looks at your PackCode table where Status = NULL, switches it to USED and then updates the other table with new PackCode. The only problem with this is you would have to either schedule it to run on a regular basis or remember to run it manually each time.

Does that make sense? Also, do you actually need or want help with actual code structure for one of the two above options?





Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[chamilz]

Thanks!

Users will use another application to create STYLE/VENDOR/COLOR/SIZE/Qty and each time
this application generates text file. (Independent Process)

At the end of the day these text files import into working table and need to run my process to assign
PACK CODE only at nigthly. (these is no user involvement)

I like your following solution. Can i have code structure for this??

"My other thought is to write another SP that goes into the table, looks for NULL PackCodes,
then looks at your PackCode table where Status = NULL, switches it to USED and then updates
the other table with new PackCode. The only problem with this is you would have to either
schedule it to run on a regular basis or remember to run it manually each time."

Thanks again for your help.

 

[Catadmin]

Two more quick questions.

Is Application Table 4 columns or 3 columns? I.E., are PACK and CODE in that table the same column or not? It's hard to tell from the way you listed your structure.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[chamilz]

3 columns.

ID PACK_CODE STATUS
1 40000 1 - used
2 40001 0 - next available
3 40002 0

PACK_CODE(Pack Table) = PACK (Working Table - 1 st table)

Sorry for confussion, these columns are same.

Thanks,

 

[Catadmin]

Chamilz,

You'll have to change the datatypes to correctly reflect your actual datatypes. I just stuck INT in the temp table definitions because it was convienent.

Also, I know there's a way to do an update based on a self inner join (which I think would be better than the following code), but I can't figure it out right now. If I do, I'll post a better version. If someone else knows of how to accomplish this, I'd welcome the information.

Create Procedure spUpdatePackCode
AS
Declare @LoopCount int,
	@PackCode int

Create Table #TempWorking (Style int, Vendor int, Color int, Size int, Qty int)

Insert into #TempWorking (Style, Vendor, Color, Size, Qty)
(Select Style, Vendor, Color, Size, Qty from Working_Table
where Pack IS NULL)

Set @LoopCount = (Select Count(*) from #TempWorking)

While @LoopCount <> 0
  Begin
   Set @PackCode = (Select min(PackCode) from Application where Status = 0)

   Update Working_Table
   Set Pack = @PackCode
    where Pack IS NULL AND Working_Table.Style = #TempWorking.Style
     AND Working_Table.Vendor = #TempWorking.Vendor 
     AND Working_Table.Color = #TempWorking.Color
     AND Working_Table.Size = #TempWorking.Size
     AND Working_Table.Qty = #TempWorking.Qty

   Update Application
   Set Status = 1
	Where PackCode = @PackCode

   Set @LoopCount = @LoopCount - 1

  END

Drop Table #TempWorking

It's a little bulky, but this should do the job for now.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[chamilz]

Thanks Catadmin!

This is really useful, I will start from here and let you know the result.

Have a greate day!

 

[darrellblackhawk]

Here's another possibility. A bit kludgy and I haven't
totally verified it yet, but it runs from a trigger.
(Haven't tested it for bulk-inserts and I apologize for
any junior constructs or known gotchas)

The code requires adding an additional field to the
working table. I called it pkey, but it's not really a
primary key. Probably should be modified to include one.

In addition, there are print commands in the trigger that
would need to be removed.


Darrell

[b][blue]// Code to create tables and trigger...[/blue][/b]

[b][green]// Tables [/green][/b]
drop table [dbo].[working]
CREATE TABLE [dbo].[working] (
    [pkey]      int not null identity(0,1),
    [pack]  	int NULL,
    [style] 	char(10) NOT NULL,
    [vendor]	char(5) NOT NULL,
    [color] 	int NOT NULL,
    [size]  	char(4) NOT NULL,
    [qty]   	int NOT NULL 
    )
GO

drop table [dbo].[pack_code]
CREATE TABLE [dbo].[pack_code] ( 
    [pack]  	int NOT NULL identity(4000,1),
    [code]  	char(1) NOT NULL,
    [status]	char(6) NULL,
    [style] 	char(10) NULL,
    [vendor]	char(5) NULL,
    [color] 	int NULL,
    [size]  	char(4) NULL,
    [qty]   	int NULL 
    )
GO

[b][green]// Trigger [/green][/b]
// Create the trigger
CREATE TRIGGER [dbo].[trNextPack]
ON dbo.working
FOR insert
AS
begin
    declare @iPkey as int
    declare @iPack as int
    declare @cStyle as char(10)
    declare @cVendor as char(5)
    declare @iColor as int
    declare @iQty as int
    declare @cSize as char(4)

    declare @iPackExists as int

    // Grab the recently inserted row
    select 
        @iPkey = pkey,
        @iPack = isnull(pack,0), // normalize null 
        @cStyle = style, 
        @cVendor = vendor,
        @iColor = color,
        @iQty = qty ,
        @cSize = size
        from inserted

    // Check if a pack_code exists for the recently inserted row
    select
        @iPackExists = pack from pack_code 
        where style = @cStyle and vendor = @cVendor
        and color = @iColor and qty = @iQty and size = @cSize

    // Normalize null values
    set @iPackExists = isnull(@iPackExists,0)

    // If Pack was null and there wasn't an existing pack code...
    if (@iPack = 0 and @iPackExists = 0)
        begin
            [red]// remove me
            print 'Inserting into pack_code'[/red]
            insert into pack_code 
                (code,status,style,vendor,color,qty,size) values
               (1,'-used',@cStyle,@cVendor,@iColor,@iQty,@cSize)

            update working set pack = @@identity where pack is null
        end
    else

    // If a pack_code was found for the recently inserted row...
    // Bypasses state where pack_code is actually inserted by the insert
    // i.e.
    // insert into working 
    //   (pack, style,vendor,color,qty,size) values
    //   (02365,'455511262','12398',002,'920',1)
        if (@iPackExists <> 0)
        begin
            [red]// remove me
            print 'Using value from pack_code'
            print @iPackExists[/red]
            update working set pack = @iPackExists where pkey = @iPkey
        end
end
GO


[b][blue]// Test inserts and selections to verify...[/blue][/b]
[red]//Run this twice to really verify[/red]

// Non-existant pack_code
insert into working 
    (style,vendor,color,qty,size) values
    ('455511262','12398',101,'920',1)

// Previously created pack_code
insert into working 
    (style,vendor,color,qty,size) values
    ('455511262','12398',101,'920',1)

// Existant pack_code
insert into working 
    (pack, style,vendor,color,qty,size) values
    (02365,'455511262','12398',002,'920',1)

// Non-existant pack_code
insert into working 
    (style,vendor,color,qty,size) values
    ('455511262','12398',101,'960',2)
                  
select * from working
select * from pack_code

 

[darrellblackhawk]

CAVEAT:
Sorry about the 'Drop table' commands, I was just testing
it on my server. I guess I don't have to say don't run this
without reviewing fully.

Darrell

 

[chamilz]

Hi,
I got a problem when updating Working_Table using following structure. This update all the "NULL" "Pack" from @PackCode.
I need to update pack for style/vendor/color/zise/Qty group. (i.e. Style/Vendor/Color)


Table:
Pack Style Vendor Color Size Qty
4000 12345 8888 002 900 1
4000 12345 8888 002 920 2
4000 12345 8888 002 930 1
4001 12345 8888 005 900 2
4001 12345 8888 005 920 2
4001 12345 8888 005 940 2
12345 8888 007

Update Working_Table
Set Pack = @PackCode
from Working_Table inner join #TempWorking
where Pack IS NULL
AND Working_Table.Style = #TempWorking.Style
AND Working_Table.Vendor = #TempWorking.Vendor
AND Working_Table.Color = #TempWorking.Color

How can i modify this code to update only specific set of recrods.

Thanks.

 

[chamilz]

darrellblackhawk ..sorry i didnt see your reply before i post my last issue..

I will give a try..thanks.

 

[Catadmin]

I got a problem when updating Working_Table using following structure. This update all the "NULL" "Pack" from @PackCode.
I need to update pack for style/vendor/color/zise/Qty group. (i.e. Style/Vendor/Color)

Sorry, chamilz. I don't understand what problem you're having with that statement. You don't want to update NULL? Or you do and it's not???

Also, looking at the code you posted, you forgot to add in the Size and Qty AND statements in the WHERE clause. Was this deliberate? I posted my code containing a check on all 5 columns because I thought you wanted to change PACK based on all 5 columns instead of just three of them.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[chamilz]

Hi Catadmin,

I didnt explain it properly. However, i found a solution using your code. Just go throuhg this and give your comments/suggestions.

Step-1: Extracted distinct style/vendor/color records where pack_code is NULL from the orginal table to a working_Table.(I forgot to highlight this in my previous posts. Always PACK is unique for Style/vendor/color.)

Original Table:
Pack Style Vendor Color Size Qty
12345 8888 002 900 1
12345 8888 002 920 2
12345 8888 002 930 1
12345 8888 005 900 2
12345 8888 005 920 2
12345 8888 005 940 2

Working Table:
pack style Vendor Color
12345 8888 002
12345 8888 005

2. Assign 'Pack' to workiing table from 'default pack code' table:

---create procedure p4p_nsb_generate_pack as
declare @loopcount int,
@packcode int

set @loopcount = (select count(*) from #working_table)

WHILE @loopcount <> 0
BEGIN
set @packcode = (select min(pack_code) from
p4p_nsb_pack_code where status = 0)

IF (@packcode = ( select pack_code from #working_table
where pack_code = @packcode))
BEGIN
-- print ' duplicate pack code'
END

ELSE

update #working_table
set pack_code = @packcode
where style_code = (select top 1 style_code from
#working_table where pack_code is Null)
and color_code = ( select top 1 color_code from
#working_table where pack_code is Null )

update p4p_nsb_pack_code
set status = 1, date_used = getdate()
where pack_code = @packcode

set @loopcount = @loopcount - 1

END


3. Original Table pack will be updated using working table.The purpose of Original table is to generate TEXT file to feed another application.

This is working fine and thanks for the help. Do you have any suggetion about update part ?


P.S. Darell: Thanks for your solution, i want to try it. This going to be my first trigger....