Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Cross Join Help Expand / Collapse
Author
Message
Posted Friday, July 25, 2014 6:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:50 AM
Points: 327, Visits: 586
I have a massive table (company, plant, resource, part number) that I need to create a from part number, to part number for every part within "Company, Plant, Resource" combo.

Trying to figure out what the best method would be.. using a cursor will put the load on one CPU and take way too long.

Ideas?
Post #1596189
Posted Friday, July 25, 2014 7:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:53 AM
Points: 2,342, Visits: 2,839
A cursor is not the right approach for this scenario. Allthough it is not very clear what your situation is, it sounds like you can just insert the results from two tables joind together into a new table.

Table_A holds the company, plant, part, etc. values
Table_B holds the 'from part', 'to part', and maybe some more relevant columns

The query would look like this:
SELECT {specify the required columns here}
INTO {new_table}
FROM Table_A
INNER JOIN Table_B
ON Table_A.part = Table_B.from_part


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1596209
Posted Friday, July 25, 2014 7:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 1,031, Visits: 6,726
dwilliscp (7/25/2014)
I have a massive table (company, plant, resource, part number) that I need to create a from part number, to part number for every part within "Company, Plant, Resource" combo.

Trying to figure out what the best method would be.. using a cursor will put the load on one CPU and take way too long.

Ideas?

SELECT company, plant, resource, MIN(partno) as [from part number], MAX(part number) as [to part number]
FROM table
GROUP BY company, plant, resource



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1596210
Posted Friday, July 25, 2014 8:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:50 AM
Points: 327, Visits: 586
dwilliscp (7/25/2014)
I have a massive table (company, plant, resource, part number) that I need to create a from part number, to part number for every part within "Company, Plant, Resource" combo.

Trying to figure out what the best method would be.. using a cursor will put the load on one CPU and take way too long.

Ideas?



You have one table... lets call it Resource_Mat. PK Company, Plant, Resource, Part Number. What I need to do is ..

Get the first row from Resource_mat and for every row in the table create a data set that contains:

Company, Plant, Resource, [Selected rows' part number], [current rows' part number]

The only restriction is that the loop needs to only work on where Comany + Plant + Resournce matches between selected row and current row.

This is why I thought about using a cursor.. but the data set is so large, and SQL will throw it all on one CPU... I expect it to take too long.

The result of the above query then gets updated/inserted into a table that holds this information about from part .. to part and then we keep up how long it takes to transitition from one part number to a different part number.
Post #1596240
Posted Friday, July 25, 2014 9:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:50 AM
Points: 327, Visits: 586
I broke down and wrote it as a Cursor... Still running so I do not know if my code is correct yet... I will create a table and some dummy data in the next post.

declare @plant as varchar(4)
, @Resource_Code as varchar(8)
, @Material_Number as varchar(18)
set nocount on;
declare i_cursor insensitive cursor
for Select Plant, Resource_Code, Material_Number From Resource_Mat
open i_Cursor

while @@fetch_status = 0
begin
insert into trans_Matrix(Company, Plant, Resource_Code, From_Material, To_Material)
select 'SPOR' as Company
, Plant
, Resource_Code
, @Material_Number as From_Material
, Material_Number as To_Material
from Resource_Mat
where @Plant = plant and @resource_code = resource_code
end
fetch next from i_Cursor into @Plant, @Resource_Code, @Material_Number
close i_Cursor
deallocate i_cursor
set nocount off;
go
Post #1596273
Posted Friday, July 25, 2014 9:18 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:50 AM
Points: 327, Visits: 586
CREATE TABLE [dbo].[Resource_Mat](
[Plant] [varchar](4) NOT NULL,
[Material_number] [varchar](18) NOT NULL,
[Production_Version] [varchar](4) NOT NULL,
[Resource_Code] [varchar](8) NOT NULL,
[Std_Batch_Size] [varchar](18) NULL,
[UOM] [varchar](3) NULL,
[Std_Run_Time] [varchar](14) NULL,
[Time_UOM] [varchar](3) NULL,
[Batch_Min] [varchar](17) NULL,
[Batch_Max] [varchar](17) NULL,
[Stocking_Strategy] [varchar](2) NULL,
[Trans_Type] [varchar](2) NULL,
[Recipe_Status] [varchar](4) NULL,
[Short_Description] [varchar](40) NULL,
[Rounding_Value] [varchar](17) NULL,
[Distribution_Key] [varchar](4) NULL,
CONSTRAINT [PK_Resource_Mat] PRIMARY KEY CLUSTERED
(
[Plant] ASC,
[Material_number] ASC,
[Production_Version] ASC,
[Resource_Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
Post #1596274
Posted Friday, July 25, 2014 9:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 12,994, Visits: 12,411
dwilliscp (7/25/2014)
dwilliscp (7/25/2014)
I have a massive table (company, plant, resource, part number) that I need to create a from part number, to part number for every part within "Company, Plant, Resource" combo.

Trying to figure out what the best method would be.. using a cursor will put the load on one CPU and take way too long.

Ideas?



You have one table... lets call it Resource_Mat. PK Company, Plant, Resource, Part Number. What I need to do is ..

Get the first row from Resource_mat and for every row in the table create a data set that contains:

Company, Plant, Resource, [Selected rows' part number], [current rows' part number]

The only restriction is that the loop needs to only work on where Comany + Plant + Resournce matches between selected row and current row.

This is why I thought about using a cursor.. but the data set is so large, and SQL will throw it all on one CPU... I expect it to take too long.

The result of the above query then gets updated/inserted into a table that holds this information about from part .. to part and then we keep up how long it takes to transitition from one part number to a different part number.


This is NOT the time to use a cursor. You really need to change the way you think about data. You have to think about what you want to do to a column, not a row.

If you can post ddl and sample data along with desired output we can show you how to do this in a set based manner.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1596278
Posted Friday, July 25, 2014 9:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:50 AM
Points: 327, Visits: 586
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'CHANC', N'11C-8260', N'0001', N'SIL', N'15.000', N'KG', N'0.100', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'0.000', N'')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'CHANC', N'11C-8260', N'0001', N'APAG', N'1000.000', N'KG', N'4.000', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'400.000', N'')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'CHANC', N'11C-8261', N'0001', N'APAG', N'1000.000', N'KG', N'4.138', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'600.000', N'')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'CHANC', N'11C-8261', N'0001', N'SIL', N'15.000', N'KG', N'0.100', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'0.000', N'')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'CHANC', N'21E-4440', N'0002', N'SIL', N'1000.000', N'KG', N'0.250', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'0.000', N'')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'LEX', N'11C-8260', N'0001', N'SIL', N'1000.000', N'KG', N'1.200', N'H', N'0.000', N'999999999.000', N'FX', N'2', N'4', N'', N'0.000', N'')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'LEX', N'11C-8261', N'0001', N'APAG', N'1000.000', N'KG', N'3.154', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'', N'600.000', N'')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'LEX', N'21E-4440', N'0001', N'SIL', N'985.000', N'KG', N'0.250', N'H', N'0.000', N'999999999.000', N'EX', N'2', N'4', N'KG', N'0.000', N'')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'LEX', N'11C-8262', N'1', N'SIL', N'1018.000', N'KG', N'0.250', N'STD', N'0.000', N'99999999.000', N'EX', N'2', N'4', N'KG', N'18.000', N'')
INSERT INTO [dbo].[Resource_Mat] ([Plant], [Material_number], [Production_Version], [Resource_Code], [Std_Batch_Size], [UOM], [Std_Run_Time], [Time_UOM], [Batch_Min], [Batch_Max], [Stocking_Strategy], [Trans_Type], [Recipe_Status], [Short_Description], [Rounding_Value], [Distribution_Key]) VALUES (N'LEX', N'11C-8263', N'1', N'SIL', N'208.000', N'KG', N'0.100', N'STD', N'0.000', N'99999999.000', N'60', N'2', N'4', N'kg', N'8.000', N'')
Post #1596284
Posted Friday, July 25, 2014 9:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:06 PM
Points: 12,994, Visits: 12,411
Your insert statements don't work. Here is the results.

String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 2, Line 30
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 2, Line 31
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 2, Line 32
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 2, Line 33
String or binary data would be truncated.
The statement has been terminated.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1596290
Posted Friday, July 25, 2014 9:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:50 AM
Points: 327, Visits: 586
I used APEX to create the inserts.. and then find and replace to turn it into dummy data.. will just key in some data later this afternoon when I get a chance.
Post #1596296
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse