SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cross Join Help


Cross Join Help

Author
Message
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)

Group: General Forum Members
Points: 834 Visits: 775
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?
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4420 Visits: 3670
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’! **
ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2062 Visits: 10373
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
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)

Group: General Forum Members
Points: 834 Visits: 775
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.
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)

Group: General Forum Members
Points: 834 Visits: 775
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
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)

Group: General Forum Members
Points: 834 Visits: 775
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26311 Visits: 17553
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 Modens 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)
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)

Group: General Forum Members
Points: 834 Visits: 775
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'')
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26311 Visits: 17553
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 Modens 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)
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)SSC Eights! (834 reputation)

Group: General Forum Members
Points: 834 Visits: 775
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search