Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

Stored procedure to save column values in array

Hello,

MySQL = “SELECT * from contacts”

set oRST = connString.execute(mySQL)

do while oRST.EOF
For i = 1 to 60
      c(i) = oRST("contact" & i)
      if c(i) > 0 then
            callcount = callcount + 1
      end if
      ctid(i) = oRST("contact" & i & "typeID")
Next

oRSt.MoveNext
loop
set oRST = nothing

How can we do very similar functionality within stored procedure?

Thanks,
andrishelp
0
andrishelp
Asked:
andrishelp
  • 5
  • 4
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Just to make sure we are on the same page about:
MySQL = “SELECT * from contacts”

set oRST = connString.execute(mySQL)

do while oRST.EOF
   For i = 1 to 60
      c(i) = oRST("contact" & i)
      if c(i) > 0 then
            callcount = callcount + 1
      end if
      ctid(i) = oRST("contact" & i & "typeID")
   Next

   oRSt.MoveNext
loop
set oRST = nothing

Open in new window

- select all records from contacts table
- for each row, there are 60 columns like contactN (e.g., "contact1", "contact2", ..., "contact60") that contain integer values
- if integer value is > 0 then increment a counter "callcount"
- additionally return contactNtypeID?

If I got all of this correctly, you could do something like this with UNPIVOT:
;WITH contacts(ContactID, Contact1, Contact60, Contact1TypeID, Contact60TypeID) AS (
   SELECT 1, 2, 3, 'A', 'B'
   UNION SELECT 2, 1, 4, 'B', 'A'
)
SELECT *
FROM (SELECT ContactID, Contact1, Contact60 FROM contacts) v
UNPIVOT (ContactValue FOR ContactNo IN (Contact1, Contact60)) upvt_v
JOIN (SELECT ContactID, Contact1TypeID, Contact60TypeID FROM contacts) t
UNPIVOT (ContactType FOR ContactTypeNo IN (Contact1TypeID, Contact60TypeID)) upvt_t
   ON upvt_t.ContactID = upvt_v.ContactID 
   AND REPLACE(ContactTypeNo, 'TypeID', '') = ContactNo

Open in new window


As you see the actual implementation may get involved with 60 columns total, so you may have to combine this with some other techniques.

But now that the data is normalized, you can GROUP BY ContactID and do COUNT() or SUM() or any other aggregate you want on columns like ContactValue.

If you can confirm what exactly you need, then can help further. To test in your environment, use the query beginning with SELECT, i.e., you don't need the ;WITH ... () part.

Kevin
0
 
andrishelpAuthor Commented:
Kevin,

Thanks for your response. I think you did answer my question.

- select all records from contacts table
- for each row, there are 60 columns like contactN (e.g., "contact1", "contact2", ..., "contact60") that contain integer values
- if integer value is > 0 then increment a counter "callcount"
- for each row, there are 60 columns like contactNtypeID (e.g., "contact1typeID","contact2typeID",...,"contact60typeID") that also contains integer values

I have tried this query as you mentioned, but it's only display the "contact1" and "contact1typeID" values. What about other 59 columns value? Do I have to write 60 SELECT statements for that?

SELECT *
FROM (SELECT ContactID, Contact1, Contact60 FROM contacts) v
UNPIVOT (ContactValue FOR ContactNo IN (Contact1, Contact60)) upvt_v
JOIN (SELECT ContactID, Contact1TypeID, Contact60TypeID FROM contacts) t
UNPIVOT (ContactType FOR ContactTypeNo IN (Contact1TypeID, Contact60TypeID)) upvt_t
   ON upvt_t.ContactID = upvt_v.ContactID
   AND REPLACE(ContactTypeNo, 'TypeID', '') = ContactNo


0
 
