Using UDF output string in SQL SELECT Query?

1
votes

I have a user-defined function (in Microsoft Access) that takes a value from two fields and returns a string, which corresponds to a column name.

This might not seem very clear; below is a simplified example of what I'm trying to do. Unfortunately I can't use the real problem as it's confidential.

Input:

 Num1 | Num2 | Field1 | Field2 | Field3 | Field4 | Field5

 .....|......|........|........|........|........|.......

 .....|......|........|........|........|........|.......

 .....|......|........|........|........|........|.......

 SQL query: "SELECT Num1,Num2,Func(Num1,Num2)

Output:

Num1 | Num2 | FieldVal

In the output, FieldVal can be the value of Field1,Field2,Field3,Field4, or Field5, depending on Num1 and Num2. As of now I have a user defined function that determines which field's being used(1,2,3,4,or 5) based on two inputs (Num1 and Num2).

I guess what I'm trying to say is that I want to query a different column depending on Num1 and Num2.

2
sqlms-accessvba
What's the question? – ChipsLetten
Why not input Field1 to Field5 into Func() and have it determine which one of those to return? – Tim Williams
@TimWilliams Because the data in fields 1 to 5 is independent of the field that needs to be returned (the selection of field is entirely dependent on Num1 and Num2) – ar1994
Maybe you've oversimplified your real use case, but I'm not seeing why you can't perform the selection of Fieldx inside your function, based on Num1 and Num2. It's difficult to offer suggestions based on the information you provide: for example we have no idea what logic is used in your function to evaluate Num1 and Num2 and thus select a field to return. – Tim Williams

2 Answers

1
votes

You could use a switch statement, like:

select  switch(
        Func(Num1,Num2) = 1, Field1,
        Func(Num1,Num2) = 2, Field2,
        Func(Num1,Num2) = 3, Field3,
        ...)
2
votes

You could use the IIF function, for example:

SELECT Num1, Num2, IIF(NUM1 < NUM2, Field1, IIf(NUM1 = NUM2,Field1, Field2))

I've used operations NUM1 < NUM2 and NUM1 = NUM2 as examples, you could use any other operation as long as the result is boolean.

Note that I only detailed two nested IIF but you can nest as many as you need.

Read up for more: http://www.techonthenet.com/access/functions/advanced/iif.php