SQL oracle: How can I get the results of 4 selects statements added & subtracted to get one value with a 'WITH' keyword

oracle sql select statement examples
how to store select query result in variable in oracle
how to assign select statement value to a variable in oracle
oracle select all rows from table
how to get column names of a table in oracle query
oracle select * from table
how select statement works in oracle
how to select single row from multiple records in oracle

My code is:

WITH
A_EXP AS (SELECT SUM(VENDOR_ORDER_TOTAL) AS VOT
          FROM VENDOR_ORDER
          WHERE VENDOR_ORDER_STATUS = 'F'
          AND VENDOR_ORDER_DATE >= TO_DATE ('01 SEPTEMBER 2019', 'DD MONTH YYYY')
          AND VENDOR_ORDER_DATE <= TO_DATE ('30 SEPTEMBER 2019', 'DD MONTH YYYY')),

B_EXP AS (SELECT SUM(SALARY_PAID) AS SP
          FROM EMP_SALARY_INFO
          WHERE FROM_DATE >= TO_DATE ('01 SEPTEMBER 2019', 'DD MONTH YYYY')
          AND TILL_DATE <= TO_DATE ('30 SEPTEMBER 2019', 'DD MONTH YYYY')),

C_EXP AS (SELECT ROUND((COUNT(P.PAY_TYPE) * 0.10), 2) AS PT
          FROM CUSTOMER_PAYMENT P INNER JOIN CUSTOMER_ORDER O
          ON P.ORDER_ID = O.ORDER_ID
          WHERE P.PAY_TYPE = 'CARD'
          AND O.ORDER_DATE >= TO_DATE ('01 SEPTEMBER 2019', 'DD MONTH YYYY')
          AND O.ORDER_DATE <= TO_DATE ('30 SEPTEMBER 2019', 'DD MONTH YYYY')),

D_INC AS (SELECT SUM(ORDER_PRETAX) AS OP
          FROM CUSTOMER_ORDER
          WHERE PAY_STATUS = 'PAID'
          AND ORDER_DATE >= TO_DATE ('01 SEPTEMBER 2019', 'DD MONTH YYYY')
          AND ORDER_DATE <= TO_DATE ('30 SEPTEMBER 2019', 'DD MONTH YYYY'))

SELECT (D_INC - (A_EXP + B_EXP + C_EXP)) AS "$$ PROFIT/LOSS - SEPT 2019"
FROM A_EXP, B_EXP, C_EXP, D_INC

The result of each select statement in the with clause returns one value. I am trying to add the expenses and subtract it from the income. When I run the whole code I am getting some error which I am unable to figure out. Am I doing something wrong? I want the result from the aliases in this format [D_INC - (A_EXP + B_EXP + C_EXP)] and I want the result to be one value, i.e.,

EXPECTED OUTPUT:

 $$ PROFIT/LOSS - SEPT 2019
 --------------------------
          -1453.84

Please use below query , in last statement of your query I have just used column name instead of cte name .

WITH A_EXP
AS (
    SELECT SUM(VENDOR_ORDER_TOTAL) AS VOT
    FROM VENDOR_ORDER
    WHERE VENDOR_ORDER_STATUS = 'F'
        AND VENDOR_ORDER_DATE >= TO_DATE('01 SEPTEMBER 2019', 'DD MONTH YYYY')
        AND VENDOR_ORDER_DATE <= TO_DATE('30 SEPTEMBER 2019', 'DD MONTH YYYY')
    )
    ,B_EXP
AS (
    SELECT SUM(SALARY_PAID) AS SP
    FROM EMP_SALARY_INFO
    WHERE FROM_DATE >= TO_DATE('01 SEPTEMBER 2019', 'DD MONTH YYYY')
        AND TILL_DATE <= TO_DATE('30 SEPTEMBER 2019', 'DD MONTH YYYY')
    )
    ,C_EXP
AS (
    SELECT ROUND((COUNT(P.PAY_TYPE) * 0.10), 2) AS PT
    FROM CUSTOMER_PAYMENT P
    INNER JOIN CUSTOMER_ORDER O ON P.ORDER_ID = O.ORDER_ID
    WHERE P.PAY_TYPE = 'CARD'
        AND O.ORDER_DATE >= TO_DATE('01 SEPTEMBER 2019', 'DD MONTH YYYY')
        AND O.ORDER_DATE <= TO_DATE('30 SEPTEMBER 2019', 'DD MONTH YYYY')
    )
    ,D_INC
