Hi Debs, etc…
I posted this in Ben’s blog as his post about query building jotted my mind about a problem we’re having placing functions via the query designer. Here is my post at Ben’s blog:
Hi Ben. I found your blog via Google. I am using TDA and have issues with the Formula way of creating a where condition. I need to select data based on a string function - ie: instr(myfield,’#’) = 0. This simply says, find my data where the “myfield” column does NOT have any hashes in the data. How can I do this in TDA diagrammatically? I can obviously type it into the SQL directly, but when I tried to keep the nice diagram intact, it “destroys” it when I reverse engineer the SQL (I hate those WHERE CLAUSE bubbles!). I tried to use the formula, placing the above string function as is, but TDA somehow places the field name before the function and this breaks SQL syntax! Any ideas? Thanks in advance
Could anyone shed more into this please? Much appreciate it. Thanks
Ah, I see you’ve already posted this to the TDA Community. Excellent! Hopefully, we’ll have some additional eyes on this to see what may be shaking out. I also observe the behavior of the generated SQL displaying the column name in the WHERE condition. However, I am able to go to the tab that displays the generated SQL and simply delete the column name in said WHERE clause. My SQL executes correctly and I’m able to right-click and Visualize the query properly. I’m currently running version 2.6.1 of TDA.
You need to build a calculated field. The calculated field builder is available from the toolbar. Give it name and click the plus sign. The edidtor for the column includes functions available for that datatype such as INSTR. Build your field and attach to a table. The column is now part of your query and can be filtered in the Where condition.
Hi Deb,
Thanks for that. I am aware of calculated fields, and now understand how they work. However I don’t think TDA can manage CASE statements. I am trying to create a subquery based on a case condition.
ie:
select (case (select oo.entity from obligations oo where oo.entity_no = o.entity_no) is null then (select dd.entity from debtor dd where dd.entity_no = o.entity_no) else (select oo.entity from obligations oo where oo.entity_no = o.entity_no) end) as entity
from obligations o, debtor d
where o.entity_no = d.entity_no
How can I do that diagrammatically without coding it directly in SQL!?
Case statements are accomodated in the Calculated fields editor also. There is a drop down editor where you can enter the case statement. See screenshot.