none
How to return sets of values based on condition in SQL Server 2005?

    问题

  • I am a beginner in SQL Server 2005 stored procedure. I can't seem to get it working as wanted.

    I have a sp that takes in parameter @caseid from a table called annot. @caseid is assigned to value of column src_caseid and can have multiple references (ref_caseid) or none in table annot. I want to set a condition and set proper shepardsflag depending on the column court from table case which I did using INNER JOIN.

    Basically this is the scenario:

    Table annot - ref_caseid, src_caseid, annotation
    Table case - caseid, court

    Example of set of results from the INNER JOIN on ref_caseid = caseid like this:

    ref_caseid   src_caseid   annotation    court
      17334         17338        Refd       high court
      17600         17338        Foll       federal court
      18271         17338        Foll       federal court
      43220         17338        Not Foll   supreme court

    Condition to be set:
    From the recordset, if federal court exists, it should only take rows of federal court. If NO federal court is found, then it will take other cases with other court values.

    To achieve this, I set a counter for federal court counts. But seems that SQL only reads the last row and set @courtFC value based on it. I've tried order by but doesn't seem working as well. Why is the counter being reset to based on the last record found?

    From sample above, the final shepardsflag value of case 17338 should be = 3 (value of @shep for 'Foll') as it should take rows with "federal court" only AND ignore the rest of the rows.

    But the current result is shepardsflag = 2 ; which is wrong

    I hope I explain well.

    Can someone please help me on the right logic? Should I create a temp table? Thanks in advance.

    Script:

    [code]
    ALTER PROCEDURE [dbo].[spUpdateShepardsFlags] @caseid int = null AS
    begin
    declare @Shep int
    declare @ref_caseid int
    declare @court int
    declare @courtFC int
    declare @annot int

    if @caseid is not null
       begin
          select @court = b.court, @ref_caseid = a.ref_caseid, @annot = a.annotation
             from cba_annot a inner join cbm_case b on a.ref_caseid = b.caseid 
             where a.src_caseid = @caseid

          if @court is not null
            begin
               if @court = 'MYFC'
                  set @courtFC = @courtFC + 1
               if @court <> 'MYFC'
                  SET @courtFC = @courtFC + 0
                PRINT 'The @courtFC counter : ' + CAST(@courtFC AS CHAR) 
            end

            if @court is not NULL 
            begin
              if @courtfc > 0
               begin 
                 if exists(select a.ref_caseid, b.court, a.annotation, a.src_caseid from 
                           cba_annot a inner join cbm_case b on a.ref_caseid = b.caseid)
                    begin
                       if exists(select src_caseid from cba_annot where (annotation like '%Refd%'
                          or annotation like '%Comp%')
                          and src_caseid = @caseid)
                          set @Shep = 4

                       if exists(select src_caseid from cba_annot where (annotation like '%Foll%'
                          or annotation like '%Aff%')
                          and src_caseid = @caseid)
                          set @ShepFC = 3

                        update cbm_case
                        set shepardsflag = @shep
                        where caseid=@caseid
                    end
                end

              else -- if @courtFC = 0
                begin --new
                 if exists(select a.ref_caseid, b.court, a.annotation, a.src_caseid from 
                           cba_annot a inner join cbm_case b on a.ref_caseid = b.caseid)
                    begin
                       if exists(select src_caseid from cba_annot where (annotation like '%Refd%'
                          or annotation like '%Comp%')
                          and src_caseid = @caseid)
                          set @Shep = 4

                       if exists(select src_caseid from cba_annot where (annotation like '%Foll%'
                          or annotation like '%Aff%')
                          and src_caseid = @caseid)
                          set @Shep = 3

                       if exists(select src_caseid from cba_annot where (annotation like '%Not Foll%'
                          or annotation like '%Dist%')
                          and src_caseid = @caseid)
                          set @Shep = 2

                        update cbm_case
                        set shepardsflag = @shep
                        where caseid=@caseid
                    end

              end -- new
          end
      else  --- if court is NULL -- case not referred by any other case
            update cbm_case
            set shepardsflag = 5
            where caseid=@caseid
      end 

    else -- if caseid is null

    -- other condition
    [/code]

    • 已编辑 Fifah 2012年7月5日 1:04
    2012年7月5日 0:46

