How to Have Multiple "With As" in Single SQL - Oracle SQL

Can we have multiple WITH AS in single sql - Oracle SQL

You can do this as:

WITH abc AS( select
             FROM ...)
, XYZ AS(select
         From abc ....) /*This one uses "abc" multiple times*/
  Select 
  From XYZ....   /*using abc, XYZ multiple times*/

SQL: Multiple select statements in one query

You appear to want to LEFT JOIN the firends and party tables and then use conditional aggregation:

SELECT dayBirth,
       TO_CHAR(dayBirth, 'FMDAY', 'NLS_DATE_LANGUAGE=English') AS day,
       COUNT(p.idmother)
         AS totalFriendsForParty,
       COUNT(p.idmother) / COUNT(*) * 100
         AS totalFriendsForPartyPercent,
       COUNT(CASE WHEN p.idmother IS NULL THEN 1 END) AS totalFriendsNoParty,
       COUNT(CASE WHEN p.idmother IS NULL THEN 1 END) / COUNT(*) * 100
         AS totalFriendsNoPartyPercent 
FROM   tblFriends f
       LEFT OUTER JOIN tblIsAssignedParty p
       ON (f.idmother = p.idmother)
GROUP BY dayBirth

Which, for the sample data:

CREATE TABLE tblFriends (id, idmother, dayBirth) AS
SELECT 1, 1, DATE '2021-09-09' FROM DUAL UNION ALL
SELECT 2, 2, DATE '2021-09-09' FROM DUAL UNION ALL
SELECT 3, 3, DATE '2021-09-11' FROM DUAL UNION ALL
SELECT 4, 3, DATE '2021-09-11' FROM DUAL UNION ALL
SELECT 5, 4, DATE '2021-09-07' FROM DUAL;

CREATE TABLE tblIsAssignedParty (idMother, codeParty, price) AS
SELECT 1, 231, 15 FROM DUAL UNION ALL
SELECT 2, 645, 28 FROM DUAL UNION ALL
SELECT 3, 164, 33 FROM DUAL;

Outputs:







































DAYBIRTHDAYTOTALFRIENDSFORPARTYTOTALFRIENDSFORPARTYPERCENTTOTALFRIENDSNOPARTYTOTALFRIENDSNOPARTYPERCENT
09-SEP-21THURSDAY210000
11-SEP-21SATURDAY210000
07-SEP-21TUESDAY001100

Inserting multiple rows in a single Oracle SQL query:

You where close, but you have much to learn.

Here is how you could do it:

INSERT INTO "SCOTT"."GREATCOLOR1" (COLOR, PAUL, JOHN, TIM, ERIC)
          select 'White', '1', '5', '1', '3' from dual
union all select 'Yello', '8', '4', '3', '5' from dual
union all select 'Black', '2', '2', '9', '1' FROM dual
;

Best way to do multi-row insert in Oracle?

This works in Oracle:

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
          select 8000,0,'Multi 8000',1 from dual
union all select 8001,0,'Multi 8001',1 from dual

The thing to remember here is to use the from dual statement.

Can I divide an amount across multiple parties and round to the 'primary' party in a single SQL query?

So you can use analytical functions to get what you are looking for. As I didn't know your exact structure, this is only an example:

SELECT s.party_id, s.member_id, 
       s.portion + DECODE(s.prime, 1, s.total - SUM(s.portion) OVER (PARTITION BY s.party_id),0)
FROM (SELECT p.party_id, p.member_id, 
             ROUND(a.amt*(p.split/100), 2) AS PORTION,
             a.amt AS TOTAL, p.prime
      FROM party p
      INNER JOIN amount a ON p.party_id = a.party_id) s

So in the query you have a subquery that gathers the required information, then the outer query puts everything together, only applying the remainder to the record marked as prime.

Here is a DBFiddle showing how this works (LINK)

N.B.: Interestingly in the example in the DBFiddle, there is a 0.01 overpayment, so the primary actually pays less.

pair two rows together in sql oracle 10g

This can be achieved using a CASE statement and the LEAD analytic function to see if the next ID is Appendix.

Query

--This is to set up the sample data
WITH
    emp_agreements (id, emp_id, agreement_type)
    AS
        (SELECT 1, 1023, 'Basic' FROM DUAL
         UNION ALL
         SELECT 2, 1023, 'Appendix' FROM DUAL
         UNION ALL
         SELECT 3, 1023, 'Basic' FROM DUAL
         UNION ALL
         SELECT 4, 1023, 'Basic' FROM DUAL
         UNION ALL
         SELECT 5, 1023, 'Basic' FROM DUAL
         UNION ALL
         SELECT 6, 1023, 'Appendix' FROM DUAL)
--Real query begins here. You will need to put in your real table name
  SELECT emp_id, status
    FROM (SELECT id,
                 emp_id,
                 agreement_type,
                 CASE LEAD (agreement_type) OVER (PARTITION BY emp_id ORDER BY id)
                     WHEN 'Appendix' THEN 'Active'
                     ELSE 'Pending'
                 END    AS status
            FROM emp_agreements)
   WHERE agreement_type = 'Basic'
ORDER BY id;

Result

   EMP_ID     STATUS
_________ __________
     1023 Active
     1023 Pending
     1023 Pending
     1023 Active

SQL multiple count for different condition as new column

Just use a CASE statement of inline if to isolate the values you want in each column, this is a PIVOT or de-normalising technique without the overheads of setting up the PIVOT clause

SELECT table1.ID, table1.FLAG
 ,COUNT(CASE WHEN table2.FLAG = 'Active' THEN 1 END) as "Active"
 ,COUNT(CASE WHEN table2.FLAG = 'Suspended' THEN 1 END) as "Suspended"
 ,COUNT(CASE WHEN table2.FLAG = 'Other' THEN 1 END) as "Other"
FROM table1
INNER JOIN table2 ON table1.ID = table2.ID
WHERE table1.ID = 123
GROUP BY table1.ID, table1.FLAG

NOTE: SUM could have been used in place of count here, count works because the missing ELSE statement will resolve in a null, and Count will not count the nulls.


Related Topics

Confused About Itzik Ben-Gan's Logical Query Processing Order in His SQL Server 2005 Book and SQL Server 2008 Book

Teradata Equivalent for Lead and Lag Function of Oracle

SQL Update Top with Order By

Sql: Select Most Recent Date for Each Category

Casting Scientific Notation (From Varchar -> Numeric) in a View

Oracle SQL - Sum and Group Data by Week

Postgresql Nested Inserts/Withs for Foreign Key Insertions

SQL Server 2008 Express Concat() Doesn't Exist

How to Generate (Or Get) a Ddl Script on an Existing Table in Oracle? I Have to Re-Create Them in Hive

The Argument 1 of the Xml Data Type Method "Value" Must Be a String Literal

Ignore Certain Columns When Using Bulk Insert

Ms SQL Server Pivot Table with Subquery in Column Clause

Generate a Unique Column Sequence Value Based on a Query Handling Concurrency

Recursive Subquerying with Sorting

Determine Latest Row Added When No Index Is Present

Using of Rownum Function with ">" Sign in Oracle

"Pivot" Table Oracle - How to Change Row Items into Columns

How to Find the .Net Framework Version Used in an Ssis 2008 R2 Package


Leave a reply



Submit