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

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