Deleting Lists

Try this query. It is not pretty but gets the trick done. What it does is looks for any keyword with the text "list_no" and try to join to that. It is looking for lists be used by other lists or extractions.

--Drop table #tmp

Declare @Curr_List_no int

Select @Curr_List_no = (Select Min(List_no) from t_list --where created_by = <Your login id here>

)

Create table #tmp (

      Parent_Created_by varchar(30),

      Parent_Last_Updated_dt datetime,

      [Parent_List_no] varchar(100),

      [Parent_List_Desc] Varchar(100),

      Child_Created_by varchar(20),

      Child_Last_Updated_dt datetime,

      [Child_List_Desc] varchar(100),

      [Child_List_no] int,

      Extraction_No varchar(100),

      Extraction_Name varchar(100)

)

--Select @Curr_List_no

While @Curr_List_no is not null

      Begin

--Select @Curr_List_no as 'After Begin'

Insert #Tmp (

      Parent_Created_by,

      Parent_Last_Updated_dt,

      [Parent_List_no],

      [Parent_List_Desc],

      Child_Created_by,

      Child_Last_Updated_dt,

      [Child_List_no],

      [Child_List_Desc],

      Extraction_No,

      Extraction_Name

)

Select

      Isnull(cast(l.created_by as varchar(30)),'Parent is an Extraction') as Parent_Created_by,

      Isnull(l.last_update_dt,'') as Parent_Last_Updated_dt,

      isnull(cast(l.list_no as varchar),'') as 'Parent_List_No',

      isnull(l.list_desc,'') as 'Parent_List_Desc',

      ll.created_by,

      Isnull(ll.last_update_dt,'') as Child_Last_Updated_dt,

      ll.List_no as 'Child_List_no',

      ll.list_desc as 'Child_List_Desc',

      Isnull(Cast(k.ka_no as varchar),'') as ka_no,

      isnull(k.Description,'') as Extraction_Name

From

    t_list_extraction le left Join

    T_LIST l on l.list_no = le.list_id Left join

      t_list ll on ll.list_no = @Curr_List_no Left Join

    impresario_cci.dbo.t_keycode k on k.keycode_id = le.list_id

where

    keyword_no in (

        select keyword_no from t_keyword where ref_tbl in ( 't_list' , 'vs_list' ) and detail_col like '%list_no%' )

        and ( charindex ( ',' + convert ( varchar , @Curr_List_no ) + ','  , ',' + Ltrim (Replace (Replace ( value1 , '(' , '' ) , ')' , '' ) + ',' ) )  > 0 )

        and list_id != @Curr_List_no

--Select @Curr_List_no as 'Before New List'

Set @Curr_List_no = (

            Select Min(List_no)

            from t_list

            where

                  list_no > @Curr_List_no)

--Select @Curr_List_no as 'After New List'

End

Select * from #tmp

order by 6

Select * from #tmp p Join #tmp pp on pp.Child_list_no = p.Parent_list_no

I'm hoping someone has a trick to do this. I have a lot of lists that were saved from extractions that I was working on for mailing/telefunding campaigns, that I have since then decided not to use, but I am unable to delete them.

I get the error message that this list is in use and cannot be deleted but I have gone through so many of my lists and extractions and do not see any of these lists in use (nor should any of my collegues be using them.)

How can I delete???? I know I could just inactivate them, which I have. But they still show up in list manager and I would like to get rid of them all together because their presence is more confusing than anything else.

Suggestions???

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Development Forum. You may reply to this message to post to the Development forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!