Kevin CrossChief Technology OfficerCommented:
Not 59 more selects, but you would have to add the other 59 columns to the respective derived table(s) and unpivot clauses. What I gave you was meant as an example with contacts 1 and 60...did only contact one show? But anyway, it is the con to unpivot that can be coded around with dynamic SQL. Let me see if I can mock something more complete up. Can you provide the CREATE DDL for the contacts table please?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
andrishelpAuthor Commented:
Here you go...
CREATE TABLE [dbo].[tempSalescalldata](
	[SalesCallID] [int] NULL,
	[contact1] [int] NULL,
	[contact1typeID] [int] NULL,
	[contact2] [int] NULL,
	[contact2typeID] [int] NULL,
	[contact3] [int] NULL,
	[contact3typeID] [int] NULL,
	[contact4] [int] NULL,
	[contact4typeID] [int] NULL,
	[contact5] [int] NULL,
	[contact5typeID] [int] NULL,
	[contact6] [int] NULL,
	[contact6typeID] [int] NULL,
	[contact7] [int] NULL,
	[contact7typeID] [int] NULL,
	[contact8] [int] NULL,
	[contact8typeID] [int] NULL,
	[contact9] [int] NULL,
	[contact9typeID] [int] NULL,
	[contact10] [int] NULL,
	[contact10typeID] [int] NULL,
	[contact11] [int] NULL,
	[contact11typeID] [int] NULL,
	[contact12] [int] NULL,
	[contact12typeID] [int] NULL,
	[contact13] [int] NULL,
	[contact13typeID] [int] NULL,
	[contact14] [int] NULL,
	[contact14typeID] [int] NULL,
	[contact15] [int] NULL,
	[contact15typeID] [int] NULL,
	[contact16] [int] NULL,
	[contact16typeID] [int] NULL,
	[contact17] [int] NULL,
	[contact17typeID] [int] NULL,
	[contact18] [int] NULL,
	[contact18typeID] [int] NULL,
	[contact19] [int] NULL,
	[contact19typeID] [int] NULL,
	[contact20] [int] NULL,
	[contact20typeID] [int] NULL,
	[contact21] [int] NULL,
	[contact21typeID] [int] NULL,
	[contact22] [int] NULL,
	[contact22typeID] [int] NULL,
	[contact23] [int] NULL,
	[contact23typeID] [int] NULL,
	[contact24] [int] NULL,
	[contact24typeID] [int] NULL,
	[contact25] [int] NULL,
	[contact25typeID] [int] NULL,
	[contact26] [int] NULL,
	[contact26typeID] [int] NULL,
	[contact27] [int] NULL,
	[contact27typeID] [int] NULL,
	[contact28] [int] NULL,
	[contact28typeID] [int] NULL,
	[contact29] [int] NULL,
	[contact29typeID] [int] NULL,
	[contact30] [int] NULL,
	[contact30typeID] [int] NULL,
	[contact31] [int] NULL,
	[contact31typeID] [int] NULL,
	[contact32] [int] NULL,
	[contact32typeID] [int] NULL,
	[contact33] [int] NULL,
	[contact33typeID] [int] NULL,
	[contact34] [int] NULL,
	[contact34typeID] [int] NULL,
	[contact35] [int] NULL,
	[contact35typeID] [int] NULL,
	[contact36] [int] NULL,
	[contact36typeID] [int] NULL,
	[contact37] [int] NULL,
	[contact37typeID] [int] NULL,
	[contact38] [int] NULL,
	[contact38typeID] [int] NULL,
	[contact39] [int] NULL,
	[contact39typeID] [int] NULL,
	[contact40] [int] NULL,
	[contact40typeID] [int] NULL,
	[contact41] [int] NULL,
	[contact41typeID] [int] NULL,
	[contact42] [int] NULL,
	[contact42typeID] [int] NULL,
	[contact43] [int] NULL,
	[contact43typeID] [int] NULL,
	[contact44] [int] NULL,
	[contact44typeID] [int] NULL,
	[contact45] [int] NULL,
	[contact45typeID] [int] NULL,
	[contact46] [int] NULL,
	[contact46typeID] [int] NULL,
	[contact47] [int] NULL,
	[contact47typeID] [int] NULL,
	[contact48] [int] NULL,
	[contact48typeID] [int] NULL,
	[contact49] [int] NULL,
	[contact49typeID] [int] NULL,
	[contact50] [int] NULL,
	[contact50typeID] [int] NULL,
	[contact51] [int] NULL,
	[contact51typeID] [int] NULL,
	[contact52] [int] NULL,
	[contact52typeID] [int] NULL,
	[contact53] [int] NULL,
	[contact53typeID] [int] NULL,
	[contact54] [int] NULL,
	[contact54typeID] [int] NULL,
	[contact55] [int] NULL,
	[contact55typeID] [int] NULL,
	[contact56] [int] NULL,
	[contact56typeID] [int] NULL,
	[contact57] [int] NULL,
	[contact57typeID] [int] NULL,
	[contact58] [int] NULL,
	[contact58typeID] [int] NULL,
	[contact59] [int] NULL,
	[contact59typeID] [int] NULL,
	[contact60] [int] NULL,
	[contact60typeID] [int] NULL
) ON [PRIMARY]

