Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    1

    Unanswered: Need help with fairly simple UNION statement.

    I'm not very familiar with SQL and have been asked to modify someone else's code, which seems pretty straightforward except I am not familiar with what the alias? 'rate-mg' at the very end is - just an alias for the query? Anyway, it's that 'rate_mg' alias that is causing a problem when I try to union with another select statement.

    Original code:
    (SELECT r.amrprrt_pidm pidm,
    t.atvrtgt_desc mg_rating,
    w.atvrate_desc mg_amt,
    s.atvrscr_desc mg_source
    FROM alumni.atvrscr s,
    alumni.atvrate w,
    alumni.atvrtgt t,
    alumni.amrprrt r
    WHERE s.atvrscr_code = r.amrprrt_rscr_code
    AND w.atvrate_code = r.amrprrt_rate_code
    AND t.atvrtgt_code = 'C'
    AND t.atvrtgt_code = r.amrprrt_rtgt_code) rate_mg,

    I need to modify it to choose the Primary record first, if there is one, else choose any qualifying record. So I added a 'AND r.amrprrt_primary_ind = 'Y' statement to the original code and then did a UNION to a similar select statement to select the record where the record is not in '_primary-ind = 'Y'.

    Below is my code, which works just fine WITHOUT the rate_mg alias at the end. If I add the 'rate_mg' at the end of the query, I get:

    AND t.atvrtgt_code = 'C')) rate_mg
    ERROR at line 33:
    ORA-00933: SQL command not properly ended

    (SELECT r.amrprrt_pidm pidm,
    t.atvrtgt_desc mg_rating,
    w.atvrate_desc mg_amt,
    s.atvrscr_desc mg_source
    FROM alumni.atvrscr s,
    alumni.atvrate w,
    alumni.atvrtgt t,
    alumni.amrprrt r
    WHERE s.atvrscr_code = r.amrprrt_rscr_code
    AND w.atvrate_code = r.amrprrt_rate_code
    AND t.atvrtgt_code = 'C'
    AND t.atvrtgt_code = r.amrprrt_rtgt_code
    AND r.amrprrt_primary_ind = 'Y')
    union
    (SELECT r.amrprrt_pidm pidm,
    t.atvrtgt_desc mg_rating,
    w.atvrate_desc mg_amt,
    s.atvrscr_desc mg_source
    FROM alumni.atvrscr s,
    alumni.atvrate w,
    alumni.atvrtgt t,
    alumni.amrprrt r
    WHERE s.atvrscr_code = r.amrprrt_rscr_code
    AND w.atvrate_code = r.amrprrt_rate_code
    AND t.atvrtgt_code = 'C'
    AND t.atvrtgt_code = r.amrprrt_rtgt_code
    AND r.amrprrt_pidm not in (SELECT r.amrprrt_pidm
    FROM alumni.amrprrt r,
    alumni.atvrtgt t
    WHERE r.amrprrt_primary_ind = 'Y'
    AND t.atvrtgt_code = 'C')) rate_mg,

    I have to have the rate_mg alias on there, as it is used later in the program:
    Select...
    From...
    Where...
    AND rate_mg.pidm(+) = c.apbcons_pidm

    Thanks for any help you can offer. So very much to learn....

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You posted an incomplete query so ... here's what I think: the whole code you posted should be named as RATE_MG, not just the second SELECT statement in this union. Shortly:
    Code:
    select *
    from (select <your first select>
          union
          select <your second select>
         ) rate_mg,
         some_other_table c
    where rate_mg.pidm(+) = c.apbcons_pidm
      and ...
    Or, reformatted query you posted:
    Code:
    select *
      from (select r.amrprrt_pidm pidm,
                   t.atvrtgt_desc mg_rating,
                   w.atvrate_desc mg_amt,
                   s.atvrscr_desc mg_source
              from alumni.atvrscr s,
                   alumni.atvrate w,
                   alumni.atvrtgt t,
                   alumni.amrprrt r
             where     s.atvrscr_code = r.amrprrt_rscr_code
                   and w.atvrate_code = r.amrprrt_rate_code
                   and t.atvrtgt_code = 'C'
                   and t.atvrtgt_code = r.amrprrt_rtgt_code
                   and r.amrprrt_primary_ind = 'Y'
            union
            select r.amrprrt_pidm pidm,
                   t.atvrtgt_desc mg_rating,
                   w.atvrate_desc mg_amt,
                   s.atvrscr_desc mg_source
              from alumni.atvrscr s,
                   alumni.atvrate w,
                   alumni.atvrtgt t,
                   alumni.amrprrt r
             where     s.atvrscr_code = r.amrprrt_rscr_code
                   and w.atvrate_code = r.amrprrt_rate_code
                   and t.atvrtgt_code = 'C'
                   and t.atvrtgt_code = r.amrprrt_rtgt_code
                   and r.amrprrt_pidm not in
                          (select r.amrprrt_pidm
                             from alumni.amrprrt r, alumni.atvrtgt t
                            where r.amrprrt_primary_ind = 'Y'
                                  and t.atvrtgt_code = 'C')
           ) rate_mg,
           some_other_table c
    where rate_mg.pidm(+) = c.apbcons_pidm
      and ...
    I hope it helps.

    If it is not a problem, could you, please, format your code and enclose it into the [code] tags which would preserve formatting? Formatted code is MUCH easier to read.

    Instructions are here.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Sorry, my idea was same as littlefoot.

    Another my thought was that a UNION in your query might be replaced by a OR, like...
    Code:
    (SELECT r.amrprrt_pidm pidm
          , t.atvrtgt_desc mg_rating
          , w.atvrate_desc mg_amt
          , s.atvrscr_desc mg_source
      FROM  alumni.atvrscr s
          , alumni.atvrate w
          , alumni.atvrtgt t
          , alumni.amrprrt r
      WHERE s.atvrscr_code = r.amrprrt_rscr_code
        AND w.atvrate_code = r.amrprrt_rate_code
        AND t.atvrtgt_code = 'C'
        AND t.atvrtgt_code = r.amrprrt_rtgt_code
        AND
        (   r.amrprrt_primary_ind = 'Y'
         OR NOT EXISTS(
               SELECT 0
                FROM  alumni.amrprrt rx
                WHERE t .atvrtgt_code = rx.amrprrt_rtgt_code
                  AND rx.amrprrt_primary_ind = 'Y'
            )
        )
    ) rate_mg
    Last edited by tonkuma; 01-12-12 at 22:05. Reason: Replaced all contents by new one.

Posting Permissions

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