12

can you assign an alias to the from clause? like:

select a - b "Markup" from retail a, cost b;

EDIT: sorry i typed that out a bit too quick and tried to simplify the question to the point where it didnt make any sense

What im actually trying to do is use aliases to compare the months between two publishing dates in the same table. Here's what i found works:

select distinct to_char(months_between((select distinct pubdate
                                        from books3 
                                        where pubid = 2), 
                                       (select distinct pubdate 
                                        from books3 
                                        where pubid = 4)), '99.99') "Answer"
                              from books3

i wanted it to looks something like this:

select distinct months_between(a,b)
from (select distinct pubdate 
       from books3 
       where pubid = 2 as a), 
     (select distinct pubdate 
      from books3 
      where pubid = 4 as b)

but that isn't working

Sinaesthetic
  • 11,426
  • 28
  • 107
  • 176

2 Answers2

21

Yes, Oracle supports table aliases. It supports AS in the SELECT list but not in the FROM list:

SELECT a.col - b.col AS markup
  FROM RETAIL a,
       COST b
 WHERE b.id = a.id

Most databases support omitting the AS keyword.

That said, table aliases aren't column aliases -- you still need to reference a specific column in the respective table in the SELECT clause, like you see in my update of your example. I also added the WHERE criteria so the query wouldn't be returning a Cartesian product.

Table aliases are sometimes required for derived tables/inline views (AKA subquery, though I find the terminology very vague):

SELECT x.col
  FROM (SELECT t.col,
               MAX(t.date)
          FROM TABLE t
      GROUP BY t.col) x

Here's your query:

Your problem was you were putting the table alias inside the derived table, when it needs to be outside the brackets/parenthesis:

SELECT DISTINCT TO_CHAR(MONTHS_BETWEEN(x.pubdate, y.pubdate), '99.99') AS "Answer"
 FROM (SELECT DISTINCT a.pubdate FROM BOOKS3 a WHERE a.pubid = 2) x,
      (SELECT DISTINCT b.pubdate FROM BOOKS3 b WHERE b.pubid = 4) y

The reason you need the distinct is because of the Cartesian product.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • @Sinaesthetic: Updated to add `AS` for the alias, but the brackets are matched in my example so my assumption is you're re-writing the query & missing a closing bracket. – OMG Ponies Feb 09 '11 at 02:04
  • yeah i double checked to match the parenthesis to be sure, and you're right, they're matched. Which makes me think there is something else wrong with the statement thats giving a misleading error. Copy and paste didnt work either – Sinaesthetic Feb 09 '11 at 02:08
  • @Sinaesthetic: Debug time -- take out the TO_CHAR and MONTHS_BETWEEN to see if that works. – OMG Ponies Feb 09 '11 at 02:10
  • did this: SELECT DISTINCT x.pubdate, y.pubdate FROM (SELECT DISTINCT a.pubdate FROM BOOKS3 AS a WHERE a.pubid = 2) AS x, (SELECT DISTINCT b.pubdate FROM BOOKS3 AS b WHERE b.pubid = 4) AS y; error line 2 -- "Missing right parenthesis" – Sinaesthetic Feb 09 '11 at 02:16
  • @Sinaesthetic: Ok, take out anything related to `y` -- the subquery and the column. Then see if that works. If it does, try the reverse -- remove all the `x` stuff & test. – OMG Ponies Feb 09 '11 at 02:48
0

The closest to what you have would be to move the AS alias out of the subquery

select distinct months_between(a.pubdate,b.pubdate)
from (select distinct pubdate 
       from books3 
       where pubid = 2) as a ,
     (select distinct pubdate 
      from books3 
      where pubid = 4) as b;

But still, the query doesn't make much sense. If there are 2 records for pubid=2 and 3 for pubid=4, you get 6 rows in the output....

months_between(a1, b1)
months_between(a2, b1)
months_between(a1, b2)
months_between(a2, b2)
months_between(a1, b3)
months_between(a2, b3)

I suspect you actually have some grouping going on, so this will compare pubid=2 and pubid=4 entries at a per-bookid level.

select
    bookid,
    to_char(months_between(
        max(case when pubid=2 then pubdate end),
        max(case when pubid=4 then pubdate end)), '99.99') "Answer"
from books
group by bookid;
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • "SQL command not properly ended". Also, it just so happens that each pubid has the same publish date for each record with that pubid – Sinaesthetic Feb 09 '11 at 02:06
  • no errors this time, although the "answer" column is all blank. but then, the object is based around the aliases idea. i already have a working query to return the answer. just trying to do it in a different way. – Sinaesthetic Feb 09 '11 at 02:12
  • @sin - which one did you try or both? – RichardTheKiwi Feb 09 '11 at 02:24