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

I haven't understood what exactly do you mean? Some most obvious variants:
https://dbfiddle.uk/RNfFICy-

• ANSI SQL complient: SELECT a.ts_name, a.used_size - coalesce(b.used_size,0) used_size FROM tableA a left join tableB b on a.ts_name = b.ts_name ORDER BY ts_name;
• Oracle native SQL syntax: SELECT a.ts_name, a.used_size - nvl(b.used_size,0) used_size FROM tableA a, tableB b where a.ts_name = b.ts_name(+) ORDER BY ts_name;
• using scalar subquery: SELECT a.ts_name, a.used_size - nvl((select b.used_size from tableB b where a.ts_name = b.ts_name),0) as used_size FROM tableA a ORDER BY ts_name;
• ANSI SQL complient lateral: SELECT a.ts_name, a.used_size - coalesce(v.used_size,0) used_size FROM tableA a outer apply ( select b.used_size from tableB b where a.ts_name = b.ts_name ) v ORDER BY ts_name;
• Oracle native SQL syntax - lateral: SELECT a.ts_name, a.used_size - nvl(v.used_size,0) used_size FROM tableA a, lateral( select used_size from tableB b where a.ts_name = b.ts_name )(+) v ORDER BY ts_name;

As you can see on DBFiddle all of the variants above return the same results as your query with UNION ALL in your case

On Tue, Apr 11, 2023 at 3:26 PM Quanwen Zhao <quanwenzhao_at_gmail.com> wrote:

> Sorry, I've no ChatGPT account to be aske a few oracle sql requirement.
>
> Maxim <mdemenko_at_gmail.com> 于2023年4月11日周二 21:14写道：
>
>> Well, for me as human with some experience in oracle dialect of sql this
>> task (and solution) look very trivial.
>> Couple of weeks ago i asked ChatGPT to give me recipe of fibonacci series
>> calculation in oracle sql and i was surprised to get the correct result,
>> the solution was using recursive with clause (either correct was its
>> solution using a model clause). Pretty optimistic i asked to rewrite a
>> query taken from
>> https://www.thegeekdiary.com/oracle-sql-script-to-report-the-list-of-files-stored-in-asm-and-currently-not-opened/
>> (a ASM related hierarchical query using connect by) into the same using
>> recursive with. After a round of 7-8 attempts i gave up - we moved in
>> circle beginning with syntactical mistakes, wrong column names and finally
>> working query but with wrong results and then again, syntactical mistakes
>> and wrong column names (i asked either to provide ddl definition of related
>> views, but ChatGPT ensured me its not required). Maybe i failed to proper
>> describe the task, but since that my opinion about its capabilities is
>> rather unpretentious. Said that, i should mention - i read recently a post
>> how ChatGPT helped to fix a relatively complex bug in a real world program
>> - there author states, he used paid version of ChatGPT and ensures - it is
>> much more powerful than a free one (it uses different engines either). If
>> somebody is interested - it can be read at
>> https://habr-com.translate.goog/ru/articles/727438/?_x_tr_sl=ru&_x_tr_tl=en&_x_tr_hl=de&_x_tr_pto=wapp&_x_tr_hist=true
>> Interesting (from my point of view) is evolving conversation between
>> author and chat
>>
>> Regards
>>
>> Maxim
>>
>> On Tue, Apr 11, 2023 at 12:45 PM Patrick Jolliffe <jolliffe_at_gmail.com>
>> wrote:
>>
>>> 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
>>>>>>
>>>>>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE
http://orasql.org

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