Re: How to complete the requirement by writing an oracle sql?

Thanks Patrick and Sayan! I've tested your method, very great and easy to understand! The most important point is simple but my approach is a bit complex initially.

create table tableA (ts_name varchar2(1), used_size number);
> insert into tableA values('a', 10);
> insert into tableA values('b', 20);
> insert into tableA values('c', 30);
> insert into tableA values('d', 40);
> insert into tableA values('e', 50);
> commit;
> create table tableB (ts_name varchar2(1), used_size number);
> insert into tableB values('a', 2);
> insert into tableB values('b', 6);
> insert into tableB values('e', 20);
> commit;

SELECT a.ts_name,
> a.used_size - b.used_size used_size
> FROM tableA a, tableB b
> WHERE a.ts_name = b.ts_name
> UNION ALL
> SELECT a.ts_name,
> a.used_size
> FROM tableA a
> WHERE a.ts_name NOT IN (SELECT b.ts_name FROM tableB b)
> ORDER BY ts_name;

T         USED_SIZE


> -          -----------------

> a          8

> b         14

> c         30

> d         40

> e         30

or:

SELECT a.ts_name,
> a.used_size - b.used_size used_size
> FROM tableA a, tableB b
> WHERE a.ts_name = b.ts_name
> UNION ALL
> SELECT a.ts_name,
> a.used_size
> FROM tableA a
> WHERE NOT EXISTS (SELECT 1 FROM tableB b WHERE b.ts_name = a.ts_name)
> ORDER BY ts_name;

T        USED_SIZE


> -          ----------------

> a          8

> b         14

> c         30

> d         40

> e         30

Best Regards
Quanwen Zhao

Patrick Jolliffe <jolliffe_at_gmail.com> 于2023年4月11日周二 18:43写道：

> Out of interest I pasted your question word for word into ChatGPT and this
> was the response I got. Fills me with hope and scares me a little.
>
> You can achieve the desired result by using a SQL join operation and
> performing the subtraction between the "USED_SIZE" and "USES_SIZE" columns
> of the two tables. Here's an example SQL query:
>
> SELECT
> COALESCE(a.TS_NAME, b.TS_NAME) AS TS_NAME,
> COALESCE(a.USED_SIZE, 0) - COALESCE(b.USES_SIZE, 0) AS USED_SIZEFROM
> table_A a
> FULL OUTER JOIN table_B b ON a.TS_NAME = b.TS_NAMEORDER BY
> TS_NAME;
>
> In this query, we use a FULL OUTER JOIN operation to combine the data from
> both tables, matching on the "TS_NAME" column. We use the COALESCE function
> to handle any NULL values that may result from the join operation.
>
> We then perform the subtraction between the "USED_SIZE" and "USES_SIZE"
> columns, using the COALESCE function again to handle any NULL values.
>
> Finally, we order the results by the "TS_NAME" column.
>
> This query should produce the desired result you described.
>
>
>
> Quanwen Zhao <quanwenzhao_at_gmail.com> escreveu no dia terça, 11/04/2023
> à(s) 01:46:
>
>> Thank you so much, I'll try it using the approach you suggested.
>>
>> Best Regards
>> Quanwen Zhao
>>
>> Sayan Malakshinov <xt.and.r_at_gmail.com> 于2023年4月10日周一 22:56写道：
>>
>>> Left join & size1-nvl(size2, 0)
>>>
>>>
>>>
>>> Best regards,
>>> Sayan Malakshinov
>>> Oracle performance tuning expert
>>> Oracle Database Developer Choice Award winner
>>> Oracle ACE
>>> http://orasql.org
>>>
>>> On Mon, 10 Apr 2023, 15:53 Quanwen Zhao, <quanwenzhao_at_gmail.com> wrote:
>>>
>>>> Hello there,
>>>>
>>>> I've a requirement about how to retrieve the final calc result from two
>>>> number of tables (A and B) from oracle database, such as:
>>>>
>>>> table A:
>>>>
>>>> TS_NAME USED_SIZE(MB)
>>>>> -------------- ------------------------
>>>>> a 10
>>>>> b 20
>>>>> c 30
>>>>> d 40
>>>>> e 50
>>>>
>>>>
>>>> table B:
>>>>
>>>> TS_NAME USES_SIZE(MB)
>>>>> --------------- ------------------------
>>>>> a 2
>>>>> b 6
>>>>> e 20
>>>>
>>>>
>>>> I expect to acquire the result like this:
>>>>
>>>> TS_NAME USED_SIZE(MB)
>>>> --------------- -------------------------
>>>> a 8
>>>> b 14
>>>> c 30
>>>> d 40
>>>> e 30
>>>>
>>>> How to use the SQL to finish it?
>>>>
>>>> Best Regards
>>>> Quanwen Zhao
>>>>
>>>

--
http://www.freelists.org/webpage/oracle-l