Skip to Main Content

Integration

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Video: Orchestration vs. Choreography | Kelly Goetsch

Bob Rhubart-OracleAug 17 2016 — edited Aug 17 2016

The new era of IT is characterized by distributed components, loose coupling, and other factors that conspire against the long-established concept of orchestration. In this 2 Minute Tech Tip, recorded in March 2016, Kelly Goetsch discusses how the concept of "choreography" offers an approach to managing workflow that is more microservices- and cloud-friendly.

Want more 2 Minute Tech Tips? Click here.

Want to star in your own 2 Minute Tech Tip? Contact bob.rhubart@oracle.com

https://www.youtube.com/watch?v=v1MS2Eas1qo

Click the YouTube icon in the video frame for full-screen viewing.

Comments

SomeoneElse

> Is there an other functionality in PL/SQL

To do what?  What are your requirements?

Nag Aswadhati

Please provide your queries and code where you are facing exact problem.

EdStevens

user10316500 wrote:

Hi

I tried using PIVOT functionality in PL/SQL 11g but it's too limited for my queries.

Maybe, maybe not.  We can't know until we see your tables, sample data, and actual requirements.

And PIVOT is a SQL option.  Not PL/SQL. 

user10316500

These are 2 examples :

1.png

2.png

BluShadow

Please read:

and post some example data in a format we can use to demonstrate a solution (e.g. create table and insert statements)

Having a screen dump of a load of figures without any explanation or logic is no use to anybody.

user10316500

There are many tables: it's difficult for me describing all tables.

I'm searching only an alternative (if exists).

At the moment I did a query only for this table (for the 2 tables above no):

3.png

with vista

as (select /*+ parallel(cs01,4) materialize*/

*

from hub.report_cs01_condiviso cs01

where 1 = 1

and id_messaggio is not null

and (case

when cs01.flusso_scarto is null

then

'Verifica HUB'

when cs01.flusso_scarto like 'PEAT%'

then

'Letture Peat'

when (cs01.flusso_scarto like 'CONSUMI_GEE_LC_T500%')

or (cs01.flusso_scarto like 'LETTURE_EE_T510%'

and cs01.processo_etl is null)

then

'Letture'

else

'Processi'

end) = 'Processi')

select /*+ cardinality(tb 100000) leading(tb) */

case

when trim (tb.stream) = 'CS01 FA' then '[FA] - Fuori Ambito'

else case when tbs.ambito is not null then '[CFA] - Candidato Fuori Ambito' else '[IA] - In Ambito' end

end

kpi_ambito_reale,

sum (decode (tb.owner, 'ACN', 1, 0)) as ACN,

sum (decode (tb.owner, 'ACN<HUB', 1, 0)) as ACN_MIN_HUB,

sum (decode (tb.owner, 'ENG<ETL', 1, 0)) as ENG_MIN_ETL,

sum (decode (tb.owner, 'ENG<ETL<ACN', 1, 0)) as ENG_MIN_ETL_MIN_ACN,

sum (decode (tb.owner, 'ETL', 1, 0)) as ETL,

sum (decode (tb.owner, 'ETL<HUB', 1, 0)) as ETL_MIN_HUB,

sum (decode (tb.owner, 'HUB<ETL', 1, 0)) as HUB_MIN_ETL,

sum (decode (tb.owner, null, 1, 0)) as "NULL",

--

sum (decode (tb.owner, 'ACN', 1, 0)) +

sum (decode (tb.owner, 'ACN<HUB', 1, 0)) +

sum (decode (tb.owner, 'ENG<ETL', 1, 0)) +

sum (decode (tb.owner, 'ENG<ETL<ACN', 1, 0)) +

sum (decode (tb.owner, 'ETL', 1, 0)) +

sum (decode (tb.owner, 'ETL<HUB', 1, 0)) +

sum (decode (tb.owner, 'HUB<ETL', 1, 0)) +

sum (decode (tb.owner, null, 1, 0))

as TOTALE_COMPLESSIVO

from vista tb,

eni_it_tmp_cs01_problem_999 tbs,

(select /*+ parallel(dtsc,4) use_nl(dtsc tdsk ana)*/

dtsc.id_messaggio,

ana.*,

tdsk.tdsk_data_invio_mail_sfac,

tdsk.tdsk_data_invio_mail_eni,

tdsk.tdsk_problem_id

from eni_tavolo_etl_dett dtsc, ENI_TAVOLO_DET_REL_ANA tdsk, ENI_TAVOLO_ETL_ANALISI ana

where dtsc.dtsc_tavolo_dett_id = tdsk.dtsc_tavolo_dett_id(+)

and tdsk.tan_tavolo_analisi_id = ana.tan_tavolo_analisi_id(+)

and dtsc.dtsc_tavolo_dett_id = dtsc.dtsc_storico_id

and dtsc.dtsc_data_fin = to_timestamp ('31-DIC-9999 00:00:00')

and dtsc.dtsc_flag_ann = 'N') tdd,

eni_crusc_utenti uo

where tbs.id_messaggio(+) = tb.id_messaggio

and tdd.id_messaggio(+) = tb.id_messaggio

and uo.ecu_utente_id(+) = tdd.tan_creator_owner

group by rollup (case

when trim (tb.stream) = 'CS01 FA' then '[FA] - Fuori Ambito'

else case when tbs.ambito is not null then '[CFA] - Candidato Fuori Ambito' else '[IA] - In Ambito' end

end)

order by 1 nulls last;

BluShadow

Well, that's fine giving us the query you're currently using, but we need tables to run it against.

How can anybody understand your issue and be able to write code for you to solve it, if they cannot run your current query.

We don't have your tables.  We don't have your data (we only need a sample to illustrate the issue).  We don't have your knowledge of what you are trying to achieve.

The best way to get answers to your issues is to provide people will an example of what the issue is, explaining the logic you want, and supplying some example data that people can use to replicate the issue and thus provide solutions.

EdStevens

user10316500 wrote:

There are many tables: it's difficult for me describing all tables.


We don't need all of your tables.  Just enough to demonstrate the general problem and general solution.

Sven W.
Answer

Your task simply seems to involve a matrix like visualization of data.

SQL is very strong at data processing. However it is not the best "language" to do visualizations.

I suggest you rethink your application modules.

Let SQL fetch the data in a normalized way.

Then use some kind of reporting tool (Apex, Cognos, Excel, ... ) to visualize the data in a matrix.

Marked as Answer by user10316500 · Sep 27 2020
1 - 9

Post Details

Added on Aug 17 2016
0 comments
742 views