parsing a comma-separatinf list

[AK]

Once upon a time there was a table:

CREATE TABLE VENDOR(VENDOR_I D INT, NAME VARCHAR(50), STATE CHAR(2))@

in a while the developers realized that a vendor may be present in
xseveral states, so the structure was changed to

CREATE TABLE VENDOR(VENDOR_I D INT, NAME VARCHAR(50), STATE_LIST
VARCHAR(150))@

STATE)LIST could contain a list of comma-separated values like this:
CA,WA,OR
Not the best approach, but out here in the field things like this
happen all the time. Everybody were absolutely happy until somebody
required a query like this:
SELECT * FROM VENDOR WHERE STATE IN ('IL', 'WI','MI')
SELECT COUNT(*), STATE FROM VENDOR GROUP BY STATE
and so on
Given a dayabase structure developed by a skilled professional, that
would be a piece of cake, but not this time.

Moving to a better structure

CREATE TABLE VENDOR(VENDOR_I D INT, NAME VARCHAR(50))@
CREATE TABLE VENDOR_IN_STATE (VENDOR_ID INT, STATE CHAR(2))@

nobody wanted to change the front end application.
As a workaround, I created a view and INSTEAD OF triggers that worked
like this:

INSERT INTO VENDOR_IN_STATE _VIEW VALUES(1, 'ABC INC.', 'AK,AL,IL')@

SELECT * FROM VENDOR
VENDOR_ID NAME
----------- --------------------------------------------------
1 ABC INC.
1 record(s) selected.

SELECT * FROM VENDOR_IN_STATE
VENDOR_ID STATE
----------- -----
1 AK
1 AL
1 IL
3 record(s) selected.

I used recursion to define both the view and the triggers:

CREATE FUNCTION PARSE_LIST(C_LI ST VARCHAR(100))
RETURNS TABLE(TOKEN VARCHAR(100))
SPECIFIC PARSE_LIST
RETURN
WITH PARSED_LIST(STE P, TOKEN, REMAINDER,LEFTM OST_COMMA)
AS(
VALUES(0, '',C_LIST,3)
UNION ALL
SELECT STEP+1 AS STEP,
CASE WHEN LEFTMOST_COMMA> 0 THEN
CAST(LEFT(REMAI NDER,LEFTMOST_C OMMA-1) AS CHAR(2))
ELSE
REMAINDER
END AS TOKEN,
CASE WHEN LEFTMOST_COMMA> 0 THEN
CAST(SUBSTR(REM AINDER,LEFTMOST _COMMA+1) AS VARCHAR(100))
ELSE
NULL
END AS REMAINDER,
LOCATE(',',SUBS TR(REMAINDER,LO CATE(',',REMAIN DER)+1)) AS
LEFTMOST_COMMA
FROM PARSED_LIST
WHERE REMAINDER IS NOT NULL
)
SELECT TOKEN FROM PARSED_LIST WHERE STEP>0

I wrapped the recursive query in UDF so that it could be reused:

It is very easy to invoke this table UDF:

SELECT * FROM TABLE(PARSE_LIS T('AK,AR,IL,OH' )) AS PARSE_LIST
TOKEN
------
AK
AR
IL

OH

I user the UDF in an INSTEAD OF trigger

CREATE TRIGGER VENDOR_IN_STATE _I
INSTEAD OF INSERT
ON VENDOR_IN_STATE _VIEW
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE VENDOR_FOUND INT;
SET VENDOR_FOUND=(S ELECT COUNT(*) FROM VENDOR WHERE NAME=N.NAME);
IF NOT(VENDOR_FOUN D>0)
THEN
INSERT INTO VENDOR(VENDOR_I D, NAME) VALUES (N.VENDOR_ID, N.NAME);
END IF;
INSERT INTO VENDOR_IN_STATE (VENDOR_ID, STATE)
SELECT N.VENDOR_ID, PARSE_LIST.TOKE N FROM
TABLE(PARSE_LIS T(N.STATE_LIST) ) AS PARSE_LIST;
END @

I would really appreciate any feedback.
Are there any simpler approaches?

[AK]

jo*******@north face.edu (--CELKO--) wrote in message news:<a2******* *************** ****@posting.go ogle.com>...

What do you think? <<

Neat but too proprietary for my taste. Remember, I am the guy who
pushes pure SQL-92 code.

I would agree. However entry-level folks weren't able to understand
neither my original solution nor yours. This is why an
easy-to-understand one was necessary.

I'd say OLAP functions are very flexible and powerful. Maybe they
deserve getting into the standard?

[Serge Rielau]

To the best of my knowledge, ROW_NUMBER() is in the standard. AFAIK it
is alse supported by Oracle. So proprietary: Nope.
is the '+' in the standard for string types? Just curious....

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Christian Maslen]

> To the best of my knowledge, ROW_NUMBER() is in the standard. AFAIK it

is alse supported by Oracle. So proprietary: Nope.
is the '+' in the standard for string types? Just curious....

Check out:

http://www.jtc1sc32.org/sc32/jtc1sc3...f?OpenDocument

The section on Window functions has all the relational OLAP stuff. I
noticed recursion and common table expressions were also there, all of
which work in Oracle and SQL Server (from Yukon on).

Christian.

[AK]

I would say that right now my top priority is to leave behind me code
that is easy to understand and maintain. This is where OLAP functions
are very handy. I love them.
I am less, if at all, concerned about portability and ANSI standards.

I think:
'abc'+'cde' will work on MS SQL Server , bot not on DB2 and not on
ORACLE
ROW_NUMBER() will work on DB@ and ORACLE but not on MS SQL Server

[--CELKO--]

>> is the '+' in the standard for string types? <<

No, concatenation is an infixed || token. We copied it from PL/I.
What was funny is that Phil Shaw, the ANSI X3H2 representative from
IBM at the time did not like this symbol because he had worked with
PL/I and hated it.

[Christian Maslen]

> I would say that right now my top priority is to leave behind me code

that is easy to understand and maintain. This is where OLAP functions
are very handy. I love them.
I agree. The bonus with OLAP functions is they perform well over very
large result sets. The ANSI 92 equivalent won't.
I am less, if at all, concerned about portability and ANSI standards.

I'm more concerned with portability - but only between the major
vendors. ANSI appears to be a good way to get consistency in the
syntax across the vendors (there's nothing worse than seeing everyone
support a feature differently) - To me, the consistent nature of the
OLAP syntax is the best thing since vendors decided to support the
same outer join syntax.