Writing to Oracle DB and then updating a "Latest record" Flag

[Anvil]

Hi Team,

I'm struggling with implementing a "latest records flag"

In my workflow, I'm doing some transformations, appending a time stamp "Timestamp (Data Write)" and then writing to an Oracle DB (appending to an existing table filled with previous writes. This works fine, I have all my previous data there with the associated "Timestamp (Data Write)" time stamp

however I'm trying to use the Formula In-DB tool to update a column in the DB called "Latest Records Flag" after the latest time stamped dataset is appended so that I can update flag to show the latest records added

Can anyone help me write something that says

if latest datetime then 1 else 0?

CASE
WHEN "Timestamp (Data Write)" = max("Timestamp (Data Write)")
THEN '1'
ELSE '0'
END
GROUP BY "Timestamp (Data Write)"

Note: 

• "Timestamp (Data Write)" is datetime datatype
• writing to oracle DB

Im getting various error messages, but here is the latest

Formula In-DB (34) DataWrapOCI: Unable to prepare the query: "WITH "Tool19_3af8" AS (SELECT * FROM "REPO_PRODFAM") SELECT "MDMS.Product Family Number", "MDMS.Product Family Name", "MDMS.Global Product Family", "MDMS.Active Substance Name", "MDMS.Introduction Year", "MDMS.Product Short Code", "MDMS.Disease Area Number", "MDMS.Product Family Status", "OMP.Product Family Number", "OMP.Product Family Name", "OMP.OMP Scenerio ID", "OMP.Molecule Type", "OMP.Responsible Planner", "OMP.RoE Factor", "OMP.Product Segmentation", "OMP.Sourcing", "OMP.EU PoL", "OMP.EU Launch Date", "OMP.US PoL", "OMP.US Launch Date", "OMP.Firewall Authorization", "Timestamp (Data Extraction)", "Run by (Data Extraction)", "Run by (Data Write)", "Timestamp (Data Write)", "Product Family.Field 1", "Product Family.Field 2", "Product Family.Field 3", "Product Family.Field 4", (CASE ¶WHEN "Timestamp (Data Write)" = max("Timestamp (Data Write)")¶THEN '1'¶ELSE '0' ¶END¶GROUP BY "Timestamp (Data Write)"¶) AS "Latest Records Flag" FROM "Tool19_3af8"" Error: ORA-00907: missing right parenthesis¶¶

[gabrielvilella]

You need two separated formula tools. One with the max("Timestamp (Data Write)") then another one with the CASE but now referencing the field you created before for the max.

CASE
WHEN "Timestamp (Data Write)" = "FIELD_NAME"
THEN '1'
ELSE '0'
END

[Anvil]

Hi Gabriel,

It looks like the issue is to do with the aggregate as you mentioned (also here:https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Formula-is-not-working-with-In-DB/td-p... )

I've tried with a summarize and join, but that also behaves badly - so I'm trying it your way, but the first formula to calculate the max date is throwing an error, asking for a grouping, but when I add an arbitrary one to test, its saying that its missing a right parenthesis, but I have no left one

To calculate a column Max normally one doesn't need to specify a group by but Alteryx is forcing this.

max ("Timestamp (Data Write)") group by "MDMS.Product Family Number"

Any tips?

[gabrielvilella]

Sorry I forgot to mention that I only got that working only with the full statement: Select Max("FieldName") from Table. And this cannot be within a Case statement. Unfortunately, I don't have an Oracle environment here for testing, only MS SQL Server.