Why isn't my data showing up in Table View?

[Margaret]

I'm missing a table in table view.

This is code I copied and pasted from a different document and I delete some stuff that I didn't need in this new document. But it loads fine. Does the fact that it's SQL make it not show up in Table View?

Cactus: SQL

SELECT "Last Name" = p.LASTNAME, "First Name" = FIRSTNAME,

    ProviderID = REPLACE(LTRIM(REPLACE(RTRIM(P.ID), '0', ' ')), ' ', '0'),

    FullName = RTRIM(P.LONGNAME),

    FromDate = convert(varchar,EA.PRESENTDATE_FROM,101), Status = STAT.[DESCRIPTION],

    Category = CATE.[DESCRIPTION], Specialty = SPEC.description, Suspension = SUSP.[DESCRIPTION],

  FROM VISUALCACTUS.VISUALCACTUS.PROVIDERS P

Thank you,

Margaret

[#2]

Can you check is there any other table have same field names so QV will auto concatenate the tables. Please check are you drooping the table later in the script ?

Also turn on the log file for reload or debug your script.

[Margaret]

Thank you.

Yes, the field name ProviderID is in both tables but isn't it supposed to be so it can connect the tables?

I am not dropping the table later in the script.

I don't know how to turn on the log file but the script loads fine. The only problem is there is no table.

~Margaret

[Peter_Cammaert]

If this is a case of autoconcatenation (which seems most plausible), you can avoid that by inserting the keyword NOCONCATENATE between Cactus: and SQL. Like in:

Cactus:

NOCONCATENATE

SQL SELECT "Last Name" = p.LASTNAME, "First Name" = FIRSTNAME,

:

I won't do any harm if you add this keyword in any case.

[Margaret]

I tried that. Still no table.

[Margaret]

Maybe it’s a problem with the connection to the database (although I did re-do the “Connect…” process) because:

I got

Cactus: SQL

SELECT     LASTNAME

FROM         VISUALCACTUS.PROVIDERS

to work all by itself in one application and Script Execution Progress said

Connecting to Cactus

Connected

Cactus << PROVIDERS 2,167 lines fetched

And it shows the table in table view.

BUT, when I paste that into the original application, it loads fine but Script Execution Progress says nothing about connecting to Cactus and only grabs 282 lines (from a spreadsheet) and there is no table in table view called Cactus.

[Peter_Cammaert]

Your last two posts may point in the direction of your queries not returning any rows at all. No rows means no internal tables. Your connection credentials may present empty DBMS tables to you.

There is an easy way to check this out. In script editor, click the Select... button immediately below the Connect... button. In the dialog that is presented, make your table selections, create the necessary where clauses to filter the data and then check out the preview pane. Are there any rows in this overview? Can you make it so that it shows some rows?

Peter

[Peter_Cammaert]

On the other hand, your last sentence is opening another path: is the code you pasted into the dysfunctional script ever executed? Are you sure you're not pasting into a SUB that is never called for example? Maybe your script has an exit script; statement that forces the engine to stop processing statements before it reaches CONNECT?

Again, an easy (temporary) trick to figure out which path the reload engine is taking through your code: place a decent number of TRACE statements in strategic locations and monitor their output in the progress window. For example:

TRACE >>> Before Spreadsheet LOAD;

can be placed immediately before the... spreadsheet LOAD statement.

In addition to that, generate a Document log and open it in a text editor. Check the execution flow (the TRACE output will be present in the log too). Something is missing, right? Can you find a clue about why the CONNECT-LOAD are missing? Or are they listed allright, but doing nothing (for example the LOAD says 0 rows returned)

Best,

Peter

[Margaret]

Thanks so much for your suggestions, Peter.

Using Select... I was able to see some rows. But when I reloaded it still didn't fetch any from the Cactus database.

I'm sure I'm not pasting into a SUB. There is no "exit script;"

I put trace statements before the spreadsheet load and that showed up in the script execution progress window.

I put a trace statement before the Cactus Connect and it never showed up.

I put a trace statement after the spreadsheet From statement and NOTHING showed up in the progress window, not even the "282 lines fetched" that was showing up before.

I told it to generate a log file, but I couldn't find where it was stored, but it looks like it is the same as what shows in the Script Execution Progress window so I've already been keeping tabs on that.

[Peter_Cammaert · Accepted solution]

A Document log is stored in the same place as your QVW, and has the same name as the latter with the extension .log attached to its name.

A Document Log shows much more than the progress window. It lists every statement in order of execution, often with additional details (no of rows and columns loaded). Loops are unfolded, procedure calls expanded etc. All in all, this is a book detailing every step of script execution.

The log will allow you to figure out where the script wanders off, and starts skipping vital statements. Care to post the log in this thread?

Best,

Peter