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. |
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' |
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' 2
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!
(18 Feb '13, 12:00)
Breck Carter
|
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. |