Need help with fairly simple UNION statement.

Susang · #1 (**permalink**) 01-11-12, 17:22

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....

Littlefoot · #2 (**permalink**) 01-12-12, 01:12

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.

tonkuma · #3 (**permalink**) 01-12-12, 20:38

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

Internet Services

Web Development

Digital Marketing

Consumer Tech