Hi! I'm seeking a way to check if a table exists in Oracle within the Alteryx Input tool. If the table exists, I aim to extract its data. Conversely, if it's absent, I intend to generate a field labeled "exists" containing the value 0.
Is this achievable? If not, selecting from a table, like 'xref_teachers,' in the event of its nonexistence would lead to an error. My primary goal is to append the pre-existing data from the table, if available, with the contents of a spreadsheet into the final result. As you see, this would work if the table already exists. In my workflow, it does not - so i would want to skip it over and not generate an error (i could filter out if a field called exists contains a 0.;
I provided a screenshot to further clarify what I am trying to achieve.
Thanks so much!
I've done this with other databases, but not in oracle, but if you're able to query the info_schema table/views (or equivalent in Oracle) have your initial input return a list of all the tables in the target DB.SCHEMA that you can then create logic that would ultimately create a SQL Statement that you can push into a Dynamic Input tool that will query the intended table if it exists or a fallback table if it doesn't.
From doing a quick Google search, it does look like you can query a VIEW to get a table list, but may require specific permissions to do so.