Deleting Retrieval Argument generates DW Error

[Former Member]

When attempting to delete a retrieval argument company_num from a report query which looks successful then generates an error: DataWindow Error "Line 22 Column 22834:incorrect syntax".

Then DataWindow "Unable to run 'C:\......xyz.pbl(dw.....pblname) attempt to rerun the report brings up DataWindow Error "Line 22...... again and again.

You finally have to say "No" to save and nothing has changed.

Any help would be appreciated.

Thanks,

Wayne

[Former Member · Accepted solution]

Try to edit source the DW and search for the "arguments=" keyword, then remove the argument that you would like to remove from there. If you have only one argument in there then just remove the whole arguments attribute(key)/value pair.

Save and then regenerate and see if you'll still get an error.

[Former Member · Accepted solution]

I get the following:

Source Editor "Save changes to dw_mdu_budget_012....srd?",

when I select Yes I again get the DataWindow Error - Line 22 Columnu22834: incorrect systax.

Still will not save.

Any other suggestions?

Wayne

[Former Member · Accepted solution]

It could be that the retrieval argument is being used in your WHERE clause or in the SQL statement. You have to remove any references to that argument before you can delete it.

[Former Member · Accepted solution]

Neil, The report runs with the remaining 3 retrieval arguments and the company_num as null. A join to a security table only allows you to see certain company records in the code. So it is possible I need to remove this first. Thanks, Wayne

[Former Member]

SOLVED:

The issue as the use of the "--" comment in the SQL i.e. -- company = company_num.

The reference in the retrieval arguments was only a coincidence.

Thank you all for your input, I learned several tips and techniques.

Thanks again!

Wayne

[Former Member]

I wasn't able to download your code, but, if you are using a Modify on the datawindow during runtime to remove the argument, then

dw_name.Modify ( "(double quotes) DataWindow.Table.Select = ' (single) " (double) + <some string> + "(double)  '(single) "(double) )

Instead of using a single quote to enclose the value in <some string>, you can use a double quotes instead, like the below

dw_name.Modify ( ' (single quotes) DataWindow.Table.Select = " (double) ' (single) + <some string> + ' (single) " (double) ' (single) )

If you are doing this in a static fashion, try to remove few subquery conditions and see if there is a problem with the opening and closing pair of double quotes (which most probably should be the issue)

In the past, I have faced errors reading 'blah blah blah incorrect syntax' when there were single quotes in the query and I try to set it to DataWindow.Table.Select dynamically

Also, you can try placing a messagebox to display 'DataWindow.Table.Select' and copy the syntax and run it in a SQL editor and see if you are able to run it without errors

Apologies if what I say doesn't make sense to the matter discussed here 🙂

[Former Member]

Here is the Edit Source file.

Thanks,

Wayne

PB Edit Code1.txt.zip

[Former Member]

Hi Wayne...

This seems to be a problem with your SQL... By the way this one is too long, and I don't believe it will be easily readable by anyone else... To verify that the issue is in SQL I would change the query to a simpler one but with the same columns (and namings) in the select clause,,, If the datawindow do not return an error, then you know that you have to review your SQL.

Andreas.

[Former Member]

By the way, is there a reason you use so many subqueries? Are you sure that any of them could not be converted to a table join? Or maybe a view?

What database are you using?

If your sql cannot be optimized, have you thought to work with a stored procedure?

I'm not sure what you are trying to do, but it will be hard to verify a > 350 lines sql command...

Andreas.

[Former Member]

I guess you are using Oracle...

Some questions:

• round(to_number((:month_number_1))/100) -> Why double parenthesis?
• is round function correctly used? (I'm not an oracle expert).
• what is the meaning of: and 'ACTUALS' = ('ACTUALS')?

Andreas.

[Former Member]

I could remove the double quotes, I am substituting a file lookup sql statement with a retrieval argument. Month_number is actually a poor naming convention from our vendor; yyyymm is the structure numeric. The ACTUALS = ACTUALS could be commented out but was another file lookup sql statement used in the vendor package which actually provides the retrieval arguments in the PB application. I am getting the results I need but have more work cleaning out useless code. Wayne

[Former Member]

One observation is the use of oracle (pl/sql) type comment:

--and month_number_2 = :month_number_1

--and proforma_version_1 = 'ACTUALS'

and budget_structure = 'Owning ResponsibilityTopLevel'

--and company = :company_num

I think this is not valid in PB datawindow.

[Former Member]

Great Scott man throw that SQL in a proc whether the syntax error is in it or not!

[Former Member]

I would also rewrite the query to use a WITH statement.  Once you're comfortable with that statement, you'll never go back to using in-line selects:

WITH

qry1 as ( select a.b.c from table1),

qry2 as ( select x,y,z from table2)

SELECT a,b,c,x,y,z

   FROM qry1

   JOIN qry2 on qry1.a = qry2.x

WHERE qry2.y = :your_pb_arg

[Former Member]

This sounds like a missing close paren or quote. The datawindow parser doesn't know where something is supposed to end and keeps going until it finds it. This would account for the 22k line length (obviously not right).

Can you paste the lines of your datawindow up through the first object AFTER the table object (this will be one of the objects you can actually see in design mode)? One of us may be able to tell you where the problem is or at least where to start looking.

[Former Member]

It might be a case of matching double quotes and brackets.

In this simple example the down arrows point to the open and closing bracket of the table statement and the up arrows point to the open and close brackets of the arguments.

The retrieve statement starts and ends with a double quote:  retrieve="  ... " arguments=

[Former Member]

That is not -> Line 22 Column 22834

Line 22 would be a few lines below that, or you must have an enormous complex retrieve statement.

And now I think of it, Having 22834 characters on a line looks very suspicious in a datawindow.

No DataWindow ought to have a line of 22KB in it.

Was at some point this datawindow manually edited? Might not be possible to tell anymore, but there definitely is something wrong with the source code or maybe some unintended size limitation.

If it is falling on the SQL, then it could be better to move the select statement to a view in the database or a stored procedure and not bother PowerBuilder with all this SQL and base the datawindow on the sp or view.

Ben

[Former Member]

This is just an example. I think the SQL counts as one line. I thought it might be useful for figuring out where the problem was.  I only takes one missing bracket and you are in trouble.

[Former Member]

Hi,

Your report has some corruption in the datawindow source.

Could be something with the retrieval argument but could also be something else.

The error says:

DataWindow Error "Line 22 Column 22834:incorrect syntax".

Try to export the source and then open the file in PowerBuilder in the File Editor.

Then go to line 23 (The line $PBExportHeader$ is extra to the 22 above).

And go to the character 22834 with the cursor. You should see the line and column number in the lower right corner of PowerBuilder in the bar. The error it encountered is the keyword or syntax just before that.

Change the name of the object on the first line to get a duplicate or working copy (The line with $PBExportHeader$).

If the corruption is not too bad you might still be able to correct the syntax by comparing with similar datawindows or an old backup if you still got one and import the object back in until it is accepted.

I have done this a few times to re-import datawindows into an earlier version by removing keywords that did not existed yet in the earlier version and changing the release number on the second line,

Ben

[Former Member]

Thanks I will give it a try.

Wayne

[Former Member]

Hi Wayne;

  Can you tell us:

1) What version & build of PB are you using?

2) Is this a migrated DW from a previous PB version?

3) Can you run the DW OK in the DW Painter?

4) Has your DBMS or DB Schema changed since the DW was last saved?

Regards ... Chris

[Former Member]

Chris, 1) Version 12.1 Build 6875 2) Don't know if it was migrated cuz it is from a vendor package. The query has 5 or 6 union and union all functions and I brought the query into an existing pbl report which had  3 retrieval argument of which 2 I could use. repurposed one. I added 3 retrieval arguments for testing but it will only allow me to remove 2 of the three I added. 3) The report runs with the remaining 3 retrieval arguments and the company_num as null. A join to a security table only allows you to see certain company records in the code. 4) No, we have had the system for approximately 2 years with no changes that I know of. Thanks, Wayne