Toad World® Forums

Code Snippet Issues


#1

I am having troubles with Code snippets query editor in Toad Analyst using an ODBC link to an excel worksheet. The problem is using an IF statement and a smaller problem as follows:

SELECT (racks.r_lwr_x + (directional_coef.dc_coef_a * rack_config.rc_delta_x))* 0.0254 + SPACE(30) AS from_x

This will not work unless I take out the + SPACE(30) portion, which I not sure why it works with other types. I attached the base file that is connect through Odbc to excel file, as well as that Query I have written already without the + SPACE(30)

The other trouble I am having is Just adding a plain table (column) like “bbbbbb” or "ii"completely constant throughout the entire report, also note that these values are not in the original excel file I just want to put it in.

Also, If statement example I am trying to write and I have tried all the possible IF/IFNULL/IF THEN ELSE statements shown in Code snippets in Toad as well as my own versions. An example of what it might look like:

IF(rack_config.rc_use_zn = 1)
THEN(MID(LTRIM(rack_config.rc_zc_label), 1,12)+ SPACE(30)
ELSE( MID(LTRIM(racks.r_zc_label), 1, 12) + SPACE(30))) AS zone
END

So if rc_use_zn equals 1 or 0
Need this to be in a single (column) table

OR Easier way I think:

IFNULL(MID(LTRIM(rack_config.rc_zc_label), 1, 12)+ SPACE(30) THEN(MID(LTRIM(racks.r_zc_label), 1, 12) + SPACE(30))) AS zone

Need this to combine into one table if first statement has a null value it is replaced by the next statement.

Note** I have tried using lots of other Code Snippets examples/templates, but they never work really. I am not sure why, since they always seem straight forward as in put this table or condition in, but never works.

Also, after writing code I can no longer use the query diagram to add tables in by checking them off so I have to manually enter them not sure the issue with this.

I really need to know whether or not it is my ability or the software that is the issue, since I am trying to pretty much customize a report for use by another program and currently it is done in another program, but it is not very user friendly and I am just trying to combine and multiply tables into one column.

Thank you in advance for any help or insight.

Message was edited by: l.mayer_390


#2

Don’t think files attached, but here they are
STN_Dairy_Cooler-10.xls (163 KB)


#3

Don’t think files attached, but here they are
Acad_Stn_Dairy_Clr-10_Test3.tsm (6.37 MB)


#4

SELECT IIf([warehouse.w_label]=‘S0’,‘So So’, IIf([warehouse.w_label]=‘MO’,‘Mo Mo’, ‘No sure’)) as Something
from …

Debbie

Thanks for the files. Both myself and another developer can execute your query with the SPACE(3) syntax. You could run into a datatype mismatch if you are not converting the fields to a string before concatenated the space. I’d have to reproduce the exact error. What error do you get?

On the if…else have you tried the IIF function.


#5

, (racks.r_lwr_x + (directional_coef.dc_coef_a * rack_config.rc_delta_x))* 0.0254 + SPACE(30) AS from_x

That is the exact code I try to use but get this error:

[Microsoft][ODBC Excel Driver] Data type mismatch in criteria expression.

And I’ll try that IIF statement when I get a moment today.


#6

try…

, LTRIM((racks.r_lwr_x + (directional_coef.dc_coef_a * rack_config.rc_delta_x))* 0.0254) + SPACE(30) AS from_x

and see if that fixes the data type mismatch issue.

Thanks.


#7

Thanks it did fix the data mismatch error


#8

Well found out why an IF,THEN, ELSE statment doesn’t work, since it uses a “CASE” instead of an IF statement but even then doesn’t work too well unlike the other software I am testing.