Dotaz: SELECT vicero výrazů

[Jirka]

Ahoj,
je možné udělat v SELECTu, že výsledek prvního výrazu se použije ve druhém výrazu? Například něco jako toto:

SELECT 1+2 INTO @x, 4*@x;

To samozřejmě nefunguje, ale třeba to nějak jde. Zatím to dělám takto:

SELECT 1+2 INTO @x; SELECT 4*@x;

Jde to udělat jedním SELECTem, nebo je nutno použít dva?

---

---

Odpovědi

[Filip Jirsák]

Asi budete muset napsat trochu víc, co vlastně chcete udělat. To, co popisujete, se dá snadno řešit obyčejným

SELECT 4*(1+2);

[Jirka]

Ono je to moc zjednodušené, v tom prvním výrazu je prostě  komplikovanější výpočet. A ve druhém je výraz, kde použiji výsledek prvního výrazu. Pokud to chci mít v jednom selectu, tak musim ten komplikovaný výpočet provést ještě jednou, to samozřejmě lze. Jen mě napadlo, zda-li na to neexistuje finta, neopakovat ještě jednou ten komplikovaný výpočet.

[Filip Jirsák]

Zkusil bych SELECT 1+2 AS x, 4*x. Ale nevím, zda to projde, asi to bude záviset i na konkrétní databázi.

[Jirka]

Toto a podobné jsem zkoušel na MySQL a bohužel nic.

[neaktivni]

Např. v PostgreSQL to nefunguje. Ale obecně funguje vnořený dotaz:

SELECT x, 4*x
FROM (SELECT 1+2 AS x) AS z

[ZAH]

Dle mne by to muselo vypadat nějak takto.

select cx, cx from (select code as cx FROM base.centers) as scx

kde cx je předvipočítáno a může se zůčastnit dalších výpočtů.

[FooBar]

Zjednodusil jsi to mozna az trochu moc brutalne;), ale jinak jestli chapu dobre co chces, tak v Oracle a PostgreSQL (nevim, jak je na tom Firebird a vim, ze MySQL to neumi) existuje WITH statement, neco jako (pgsql):

WITH

AS ( SELECT 1+2 AS kouzelnenumero )

SELECT

 kouzelnenumero, 4*kouzelnenumero FROM tri

[FooBar]

err... WITH tri AS [...] ... je brzo rano

[ZAH]

To moje funguje určitě na všem co umí select from select. Jinak prvotní dotaz byl také dost zjednodušený.

[pht]

Kde nefunguje WITH tak ho lze nahradit viewem, nebo dočasnou tabulkou, nebo vloženou procedurou.

[podlesh]

Pointa použití WITH v tomto případě spočívá v tom, že je součástí SELECT příkazu, že nevyžaduje vůbec žádná práva a že nevyžaduje žádný úklid.

Jinak bych ještě doplnil, že WITH konstrukce je i v DB2 (myslím že tam je dokonce "původní", ale nejsem si jist).

[default]

Přílohy:

• with-example.sql (0 bytů)
• with-example.out (0 bytů)

Pointa WITH (factoring klauzule) je hlavně v tom, že materializuje. Bez indexů. A z toho vyplývá i využití. Třeba Oracle umí udělat in-line view, ale někdy ne, což je zákeřné.

Následující příklad ukazuje in-line view:

CREATE TABLE my_all_tables (
    table_id NUMBER(10) NOT NULL,
    table_name VARCHAR2(30) NOT NULL)
/

ALTER TABLE my_all_tables ADD CONSTRAINT pk_mat_tid PRIMARY KEY (table_id)
/

CREATE TABLE my_all_schemas (
    schema_name VARCHAR2(30) NOT NULL,
    table_id NUMBER(10) NOT NULL)
/

INSERT ALL
    INTO my_all_tables (table_id, table_name)
        VALUES (table_id1, table_name1)
    INTO my_all_schemas (schema_name, table_id)
        VALUES (table_schema1, table_id1)
SELECT
    ROW_NUMBER() OVER (ORDER BY att.table_name ASC) AS table_id1,
    att.owner AS table_schema1,
    att.table_name AS table_name1