AS (
    SELECT SUM(ORDER_PRETAX) AS OP
    FROM CUSTOMER_ORDER
    WHERE PAY_STATUS = 'PAID'
        AND ORDER_DATE >= TO_DATE('01 SEPTEMBER 2019', 'DD MONTH YYYY')
        AND ORDER_DATE <= TO_DATE('30 SEPTEMBER 2019', 'DD MONTH YYYY')
    )
SELECT (OP - (VOT + SP + PT)) AS "$$ PROFIT/LOSS - SEPT 2019"
FROM A_EXP,B_EXP,C_EXP,D_INC

4 Selecting Table Data, A query, or SQL SELECT statement, selects data from one or more tables or views. For instructions, see "Running Queries in SQL Developer". Note: When the result of a query is displayed, records can be in any order, unless you specify � Although two SQL statements may produce the same result, Oracle may process one faster than the other. You can use the results of the EXPLAIN PLAN statement to compare the execution plans and costs of the two statements and determine which is more efficient. This example shows the execution plans for two SQL statements that perform the same


You can use this query for the expected result:

SELECT (OP - (VOT + SP + PT)) AS "$$ PROFIT/LOSS - SEPT 2019"
    FROM A_EXP, B_EXP, C_EXP, D_INC

The UNION [ALL], INTERSECT, MINUS Operators, If a SQL statement contains multiple set operators, then Oracle Database but any query selects values of type BINARY_FLOAT , then the returned values have a column alias for the expression in order to refer to it in the order_by_clause . UNION Example The following statement combines the results of two queries� Parameter Description; SQL VERB: SQL VERB refers to one of the keywords: DELETE, INSERT, MERGE, SELECT, or UPDATE.You embed a statement level optimizer hint in comment syntax and if the comment syntax contains a statement level optimizer hint, the comment syntax must follow the SQL VERB.


Your problem is that you don't specify the column names in the final select statement. Which generate the strange error statement if you specified the columns names your query should run. Here is my trial:

with
    a_exp as ( select 2 as vot)
    ,b_exp as (select 3 as sp)
    ,c_exp as (select 1 as pt)
    ,d_exp as (select 10 as op)
    select (d_exp.op - (a_exp.vot +b_exp.sp+c_exp.pt))
    from a_exp,b_exp,c_exp,d_exp

Getting Answers with SELECT, The goal of writing a SQL query is usually to get the answer from the database to a When you include SELECT statements in programmatic blocks of code ( which When you want to obtain the results for a single statement, place your cursor� Is there any way get these 3 sql statements ff I run a query against system views (before end of this transaction) Another question on sql_trace is: You are correct, I can get all sql statements for a session by using sql_trace. Can I use any trace or other mechanism to get all sql statement in the executed order?


SELECT INTO Statement, For a full description of the SELECT statement, see Oracle Database SQL Reference. BULK COLLECT INTO ), this statement retrieves an entire result set at once. Anything that can follow the FROM clause in a SQL SELECT statement � The condition in the WHERE clause can be any SQL condition (for information about SQL conditions, see Oracle Database SQL Language Reference). The query in Example 4-3 selects data only for employees in department 90.


Querying and Manipulating Data, On the right-hand side of the Oracle SQL Developer window, click the Data tab. A top-level SELECT statement returns results of a query, and a query nested within Example 2-4 uses a SELECT statement to return the columns with column Example 2-11 shows how the REGEXPR_LIKE expression selects rows where� JSON Query Node. SQLDev/Oracle Data Miner 4.1 added a new JSON Query node that allows users to query JSON data as relational format. In additional, the current Data Source node and Create Table node are enhanced to allow users to specify JSON data in the input data source either from a Database table/view or an External Table that has been mapped to the Oracle Big Data Appliance.


Querying for and Displaying Data, Adding Dynamic Content to the JSP Page: Database Query Results Define your SQL statements with the methods available for the connection object. Compile the JavaClient.java file to check for compilation errors. This statement selects all employees whose names include the sequence of characters that the user� 2. This introduces the possibility of sql injection. When I review an Apex application, I do an export of the application, remove all spaces and search for '||: If I see that string, I assume there is a problem with the application until someone can prove it isn't a problem. Anton