To: Debbie Peabody, Software Development Lead

From: Eric Chilenskas, Performance Data Specialist, Colorado Department of Human Services

TOAD DATA POINT 3.502926 ERRATA

Hi, Debbie, et. al,

These are the two biggest bugs for me that hinder development in TDP.

1. Automation will not pass lists correctly. Here is a list of counties being passed as the COUNTY_GRP parameter to a .tdr file with underlying SQL script (Oracle 11g database). The list is passed as ‘ALAMOSA’,’BACA’,’BENT’ with single quotes around each county, separated by commas.

The SQL code for the COUNTY_GRP parameter is

SELECT X, Y, Z FROM TABLE

WHERE CNTY_NM IN (&COUNTY_GRP)

Here is the successful result. The COUNTY_GRP is the line shown in gold:

However, when a .tdr file is placed in Automation, you can see the red error circle below. If you try to set COUNTY_GRP to ‘ALAMOSA’,’BACA’,’BENT’ the error says: “’ALAMOSA’,’BACA’,’BENT’” cannot be converted to string (mismatched input ‘,’ expecting EOF line 1:9). Note that there is a double quote around “ ‘ALAMOSA’,’BACA’,’BENT’ “.

Removing the single quotes doesn’t help. Passing the COUNTY_GRP parameter as ALAMOSA, BACA, BENT without quotes results in

The log shows Variable "COUNTY_GRP" set to 'ALAMOSA, BACA, BENT' which results in a Report_1 - ORA-00904: "BENT": invalid identifier error. It’s being treated as one long string, not a list of individual counties, and does not match the database.

I’ve tried playing around with special characters, etc. to no avail. It’s frustrating to develop something in SQL and .TDR, then find out it won’t work in Automation. This forces me to hard code the COUNTY_GRPs every time I run reports.

This bug is #1 on my Wish List to be fixed.

2. In Automation, Export Wizard Doesn't Pass DateTime Correctly, Throws "not a valid month" error

In this case, I’m using the Export Wizard (NOT the’ Select to File Activity’ ) because the Export Wizard activity has the “append” feature, allowing me to append rows to an existing spreadsheet tab. The Select to File activity does not have this feature in TDP 3.5 and will overwrite, rather than append data on a spreadsheet tab.

Note: it was suggested that I try the 3.6 Beta, in which ‘Select to File’ does have the append option. However, when I tried it, SQL that worked perfectly fine in 3.5 throws ORA-12704: character set mismatch. I don’t have the time to constantly be looking for workarounds, so I went back to 3.5.

Here’s a screenshot of what happens when you try to pass a datetime variable to Export Wizard. The datetime fields are set in the Set Variable activity as ‘03/01/2014’ and ‘05/31/2014’

Log:

Starting Automation Script

Setting up environment

Script run by Toad Data Point 3.5.0.2926

Build started

Compiling script

Warning: No Exception Handler. To send an Email when there is an error, change the value of Email on Error to true and enter email values.

Build completed

Begin execution script activities

Variable "TSTART_DATE" set to 3/1/2014 12:00:00 AM

Variable "TEND_DATE" set to 5/31/2014 12:00:00 AM

Running export template C:\Users\ChilenEX\Desktop\County C-Stat\AUTOMATION\SQL\DASHBOARD\Test.txp

Export_1 - ORA-01843: not a valid month

Failed

Note that it mentions ‘not a valid month.’ Someone suggested I try passing to_date(‘01/01/2014’,’mm/dd/yyyy’). This results in the Set Variable Activity Input tab giving an error that reads “2nd parameter unsupported format specifier: ‘mm’” Seems to be something with interpreting months in a datetime parameter.

As requested, I’ve attached the export template for this problem (Test.txp).

3. select * from AutoRange_A where measure= 'Timeliness' and cnty_nm = :county

4. union all

5. select * from AutoRange_A where measure= 'Response' and cnty_nm = :county

6. Toad crashes immediately anytime there's more than one parameter in the 'Select to File' activity and the database is Excel.  If I run just one of those lines, it works fine.  

7. It still crashes if I rename the second parameter to :county2.  

8. This is TDP 3.5