FROM
    all_tables att
/

COMMIT
/

ALTER TABLE my_all_schemas ADD CONSTRAINT fk_mas_mat FOREIGN KEY (table_id) REFERENCING my_all_tables (table_id)
/

CREATE INDEX idx_mat_tn ON my_all_tables (table_name)
/

EXPLAIN PLAN FOR SELECT
    '"' || mas.schema_name || '"."' || mat.table_name || '"' AS full_name
FROM
    my_all_schemas mas INNER JOIN my_all_tables mat ON (mas.table_id = mat.table_id)
WHERE
    mat.table_name LIKE 'C%'
/

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
/

EXPLAIN PLAN FOR SELECT
    '"' || dta.schema_name || '"."' || dta.table_name || '"' AS full_name
FROM
    (SELECT
        mas.schema_name,
        mat.table_name
    FROM
        my_all_schemas mas INNER JOIN my_all_tables mat ON (mas.table_id = mat.table_id)) dta
WHERE
    dta.table_name LIKE 'C%'
/

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
/

EXPLAIN PLAN FOR WITH dta AS (SELECT
        mas.schema_name,
        mat.table_name
    FROM
        my_all_schemas mas INNER JOIN my_all_tables mat ON (mas.table_id = mat.table_id))
SELECT
    '"' || dta.schema_name || '"."' || dta.table_name || '"' AS full_name
FROM
    dta
WHERE
    dta.table_name LIKE 'C%'
/

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
/

DROP TABLE my_all_schemas PURGE
/

DROP TABLE my_all_tables PURGE
/

A výstup:

CREATE TABLE succeeded.
 ALTER TABLE my_all_tables succeeded.
CREATE TABLE succeeded.
5254 rows inserted
COMMIT succeeded.
 ALTER TABLE my_all_schemas succeeded.
CREATE TABLE succeeded.
 ALTER TABLE my_all_tables succeeded.
CREATE TABLE succeeded.
5254 rows inserted
COMMIT succeeded.
 ALTER TABLE my_all_schemas succeeded.
CREATE INDEX succeeded.
EXPLAIN PLAN succeeded.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1386169526

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |   171 | 10260 |    11  (10)| 00:00:01 |
|*  1 |  HASH JOIN                   |                |   171 | 10260 |    11  (10)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| MY_ALL_TABLES  |   171 |  5130 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_MAT_TN     |   171 |       |     3   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | MY_ALL_SCHEMAS |  2627 | 78810 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("MAS"."TABLE_ID"="MAT"."TABLE_ID")
   3 - access("MAT"."TABLE_NAME" LIKE 'C%')
       filter("MAT"."TABLE_NAME" LIKE 'C%')

Note
-----
   - dynamic sampling used for this statement

22 rows selected

EXPLAIN PLAN succeeded.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1386169526

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |   171 | 10260 |    11  (10)| 00:00:01 |
|*  1 |  HASH JOIN                   |                |   171 | 10260 |    11  (10)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| MY_ALL_TABLES  |   171 |  5130 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_MAT_TN     |   171 |       |     3   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | MY_ALL_SCHEMAS |  2627 | 78810 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("MAS"."TABLE_ID"="MAT"."TABLE_ID")
   3 - access("MAT"."TABLE_NAME" LIKE 'C%')
       filter("MAT"."TABLE_NAME" LIKE 'C%')

Note
-----
   - dynamic sampling used for this statement

22 rows selected

EXPLAIN PLAN succeeded.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1386169526

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |   171 | 10260 |    11  (10)| 00:00:01 |
|*  1 |  HASH JOIN                   |                |   171 | 10260 |    11  (10)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| MY_ALL_TABLES  |   171 |  5130 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_MAT_TN     |   171 |       |     3   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | MY_ALL_SCHEMAS |  2627 | 78810 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("MAS"."TABLE_ID"="MAT"."TABLE_ID")
   3 - access("MAT"."TABLE_NAME" LIKE 'C%')
       filter("MAT"."TABLE_NAME" LIKE 'C%')

