Check comma seperated values for a Row

[Former Member]

CREATE TABLE #tmp_tbl
          ( --line_num              integer DEFAULT AUTOINCREMENT,
          qualifier_state_id    integer
          , event_id              integer
          , group_id              integer
          , state_number          integer
          , qualifier_state_name  varchar(255)
          ) 
INSERT INTO #tmp_tbl (qualifier_state_id,event_id,group_id,state_number,qualifier_state_name)
VALUES ('62,58',1680,3,2,'BLUE,MEDIUM')

Now when someone try to insert into this table values such as below then IT should give error

INSERT INTO #tmp_tbl (qualifier_state_id,event_id,group_id,state_number,qualifier_state_name)
VALUES ('58,62',1680,3,2,'MEDIUM,BLUE')

so basically I need to check '62,58' is same as '58,62' what function i need to use for that? I tried LIST function in where clause but that did not work.

[Former Member]

Just need to change data type of qualifier_state_id to varchar(256) instead of integer.

[graeme_perrow]

Please use the commenting feature to leave comments on other answers rather than posting new answers. Thanks.

[Breck_Carter]

Yes, sir! ...and I will be sure to post those comments as replies to the correct items 🙂

[Breck_Carter]

CREATE TABLE t
          ( qualifier_state_id    varchar(255) unique
          , event_id              integer
          , group_id              integer
          , state_number          integer
          , qualifier_state_name  varchar(255)
          );

CREATE PROCEDURE f 
          ( @qualifier_state_id    varchar(255)
          , @event_id              integer
          , @group_id              integer
          , @state_number          integer
          , @qualifier_state_name  varchar(255) )
BEGIN
INSERT t 
SELECT @qualifier_state_id, 
       @event_id,  
       @group_id,  
       @state_number,  
       @qualifier_state_name
 WHERE ( SELECT LIST ( row_value,
                       ','
                       ORDER BY row_value ) 
           FROM sa_split_list ( @qualifier_state_id, ',' ) ) 
NOT IN ( SELECT ( SELECT LIST ( row_value,
                                ','
                                ORDER BY row_value ) 
                    FROM sa_split_list ( t.qualifier_state_id, ',' ) )
           FROM t );
COMMIT;
END;

CALL f ('62,58',1680,3,2,'BLUE,MEDIUM');
CALL f ('58,62',1680,3,2,'MEDIUM,BLUE');
CALL f ('11,22',1234,5,6,'LARGE,RED');
CALL f ('22,11',1234,5,6,'LARGE,RED');

SELECT * FROM t ORDER BY qualifier_state_id;

qualifier_state_id,event_id,group_id,state_number,qualifier_state_name
'11,22',1234,5,6,'LARGE,RED'
'62,58',1680,3,2,'BLUE,MEDIUM'

[Former Member]

Thanks a lot for the hints. I made the change in original sp where it was not sorting values to insert into temp table. So now no need to do anything but thanks a lot for all the help.

[Former Member]

Thanks but i think thats too much complicated. I can do this, Here is my user table,

CREATE TABLE dba.t_retail_qualifier_state_list (qualifier_seq_id integer, event_id integer, qualifier_state_ids varchar(25), qualifier_state_names  varchar(255))
INSERT INTO  dba.t_retail_qualifier_state_list VALUES(1,1680, '55,62','MEDIUM,BLUE') 
SELECT * FROM dba.t_retail_qualifier_state_list

AND from SP input parameters I am creating a temp table where i have below data,

CREATE TABLE #tmp_tbl
          ( --line_num              integer DEFAULT AUTOINCREMENT,
          qualifier_state_id    varchar(30)
          , event_id              integer
          , group_id              integer
          , state_number          integer
          , qualifier_state_name  varchar(255)
          ) 
INSERT INTO #tmp_tbl (qualifier_state_id,event_id,group_id,state_number,qualifier_state_name)
VALUES ('62,55',1680,3,2,'BLUE,MEDIUM')
SELECT * FROM #tmp_tbl

Now I need to get qualifier_seq_id from dba.t_retail_qualifier_state_list table when the value already exists. So right now in dba.t_retail_qualifier_state_list , we have '55,62'. And my temp table has '62,55' which is same then get qualifier_seq_id from dba.t_retail_qualifier_state_list. So now only need to join 2 tables and get the value if exists.

SELECT a.qualifier_seq_id
         --INTO @lQualifierSeqId
         FROM dba.t_retail_qualifier_state_list  a
            , #tmp_tbl                           b
        WHERE a.event_id              = b.event_id
          AND LIST(a.qualifier_state_ids)   = LIST(b..qualifier_state_id) --But List doesnt work in where clause
          --AND a.qualifier_state_names = 'BLUE,MEDIUM'

[Breck_Carter]

Much simpler... the "doesn't work" part is just a minor obstacle 🙂

LIST is an aggregate function, not a scalar function like sa_split_list.

LIST takes multiple values and combines them into a comma-separated list. The qualifier_state_id is ALREADY a comma-separated list so your REALLY need to break it apart (like sa_split_list) before combining it in a different order.

Perhaps, once you understand how LIST works, you will come up with a simpler solution that actually works... we all look forward to seeing it!