Open in new window

0
 
andrishelpAuthor Commented:
Yes, It shows only "contact1" value not "contact60" when I run that query.
0
 
andrishelpAuthor Commented:
Actually, Contact60 did not have any values for each row, that's why it did not display.
0
 
andrishelpAuthor Commented:

Now I know how that select query works. But I have one question, what is the purpose of writing this "AND REPLACE(ContactTypeNo, 'TypeID', '') = ContactNo" to that select query. since it's giving me the same result without it too.

0
 
Kevin CrossChief Technology OfficerCommented:
Yes, NULL values will be excluded, which is why it fits your need here as it will automatically do some filtering for your callcount.
DECLARE @con_cols VARCHAR(1000), @typ_cols VARCHAR(1000), @sql VARCHAR(4000);

/* set column names to variables using XML concatenation trick */
SET @con_cols = STUFF((SELECT ','+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tempSalescalldata'
AND COLUMN_NAME LIKE 'contact%[0-9]'
ORDER BY ORDINAL_POSITION
FOR XML PATH('')), 1, 1, '');

SET @typ_cols = STUFF((SELECT ','+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tempSalescalldata'
AND COLUMN_NAME LIKE 'contact%[0-9]typeID'
ORDER BY ORDINAL_POSITION
FOR XML PATH('')), 1, 1, '');

/* create dynamic SQL string using column names collected above */
SET @sql = 'SELECT upvt_v.*, upvt_t.ContactType
FROM (SELECT [SalesCallID], ' + @con_cols + ' FROM [dbo].[tempSalescalldata]) v
UNPIVOT (ContactValue FOR ContactNo IN (' + @con_cols + ')) upvt_v
JOIN (SELECT [SalesCallID], ' + @typ_cols + ' FROM [dbo].[tempSalescalldata]) t
UNPIVOT (ContactType FOR ContactTypeNo IN (' + @typ_cols + ')) upvt_t
   ON upvt_t.[SalesCallID] = upvt_v.[SalesCallID]
   AND upvt_t.ContactTypeNo = upvt_v.ContactNo + ''typeID''';

PRINT (@sql); /* shows SQL, can execute manually at command line */
EXEC (@sql); /* executes the dynamic SQL string */

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
If contact60 column was not NULL what you would have seen is that you would have had multiple rows that need to be correlated. Basically the correlation is 'contact1' = 'contact1typeid' (if you remove the 'typeid' piece -- alternatively, you can add 'typeid' to 'contact1' which is what the new script does).
0
 
Anthony PerkinsCommented:
And of course this would be a good time to ask:  What were you thinking when you designed that table that way?  Or is this something you inherited and cannot change?  If you think converting the data using PIVOT is tough then you have not seen anything yet.

If you can are able this table is screaming:  "Please, normalize me!" :)
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now