全部回复

  • To get rows with Federal Court and if there are no such rows, take the first other row, you can use the simple select statement, e.g.

    ;with cte as (select a.ref_caseid, b.court, a.annotation, a.src_caseid, row_number() over (partition by a.scr_CaseID order by case when b.court = 'Federal Court' then 1 else 2 end) as Row from 
                           cba_annot a inner join cbm_case b on a.ref_caseid = b.caseid)

    select * from cte where Row = 1  -- this will select rows for Federal Court and if there is no Federal Court row, it will select something else.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    2012年7月5日 2:02
    版主
  • Thanks for this Naomi. I've tested but the select * from cte only return first row of 'federal court' ( even though i have 2 federal courts as my sample input).

    And even with only 1 result from 'federal court', the calculation for shepardsflag is still derived from courts other than 'federal court'; which is wrong.

    After "select * from cte where row=1", should I apply any other select like what I've applied in BOLD below?

    Appreciate your assistance on this. Thanks so much.

          ;with cte as (select a.ref_caseid, b.court, a.annotation, a.src_caseid, row_number() over 

             (partition by a.src_CaseID order by case when b.court = 'MYFC' then 1 else 2 end) as Row 
             from cba_annot a inner join cbm_case b on a.ref_caseid = b.caseid 
             where a.src_caseid = @caseid)

          select * from cte where Row = 1  -- this will select rows for Federal Court and if there is no Federal Court row, it will select something else.
                        begin
                       if exists(select src_caseid from cba_annot where (annotation like '%Refd%'
                          or annotation like '%Comp%')
                          and src_caseid = @caseid)
                          set @Shep = 4

                       if exists(select src_caseid from cba_annot where (annotation like '%Foll%'
                          or annotation like '%Aff%')
                          and src_caseid = @caseid)
                          set @ShepFC = 3

    -- rest of calculation depending on annotation value


    2012年7月5日 5:18
  • I think you need to provide a concise example of what do you want to achieve - e.g. sample tables + data as insert statements + desired result. I just gave you the idea of how to solve one of the problems you listed.

    If you need to output both Federal Court rows you may want to change ROW_NUMBER() to RANK(). In this case it will return both rows for Federal Court. If there are no Federal Court rows, but there are rows with something else (Say, Municipal Court) and there are two of such rows, both will be returned using RANK() also.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    2012年7月5日 13:25
    版主
  • Yes, now with RANK... the CTE table generated is listing the right courts (Federal courts (if exist) and other courts (if federal court does not exist). Example of output CTE table:

    ref_caseid   src_caseid   annotation    court
      17600         17338        Followed       federal court
      18271         17338        Not Followed       federal court
      88880         17338        Referred       federal court

    If I removed the federal court data, then CTE will have other courts value; for example:

    ref_caseid   src_caseid   annotation    court
      43356         17338        Referred       municipal court
      16225         17338        Distinguished       supreme court

    Now that i have the right rows in CTE, i want to access and manipulate it further. For instance,

    if annotation = 'Referred' then set flag = 4; 
    if annotation = 'Followed' then set flag = 3;
    if annotation = 'Not Followed' then set flag = 2;

    But I can't seem to access CTE after this query: "select * from cte where Row = 1".

    It is throwing me with this error message after I execute it.. "Invalid object name 'cte'

    Why can't I access CTE multiple times?

    Btw, what type of table does CTE belongs to? Is it partition table? 

    Looking forward for your reply. Thanks once again, Naomi.


    2012年7月6日 1:59
  • CTE by definition can only be accessed once by the immediate query that follows it. If you need to access that data more than once, you need to use temporary table instead of the CTE.

    You can also do:

    select case Annotation when 'Referred' then 4 when 'Followed' then 3 when 'Not Followed' then 2, * 

    from cte where Row = 1

    -----------

    So, you will create the extra flag and also get the rest of the data (only 2 Federal Court rows).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    2012年7月6日 2:03
    版主
  • I created a temp table; but it's prompting "Invalid syntax near ')'".

    CREATE TABLE #temptable(
    ref_caseid int,
    src_caseid int,
    court nvarchar(30), 
                    annotation nvarchar(30))

    INSERT INTO #temptable (ref_caseid, src_caseid, court, annotation)
    SELECT  ref_caseid, src_caseid, court, annotation
    FROM (select a.ref_caseid, b.court, a.annotation, a.src_caseid, rank() over 
             (partition by a.src_CaseID order by case when b.court = 'MYFC' then 1 else 2 end) as Row 
             from cba_annot a inner join cbm_case b on a.ref_caseid = b.caseid 
             where src_caseid = 17338 and row = 1)

    I've tried several other ways but nothing seem to work. Appreciate your feedback on this. Thanks.

    2012年7月6日 5:56
  • Try

    INSERT INTO #temptable (ref_caseid, src_caseid, court, annotation)
    SELECT  ref_caseid, src_caseid, court, annotation
    FROM (select a.ref_caseid, b.court, a.annotation, a.src_caseid, rank() over 
             (partition by a.src_CaseID order by case when b.court = 'MYFC' then 1 else 2 end) as Row 
             from cba_annot a inner join cbm_case b on a.ref_caseid = b.caseid 
             where a.src_caseid = 17338) Derived where row = 1

    You need to give the derived table an alias (in this case Derived) and you need to reference Row column outside the derived table.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    2012年7月6日 13:08
    版主
  • Hi Naomi, I tried it and it's working brilliantly, thanks so much.

    The sp is working fine if I execute one caseid at a time. But if I execute the sp with all caseid, it didn't work; not assigning the values correctly. Could it be because of the temp table?

    I'm pasting my script here for your review:

    CREATE TABLE #court( ref_caseid int, src_caseid int, court nvarchar(30), annotation nvarchar(30)) INSERT INTO #court (ref_caseid, src_caseid, court, annotation) SELECT ref_caseid, src_caseid, court, annotation FROM (SELECT a.ref_caseid, b.court, a.annotation, a.src_caseid, RANK() OVER (PARTITION BY a.src_CaseID ORDER BY CASE WHEN b.court = 'MYFC' THEN 1 ELSE 2 END) AS Row FROM cba_annot a INNER JOIN cbm_case b ON a.ref_caseid = b.caseid WHERE a.src_caseid = @caseid) Derived WHERE row = 1 begin if exists(select * from #court where (annotation like '%Rel%') and src_caseid = @caseid) set @Shep = 5 if exists(select * from #court where (annotation like '%Cons%' or annotation like '%Cited%' or annotation like '%Expl%' or annotation like '%Refd%' or annotation like '%Comp%' or annotation like '%Discd%' or annotation like '%Ment%' or annotation like '%Noted%' or annotation like '%Ntd%' or annotation like '%Refd to%') and src_caseid = @caseid) set @Shep = 4 if exists(select * from #court where (annotation like '%Appl%' or annotation like '%Appr%' or annotation like '%Foll%' or annotation like '%Aff%' or annotation like '%SLR%' or annotation like '%Adopted%' or annotation like '%Adptd%' or annotation like '%App%' or annotation like '%Leave Refused%' or annotation like '%Refused%' or annotation like '%Upheld%') and src_caseid = @caseid) set @Shep = 3 if exists(select * from #court where (annotation like '%Dist%' or annotation like '%Distd%' or annotation like '%Qstd%' or annotation like '%Var%' or annotation like '%SLG%' or annotation like '%Dbtd%' or annotation like '%Leave Allowed%' or annotation like '%LG%' or annotation like '%Not Foll%' or annotation like '%Not Appl%' or annotation like '%Not Appr%' or annotation like '%Dissented%' or annotation like '%Not Adopted%' or annotation like '%Set aside%' or annotation like '%Rev%') and src_caseid = @caseid) set @Shep = 2 if exists(select * from #court where (annotation like '%Disap%' or annotation like '%Ovrr%' or annotation like '%Rescinded%' or annotation like '%Superseded by%') and src_caseid = @caseid) set @Shep = 1 update cbm_case set shepardsflag = @shep where caseid=@caseid

    end drop table #court


    2012年7月24日 5:54
  • I am sorry, it has been a while and I am on vacation in Israel now, so I suggest to start a new thread with this problem.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    2012年7月25日 6:21
    版主