Your order by clause misspelled CODESLSP

Thanks for the response.

Actually, above was a type-o. Even if I go:

"SELECT * FROM ARSAP"

It fails.

So

      myview = dbLink.OpenView("CS0120")

       myview.Browse("SELECT * FROM ARCUS", True)

Above works, but for view AR0018, ANY and ALL sql NEVER works, so this fails:

     myview = dbLink.OpenView("AR0018")

     myview.Browse("SELECT * FROM ARSAP", True)

So the issue is any and all SQL for view AR0018 NEVER works. It possible that some related view needs to be opened? However, this DOES work:

This for example does work:

        myview = dbLink.OpenView("AR0018")

        myview.Browse("NAMEEMPL = ""Bill Bhaisson"" ", True)

So setting a filter DOES work, but using SQL does not. I fired up the dbspy, and it shows the above table of ARSAP being opened when I execute openView above, but my code halts ad fails on above Browse().

If I were to "guess"? I find AR0018 will NEVER allows SQL, but only allows a filter.

And also note that the above filter FAILS if you use single quotes. (so the long time filter approach used in VBA or vb.net works for above. However for CS0120, we see that sql works just fine, but you MUST MUST use single quotes for the condition.

So I guess the question:

Why for CS0120, I can NEVER use double quotes, but for AR0018, I must use double quotes?

Why for CS0120, any and all SQL works just fine?

Why for AR0018 any SQL never ever works?

I guess I trying to get beyond the coin flipping process as to why SQL works in one case, and not the other. And I guess I trying to get beyond the coin flip as to why in one case, double quotes work, single quotes fail, and in the first case, I can use single quotes for the condition?

I mean, we have to get beyond the coin flipping process if we going to be able to rely on this interface to sage, else all is much lost.

How can one develop software if the whole process is based on a guess? Surely there has to be a better answer then please guess!!!

So no matter how I spin this, for AR0018 we find:

NEVER sql works

Must use double quotes for a condition. (which is the 100% opposite of the CS0120.

I am out of ideas, except that one has to make a wish kind of approach to software development? Surely this cannot be the future of this interface?

Anyway, appreciate the follow up - hoping someone has a "rule" for the above issue other then flipping a coin and trying both approaches?

Regards,

Albert D. Kallal

Edmonton, Alberta Canada

[email protected]

The very short answer is that CS0120 allows you to run SQL statements against the database and it is the only view that allows for that.  CS0120 has the capacity to create a field list that you can access like a Sage view (which is what InternalSet 256 is doing (you don't need this if the SQL doesn't return data)). Every other view has the browse conditions passed through a database agnostic layer (for historical purposes).  With CS0120 there are a few 'gotchas' to watch out for especially when processing return values that return a VARCHAR (cast the resulting field to a CHAR to avoid problems).

Every other view uses the non-SQL syntax.

You can put your coin down.

Well, that could not be more clear! - thank you so much!

I am 100% fine with this.

My "bad whopper" was assuming that any view could accept SQL , and that is NOT the case (and NEVER been the case! (bad assuming on my part).).

Given your answer, then as you state - no coin flip at all here.

And I did just try my 2nd statement with CS0120 - and it works just fine.

So my BIG mistake here was assuming that other views can accept SQL in place of the LONG time approach that EVERYONE on the planet has used for years in regards to this interface (Ie: a filter + double quotes).

I Really do approached the follow up. Simply my bad to assume that any view will Accept SQL - clearly that is not the case, and a rather bad assume on my part.

Appreciate the simple answer on your part. At least I am not going crazy here.

Once again, thank you. - and I am most happy to take my coin back home!!

BTW

I built interfaces to QuickBooks, sage50 (simply), and now am working with sage 300?

Sage 300 is amazing in regards to the software interface. That interface is clean, and is VERY far beyond anything else I seen in this industry.

Regards,

Albert D. Kallal

Edmonton, Alberta, Canada

No problem.  The historical reason is that Sage used to work with Pervasive, DB2 and Oracle as well as SQL Server so you could write the 'where clause' in the view.browse statement in a unified way and it would be translated to the correct syntax.

Happy coding!