The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
User defined function to look up value of a table column using a value in another column
Hi,
I would like to write a function that would return the value of a particular column from a table using the value of another column as input.
Here are some rows from my table:
Key Value
1 Chicago
2 New York
3 Los Angeles
I want the function to return Chicago if I call myfunction(1), New York if I call myfunction(2) and so on ...
Can someone please guide me how to write such a function? I am using Vertica 8.0.1-3
Thanks,
Thomas
0
Comments
What is the use case for your function?
Why not simply use a sub-select as a column or use a table join?
Examples:
If you still want the function you could create a User Defined SQL Function:
Instead of maintaining the lookup data (i.e. cities) in a table, you have to maintain the function.
Note that a table join would perform much better than a function call.
If you upgrade to Vertica 8.1, the "Flattened Tables" feature might help you depending on your use case...
Example: