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.

asked 16 Feb '13, 11:34

roadtrain64's gravatar image

roadtrain64
1366710
accept rate: 0%

edited 19 Feb '13, 10:52

Mark%20Culp's gravatar image

Mark Culp
23.2k9132272


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'
permanent link

answered 17 Feb '13, 14:23

Breck%20Carter's gravatar image

Breck Carter
26.2k430600866
accept rate: 20%

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'

permanent link

answered 18 Feb '13, 11:17

roadtrain64's gravatar image

roadtrain64
1366710
accept rate: 0%

edited 18 Feb '13, 11:55

Breck%20Carter's gravatar image

Breck Carter
26.2k430600866

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.

permanent link

answered 19 Feb '13, 12:09

roadtrain64's gravatar image

roadtrain64
1366710
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×12
×2

question asked: 16 Feb '13, 11:34

question was seen: 931 times

last updated: 19 Feb '13, 12:09