Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    2,296

    Unanswered: one match = all match

    ok, so I've been trying to figure out how to do this and I can't seem get my
    hands around it.

    Requirement: must be done in a select statement (eventually going into an MV)

    I have two tables, if test1a matches the criteria in test1 then all the indiv_id need to be marked.

    example:
    PHP Code:
    sqlselect indiv_idsrc_cd from test1;

      
    INDIV_ID SRC_CD
    ---------- --
             
    1 a
             1 b
             1 c
             1 d

    sql
    select from test1a;

      
    INDIV_ID SRC_CD
    ---------- --
             
    1 a
             2 a

    /* If one indiv_id and src_cd match then mark as a redeemer */
    sqlselect a.indiv_ida.src_cd,
      
    2  case when b.indiv_id is not null then 'REDEEMER' else NULL end redeemer_ind
      3  from test1 a
      4  left outer join test1a b on a
    .indiv_id b.indiv_id and a.src_cd b.src_cd;

      
    INDIV_ID SR REDEEMER
    ---------- -- --------
             
    1 a  REDEEMER
             1 d
             1 b
             1 c

    /* what i REALLY want is all indiv_ids should be a redeemer if ANY
     indiv_id/src_cd matches like the output below */
      
    INDIV_ID SR REDEEMER
    ---------- -- --------
             
    1 a  REDEEMER
             1 d  REDEEMER
             1 b  REDEEMER
             1 c  REDEEMER 
    so, how can I get this done simply?
    Granted the true MV has a ton of outer joins to other tables, but this is the
    point I am stuck at. I can't think f a way to do it without a bunch of
    subqueries.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Are you saying even one subquery is not allowed because I think you can do it with a single subquery in the select part i.e.

    Code:
    select x.indiv_id, x.src_cd, 
    (
         select case when count(*)>0 then 'REDEEMER' end 
         from test1 a, test1a b
         where a.indiv_id = b.indiv_id and a.src_cd = b.src_cd
         and a.indiv_id=x.indiv_id
    ) redeemer
    from test1 x
    Havent tried the above sql but you get the idea.

    Alan

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    I have tried that but my driving table (test1) is so large that the real
    query/MV takes forever to build.

    i haven't tried your specific style of subquery so
    I am going to try again and see if a helpful index of some kind is missing.

    thanks for giving it a second set of eyes.

    currently they have an actual TABLE that gets updated with something like:
    PHP Code:
    update test1
    set redeemer_cd 
    'REDEEMER'
    where indiv_id in (
          
    select a.indiv_id
          from test1 a
          innner join test1a b on a
    .indiv_id b.indiv_id and a.src_cd b.src_cd); 
    since the "table" needs to be refreshed every day I would rather turn it into
    a MV instead of a table.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    OK! I got it working! Thanks for the post.
    For my requirements I created a function-based index and had to
    modify the CASE statement to outside the subquery but the thing
    works great.

    here was the final product:
    PHP Code:
    select distinct
       pr
    .indiv_id,
       case 
    when (
            
    select count(*)
            
    from TEST1A aMAIN_TABLE b
            where a
    .indiv_id b.indiv_id and a.inbd_src_cd_id b.inbd_src_cd_id
            
    and a.indiv_id=pr.indiv_id and nvl(a.redeem_dtto_date('01011980','mmddyyyy')) > to_date('01011980','mmddyyyy')
       ) > 
    0 then 'REDEEMER' else 'NON-REDEEMER' end as REDEEMER_IND
    from
       MAIN_TABLE pr
    .... 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I know you've already done it, but - does something like this make any sense? Seems to be working for a small test table(s); I have no idea what happens in real case situation.
    Code:
    select t1.indiv_id, t1.src_cd,
           case
             when (select count(*) from
                     (select indiv_id, src_cd from test1
                      intersect
                      select indiv_id, src_cd from test1a
                     )
                  ) > 0 then 'REDEEMER'
             else
               'NON-REDEEMER'
             end redeemer
    from test1 t1;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •