Remi, Noel, sushila, Frank, etc....
-
For some reason, this did not post. I'll try again.
I found something that was simple enough to deal with the issue of a cursor, but also would not compromise releasing any data. I have changed some of the names of fields, but others are common enough that I simply left them as is...
Basically, the #Final table 'can' have a large amount of records dealing with a lot of money. This particular cursor shows international codes and percentages, but I was able to find one that did not have the added difficulty of prorating the percentages - that gets real messy...
The #Intl1 table should be fairly self explanatory.
Fortunately, the various codes are simply concatenated into one column, so that should make using this as an example much easier!
Thanks for your help. Hopefully your solution(s) are something we can use in other areas of our coding as well!!
-- International Codes and Percentages...
SELECT C.Country_Name, i.id_intl, i.special_id, i.country_code, i.pct, i.intl_marker
INTO #intl1
FROM tb_intl i
LEFT JOIN Countries C ON( i.country_code = C.NIH_State_Country_Code)
CREATE CLUSTERED INDEX [CIX_id_intl_award] ON [#intl1]( [id_intl], [special_id] )
----------------------------------------------------------------------------------
-- This needs to be a cursor as a special_id can have many International Codes...
DECLARE int_csr CURSOR FAST_FORWARD FOR
SELECT i.id_intl
FROM #intl1 i
INNER JOIN #final f ON( i.special_id = f.special_id)
GROUP BY i.id_intl
ORDER BY i.id_intl
OPEN int_csr
FETCH int_csr INTO @id_intl
WHILE @@fetch_status = 0
BEGIN
UPDATE #final SET
intl_codes1 = ISNULL( f.intl_codes1, '') + ' ' +
CONVERT( varchar, ISNULL( i.country_code, '')) +
' ' + ISNULL( i.country_name, '') + ' ' +
CONVERT( varchar, ISNULL( i.pct, '')) + ' ' +
CONVERT( varchar, ISNULL( i.intl_marker, ''))
FROM #final f
INNER JOIN #intl1 i ON( f.special_id = i.special_id
AND i.id_intl = @id_intl)
FETCH int_csr INTO @id_intl
END
CLOSE int_csr
DEALLOCATE int_csr
I will check as often as I can on your replies! If you need more information, I will provide what I can.
Farrell
I wasn't born stupid - I had to study.
-
Can't convert it without sample data and expected results to make sure we're ok.
-
Crap! I knew I should have done that... That will take me a bit of time, (and that is where I have to make sure I do not release any real data...).
Sorry for such a novice mistake. I will get back to this...
I wasn't born stupid - I had to study.
-
there's a script to generate the insert statements :
-
This is very down and dirty, but it gets the point across. I realized special_id could be used instead of id_intl, but there are other Business Rules, (you will note some problems in comments for the INSERTs) that I chose not to include.
Again, thanks in advance...
DECLARE @id_intl integer
CREATE TABLE #Final( special_id integer,
intl_codes1 varchar(255))
INSERT INTO #Final VALUES( 1, '')
INSERT INTO #Final VALUES( 2, '')
INSERT INTO #Final VALUES( 3, '')
INSERT INTO #Final VALUES( 4, '')
INSERT INTO #Final VALUES( 5, '')
INSERT INTO #Final VALUES( 6, '')
INSERT INTO #Final VALUES( 7, '')
INSERT INTO #Final VALUES( 8, '')
INSERT INTO #Final VALUES( 9, '')
INSERT INTO #Final VALUES( 10, '')
-- International Codes and Percentages...
-- I cannot change the design of these tables...
CREATE TABLE #intl1( id_intl integer IDENTITY(1,1),
Country_Name varchar(80),
special_id integer,
country_code integer,
pct integer,
intl_marker integer)
INSERT INTO #intl1 VALUES( 'Nigeria', 1, 495, 20, 4)
INSERT INTO #intl1 VALUES( 'USA', 1, 395, 50, 3)
INSERT INTO #intl1 VALUES( 'Mexico', 1, 295, 35, 3)
-- this will exceed 100% - this record is an example of one of our
-- proration difficulties; but not for this excercise, just and example...
INSERT INTO #intl1 VALUES( 'Nigeria', 2, 495, 80, 5)
-- this will only have one intl_codes1 (80% is acceptable...)
INSERT INTO #intl1 VALUES( 'USA', 3, 395, 20, 4)
INSERT INTO #intl1 VALUES( 'Nigeria', 3, 495, 20, 4)
INSERT INTO #intl1 VALUES( 'Nigeria', 3, 495, 50, 3)
-- another issue not dealt with in this exercise, (choosing one of
-- multiple records...)
INSERT INTO #intl1 VALUES( 'Mexico', 4, 295, 50, 4)
INSERT INTO #intl1 VALUES( 'USA', 4, 395, 50, 6)
-- this will amount to required 100%
-- special_id #5 will have no intl_codes1
INSERT INTO #intl1 VALUES( 'Puerto Rico', 6, 195, 20, 4)
INSERT INTO #intl1 VALUES( 'Mexico', 6, 295, 20, 5)
INSERT INTO #intl1 VALUES( 'El Salvador', 6, 595, 20, 6)
INSERT INTO #intl1 VALUES( 'Venezuela', 6, 695, 20, 3)
INSERT INTO #intl1 VALUES( 'USA', 6, 395, 10, 4)
INSERT INTO #intl1 VALUES( 'Chile', 6, 795, 20, 2)
-- again, over 100%
INSERT INTO #intl1 VALUES( 'Nigeria', 7, 495, 20, 2)
INSERT INTO #intl1 VALUES( 'Tanzania', 7, 895, 80, 3)
-- this will amount to required 100%
INSERT INTO #intl1 VALUES( 'Canada', 8, 995, 25, 1)
INSERT INTO #intl1 VALUES( 'USA', 8, 395, 25, 2)
INSERT INTO #intl1 VALUES( 'Russia', 8, 95, 25, 3)
INSERT INTO #intl1 VALUES( 'Antartica', 8, 550, 25, 4)
-- this will amount to required 100%
INSERT INTO #intl1 VALUES( 'Nigeria', 9, 495, 100, 4)
-- this will only have one intl_codes1
INSERT INTO #intl1 VALUES( 'China', 10, 495, 20, 6)
INSERT INTO #intl1 VALUES( 'Unknown', 10, 1, 20, 6)
INSERT INTO #intl1 VALUES( 'Tiawan', 10, 25, 20, 5)
INSERT INTO #intl1 VALUES( 'Japan', 10, 26, 20, 5)
INSERT INTO #intl1 VALUES( 'Tunisia', 10, 27, 20, 4)
CREATE CLUSTERED INDEX [CIX_id_intl_award] ON [#intl1]( [id_intl], [special_id] )
----------------------------------------------------------------------------------
-- This needs to be a cursor as a special_id can have many International Codes...
DECLARE int_csr CURSOR FAST_FORWARD FOR
SELECT i.id_intl
FROM #intl1 i
INNER JOIN #final f ON( i.special_id = f.special_id)
GROUP BY i.id_intl
ORDER BY i.id_intl
OPEN int_csr
FETCH int_csr INTO @id_intl
WHILE @@fetch_status = 0
BEGIN
UPDATE #final SET
intl_codes1 = ISNULL( f.intl_codes1, '') + ' ' +
CONVERT( varchar, ISNULL( i.country_code, '')) + ' ' +
ISNULL( i.country_name, '') + ' ' +
CONVERT( varchar, ISNULL( i.pct, '')) + ' ' +
CONVERT( varchar, ISNULL( i.intl_marker, ''))
FROM #final f
INNER JOIN #intl1 i ON( f.special_id = i.special_id
AND i.id_intl = @id_intl)
FETCH int_csr INTO @id_intl
END
CLOSE int_csr
DEALLOCATE int_csr
SELECT special_id, intl_codes1 FROM #Final
DROP TABLE #Final
DROP TABLE #intl1
/* EXPECTED OUTPUT
special_id intl_codes1
------------------------------ -----------------------------------------------------------------------------------------------------------------------------
1 495 Nigeria 20 4 395 USA 50 3 295 Mexico 35 3
2 495 Nigeria 80 5
3 395 USA 20 4 495 Nigeria 20 4 495 Nigeria 50 3
4 295 Mexico 50 4 395 USA 50 6
5
6 195 Puerto Rico 20 4 295 Mexico 20 5 595 El Salvador 20 6 695 Venezuela 20 3 395 USA 10 4 795 Chile 20 2
7 495 Nigeria 20 2 895 Tanzania 80 3
8 995 Canada 25 1 395 USA 25 2 95 Russia 25 3 550 Antartica 25 4
9 495 Nigeria 100 4
10 495 China 20 6 1 Unknown 20 6 25 Tiawan 20 5 26 Japan 20 5 27 Tunisia 20 4
*/
I wasn't born stupid - I had to study.
-
Farrell, I've shown this trick so many times I'm surprised you didn't pick it up yet >>>
IF Object_id('ListTableColumns') > 0
DROP FUNCTION ListTableColumns
GO
CREATE FUNCTION dbo.ListTableColumns (@TableID as int)
RETURNS varchar(8000)
AS
BEGIN
Declare @Items as varchar(8000)
SET @Items = ''
SELECT
@Items = @Items + C.Name + ', '
FROMdbo.SysColumns C
WHEREC.id = @TableID
AND OBJECTPROPERTY(@TableID, 'IsTable') = 1
ORDER BYC.Name
SET @Items = LEFT(@Items, ABS(DATALENGTH(@Items) - 2))
RETURN @Items
END
GO
Select dbo.ListTableColumns(Object_id('SysObjects'))
--base_schema_ver, cache, category, crdate, deltrig, ftcatid, id, indexdel, info, instrig, name, parent_obj, refdate, replinfo, schema_ver, seltrig, stats_schema_ver, status, sysstat, type, uid, updtrig, userstat, version, xtype
DROP FUNCTION ListTableColumns
Just convert that fonction to do the concatenation work and you'll be home free WITHOUT temp tables or loops.
-
Thanks. I knew I should have taken the time to include the other processes.
There are a lot of business rules and I cannot really go into them all; in other words, this is really not just concatenating records... I will see how I can incorporate this approach as I use functions in other areas and really like their use.
But, don't you still end up with the same amount of calls back and fourth to capture all of the records for each row? That is one part of this that I do not fully understand...
I wasn't born stupid - I had to study.
-
It's less efficiant than a single select, but it still takes less ressources than updating a temp table for every occurance of every row and then reselecting the temp table.
-
That's a worthwhile trade off. Thanks.
I wasn't born stupid - I had to study.
-
Yup this code really deals more with reporting problems than basic sql operations.
-
Sorry its not more exciting...
I wasn't born stupid - I had to study.
-
Governement is not meant to be entertaining
. -
LOL. Excellent response.
I wasn't born stupid - I had to study.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply