CODEWITHSUNDEEP
sqloracle-databasecount
naben
naben

Reputation: 13

Return COUNT rows of two SELECT related by UNION

I want to return the number of rows with columns from two Tables related by UNION. I wrote this query

SELECT(
      (SELECT * FROM(
       (SELECT
        ID_COMPTE,
        TITLE,
        LINK,
        DATE_CREAT,
        DATE_MODIF,
        'TF1' AS "TYPE_FICHIER",
        case when DATE_MODIF is null then DATE_CREAT else DATE_MODIF end as LAST_UPDATE FROM FIRST_TABLE FFF where ID_COMPTE= 11111111)

        UNION

        (SELECT
        ID_COMPTE,
        TITLE,
        LINK,
        DATE_CREAT,
        DATE_MODIF,
        'TF2' AS "TYPE_FICHIER",
        case when DATE_MODIF is null then DATE_CREAT else DATE_MODIF end as LAST_UPDATE FROM SECOND_TABLE SSS where ID_COMPTE= 11111111)
        order by LAST_UPDATE desc
        ) parentSelect WHERE ROWNUM BETWEEN 0 AND 2)), count(firstSelect) FROM firstSelect;

The purpose is to return the the last two rows with the count of all the rows of Table 1 and Table 2. The query without the count works fine it's just the count that cause problem, I don't know how to insert it. I tried also to use count() for each SELECT and SUM in the parent SELECT but it doesn't work.

Upvotes: 0

Views: 81

Answers (2)

EvilTeach
EvilTeach

Reputation: 28882

This concept should work for you. Basically you select the data you want in the with clause. Then in the main select, you select your data, and the count.

WITH
base AS
(
    SELECT 'TEST1' DATA FROM DUAL
    UNION ALL 
    SELECT 'TEST2' DATA FROM DUAL
    UNION ALL
    SELECT 'TEST3' DATA FROM DUAL
)
SELECT (SELECT COUNT(*) FROM base) AS KOUNT, base.*
FROM base
;

Upvotes: 1

Abhishek Sharma
Abhishek Sharma

Reputation: 360

You can use #Temp ( TempTable ). Insert or manupulate the rows in it which you want to have and finally return it from stored procedure.

Upvotes: 0

Related Questions