Note
-----
   - dynamic sampling used for this statement

22 rows selected

 DROP TABLE my_all_schemas succeeded.
 DROP TABLE my_all_tables succeeded.

Ale jakmile uděláš něco složitějšího:

WITH dta AS (SELECT
        mas.schema_name,
        mat.table_name,
        LENGTH(mat.table_name) AS table_name_len
    FROM
        my_all_schemas mas INNER JOIN my_all_tables mat ON (mas.table_id = mat.table_id))
SELECT
    '"' || dta.schema_name || '"."' || dta.table_name || '" (' || gen.val || ')' AS full_name
FROM
    dta INNER JOIN (SELECT
            gen.val
        FROM
            (SELECT
                LEVEL AS val
            FROM
                dual
            CONNECT BY
                LEVEL <= (SELECT MAX(dta.table_name_len) FROM dta)) gen) gen
        ON ((dta.table_name_len <= 1 AND gen.val = 1) OR (dta.table_name_len > 1 AND gen.val <= dta.table_name_len))
WHERE
    dta.table_name LIKE 'C%'
/

pak LOAD AS SELECT a čau:

EXPLAIN PLAN succeeded.
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
Plan hash value: 330330509

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                           |     8 |   480 |    21   (5)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION      |                           |       |       |            |          |
|   2 |   LOAD AS SELECT                |                           |       |       |            |          |
|*  3 |    HASH JOIN                    |                           |  2627 |   153K|    11  (10)| 00:00:01 |
|   4 |     TABLE ACCESS FULL           | MY_ALL_SCHEMAS            |  2627 | 78810 |     5   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL           | MY_ALL_TABLES             |  2627 | 78810 |     5   (0)| 00:00:01 |
|   6 |   NESTED LOOPS                  |                           |     8 |   480 |    10   (0)| 00:00:01 |
|   7 |    VIEW                         |                           |     1 |    13 |     2   (0)| 00:00:01 |
|*  8 |     CONNECT BY WITHOUT FILTERING|                           |       |       |            |          |
|   9 |      FAST DUAL                  |                           |     1 |       |     2   (0)| 00:00:01 |
|  10 |      SORT AGGREGATE             |                           |     1 |    13 |            |          |
|  11 |       VIEW                      |                           |  2627 | 34151 |     8   (0)| 00:00:01 |
|  12 |        TABLE ACCESS FULL        | SYS_TEMP_0FD9D6608_51DED0 |  2627 |   120K|     8   (0)| 00:00:01 |
|* 13 |    VIEW                         |                           |     8 |   376 |     8   (0)| 00:00:01 |
|  14 |     TABLE ACCESS FULL           | SYS_TEMP_0FD9D6608_51DED0 |  2627 |   120K|     8   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("MAS"."TABLE_ID"="MAT"."TABLE_ID")
   8 - filter(LEVEL<= (SELECT MAX("DTA"."TABLE_NAME_LEN") FROM  (SELECT /*+ CACHE_TEMP_TABLE ("T1")
              */ "C0" "SCHEMA_NAME","C1" "TABLE_NAME","C2" "TABLE_NAME_LEN" FROM "SYS"."SYS_TEMP_0FD9D6608_51DED0"
              "T1") "DTA"))
  13 - filter("DTA"."TABLE_NAME" LIKE 'C%' AND ("DTA"."TABLE_NAME_LEN"<=1 AND "GEN"."VAL"=1 OR
              "DTA"."TABLE_NAME_LEN">1 AND "GEN"."VAL"<="DTA"."TABLE_NAME_LEN"))

Note
-----
   - dynamic sampling used for this statement

35 rows selected

Zatímco, když si ty dotazy rozkopíruješ:

SELECT
    '"' || dta.schema_name || '"."' || dta.table_name || '" (' || gen.val || ')' AS full_name
