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

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

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