Code Snippet Issues

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

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

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

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.

, (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.

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.

Thanks it did fix the data mismatch error

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.