FROM
    (SELECT
        mas.schema_name,
        mat.table_name,
        LENGTH(mat.table_name) AS table_name_len
    FROM
        my_all_schemas mas INNER JOIN my_all_tables mat ON (mas.table_id = mat.table_id)) dta
    INNER JOIN (SELECT
            gen.val
        FROM
            (SELECT
                LEVEL AS val
            FROM
                dual
            CONNECT BY
                LEVEL <= (SELECT
                        MAX(dta.table_name_len)
                    FROM
                        (SELECT
                            mas.schema_name,
                            mat.table_name,
                            LENGTH(mat.table_name) AS table_name_len
                        FROM
                            my_all_schemas mas INNER JOIN my_all_tables mat ON (mas.table_id = mat.table_id)) dta)) gen) gen
        ON ((dta.table_name_len <= 1 AND gen.val = 1) OR (dta.table_name_len > 1 AND gen.val <= dta.table_name_len))
WHERE
    dta.table_name LIKE 'C%'
/

dostaneš:

EXPLAIN PLAN succeeded.
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 2113348083

---------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                |     1 |    73 |    12   (9)| 00:00:01 |
|*  1 |  HASH JOIN                       |                |     1 |    73 |    12   (9)| 00:00:01 |
|   2 |   NESTED LOOPS                   |                |       |       |            |          |
|   3 |    NESTED LOOPS                  |                |     1 |    43 |     6   (0)| 00:00:01 |
|   4 |     VIEW                         |                |     1 |    13 |     2   (0)| 00:00:01 |
|*  5 |      CONNECT BY WITHOUT FILTERING|                |       |       |            |          |
|   6 |       FAST DUAL                  |                |     1 |       |     2   (0)| 00:00:01 |
|   7 |       SORT AGGREGATE             |                |     1 |    43 |            |          |
|*  8 |        HASH JOIN                 |                |  2627 |   110K|    11  (10)| 00:00:01 |
|   9 |         TABLE ACCESS FULL        | MY_ALL_SCHEMAS |  2627 | 34151 |     5   (0)| 00:00:01 |
|  10 |         TABLE ACCESS FULL        | MY_ALL_TABLES  |  2627 | 78810 |     5   (0)| 00:00:01 |
|* 11 |     INDEX RANGE SCAN             | IDX_MAT_TN     |   171 |       |     2   (0)| 00:00:01 |
|* 12 |    TABLE ACCESS BY INDEX ROWID   | MY_ALL_TABLES  |     1 |    30 |     4   (0)| 00:00:01 |
|  13 |   TABLE ACCESS FULL              | MY_ALL_SCHEMAS |  2627 | 78810 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("MAS"."TABLE_ID"="MAT"."TABLE_ID")
   5 - filter(LEVEL<= (SELECT MAX(LENGTH("MAT"."TABLE_NAME")) FROM "MY_ALL_TABLES"
              "MAT","MY_ALL_SCHEMAS" "MAS" WHERE "MAS"."TABLE_ID"="MAT"."TABLE_ID"))
   8 - access("MAS"."TABLE_ID"="MAT"."TABLE_ID")
  11 - access("MAT"."TABLE_NAME" LIKE 'C%')
       filter("MAT"."TABLE_NAME" LIKE 'C%')
  12 - filter("GEN"."VAL"=1 AND LENGTH("MAT"."TABLE_NAME")<=1 OR
              LENGTH("MAT"."TABLE_NAME")>1 AND "GEN"."VAL"<=LENGTH("MAT"."TABLE_NAME"))

Note
-----
   - dynamic sampling used for this statement

36 rows selected

Ano, příklad není ideální, ale nic lepšího mě teď narychlo nenapadá.

Oracle 11gR1

[Murry]

Moc jsem nepochopil tvuj problem (tak asi jak ten zbytek lidi nade mnou), nicmene neresi to:
SELECT x,y FROM (SELECT ...) popr. JOIN?

[neaktivni]

Hurá, jde to:

SELECT @x:=1+2, 4*@x;
+---------+------+
| @x:=1+2 | 4*@x |
+---------+------+
|       3 |   12 | 
+---------+------+

Je to tedy vyřešeno, jen jsem už zapoměl, proč jsem to vlastně chtěl...