Custom Grouping in Toad Visual Query Builder

Toad Data Point & Toad Intelligence Central
1

Hi,

I am using Toad Visual query builder to build .TSM filed for our users. I am unable to find any custom grouping functions/mechanism available for this. Please go through the below example

Suppose i have a column (say EMP_ID) with the distinct values (1,2,3,4,5), i need a to have a new column(EMP_NICK_NAME) which checks the EMP_ID and show a string like below

1 --> ADAM

2 --> BEN

3–> CINDY

4–>DAN

5–> ETHAN

( consider , I do not have a reference table which has both the ID and the respective NICK_NAME and the user need to change this grouping on demand )

Its similar to a case/if statement in SQL, but i need this to functionality in the visual query builder, i have tried all the calculated column functions available and couldn’t recreate it.

If any one know how to do this , Please help me out .

Thanks,

-Surya

2

We need some more information on this.

I understand that you have one table with EMP_ID and you would like to add a calculated column EMP_NICK_NAME based on EMP_ID. How do you get information about the nick names? Is it in some different table? Or its in the same column. Can you give us some example for data? Is it concatenation of two columns? EMP_ID and NICK_NAME resulting in third column EMP_NICK_NAME? Please clarify.

Thanks.

3

Hi Shruthi,

In this case, we do not have both of them in a table, only the EMP_ID is available in the table and the NICK NAME is something the user creates on the go. (Instead of seeing 1,2, he sees the name )

SImilar to a hard coded CASE Statement in SQL

Thanks,

Surya

4

Currently, Query builder does not support calculated fields with CASE statement. One more options is to try Transform & Cleanse in Toad Data Point. We have calculated column rule or Group Column Rule which can create this new column NICK NAME based on EMP_ID. You can get SQL if you are using Oracle or MySql connection.

Let me know if you need any more help. Thanks.

5

Hi Surya

You can manually add a case statement.

To do that

  1. Add calculated field and press the drop down arrow to open editor (marked # 1).

  2. After that press the second drop down (marked as # 2) to invoke the full SQL Editor.

Case.png
Case.png891×656 48.7 KB

There you can type the CASE statement

Regards

Aleksey