CODEWITHSUNDEEP
oracle-databaseselectconstants
Buksy
Buksy

Reputation: 12228

Select constant value

I have 2 views:

  1. View VIEW5_SUMA that contains SUM of prices for all items (cars), it is one row and it's constant I need to "join"
  2. View VIEW4_SUMA_AUTA that contains SUM of all incomes for specific item (car)

SQL> SELECT * FROM VIEW5_SUMA ;

CELKOVA_TRZBA
-------------
         5806

SQL> SELECT * FROM VIEW4_SUMA_AUTA ;

TRZBA_AUTA    ID_AUTO
---------- ----------
       360          1
       ...        ...

I need to create another view, that will contain percents of income for every item. The problem is, I dont know how to "JOIN" that one row from VIEW5_SUMA (constant of total income) to my select so I can calculate with it.

This is what I got so far but it returns error:

CREATE VIEW VIEW6 AS
SELECT
  t1.typ,
  t1.specifikacia_typu,
  t1.SPZ,
  t2.trzba_auta/(t3.celkova_trzba/100) AS percenta
FROM AUTA t1, VIEW5_SUMA t3
JOIN VIEW4_SUMA_AUTA t2 ON t1.id_auto = t2.id_auto
;

Upvotes: 0

Views: 655

Answers (3)

cyon
cyon

Reputation: 9538

The problem you see is due to the fact that the JOIN binds tighter than the comma so what you wrote is equivalent to

FROM AUTA t1, (VIEW5_SUMA t3 JOIN VIEW4_SUMA_AUTA t2 ON t1.id_auto = t2.id_auto)

Seeing how there is no table t1 inside the parenthesis this won't work.

That means all you have to do is to change the order so that the binding works as you want it:

CREATE VIEW VIEW6 AS
 SELECT
  t1.typ,
  t1.specifikacia_typu,
  t1.SPZ,
  t2.trzba_auta/(t3.celkova_trzba/100) AS percenta
 FROM AUTA t1 JOIN VIEW4_SUMA_AUTA t2 ON t1.id_auto = t2.id_auto,
 VIEW5_SUMA t3
 ;

Alternatively you can use a CROSS JOIN which is the same thing as a comma because it results in a cartesian product or changes into an inner join if there is a where clause.

 FROM AUTA t1 CROSS JOIN VIEW5_SUMA t3 JOIN VIEW4_SUMA_AUTA t2 ON (...)

 FROM AUTA t1 JOIN VIEW4_SUMA_AUTA t2 ON (...) CROSS JOIN VIEW5_SUMA t3

The CROSS JOIN is a JOIN so you have the expected parenthesis ((CROSS JOIN) JOIN )

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191315

'Returns error' is not helpful in diagnosing the problem. I'd guess it says that t1.id_auto is an invalid identifier.

Mixing the old join syntax (multiple comma-separated tables in the from clause and the join condition in the where clause) and the 'new' syntax (join and on) is confusing and doesn't always work; I'd recommend always using the 'new' syntax anyway.

You can do this with join, but you have to only use that form. Since there is no join condition between t1 and t3, you need a cross join. This produces the cartesian product of the two tables, which isn't often what you want, but in this case since one the of the tables has a single row it seems appropriate.

CREATE VIEW VIEW6 AS
SELECT
  t1.typ,
  t1.specifikacia_typu,
  t1.SPZ,
  t2.trzba_auta/(t3.celkova_trzba/100) AS percenta
FROM AUTA t1
CROSS JOIN VIEW5_SUMA t3
JOIN VIEW4_SUMA_AUTA t2 ON t1.id_auto = t2.id_auto

Building views on top of views isn't always a good idea and can cause performance issues.

Upvotes: 0

Buksy
Buksy

Reputation: 12228

Oh I found one way just after posting my question. If I dont use JOIN at all but rather specify multiple tables in FROM and then join tables with WHERE it works.

SELECT
  t1.typ,
  t1.specifikacia_typu,
  t1.SPZ,
  t2.trzba_auta/(t3.celkova_trzba/100) AS percenta
FROM AUTA t1, VIEW4_SUMA_AUTA t2, VIEW5_SUMA t3
WHERE
  t1.id_auto = t2.id_auto
;

But I'm still curious if there's way to do this with JOIN.

Upvotes: 0

Related Questions