Tag: list

ORA-00904: “WM_CONCAT”: invalid identifier

I’ve never run into wm_concat before when using Oracle, but I saw it in a statement similar to this earlier today:

SELECT WM_CONCAT(id)
FROM chris.item
WHERE item_type LIKE '%floppy%';

Of course, it didn’t work:

ORA-00904: "WM_CONCAT": invalid identifier

It looks like WM_CONCAT isn’t a supported command (Error when using wm_concat function), and possibly the code I was debugging stopped working when the database was upgraded to 12c.

WM_CONCAT is intended to return the results as a comma-separated list. A strange idea, but it this particular case it almost made sense. A supported replacement that aggregates results into a single string is LISTAGG.

SELECT LISTAGG(id, ',')
WITHIN GROUP (ORDER BY id)
FROM chris.item
WHERE item_type LIKE '%floppy%'
GROUP BY 1;

In this case, I did not want the results separated into groups, so I used GROUP BY 1 to put all results in the same group.

There are a number of other string aggregation techniques for Oracle, if you ever find yourself with such a need.

Converting lines to a list in ColdFusion

I’m so used to dealing with comma-delimited lists in ColdFusion that I would sometimes take a data file that had one item per line and replace the newline characters with commas.

It’s easy to use the carriage return [chr(13)] and line feed [chr(10)] characters as list delimiters, though, and remove the intermediary step. Here’s a quick example:

<cfsavecontent variable="data">
this
is
a
list
with
one
word
per
line
</cfsavecontent>

<cfoutput>
    <ol>
        <cfloop list="#data#" delimiters="#chr(13)##chr(10)#" index="line">
            <li>#line#</li>
        </cfloop>
    </ol>
</cfoutput>

Which produces the following:

  1. this
  2. is
  3. a
  4. list
  5. with
  6. one
  7. word
  8. per
  9. line

(I can’t believe I didn’t think of this until today!)

Javascript Array Sort & Random Ordering

Recently a colleague and I were looking at some Javascript code that randomizes a list of elements. The code did this by creating an array of list-item elements, and then passing a comparison function that returns a random result to the array.sort() method. Unfortunately, the random order was anything but random: after reloading the page 50 times, the distribution was skewed heavily towards the original array ordering.

In case you don’t feel like reading my entire exploration of this topic, I’ll give you the short version:
Don’t use array.sort() to randomize arrays! There are methods of randomizing arrays that produce better (i.e. more random) results, and that are (probably) faster.
Continue reading Javascript Array Sort & Random Ordering

Using a list or array as a PowerShell script input parameter

One of my colleagues created a PowerShell script that we use to migrate SharePoint 2010 sites from the SharePoint 2007 interface (UI 3) to the SharePoint 2010 interface (UI 4). The script works rather well for updating one or two sites at a time:

Set-UserInterface2010.ps1 -url "https://my.sharepoint.site/path/"

Today I received a request to update a list of 32 sites. After updating one, I thought–this is going to be tedious. We can improve this.

First, I updated the parameter to accept an array of strings instead of a single string.

Before
param([string]$url = $(throw "Please specify a Site URL to convert to the SP2010 look and feel"))

After
param([string[]]$url = $(throw "Please specify a Site URL to convert to the SP2010 look and feel"))

Next, I wrapped the call to the main function in a ForEach loop:
ForEach ( $siteurl in $url ) {
...
# Throw in a line break to separate output
Write-Host ("`n`n")
}

The script can still take a single site as input (the string is treated as a string array with a single element), but now I can also pass it a list:

Set-UserInterface2010.ps1 -url "https://my.sharepoint.site/path1/","https://my.sharepoint.site/path2/","https://my.sharepoint.site/path3/"

Here’s hoping that taking a couple minutes to update the script and running it once saved me more time than running it 32 times!

Importing Data into a SharePoint List

SharePoint Joel’s recent post, Managing Large Lists in SharePoint for Users and Site Admins got me interested in testing the 5000 list view item limit.

It also gave me a good opportunity to put fakenamegenerator.com to the test. The site quickly provided me with a list of 6000 random names.

Now, how to get them into a SharePoint list? Continue reading Importing Data into a SharePoint List