I am in need of some assistance/advice please.
I have a query in MSSQL as an openquery to a Postgres Linked Server; a CASE statement actually, in a query that allowed me to check to see if a value in a column could be cast as 'FLOAT' and if not, return a '1'.
I need to be able to use something like this as this column ('OLD_LABEL') contains user entered data that isn't standardized (values range from 'user' to 'North' to NULL to 12345 to 'SET1024'). So to pull back records that have a specific value (to not break automation, I need REAL numbers ranging from '0' to '9999.995') I used the below:
CASE WHEN OLD_LABEL IS NULL THEN ''0'' WHEN try_cast(OLD_LABEL AS FLOAT) IS NULL THEN ''1'' ELSE OLD_LABEL END AS NEW_LABEL
However, it was moved to a Postgres db and now my case expression and subsequent automation won't work (since try_cast is a built in function to MSSQL). What I want to do is pretty simple in theory - however I am not familiar with Postgres enough to come up with a solution on my own
How do I say this as when value isn't a recognizable/real number in the column 'OLD_LABEL' then set/replace value to '1'
The caveat is that when the existing value in 'OLD_LABEL' is not a 0 or NULL, and is an actual number then I need to keep/use that number in the 'NEW_LABEL' column - otherwise replace that value with a '1'
Is there a way to do this? Additionally - I only have 'READ' access to the Postgres DB, so anything that would require table creation, functional changes to the DB, etc are out of my reach (and no way to get the permissions to do so as I don't 'own' the DB I am querying) - I do have admin access to the MSSQL DB I am running the openquery from however.