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. |