i want to do sub queries , which the data comes from the same selected result . the difference is the xIndex value . code bellow

    with src as (
            SELECT ROWNUM as xIndex,"LoginResult", ROUND(SYSDATE -"LastLoginTime") as "lastLogonDays"
            from "tb_UserStatus" 
            where "UserId" = userId
            ORDER BY "CreateTime" DESC
    )
    SELECT src."lastLogonDays", src."LoginResult"
    into lastLogonDays, hasLogon
    from src 
    where src.xIndex = 1;
    
    SELECT src."lastLogonDays", src."LoginResult"
    into reverse2ndDaysDiff, reverse2ndLoginResult
    from src 
    where src.xIndex = 2;

the code block

    SELECT src."lastLogonDays", src."LoginResult"
    into lastLogonDays, hasLogon
    from src 
    where src.xIndex = 1;

is success. but , i want another query at the same time

    SELECT src."lastLogonDays", src."LoginResult"
    into reverse2ndDaysDiff, reverse2ndLoginResult
    from src 
    where src.xIndex = 2;

and it failed.

is it possible the have multiple queries from the same with as result ? if it is possible , what should do ?

CodePudding user response：

No. A "common table expression" (CTE) does NOT survive after the query that declared it has been completed.

However you can run more than 1 CTE within a query, e.g.:

with src as (
        SELECT ROWNUM as xIndex,"LoginResult", ROUND(SYSDATE -"LastLoginTime") as "lastLogonDays"
        from "tb_UserStatus" 
        where "UserId" = userId
        ORDER BY "CreateTime" DESC
    ),
nxtCTE as (
    SELECT src."lastLogonDays", src."LoginResult"
    --into lastLogonDays, hasLogon
    from src 
    where src.xIndex = 1
    )
SELECT src."lastLogonDays", src."LoginResult"
--into reverse2ndDaysDiff, reverse2ndLoginResult
from src 
where src.xIndex = 2;

nb: Oracle may use the term "Subquery Factoring" instead of the much more frequently used term "common table expression" - both refer to use of the "with clause". e.g. this page

Whilst CTEs may seem like tables, or "temporary tables", they are not exactly the same as either of those, particularly in that they disappear once the query that created them is complete.

CodePudding user response：

A CTE (WITH clause) is an ad-hoc view valid for only the query to which it belongs. If you want to have a view available for more than one query, store it with CREATE VIEW.

Now let's look at your query:

• You are using case-sensitive column names and are thus forced to remember correct case and use quotes. It is not recommended to design your database case-sensitively.
• You select from "tb_UserStatus" where "UserId" = userId. So there are two columns called user ID in the table ("UserId" and "USERID")? This sounds like a very bad idea. Did you mean to use a bind variable instead maybe ("UserId" = :userId)? This is not possible in a stored view, though.
• You select ROWNUM. This is an arbitrary number indicating in which order Oracle happens to access the rows. It doesn't seem to make much sense to select it.
• You have an ORDER BY clause in your view. View results have no order. The clause is superfluous. There are other DBMS that would even report a syntax error here.

And then, what is your incentive? You want to run the same query, but with a different condition? Then you can use a bind variable: where src.xIndex = :xindex;