Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Faking Multidimensional Arrays in T-SQL made easy! Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, November 4, 2008 10:12 AM
 SSC Rookie Group: General Forum Members Last Login: Monday, October 3, 2016 4:57 PM Points: 39, Visits: 195
 Very poor. Exhibits a fundamental lack of understanding of multi-dimensional arrays. This shows why academic journals have articles reviewed before they're published.
Post #596721
 Posted Tuesday, November 4, 2008 10:40 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, August 15, 2014 12:06 PM Points: 3, Visits: 47
 Okay, corrected the code for SQL 2008 (at least this is what worked for me)./* create the basic table structures needed */CREATE TABLE [X_DIMENSION] ( [LOC] [int] NULL , [X_ELEMENT] [varchar] (2000) NULL )CREATE TABLE [Y_DIMENSION] ( [LOC] [int] NULL, [Y_ELEMENT] [varchar] (2000) NULL )CREATE TABLE [X_Y_INDEX] ( X_LOC [int] NULL, Y_LOC [int] NULL )/* Now we create some data to place into the tables, indexed */INSERT X_Y_INDEX (X_LOC, Y_LOC)SELECT 5,7INSERT X_DIMENSION (LOC,X_ELEMENT)SELECT 5,'DATA IN ELEMENT 5 'INSERT Y_DIMENSION (LOC,Y_ELEMENT)SELECT 7,'REMAINING DATA FOR ELEMENT 5,7'DECLARE @X INT, @Y INTSET @X = 5 -- or whatever method of loading you wantSET @Y = 7SELECT A.X_ELEMENT + B.Y_ELEMENT FROM X_DIMENSION A, Y_DIMENSION B WHERE A.LOC = @X AND B.LOC = @Y/* The Query returns the concatenated value! *//* DATA IN ELEMENT 5, REMAINING DATA IN ELEMENT 7*/
Post #596750
 Posted Tuesday, November 4, 2008 10:52 AM
 Say Hey Kid Group: General Forum Members Last Login: Friday, July 11, 2014 1:42 PM Points: 696, Visits: 43
 Unfortunately you have wasted your time because the code is utterly worthless. Sorry, but there is no other way to describe it. Tim Wilkinson"If it doesn't work in practice, you're using the wrong theory"- Immanuel Kant
Post #596755
 Posted Tuesday, November 4, 2008 12:51 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 3:07 PM Points: 42,078, Visits: 39,467
Post #596835
 Posted Tuesday, November 4, 2008 12:52 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 3:07 PM Points: 42,078, Visits: 39,467
 stax68 (11/4/2008)Jeff Moden (11/4/2008)Nah... not true... in SQL Server 2005 and up, there's basically no limit to passing arrays...http://www.sqlservercentral.com/articles/T-SQL/63003/The author just didn't go far enough in showing how to use the EAV tables he made in his examples as a 3 dimensional array. Most people blow a couple of gears for anything above a 3 dimensional array so 3 dimensions is a good place to stop. People that need more than that will be able to figure it out quite easily.Randal, nice job at "standing" up an array in "memory". It's a simple concept that far to many folks have forgotten and some have never learned. It's good to see it in print.Hello Jeff M? Any explanation of the above forthcoming?Heh... you gotta be more patient... I actually work for a living, ya know? :P --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." Helpful Links:How to post code problemsHow to post performance problems
Post #596836
 Posted Tuesday, November 4, 2008 12:54 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 3:07 PM Points: 42,078, Visits: 39,467
 stax68 (11/4/2008)Jeff M:What isn't true? I am talking about the limitations on passing table variables, not string representations of arrays, so the link provided seems beside the point.Agreed... you can't pass tables. Just strings that can quickly be shredded into arrays. ;) --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." Helpful Links:How to post code problemsHow to post performance problems
Post #596837
 Posted Tuesday, November 4, 2008 12:59 PM
 Forum Newbie Group: General Forum Members Last Login: Thursday, May 24, 2012 6:33 PM Points: 6, Visits: 24
 why not just do the following?DECLARE @AR TABLE ( ROW int NULL, COL int NULL, VALUE varchar(2000) NULL )-- add some dummy dataINSERT @AR (ROW, COL, VALUE) SELECT 5,7,'DATA IN ELEMENT 5,7'-- retrieve itSELECT VALUE FROM @AR WHERE ROW = 5 AND COL = 7it's much easier...seng
Post #596845
 Posted Tuesday, November 4, 2008 1:11 PM
 Say Hey Kid Group: General Forum Members Last Login: Friday, July 11, 2014 1:42 PM Points: 696, Visits: 43
 Jeff Moden (11/4/2008)No, I don't think the article was about 3D arrays... go back and read what I posted in my first post. ;).I read it the first time. You seemed to say (and therefore like it or not did say) it was about 3D arrays, but the author didn't go far enough in his explanation.But, with a little imagination, you can create a "3d" array... or at least a 2 dimensional representation of one.NS, Sherlock? [wink] Tim Wilkinson"If it doesn't work in practice, you're using the wrong theory"- Immanuel Kant
Post #596862
 Posted Tuesday, November 4, 2008 1:13 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 3:07 PM Points: 42,078, Visits: 39,467
 Keep it clean, slick. ;) And, don't blame me if you don't understand what I'm saying. --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." Helpful Links:How to post code problemsHow to post performance problems
Post #596865
 Posted Tuesday, November 4, 2008 1:23 PM
 Forum Newbie Group: General Forum Members Last Login: Monday, June 1, 2009 7:27 AM Points: 3, Visits: 26
 WoW! This seems to have brought out a ton of thinkers!The article is to provide a look back into history as to how arrays were first created - be they 2d or 3d or more.There are going to be some follow-on articles about how to use this in practical, everyday (for some of us) applications, and possibly even to go on into rocket science.A couple of comments, here might be appropriate though -1) You do NOT have to pass tables to procedures - especially if the data is already stored on a table on disk. You don't even have to pass pointers. Everything is already there.2) Captain Kirk once said, in his final battle with Khan - "He is evidencing classic two dimensional thinking" - we all evidence that quite often. 3) Just because it is new and improved does not mean it is necessarily better.4) Show me how passing the word 'YES' (3 bytes) can be dome more efficiently using XML? Take it even further, lets pass a single bit - 0 or 1 to another process using XML and have it take up as few bytes.This is the concept in the real engineering world - not the concepts that are taken in the lofty world of education.Randal BurkeI wish there was a "head scratchin' " emoticon here...
Post #596871

 Permissions