February 21, 2011 at 10:42 am
I have three tables to link. ORDER DETAL/MATERIAL/MATRIX
I can link ORDER DETAIL and MATRIX by the "Customer ID" field.
I can link ORDER DETAIL and MATERIAL by the "Material ID" field.
The MATERIAL table has a "Class" column that I need to link to the MATRIX table. The problem I have is each possible class is setup as its own column in the MATRIX table.
Any possible ways to link this or should I just create a separate record for each class?
(I can make any changes to the MATRIX table I need but the other tables can not be re-structured)
February 21, 2011 at 12:02 pm
Hi dcase,
Without seeing the DDL with some sample data - I'm not sure what I'm posting will help you.
If this concept doesn't solve your problem, please post DDL and some sample rows.
SELECT Col1, Coln...
FROM TableA A
JOIN TableB B
ON A.x = B.x
OR A.y = B.y
February 21, 2011 at 12:33 pm
CREATE TABLE [dbo].[SO_Header](
[Sales_Order] [dbo].[Key10] NOT NULL,
[Customer] [dbo].[Key10] NOT NULL,
[Ship_To] [dbo].[KeyNum] NULL,
[Contact] [dbo].[KeyNum] NULL,
[Sales_Rep] [dbo].[Key6] NULL,
[Order_Taken_By] [dbo].[Key6] NULL,
[Ship_Via] [dbo].[T_UCode] NULL,
[Tax_Code] [dbo].[T_UCode] NULL,
[Terms] [dbo].[T_UCode] NULL,
[Sales_Tax_Amt] [money] NOT NULL CONSTRAINT [DF_SO_Header_Sales_Tax_Amt] DEFAULT (0),
[Sales_Tax_Rate] [float] NOT NULL CONSTRAINT [DF_SO_Header_Sales_Tax_Rate] DEFAULT (0),
[Order_Date] [datetime] NOT NULL,
[Promised_Date] [datetime] NULL,
[Customer_PO] [dbo].[T_20] NULL,
[Status] [dbo].[T_8] NOT NULL,
[Total_Price] [money] NOT NULL CONSTRAINT [DF_SO_Header_Total_Price] DEFAULT (0),
[Currency_Conv_Rate] [float] NOT NULL CONSTRAINT [DF_SO_Header_Currency_Conv_Rate] DEFAULT (1),
[Trade_Currency] [dbo].[KeyNum] NULL,
[Fixed_Rate] [bit] NOT NULL CONSTRAINT [DF_SO_Header_Fixed_Rate] DEFAULT (1),
[Trade_Date] [datetime] NULL,
[Note_Text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Comment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Last_Updated] [datetime] NOT NULL CONSTRAINT [DF_SO_Header_Last_Updated] DEFAULT (getdate()),
[User_Values] [int] NULL,
[Source] [dbo].[T_20] NULL,
[Prepaid_Tax_Amount] [money] NULL DEFAULT ((0)),
CONSTRAINT [PK_SO_Header] PRIMARY KEY NONCLUSTERED
==========================================================================================
CREATE TABLE [dbo].[SO_Detail](
[SO_DetailKey] [dbo].[KeyNum] IDENTITY(1,1) NOT NULL,
[SO_Detail] [dbo].[KeyNum] NULL,
[Sales_Order] [dbo].[Key10] NOT NULL,
[SO_Line] [dbo].[T_6] NOT NULL,
[PO] [dbo].[Key8] NULL,
[Line] [dbo].[T_6] NULL,
[Material] [dbo].[T_30] NULL,
[Ship_To] [int] NULL,
[Drop_Ship] [bit] NOT NULL CONSTRAINT [DF_SO_Detail_Drop_Ship] DEFAULT (0),
[Quote] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Job] [dbo].[Key10] NULL,
[Status] [dbo].[T_9] NOT NULL CONSTRAINT [DF_SO_Detail_Status] DEFAULT ('Open'),
[Make_Buy] [dbo].[T_1] NOT NULL CONSTRAINT [DF_SO_Detail_Make_Buy] DEFAULT ('M'),
[Unit_Price] [float] NOT NULL,
[Discount_Pct] [float] NOT NULL CONSTRAINT [DF_SO_Detail_Discount_Pct] DEFAULT (0),
[Price_UofM] [dbo].[UofM] NOT NULL CONSTRAINT [DF_SO_Detail_Price_UofM] DEFAULT ('ea'),
[Total_Price] [money] NOT NULL,
[Deferred_Qty] [float] NOT NULL CONSTRAINT [DF_SO_Detail_Deferred_Qty] DEFAULT (0),
[Prepaid_Amt] [money] NOT NULL CONSTRAINT [DF_SO_Detail_Prepaid_Amt] DEFAULT (0),
[Unit_Cost] [float] NOT NULL,
[Order_Qty] [float] NOT NULL CONSTRAINT [DF_SO_Detail_Order_Qty] DEFAULT (0),
[Stock_UofM] [dbo].[UofM] NOT NULL,
[Backorder_Qty] [float] NOT NULL CONSTRAINT [DF_SO_Detail_Backorder_Qty] DEFAULT (0),
[Picked_Qty] [float] NOT NULL CONSTRAINT [DF_SO_Detail_Picked_Qty] DEFAULT (0),
[Shipped_Qty] [float] NOT NULL,
[Returned_Qty] [float] NOT NULL,
[Certs_Required] [bit] NOT NULL CONSTRAINT [DF_SO_Detail_Certs_Required] DEFAULT (0),
[Taxable] [bit] NOT NULL CONSTRAINT [DF_SO_Detail_Taxable] DEFAULT (0),
[Commissionable] [bit] NOT NULL CONSTRAINT [DF_SO_Detail_Commissionable] DEFAULT (0),
[Commission_Pct] [float] NOT NULL CONSTRAINT [DF_SO_Detail_Commission_Pct] DEFAULT (0),
[Sales_Code] [dbo].[T_UCode] NULL,
[Note_Text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Promised_Date] [datetime] NULL,
[Last_Updated] [datetime] NOT NULL CONSTRAINT [DF_SO_Detail_Last_Updated] DEFAULT (getdate()),
[Description] [dbo].[T_30] NULL,
[Price_Unit_Conv] [float] NULL CONSTRAINT [DF_SODetail_PriceUofM] DEFAULT (1),
[Rev] [dbo].[T_8] NULL,
[Tax_Code] [dbo].[T_15] NULL,
[Ext_Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Cost_UofM] [dbo].[T_4] NULL,
[Cost_Unit_Conv] [float] NULL,
[Res_Type] [smallint] NULL,
[Res_ID] [dbo].[T_50] NULL,
[Res_Qty] [float] NULL,
[Partial_Res] [bit] NULL,
[Prepaid_Trade_Amt] [money] NULL DEFAULT ((0)),
[ObjectID] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_SO_Detail] PRIMARY KEY NONCLUSTERED
============================================================================================
CREATE TABLE [dbo].[Material](
[Material] [dbo].[Key30] NOT NULL,
[Primary_Vendor] [dbo].[Key10] NULL,
[User_Values] [dbo].[KeyNum] NULL,
[Shape] [dbo].[Key10] NULL,
[Location_ID] [dbo].[Key10] NOT NULL,
[Sales_Code] [dbo].[T_15] NULL,
[Description] [dbo].[T_30] NULL,
[Type] [dbo].[T_1] NOT NULL,
[Status] [dbo].[T_8] NOT NULL,
[Pick_Buy_Indicator] [dbo].[T_1] NOT NULL,
[Class] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Rev] [dbo].[T_8] NULL,
[Stocked_UofM] [dbo].[UofM] NOT NULL,
[Purchase_UofM] [dbo].[UofM] NOT NULL,
[Cost_UofM] [dbo].[UofM] NOT NULL,
[Price_UofM] [dbo].[UofM] NOT NULL,
[Selling_Price] [float] NULL CONSTRAINT [DF_Material_Selling_Price] DEFAULT (0),
[Standard_Cost] [float] NOT NULL CONSTRAINT [DF_Material_Standard_Cost] DEFAULT (0),
[Average_Cost] [float] NULL CONSTRAINT [DF_Material_Average_Cost] DEFAULT (0),
[Last_Cost] [float] NULL CONSTRAINT [DF_Material_Last_Cost] DEFAULT (0),
[On_Order_Qty] [float] NULL CONSTRAINT [DF_Material_On_Order_Qty] DEFAULT (0),
[Order_Point] [float] NULL CONSTRAINT [DF_Material_Order_Point] DEFAULT (0),
[Reorder_Qty] [float] NOT NULL CONSTRAINT [DF_Material_Reorder_Qty] DEFAULT (0),
[Lead_Days] [smallint] NOT NULL CONSTRAINT [DF_Material_Lead_Days] DEFAULT (0),
[UofM_Conv_Factor] [float] NOT NULL CONSTRAINT [DF_Material_UofM_Conv_Factor] DEFAULT (0),
[Lot_Trace] [bit] NOT NULL CONSTRAINT [DF_Material_Lot_Trace] DEFAULT (0),
[Rd_Whole_Unit] [bit] NOT NULL CONSTRAINT [DF_Material_Rd_Whole_Unit] DEFAULT (0),
[Ext_Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Make_Buy] [dbo].[T_1] NOT NULL,
[Vendor_Reference] [dbo].[T_30] NULL,
[Drawing] [dbo].[T_16] NULL,
[Use_Price_Breaks] [bit] NOT NULL CONSTRAINT [DF_Material_Use_Price_Break] DEFAULT (0),
[Note_Text] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Last_Updated] [datetime] NOT NULL CONSTRAINT [DF_Material_Last_Updated] DEFAULT (getdate()),
[GL_Account] [dbo].[T_100] NULL,
[Price_Unit_Conv] [float] NULL CONSTRAINT [DF_Material_PriceUofM] DEFAULT (1),
[Holder_ID] [dbo].[T_50] NULL,
[Job] [dbo].[T_10] NULL,
[Tax_Code] [dbo].[T_15] NULL,
[Taxable] [bit] NULL,
[IS_Length] [float] NULL,
[IS_Width] [float] NULL,
[IS_Weight_Factor] [float] NULL,
[IS_Thickness] [float] NULL,
[Stock_Item] [dbo].[T_50] NULL,
[Affects_Schedule] [bit] NULL,
[ObjectID] [uniqueidentifier] NOT NULL,
[Shape_OID] [uniqueidentifier] NULL,
[Tooling] [bit] NULL,
[IsSerialized] [bit] NOT NULL DEFAULT ((0)),
[MaxUsage] [int] NULL DEFAULT ((0)),
[ShelfLife] [float] NULL DEFAULT ((0)),
[ShelfLifeUofM] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Material] PRIMARY KEY NONCLUSTERED
====================================================================================
CREATE TABLE [dbo].[PriceMatrix](
[Cust ID] [dbo].[Key30] NULL,
[Customer] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NTBE] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NTOE] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NTOEGR] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SJDI] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SJSS] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[T150] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TISO] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ANSI] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Plate_Flange] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Backup_Flange] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[T43000] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[T63000] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SE304] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SE316] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BW304_6] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BW3048plus] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BW316_6] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BW3168plus] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Ball_Valve] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Casting] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CORE] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ACBELLED] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ACGROOVED] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Price_Code] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[D_O] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NOTES] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [IX_PriceMatrix] UNIQUE NONCLUSTERED
February 21, 2011 at 12:48 pm
I'm trying to list all "lines" from PO_Detail for a specific PO. Once I find the material class for the specific line I want to look up the customer and class on "PriceMatrix" and return the value. The way "PriceMatrix" is setup I can't do much besides link on "Customer ID".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply