July 11, 2010 at 7:18 am
I have a master file that I have inherited which has duplicate Rows for the same Account Code due to Typo's and Case's and different way of saying the same thing. This is because the Warehouse draws from 14 countries where some of them have not been brought to standard.
e.g.: 678900 Salaries & Wages
678900 Sal & Wag
678900 Salries and Wages
726353 Temp Wages
726353 Casual Workers Wages
I want to reference (Join) to this table and return the Description of the 1st occurring row pertaining to a specific code. i.e. All Transactions with the AccountCode = 678900 must have a column with 'Salaries & Wages'. Currently I am getting duplicate records because there are 3 or more posibilities any transaction could reference.
I hope my dilemma is clear, and I would REALLY appreciate any advice.
Thanks
Mac
July 11, 2010 at 8:33 am
Mac we probably need a little more info...does the table for Account Code have an identity/primary key, in addition to the two columns you showed us? are their foreign keys to that primary key? what is the name of the table itself? not [Account Code] with a space, right?
It would be best if you posted the CREATE TABLE definition for clarity. show us the PK values for all three account codes of "678900"
I'm assuming that yes, there IS a PK column.
i started writing some specific code, but it's dumb to guess at your schema structure...
give us some more information, maybe a couple of tables from sp_fkeys [Account Code Table] so we can give some concrete examples.
Lowell
July 11, 2010 at 8:47 am
Also, for "first" to you mean lowest numerical value? SQL Server has no concept of the ordering of rows, so you need to be clear about which one of the duplicates you would choose.
July 11, 2010 at 9:10 am
Lowell, this is bad architecture. There is a primary key column but the reference FK is to the actually account Number value, which is duplicated (the account number stats the same but there are three or more records with slightly differing descriptions. The FK in the transaction table references the Account_Code_AK (alternate key) column (which, as I have said, is not unique any more). I want to take the first occurring row (for that account number [Account_Code_AK] and only see that in my sub-select (JOIN).
July 11, 2010 at 9:12 am
Any one - so long as it is the same row referenced!
July 11, 2010 at 9:16 am
Mac:
CREATE TABLE definition?
the actual [Account_Code_AK] values for one of the items that is duplicated so we can see?
names of the tables that reference that alternate key?
Lowell
July 11, 2010 at 9:25 am
USE [BSUA_MastMeta]
GO
/****** Object: Table [MastMeta].[DIM_AccountStructure] Script Date: 07/11/2010 17:24:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [MastMeta].[DIM_AccountStructure](
[Account_SK] [int] NOT NULL,
[Account_BK] [varchar](50) NOT NULL,
[Account] [varchar](100) NULL,
[DrCr_IND] [char](2) NULL,
[Operator] [char](1) NULL,
[SeqNo] [int] NULL,
[Formula_BK] [varchar](1000) NULL,
[Formula_SK] [varchar](1000) NULL,
[Properties] [varchar](100) NULL,
[ParentAccount_BK] [varchar](50) NULL,
[ParentAccount_SK] [int] NULL,
[AccountType_SK] [int] NULL,
[AccountType_BK] [char](2) NULL,
[AccountType] [varchar](75) NULL,
[Statement_SK] [int] NULL,
[Statement_BK] [char](2) NULL,
[Statement] [varchar](75) NULL,
[Profitability] [varchar](50) NULL,
[AllocationRule] [varchar](50) NULL,
[RuleDescription] [varchar](400) NULL,
[Operation_Code_SK] [int] NULL,
[Operation_Code_BK] [varchar](5) NULL,
[Operation_BSUCode_SK] [varchar](2) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
July 11, 2010 at 9:33 am
This is the whole gist of the issue: (Note- I'm trying a TOP 1 WHERE...) but obviously that isn't kosher!!!
DROP TABLE #tempGlMastDesc
SELECT DISTINCT STRM.*
--INTO #tempGlMastDesc
FROM
(SELECT
SUBSTRING(STRU.[Account_BK],9,6) AS Account_Code
--, DrCr_IND
, STRU.AccountType_BK
--, STRU.AccountType
--, STRU.Statement_BK
--, STRU.[Statement]
----, STRU.[Account_BK]
, REPLACE(SUBSTRING(STRU.[Account],18,3),' ','')+SUBSTRING(STRU.[Account],21,29) AS [Account_Description]
FROM BSUA_MastMeta.MastMeta.DIM_AccountStructure AS STRU
WHERE LEN(STRU.Account_BK) = 14 AND STRU.AccountType_BK <> 0
UNION ALL
SELECT SUBSTRING(STRF.[Account_BK],9,6) AS Account_Code
--, DrCr_IND
, STRF.AccountType_BK
--, STRF.AccountType
--, STRF.Statement_BK
--, STRF.[Statement]
--, STRF.[Account_BK]
, REPLACE(SUBSTRING(STRF.[Account],18,3),' ','')+SUBSTRING(STRF.[Account],21,29) AS [Account_Description]
FROM BSUA_MastMeta.MastMeta.DIM_AccountStructure AS STRF
WHERE LEN(STRF.Account_BK) = 14 AND STRF.AccountType_BK <> 0
) AS STRM
ORDER BY STRM.AccountType_BK,Account_Code
SELECT DISTINCT STRM.*, DESCR.Account_Description FROM
(SELECT
SUBSTRING(STRU.[Account_BK],9,6) AS Account_Code
, DrCr_IND
, STRU.AccountType_BK
, STRU.AccountType
, STRU.Statement_BK
, STRU.[Statement]
--, STRU.[Account_BK]
--, REPLACE(SUBSTRING(STRU.[Account],18,3),' ','')+SUBSTRING(STRU.[Account],21,29) AS [Account_Description]
--INTO #tempGlMast
FROM BSUA_MastMeta.MastMeta.DIM_AccountStructure AS STRU
WHERE LEN(STRU.Account_BK) = 14 AND STRU.AccountType_BK <> 0
UNION ALL
SELECT SUBSTRING(STRF.[Account_BK],9,6) AS Account_Code
, DrCr_IND
, STRF.AccountType_BK
, STRF.AccountType
, STRF.Statement_BK
, STRF.[Statement]
--, STRF.[Account_BK]
--, REPLACE(SUBSTRING(STRF.[Account],18,3),' ','')+SUBSTRING(STRF.[Account],21,29) AS [Account_Description]
FROM BSUA_MastMeta.MastMeta.DIM_AccountStructure AS STRF
WHERE LEN(STRF.Account_BK) = 14 AND STRF.AccountType_BK <> 0
) AS STRM
RIGHT OUTER JOIN (SELECT TOP 1 * FROM #tempGlMastDesc
AS REF WHERE REF.Account_Code = STRM.Account_Code)
AS DESCR
ON DESCR.Account_Code = STRM.Account_Code
ORDER BY STRM.AccountType_BK,STRM.Account_Code
DROP TABLE BSUA_VRSL_ODS.FINTXN.ODS_FinGLTxn
--GO
SELECT
[MAPOPER].[Code] AS [TM1_Mapping_Country_Oper]
,SUBSTRING(GlCode,9,6) AS [TM1PARMVAL]
,GlYear
,GlPeriod
,Company
,ACTOP.Operation_AK
,MASTGLFC.[AccountType_BK] AS ParentAccount
,MASTGLFC.AccountTypeAS MainParentAccountDescription
,[GlCode]
,SUBSTRING(MASTGLFC.Account,19,40)AS AccountDescription
, SUBSTRING([GlCode],1,3) AS [Site_Code]
, SUBSTRING([GlCode],6,3) AS [Cost_Centre]
, SUBSTRING([GlCode],9,4) AS [AC_Number]
, SUBSTRING([GlCode],13,2) AS [Sub_Account]
,[Source]
,SRC.GLSourceDesc
,[Origin]
,ORIG.GLOriginDesc
,OriginZoomKey
,MASTGLFC.DrCr_INDAS DR_CR
,([EntryValue]) AS Value
,[Comment]
,[Reference]
,[JnlDate]
,[Journal]
,MASTGLFC.[Statement_BK]AS StatementKey
,MASTGLFC.[Statement]AS StatementDescription
,PRD.CalendarPeriodMonth_Desc
,PRD.CalendarPeriodMonth_Val
,[Line]
,[EntryGroup]
INTO BSUA_VRSL_ODS.FINTXN.ODS_FinGLTxn
FROM SYSPRO.EncoreCompanyA.dbo.GenTransaction AS FINTXN
LEFT OUTER JOIN #tempGlMast AS MASTGLFC
ON FINTXN.[GlCode] = MASTGLFC.Account_BK
INNER JOIN [BSUA_MastMeta].ETL_Meta.ETL_Control_PeriodMonth AS PRD
ON FINTXN.GlPeriod = PRD.FinancialPeriodMonth_Val
LEFT OUTER JOIN BSUA_MastMeta.ETL_Meta.ETL_Control_Operation AS ACTOP
ON ACTOP.Oper_Is_Active = 1
INNER JOIN BSUA_MastData_Staging.dbo.Map_SYSPRO_TM1_OPERCODES AS MAPOPER
ON ACTOP.Operation_AK = RTRIM(MAPOPER.[Alt Description])
INNER JOIN BSUA_MastMeta.BRE_Meta.Mast_GLSource AS SRC
ON SRC.GLSource_AK = FINTXN.Source
INNER JOIN BSUA_MastMeta.BRE_Meta.Mast_GLOrigin AS ORIG
ON ORIG.GLOrigin_AK = FINTXN.Origin
--WHERE MASTGLFC.[AccountType_BK] = 60
WHERE FINTXN.GlYear >= YEAR(GETDATE())-2
AND (MASTGLFC.AccountType_BK IN ('60','65')
OR (Source = 'WP' AND Origin = 'COSB'))
GROUP BY ACTOP.Operation_AK
,[MAPOPER].[Code]
,[Company]
,[GlCode]
,MASTGLFC.[AccountType_BK]
,MASTGLFC.AccountType
,MASTGLFC.Account
,[Statement_BK]
,[Statement]
,MASTGLFC.DrCr_IND
, FINTXN.GlCode
,[GlYear]
,[GlPeriod]
,PRD.CalendarPeriodMonth_Desc
,PRD.CalendarPeriodMonth_Val
,[Line]
,[Source]
,SRC.GLSourceDesc
,[Journal]
,[JnlDate]
,[Reference]
,[EntryValue]
,[Comment]
,[Origin]
,ORIG.GLOriginDesc
,OriginZoomKey
,[EntryGroup]
ORDER BY FINTXN.GlYear DESC, FINTXN.GlPeriod DESC, FINTXN.GlCode,FINTXN.Source, FINTXN.Origin
DROP TABLE #tempGlMast
There are two STRUCTURE tables, one for FIXED COSTs and the other for the rest. I need the transaction from both. But reference one version of the truth for description (SUBSTRING 9,6) of the GlCode! Somewhere along the line, different countries are using differencs COST CENTREs, the logic is actually built into the GLCode.
July 11, 2010 at 10:53 am
Unfortunately, it's still not clear how to identify "the first" row you want to keep.
When building the #tempGlMastDesc table you end up with duplicates but without any sorting criteria that could be applied.
A few more issues to notice:
Why do you use a UNION ALL query with two identical subselects???
All you end up with is a duplicate set of data that will cleaned up with your "SELECT DISTINCT STRM.* " statement.
I probably would populate the temp table holding the description the following way:
;WITH cte AS
(
SELECT
SUBSTRING(STRU.[Account_BK],9,6) AS Account_Code
, STRU.AccountType_BK
, REPLACE(SUBSTRING(STRU.[Account],18,3),' ','')+SUBSTRING(STRU.[Account],21,29) AS [Account_Description]
, ROW_NUMBER() OVER(PARTITION BY SUBSTRING(STRU.[Account_BK],9,6),AccountType_BK ORDER BY the_column_I_dont_know) AS ROW
FROM BSUA_MastMeta.MastMeta.DIM_AccountStructure AS STRU
WHERE LEN(STRU.Account_BK) = 14 AND STRU.AccountType_BK <> 0
)
SELECT Account_Code,AccountType_BK,Account_Description
INTO #tempGlMastDesc
FROM cte
WHERE ROW=1
It would be a lot easier if you would provide some fake sample data to show us what you'refaced with together with your expected result. As far as I can see the query seems to be more complicated than it needs to be...
I'm not sure if AccountType_BK needs to be included or not...
As a second step I would get all data that I need except the description column and build the result set. And, finally, join the two subresults.
July 12, 2010 at 4:41 am
The SK is obviously UNIQUE to the Row in the AccountStructure Table. Problem is - AccountStructure_FixedCosts contains all the FixedCosts Codes, Some but not ALL exist in the Account Structure Table. I have to do a UNION, then filter out Duplicate Account_BK's until I have one version of the truth where I reference ONE unique row with one version of the description.
The UNION ALL binds the two together, the 2nd UNION all was my attempt to bring in the UNIQUE Master reference.
Thinking about just using a cursor and building an UNIQUE reference Table Variable and join it to the Transactions so the description comes through. Everything else about these duplicate records is IDENTICAL except the spelling of the Description.
July 12, 2010 at 5:18 am
Thanks. Imu! Your logic has solved my problem. Just took it a step further.
Cheers
Dude!!:-D
July 12, 2010 at 11:26 am
BitLeftOfCentre (7/12/2010)
Thanks. Imu! Your logic has solved my problem. Just took it a step further.Cheers
Dude!!:-D
Would you mind shring your solution? We might be able to improve it even further.
As a side note: someone who's calling me "dude" has a high chance to get on my personal blacklist...
This is a professional forum.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply