Database lookup returning no results

[redc]

I've set up a database lookup, but it's not returning any results; it should be returning 5 events. Here are the scenarios (and responses) I've tried (yes, some of them are pretty basic/dumb).

---

| lookup SMBkSalesCF

Error in 'lookup' command: Must specify one or more lookup fields.

---

| lookup SMBkSalesCF ValueCode

No results found.

---

| lookup SMBkSalesCF ValueCode ValueDesc

Error in 'lookup' command: All of the fields in the lookup table are specified as lookups, leaving no destination fields.

---

| lookup SMBkSalesCF ValueCode ValueDesc output MyFields

Error in 'lookup' command: Could not find all of the specified destination fields in the lookup table.

---

| lookup SMBkSalesCF ValueCode ValueDesc output ValueCode

No results found.

---

| lookup SMBkSalesCF ValueCode output ValueCode

No results found.

---

| lookup SMBkSalesCF ValueCode ValueDesc output ValueCode ValueDesc

No results found.

---

I've built this file statically as a CSV, and it looks like this:

ValueCode,ValueDesc
A,1-10
B,11-20
C,21-50
D,51-500
E,501+

Practical use-case scenario:

| dbquery MyDatabase "select MyField1, MyField2 from MyTable" | lookup SMBkSalesCF ValueCode as MyField2 OUTPUT ValueDesc

---

Sample data return expected:

MyField1 | MyField2 | ValueDesc
1        | A        | 1-10
2        | C        | 21-50
3        | C        | 21-50
4        | B        | 11-20
5        | E        | 500+

---

Actual returned (okay, having problems getting the table for the actual results to display, but basically, MyField1 and MyField2 match the expected results, but ValueDesc is empty):

MyField1 | MyField2 | ValueDesc
1 | A |
2 | C |
3 | C |
4 | B |
5 | E |

---

Maybe I'm misunderstanding how database lookups work, but I'm expecting to get the textual value (ValueDesc) for the code value (ValueCode/MyField2) for each of those records. Instead, I get zilch.

What am I doing wrong?

[redc · Accepted solution]

D'oh! The problem was that MyField2 is actually storing the "ValueKey" table value, NOT the "ValueCode" table value. "ValueKey" is numeric (1, 2, 3, 4, 5) and "ValueCode" is alpha (A, B, C, D, E). So of course, there was no match!

I added "ValueKey" to the lookup and changed the command to:

| dbquery MyDatabase "select MyField1, MyField2 from MyTable" | lookup SMBkSalesCF ValueKey as MyField2 OUTPUT ValueDesc

And got the expected results set.

[redc]

This is what the "Actual results returned" is supposed to be. For some reason, when I edit the question, it has this in there, but when you actually view the question, it's doing something completely different...

MyField1 | MyField2 | ValueDesc
1 | A |
2 | C |
3 | C |
4 | B |
5 | E |