Hello!
I’m currently working on creating my first portal2 in Novacura, and I’m attempting to populate a table with data via a machine step. My goal is to utilize the PIVOT function in Oracle, but unfortunately, I’m encountering some challenges. Could you provide any guidance or assistance?
Is it posible to use the pivot function in a machine step?
Hi,
That should work as the portlet should be pretty agnostic to what the workflow (data source) provides it.
If you start at the db level, do you get the Pivot function to work (in PL)?
And from there, can you make it work in a user workflow, for example in a data grid?
Pivot is a pretty common solution, I found one old example here which is portal 1 and MS SQL server, so maybe not be the optimal example for you, but it shows that it works.
An issue though is that you need to have the pivoted columns (months in the example above) hard-coded. The table portlet (nor others) does not dynamically add columns based on the result of the query.
Thank you for the quick response. Initially, the query worked perfectly at the database level. However, I’m currently attempting to integrate it into a user workflow, and I’m encountering an issue where it consistently complains about a ‘missing alias.’ I’ve ensured that I’ve provided aliases for all the relevant queries. Any insights on why this might be happening would be greatly appreciated.
Another question that pop up,When a user is running the portal and we utilize the ‘Data source’ functionality to connect to a database, the question arises: can we directly retrieve data from the current user within the database, or is it necessary to implement a workflow to facilitate this process?
You need to wrap the aliases with double quotations. Select Col “Col” from table
The credentials are set on the connector
Hi,
flow machine steps (for multiple records) are sometimes stubborn regarding aliasing 
So you cant just use select * in them (as you can for single record), you must actually provide aliases to be used in the output. (and always wrap aliases with double quotes)
So this would work:
(and i can see Ola responded to the current user part of the question already (he truly is the fastest gun…i mean…helping hand…of the north ))
Hope this helps!
B R
Ivan
Thanks for the quick responses @OlaCarlander and @ivstde , really apreciate it. This query , which involves working with the pivot function, was the one I struggled with during a machine step. However, with your help, I’ve managed to overcome most of the challenges. Thank you very much!
But now that I found a way to get the inlogged user with data source → Database SO I’m gonna go with that instead. This part I refer to = ‘:UserName’
SELECT
*
FROM (
SELECT
ifsapp.activity_api.Get_Activity_Responsible(activity_seq) “Ansvarig”,
a.name “Namn”,
ifsapp.activity_api.Get_Short_Name(a.activity_seq) “Kortnamn”,
a.status AS “Status”,
a.activity_seq “Aktivitetsekven”,
a.task_id “Uppgiftsid”
FROM ifsapp.ACTIVITY_TASK_EXT2 a
WHERE
ifsapp.activity_api.Get_Activity_Responsible(activity_seq) = ‘:UserName’
AND a.project_id = ‘7700’
AND a.activity_no = ‘010’
AND a.sub_project_id LIKE ‘400%’
AND a.activity_no IS NOT NULL
AND a.early_start > ‘2023-10-01’
AND a.status IN (0, 1)
AND a.project_id IN (
SELECT project_id
FROM ifsapp.activity b
WHERE b.objstate = ‘Released’
AND upper( b.C_PROCESS_FLOW ) <> upper( ‘Fakturerad’)
AND trunc(b.date_created) >= ‘2023-01-01’
AND a.activity_no = b.activity_no
)
)
PIVOT (
MAX(“Status”),
MAX(“Uppgiftsid”)
FOR “Namn” IN (
‘1.01.1 - Platsbesök Bokad & DK Signal 1’ as “task1”,
‘1.01.2 - Platsbesök’ as “task2”,
‘1.02.1 - Slot-Begäran Skickad’ as “task3”,
‘1.02.2 - Slot-Begäran Godkänd’ as “task4”,
‘1.03.1 - FBL & DK Signal 2’ as “task5”,
‘1.04.1 - GVB’ as “task6”,
‘1.05.1 - Mängdning’ as “task7”,
‘1.06.1 - AMP’ as “task8”,
‘1.07.1 - Arbetshandlingar Skapade’ as “task9”,
‘1.07.2 - Arbetshandlingar Delade’ as “task10”,
‘1.08.1 - Materiel Beställt’ as “task11”,
‘1.08.2 - Materiel Levererat’ as “task12”,
‘1.09.1 - Tillstånd Ansökt’ as “task13”,
‘1.09.2 - Tillstånd Godkänd & DK Signal 4’ as “task14”,
‘1.10.1 - Avtal Fastighetsägare Ansökt’ as “task15”,
‘1.10.2 - Avtal Fastighetsägare Godkänd & DK Signal 5’ as “task16”,
‘1.11.1 - TA-plan Ansökt’ as “task17”,
‘1.11.2 - TA-plan Godkänd’ as “task18”,
‘1.12.1 - Utförande Resurser Bokade’ as “task19”,
‘2.01.1 - Schakt & DK Signal 6’ as “task20”,
‘2.02.1 - Geodetisk Mätning Beställd’ as “task21”,
‘2.02.2 - Geodetisk Mätning Mottagen’ as “task22”,
‘2.03.1 - Dagböcker’ as “task23”,
‘2.04.1 - Dokumentation’ as “task24”,
‘2.05.1 - Fakturor’ as “task25”,
‘2.06.1 - Återställning Beställd’ as “task26”,
‘2.06.2 - Återställning G01-order Begärd’ as “task27”,
‘2.07.1 - Avsluta Aktivitet & Delprojekt’ as “task28”
)
)
ifsapp.activity_api.Get_Activity_Responsible(activity_seq) = ‘:UserName’
A tip from another thread improved my code, ty.
