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

Thank you so much Sayan, a bunch of methods you provided are very nice. For about why I used UNION ALL originally, just know tableA LEFT JOIN tableB but how to handle NULL value is stucked by my mind, you're using nvl() to solve my confusion subtly.

Sayan Malakshinov <xt.and.r_at_gmail.com> 于2023年4月11日周二 22:52写道：

> 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