03-19-2018 03:13 PM
I have a table with FAR_ID and REFERRED_FAR_ID.
I need to update every FAR_ID with most recent REFERRED_FAR_ID
It can be done in first iteration, but it can also require several rounds until most recent is found
FAR_ID REFERRED_FAR_ID 1 3 3 5 5 6
The result I need is:
FAR_ID REFERRED_FAR_ID 1 6
Oracle SQL code that does the job is:
SELECT CONNECT_BY_ROOT FAR_ID AS OLD_FAR, FAR_ID AS NEW_FAR, ADRESSE_ID AS ADDRESS_ID, ADDRESS_STATUS, current_date AS LAST_UPDATE FROM FAR_TABLE WHERE REFERRED_FAR_ID = 0 CONNECT BY NOCYCLE PRIOR REFERRED_FAR_ID = FAR_ID
Is there a simple way of setting this in SAS DI Studio?
Thanks in advance!
03-20-2018 03:12 AM
03-20-2018 03:59 AM
If both your source and target table reside in Oracle and given that the Oracle SQL syntax extension makes this task so beautifully simple: I'd go for explicit pass-through SQL and I'd implement without hesitation via user written code or if used several times via a custom transformation. I'd still map on metadata level source to target to support metadata level data lineage and impact analysis.
How much of the generated mapping information you then actually use in your code will depend on how much effort you want to invest. If it was me: For user written code I'd only use the libref metadata based, for a custom transformation I'd consider to implement as metadata driven as possible but make the call to what length I'm going based on how many times I'd expect to use the custom transformation and how much change to mapping/my DIS job I expect in the next 3 years or so.
03-21-2018 07:43 AM
I plan to do so if there is not other (clever) way.
This is only one of the use-cases I have in regards to hierarchical queries which can be fairly easily done in Oracle, but I am missing the method in SAS DI.
03-21-2018 07:58 AM
There isn't an OOTB DIS transformation which generates such Oracle SQL for you. You can always create your own reusable custom transformation though (will require good SAS macro coding skills) or you just go for user written code.
I can think of ways how to program this in SAS syntax but nothing will be as simple as what Oracle offers with it's ready made syntax for such use cases - unless there is some SAS procedure I don't know of in the network analytics space which could be used for this.