Skip to main content
B
Balance
Inspiring
July 9, 2008
Answered

Processing a list

  • July 9, 2008
  • 5 replies
  • 455 views
I have a column, let's call it "BGT_CDS", in a database that stores values as such:

row 1 -- "RING","AFFAIRS"
row 2 -- "MEMBER","TREASURER"
row 3 -- "VP, FINANCE"

I need to write these rows to a different table, but when I use the following code:

<cfloop from="1" to="#listLen(valueList(getData.BGT_CDS))#" index="i">
INSERT INTO comm_ACGI (BGT_CD)
VALUES
('#replace(listGetAt(valueList(getData.BGT_CDS),i),"""","","ALL")#')
</cfloop>

It comes back with this error:

Invalid list index 2.
In function ListGetAt(list, index [, delimiters]), the value of index, 2, is not a valid as the first argument (this list has 1 elements). Valid indexes are in the range 1 through the number of elements in the list.

The problem is that it thinks "VP, FINANCE" is two values when in fact it's one (the quotes are used to seperate each element). Can someone please shed some light?

TIA
    This topic has been closed for replies.
    Correct answer Balance
    Dan - Thanks for your tip. I ended up using your logic to fix my issues. With a few tweaks...

    <cfoutput query="getData">

    <cfset somelist = replace(POSITION_CDS, '","', '"', "all") />
    <cfset somelist = replace(somelist, '"', "|", "ALL") />

    <cfloop from="1" to="#listLen(someList,"|")#" index="i">
    INSERT STATEMENT
    </cfloop>

    </cfoutput>

    5 replies

    B
    BalanceAuthorCorrect answer
    Inspiring
    July 10, 2008
    Dan - Thanks for your tip. I ended up using your logic to fix my issues. With a few tweaks...

    <cfoutput query="getData">

    <cfset somelist = replace(POSITION_CDS, '","', '"', "all") />
    <cfset somelist = replace(somelist, '"', "|", "ALL") />

    <cfloop from="1" to="#listLen(someList,"|")#" index="i">
    INSERT STATEMENT
    </cfloop>

    </cfoutput>
    D
    Dan_Bracuk
    Inspiring
    July 10, 2008
    You say these lists are in a db table. That being the case, select them with a cfquery. Let's call that q1.

    Then you loop through q1.
    inside this loop you,
    replace all the ","s with |
    leaving you with
    row 1 -- "RING|AFFAIRS"
    row 2 -- "MEMBER|TREASURER"
    row 3 -- "VP, FINANCE"
    Then take away the first and last double quotes. Use RemoveChars in case your data has some.
    leaving you with
    row 1 -- RING|AFFAIRS
    row 2 -- MEMBER|TREASURER
    row 3 -- VP, FINANCE

    Each row is now a | delimited list. You can loop through it to do your inserts. Note that you have to specify the delimiter every time you refer to the list and that this loop is nested inside the first one.
    D
    Dan_Bracuk
    Inspiring
    July 9, 2008
    Your first delimiter is a double quote, comma, double quote. Start by replacing those three characters by something less likely to be in your data. I like pipes myself.

    Then replace the 1st and last double quote with an empty string and your good to go.
    B
    BalanceAuthor
    Inspiring
    July 10, 2008
    Sorry, but I'm not sure I fully understand. I updated the code to this:

    <cfloop from="1" to="#listLen(valueList(getData.CMT_POSITION_CDS))#" index="i">
    <cfscript>
    CMT_POSITION_CDS = replace(listGetAt(valueList(getData.CMT_POSITION_CDS),i),"""","|","ALL");
    </cfscript>

    CMT_POSITION_CDS = #CMT_POSITION_CDS#
    <br /><br />
    </cfloop>

    CMT_POSITION_CDS = |RING|

    CMT_POSITION_CDS = |AFFAIRS|

    CMT_POSITION_CDS = |MEMBER|

    CMT_POSITION_CDS = |TREASURER|

    CMT_POSITION_CDS = |VP

    CMT_POSITION_CDS = FiNANCE|


    It needs to be:

    CMT_POSITION_CDS = |RING|

    CMT_POSITION_CDS = |AFFAIRS|

    CMT_POSITION_CDS = |MEMBER|

    CMT_POSITION_CDS = |TREASURER|

    CMT_POSITION_CDS = |VP, FINANCE|
    A
    Anonymous
    July 9, 2008
    Oh - you should probably replace the VALUES with the extra delimeter too!
    A
    Anonymous
    July 9, 2008
    If you change your loop to something like:

    <cfloop from="1" to="#listLen(valueList(getData.BGT_CDS), "-")#" index="i">

    Then it will look for a dash to delimit the various values in your list, not the default comma.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices