Sql Query Help
-
Hi Experts,
I got a new work from my RM. Requirement is,
Table Name : AcctMast
Records : 2
-----------------------------------------------
Table Structure :
NewTransCode
MSC001
MSC002
------------------------------------------------
Table Name : AcctDetails
RecordCount : 2500
------------------------------------------------
Table Structure :
TransTypeCode AcctNbr SubAcctNbr SubAcctCode DrCrInd NewCode
MSC001 4500 100 DIVD DR MS-CONM
MSC001 6000 500 NULL CR MS-CONM
MSC002 7000 250 DIVM DR MS-REVM
MSC002 6500 150 DIVM CR MS-REVM
MSC002 8000 700 NULL CR MS-REVM
-----------------------------------------------------------------
Expected output format is :
TableName : Custom_Conditions
------------------------------------------------------------------
Table Structure:
TransTypeCode IDNo ColumnName Operator Value LogicalOperator
MSC001 1 AcctNbr = 4500 AND
MSC001 1 SubAcctNbr = 100 AND
MSC001 1 SubAcctCode = DIVD AND
MSC001 1 DrCrInd = DR NULL
MSC001 2 AcctNbr = 6000 AND
MSC001 2 SubAcctNbr = 500 AND
MSC001 2 SubAcctCode = NULL AND
MSC001 2 DrCrInd = CR NULL
MSC002 1 AcctNbr = 7000 AND
MSC002 1 SubAcctNbr = 250 AND
MSC002 1 SubAcctCode = DIVM AND
MSC002 1 DrCrInd = DR NULL
MSC002 2 AcctNbr = 6500 AND
MSC002 2 SubAcctNbr = 150 AND
MSC002 2 SubAcctCode = DIVM AND
MSC002 2 DrCrInd = CR NULL
MSC002 3 AcctNbr = 8000 AND
MSC002 3 SubAcctNbr = 700 AND
MSC002 3 SubAcctCode = NULL AND
MSC002 3 DrCrInd = CR NULL
----------------------------------------------------------------------
Reason is , We are generating the where clause condition dynamically.
So he wants to change the format of 'AcctDetails' table rows to the above format.
I have written a query and it is giving expected result.
Code :
------------------------------------------------------------------
create procedure GenRegularConditions_pr
as
begin
begin
truncate table custom_conditions
end
select Seq = IDENTITY(int,1,1),NewTransTypeCode
into #TransCode
from ( select NewTransTypeCode
from AcctMast ) a
declare @TransMin int ,@TransMax int,@TransTransCode varchar(10)
select @TransMin = 1
select @TransMax = count(*) from #TransCode
while @TransMin <= @TransMax
Begin
select @TransTransCode = NewTransTypeCode
from #TransCode
where Seq = @TransMin
select Seq = IDENTITY(int,1,1),*
into #AcctDetails
from AcctDetails
where NewTransTypeCode = @TransTransCode
declare @min-2 int,@Max int
select @min-2 = 1
select @max-2 = count(*) from #AcctDetails
Begin
insert into Custom_Conditions (ID,Column_Name,Operator,Value,Condition_Concat,NewTransTypeCode)Select @min-2,'AcctNbr','=',AcctNbr,'AND',@TransTransCode
from #AcctDetails
where Seq = @min-2
insert into Custom_Conditions (ID,Column_Name,Operator,Value,Condition_Concat,NewTransTypeCode)
Select @min-2,'SubAcctNbr','=',SubAcctNbr,'AND',@TransTransCode
from #AcctDetails
where Seq = @min-2
insert into Custom_Conditions (ID,Column_Name,Operator,Value,Condition_Concat,NewTransTypeCode)
Select @min-2,'SubAcctCode','=',SubAcctCode,'AND',@TransTransCode
from #AcctDetails
where Seq = @min-2
insert into Custom_Conditions (ID,Column_Name,Operator,Value,Condition_Concat,NewTransTypeCode)
Select @min-2,'AcctEntry.DrCrInd','=',DrCrInd,null,@TransTransCodefrom #AcctDetails
where Seq = @min-2
End
select @TransMin = @TransMin +1
drop table #AcctDetails
End
-- End of Procedure
End
------------------------------------------------------------------------
Can any expert share your valuable comments and suggestions about my code ? If it can be done by alternate way,Please share your code with me.
Regards
Karthik
karthik
-
I don't have an alternate method as I'm still trying to wrap my head around why you'd want to do this. I can see where you might want to store user-defined filters, but it looks like you want to store every combination of column/value for a specified set of columns, with no user input. I'm also confused with what you are trying to do with the "AND" and "NULL" in the logical operator column. Are you really forcing the users to use AND, as well as requiring them to give a value for every one of the four columns? What about "OR" (and other operators), or people who don't care what the DrCrInd, SubAcctCode, etc. is when filtering? Additionally, don't SubAcctNbr and SubAcctCode have a 1 to 1 relationship?
Share with us what you want to accomplish and we can probably come up with some better feedback.
PS What's an RM?
-
User will not enter any values. 'AND' and 'NULL' values are constant values.
what i have mentioned in the Insert statement is the constant value .
I want to split the AcctDetails table format to the mentioned format.
Regards
Karthik
karthik
-
Hi Experts,
Please give me your valuable suggestion and Comments about my code.
I am very interesting to hear some suggestion and comments. Because i will change my coding style in future.
Regards
Karthik
karthik
-
any comments or suggestion ?
karthik
-
No comments or suggestions here, just a lot of questions that aren't being answered. I can't come up with a good reason to do what your code is doing, and while there may very well be one, you're not sharing. If I don't know why you would want to do that, I can't really suggest an alternate method that would work better for you.
-
1. As David mentioned, I am not sure why you would want to do this.
2. Avoid SELECT INTO in production code as it puts schema locks on tempdb.
3. Avoid loops. (ie Use set based code)
4. In SQL2005 this would be a lot easier using the ROW_NUMBER() function. In SQL2000 you will have to use a #temp table.
Something like the following should do the transformation. I am not convinced it is a good idea.
CREATE TABLE #temp
(
TransTypeCode char(6) COLLATE DATABASE_DEFAULT NOT NULL
,AcctNbr int NOT NULL
,SubAcctNbr int NOT NULL
,SubAcctCode char(4) COLLATE DATABASE_DEFAULT NULL
,DrCrInd char(2) COLLATE DATABASE_DEFAULT NOT NULL
,IDNo int IDENTITY NOT NULL
,PRIMARY KEY (TransTypeCode, IDNo)
)
INSERT INTO #temp(TransTypeCode, AcctNbr, SubAcctNbr, SubAcctCode, DrCrInd)
SELECT TransTypeCode, AcctNbr, SubAcctNbr, SubAcctCode, DrCrInd
FROM AcctDetails
ORDER BY TransTypeCode
OPTION (MAXDOP 1)
SELECT T.TransTypeCode
,T.IDNo - D.MinIDNo + 1 AS IDNo
,CASE N.N
WHEN 1 THEN 'AcctNbr'
WHEN 2 THEN 'SubAcctNbr'
WHEN 3 THEN 'SubAcctCode'
WHEN 4 THEN 'DrCrInd'
END AS ColumnName
,'=' AS Operator
,CASE N.N
WHEN 1 THEN CAST(AcctNbr AS varchar(10))
WHEN 2 THEN CAST(SubAcctNbr AS varchar(10))
WHEN 3 THEN CAST(SubAcctCode AS varchar(10))
WHEN 4 THEN CAST(DrCrInd AS varchar(10))
END AS Value
,CASE N.N
WHEN 4 THEN CAST(NULL AS char(3))
ELSE 'AND'
END AS LogicalOperator
FROM #temp T
JOIN (
SELECT T1.TransTypeCode, MIN(T1.IDNo) AS MinIDNo
FROM #temp T1
GROUP BY T1.TransTypeCode
) D
ON T.TransTypeCode = D.TransTypeCode
CROSS JOIN (
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4
) N (N)
-
Karthik,
I get it from your description... Can't work on it right now because I'm on the way to work, but I've got to ask...
Why doesn't "he" want these flattened out to form a full WHERE condition? Like this...
TransTypeCode IDNo Condition MSC001 1 AcctNbr = 4500 AND SubAcctNbr = 100 AND SubAcctCode = 'DIVD' AND DrCrInd = 'DR' MSC001 2 AcctNbr = 6000 AND SubAcctNbr = 500 AND SubAcctCode = NULL AND DrCrInd = 'CR' NULL ...etc...
It would be both much easier to create and much easier to use in the app...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
I spoke to my RM (Reporting Manager) and he did not accept it
he wants to populate the data as i mentioned format only.
Please help me to sort out this issue.
Regards
Karthik
karthik
-
Ken,
Your query is giving almost exact output. But only thing is ID number has to regenarate each and every transaction type code.
Regards
Karthik
karthik
-
Sorry Ken ! your code is satisfying my need. Thanks a lot.
I am unable to understand your logic.Can you please explain me the logic ?
Regards
Karthik
karthik
-
- The #temp table creates an unique ID for each row in AcctDetails. (Ordered by TransTypeCode)
- The derived table, D, has the minimum ID for each TransTypeCode. (This is needed for the calculation of IDNo)
- The CROSS JOIN to the derived table, N, produces 4 rows for each original row. (You could JOIN to a Number/Tally table instead - search this site if you do not understand this.)
- The CASE statements control what should be displayed based on N.N
I suggest you look up the syntax in BOL (Books Online)
-
ken,
Can you just explain me the N.N part alone ?
Regards
Karthik
karthik
-
Anybody explain me the N.N Part alone ?
karthik
-
Sure... the "N" part of the N(N) thing is just a way of assigning the derived table an alias of "N". The "(N)" part of that assigns the only column in the derived table an alias of "N". The following code example effectively demonstrates the use of this not-so-well-known form of table and column aliasing...
SELECT N.*FROM (
SELECT 1,2 UNION ALL
SELECT 2,3 UNION ALL
SELECT 3,4 UNION ALL
SELECT 4,5
) N (X,Y)
I've never found reference to this form of column aliasing in Books Online and think it may be one of those very nice "undocumented features"... but, just because I haven't been able to find reference to it, doesn't mean it's an "undocument feature"...
Anyone know where it may be found in BOL?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply