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

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
>>>>
>>>

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