Query help

[WhiteHatMarine]

I have a tricky query I cannot seem to come up with a good solution for. The first query functions. Second needs works. I would like to make this a single query, need help with that. The WHERE statement in Query2, not sure how to handle this as well. Query2 should be the result set. Code being executed in ColdFusion not native VFP.


Query1
SELECT WorkOrderHeader.wono , WorkOrderDetail.partno , SUM(WorkOrderDetail.qtyaloc) AS MatAllocated, MIN(WorkOrderHeader.reqdte) AS MinReqDate
FROM WorkOrderHeader INNER JOIN WorkOrderDetail ON WorkOrderHeader.wono = WorkOrderDetail.wono
GROUP BY WorkOrderDetail.partno
WHERE WorkOrderHeader.status < 0;

Query2
SELECT InvItem.item, InvLoc.lonhand, Query1.MatAllocated, Query1.MinReqDate
FROM InvItem INNER JOIN InvLoc ON InvItem.item = InvLoc.item INNER JOIN Query1 ON InvItem.item = Query1.partno
WHERE [InvItem.ionhand]-[Query1.MatAllocated]-[InvLoc.orderpt]+[InvLoc.lonordr]) <= 0;


Thanks!
Adrian

 

[Mike Lewis]

Regarding the WHERE clause in Query2, that will surely give an error. As far as I can see, the four items in the expression are all numeric. But you are using square brackets to delimit them. In VFP, square brackets delimit strings. You should remove the square brackets, and also the spurious closing parenthesis at the right-hand end of the expression.

As far as combining the two queries are concerned, I suggest that you don't try to do that. Given that you are finding the whole thing difficult to understand, you can make it easier for yourself by keeping them as two separate queries. That might or might not be the more efficient way of dealing with it, but if it helps you understand how to do it, which in turn will make it easier to debug and maintain, then that would be worth doing.

The only thing I would change is to remove the hard-coded references to Query1 in the second query. Instead, add

I[tt]NTO CURSOR csrWorkOrders[/tt]

at the end of the first query, and then reference csrWorkOrders in place of Query1 in the second.

You should also consider aliasing the table names in both queries. So, in Query1, instead of:

[tt]FROM WorkOrderHeader INNER JOIN WorkOrderDetail[/tt]

do this:

[tt]FROM WorkOrderHeader wh INNER JOIN WorkOrderDetail wd[/tt]

Then refer to wh and wd in place of WorkOrderHeader and WorkOrderDetail respectively throughout the other parts of the query. That won't change the functioning or efficiency of the query, but it will make the whole thing shorter and therefore easier to read.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[bborissov]

SELECT InvItem.item
     , InvLoc.lonhand
     , Query1.MatAllocated
     , Query1.MinReqDate
FROM InvItem 
NNER JOIN InvLoc ON InvItem.item = InvLoc.item 
INNER JOIN (SELECT WorkOrderHeader.wono
                  ,WorkOrderDetail.partno
                  ,SUM(WorkOrderDetail.qtyaloc) AS MatAllocated
                  ,MIN(WorkOrderHeader.reqdte) AS MinReqDate
             FROM WorkOrderHeader
             INNER JOIN WorkOrderDetail ON WorkOrderHeader.wono = WorkOrderDetail.wono
             GROUP BY WorkOrderDetail.partno
             WHERE WorkOrderHeader.status < 0) Query1 ON InvItem.item = Query1.partno
WHERE [InvItem.ionhand]-[Query1.MatAllocated]-[InvLoc.orderpt]+[InvLoc.lonordr]) <= 0;

Borislav Borissov
VFP9 SP2, SQL Server

 

[WhiteHatMarine]

Thanks guys, here is more information I should have provided initially.

Query1 & Query2 are names I just placed in this post for easier reading, they cannot be referenced by the query as coded now.

This query was working in MSaccess with linked tables but issues with full outer join and needding to move the process to a different platform lead me to re-writing it.

This statement is from msaccess when everything worked as a single query: [InvItem.ionhand]-[Query1.MatAllocated]-[InvLoc.orderpt]+[InvLoc.lonordr] <= 0

I didnt expect to use it as it was written above but I provided for reference and need to accomplish the same goal.

The issue (for me) with having 2 queries is how do I reference the query1.MatAllocated in Query2 witout a JOIN or sub-query?


Borislav's example looks promising! How does the Query1 get referenced?

 

[WhiteHatMarine]

I updated using Borislavs example, fixed few small errors but still getting a syntax error.

SELECT 
  a.item 
, a.ionhand 
, b.lonhand 
, b.orderpt 
, b.lonordr 
, getQuery1.MatAllocated 
, getQuery1.MinReqDate
FROM icitem10 a
INNER JOIN iciloc10 b ON a.item = b.item
INNER JOIN (SELECT d.wono
                  ,e.partno 
                  ,SUM(e.qtyaloc) AS MatAllocated 
                  ,MIN(d.reqdte) AS MinReqDate 
FROM ppwohd10 c INNER JOIN ppwodt10 d ON c.wono = d.wono GROUP BY d.partno WHERE c.status < 4
            ) getQuery1 ON a.item = getQuery1.partno

WHERE [a.ionhand]-[getQuery1.MatAllocated]-[b.orderpt]+[b.lonordr] <= 0 
;

 

[Mike Lewis]

Regarding replacing the hard-coded Query1, I answered that in my first post above. Essentially, when you do a SELECT in VFP and don't specify an INTO clause, then VFP automatically creates a cursor named QUERY. You can reference that cursor in subsequent queries (whci I assumed was what you were doing). But it is better practice to explicitly send the results to a named cursor and to use that cursor in your subsequent queries. Hence my suggetion of using INTO CURSOR.

With regard to syntax errors, if your code is exactly as you posted it, then it will fail because it stretches over multiple physical lines. You need to put a semi-colon at the end of each line to indicate that the code continues on the next iine. And remove the final semi-colon for the same reason.

Also, did you read what I said about the user of square brackets in the WHERE clause?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Well, ColdFusion isn't VFP at all, not even related.

You're in the right place, as Tek Tips itself is in ColdFusion, but in the wrong forum.

Or are you making a query on DBFs in CF via the VFP ODBC driver? Then you're limited to VFP6 capabilities, which exclude many subquery options. you'd be better off, if CF allows the usage of OLEDB providers, as you then can use VFP9's SQL engine via VFP9 OLEDB Provider. As far as I googled OLEDB capabilities of Cold Fusion ended with the MX version.

A route worth going to make use of advanced SQL dialect is using an MSSQL Server adding the VFP data as linked server with the VFP OLEDB Provider and then using the linked server connected to MSSQL via MSSQL ODBC drivers you're making indirect access to DBFs via the route of CF-> MSSQL ODBC -> VFP OLEDB Provider -> DBFs.

Bye, Olaf.

 

[WhiteHatMarine]

Mike: I did read your post well, thank you. I was not sure how to utilize the cursor, perhaps it would work in Cold Fusion as well, I just did not expand on that idea. The brackets came over from e semi-functioning version of this query in MSAccess. I believe in Access and VFP ODBC, brackets indicate SELECT alias's (don't quote me on that).

Olaf: I am making queries on DBFs in CF via the VFP ODBC driver. I also have HXTT Pure Java JDBC Driver functioning, which supports Foxpro, VFP(3.0 - 9.0). I post here as these queries seem VFP specific and the CF people rarely have VFP experience. I see your point, should I cross post in CF forum?

I do have this functioning using a kludgy cfloop / query of query, adding cells etc. Just too slow. I am hoping to accomplish in a single query.

I like the MSSQL suggestion.


Thanks,Adrian

 

[Olaf Doschke]

I don't know how the HXTT Pure Java JDBC Driver would support VFP, but if it does, use it instead of the VFP ODBC driver, as you can only make subqueries in the places Mike and Boris used them with VFP9 and so, of course, this fails. VFPs ODBFC driver was deprecated with Vresion6 and replaced by OLEDB Provider, Newer capabilities of the SQL language of VFP are not available in native VFP ODBC.

Yes, in Access square brackets are name-delimiters of names with spaces in them, in VFP these are not at all allowed, and square brackets are string delimiters. As Mike said you must remove them and you kept them, it doesn't look like you read thoroughly. Or you don't believe. That's both not in your interest.

Also, the ideal way to send queries via ODBC, in general, is without line feeds. So prepare a multiline query string, if that's easier (in VFP we have TEXT...ENDTEXT, in PHP you have Heredoc syntax, I don't know if it's simply using multiple lines in CF). That removes the problem of semicolons. In VFP you use them as line continuation and NOT as a terminal character, VFP is simply single line per command with line continuation.

If what you had and "seemed" like a VFP query to you already isn't VFP syntax at all, are you actually knowing the DBF files at all? You might still be barking up the totally wrong tree here.

You cleared up, that this is for querying via ODBC and not natively querying some CF stuff, I don't know CF at all. What you said sounded to me you were doing pure CF, so no, a cross-post to some CF forum won't help you with the query syntax in whatever database you query, but you should really know what database you query first, before asking for the wrong SQL dialect.

Bye, Olaf.

 

[Olaf Doschke]

Another topic. you should get more error info from a failing query. The usage of ODBC via some call like PHPs PDO::exec or any similar should also provide an error handling capability, VFP's error system will give an error number and message, typically saying much more than just "syntax error". get this going in detail, or it's just very hard to analyze code and get a query to up and runnning.

Bye, Olaf.

 

[WhiteHatMarine]

First and foremost, I am here with eyes wide open. I do believe I am not doubting the help. I am grateful. I simply failed to remove [] from my post. This is mostly because I was testing the query in MSaccess linked to DBF's and the [] are supported. As many people do I edited my code to attempt to simplify for my example. Apologies.

I am not developing in VFP, at all.
I am accessing DBF via ODBC and HXTT drivers (I see your point on VFP driver)
I am developing and writing queries in Cold Fusion

I am looking to bounce a few questions off the gurus who deal with these things more regulatory than myself which is rarely. I have enough experience to weed through the answers. Based on past experience much of the VFP support (that I require) does work in Cold Fusion environment, so this forum has been an excellent resource if you look at a past post of mine.

Many thanks.
Adrian

 

[Olaf Doschke]

Fine, but testing in Access is not helpful at all. If you didn't recognize SQL has dialects with each and every database system and evolves with each and any version of them, it's time to do so.

Perhaps the route ofMSSQL and linked server will have more success and the HXTT driver also only supports a limited set of VFP sql.
You need a 32bit SQL Server to be able to see the 32bit OLEDB Provider for making the linked server.

Bye, Olaf.

 

[Olaf Doschke]

I simply failed to remove [] from my post.

I don't get what you're saying here. Isn't it far easier to let us see what you execute by simple copy&paste of actual code.

Bye, Olaf.

 

[WhiteHatMarine]

In between replies I have been trying many, many things and changing things quickly, working to find a acceptable solution. Definitely not twiddling my thumbs waiting for replies Olaf. Since I do recognize various SQL dialects, testing in access does have its benefits for me in this situation. The engine is a bit more revealing and the driver plays similar with Cold Fusion and DBF. Your insight helped me move on and find an alternate solution, thank you.

Here was the last attempt at the query which did not work for reasons you mentioned. Enjoy.

SELECT ITM.item ,ITM.ionhand ,ILO.lonhand ,ILO.orderpt ,ILO.lonordr ,DT.MatAllocated , DT.MinReqDate
 FROM ((icitem) ITM LEFT JOIN "iciloc" ILO ON ITM.item = ILO.item ) 
  INNER JOIN 
     SELECT WOH.wono ,WOD.partno ,SUM(WOD.qtyaloc) AS MatAllocated ,MIN(WOH.reqdte) AS MinReqDate 
     FROM ((ppwohd) WOH LEFT JOIN ppwodt WOD ON WOH.wono = WOD.wono ) 
     WHERE 1 = 2 GROUP BY WOD.partno
  DT ON ITM.item = WOD.partno 
WHERE 3=4

 

[Olaf Doschke]

Yes, obviously "iciloc".

But as you know better than me that Access syntax is equal to VFP, well, that won't matter.
You should perhaps listen to experts of another SQL dialect you don't know well, or you can spare asking at all.

VFP doesn't have name delimiters as Access or MSSQL, it simply doesn't allow spaces in table or field names. They are (obviously) allowed in DBF file names, but if you open a dbf with a space in it's file name, the alias name given to it will then have an underscore _ instead.

Besides that you seem to not have established a decent error handling on the CF side about ODBC error messages coming back from the driver.

Bye, Olaf.

 

[Mike Lewis]

And there is still the issue of semi-colons as line-continuation characters. Obviously, these are not needed in Access (and not in most other dialects, where a semi-colon terminates the entire statement).

If your statement is working in Access, that good. But when the time comes to run it against VFP, you will either have to insert the semi-colons or concatenate all the physical lines into one.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Yes, which as harsh as it comes over just repeats the impression you give.
It's fine that you are more diligent than what you show, but we can only help you with what you post here and seeing your queries don't reflect advice given to you is frustrating for us, at least for me.
Mike and I are not talking about possible other variations you "could" try, but absolutely essential non-optional ways VFP syntax has to be.

You can't query FROM "tablename" or from [tablename] like in Access, you have to query from tablename in VFP syntax, there is NO alternative, VFP does not support any name delimiters.
You have to have semicoli at the end of EACH line EXCEPT the last line in multiline VFP queries, totally opposite of what other languages are doing. In VFP semicolon is line continuation just as _ is in VB, VFP has NO end of command, a command is typically just in one line (except you logically "continue" lines by semicolons). The simplest solution to avoid that need for semicoli is format a query into a single line. Easy to do.

Even if changing that does not make the query work, it's no reason to question what we say and revert back to what is known to not work. You'll have further errors. Only showing them, we can help you.

We could test your queries, but not without having your data. Syntax alone could only be checked for compilation, and it's quite useless to do that from our side, as it still leaves open many possible pitfalls with non-matching types and many more problems the compiler can't predict at compile time. Even though VFP is string typed in terms of database fields.

Also the influence of the HXTT driver is completely unknown, we do native VFP, not using a third party driver setting up its own rules for syntax. In the end only the developers of the HXTT drivers can help you with what SQL queries they support, especially if their driver doesn't make use of the VFP runtimes at all, but is an implementation of an SQL engine combined with from scratch implementation of DBF/CDX/FPT file usage from file specifications, somebody did. It's to be expected this engine then isn't VFP compatible at all.

Go the route of a linked server via OLEDB Provider and you're at the official VFP query capabilities, maybe much advanced in comparison the query options the HXTT drivers offer.

Bye, Olaf.

 

[mjcmkrsr]

Hi,

Try this code. At least it doesn't throw a syntax error under VFP9

SELECT ITM.item, ITM.ionhand, ILO.lonhand, ILO.orderpt, ILO.lonordr, DT.MatAllocated, DT.MinReqDate ;
 FROM icitem ITM LEFT JOIN iciloc ILO ON ITM.item = ILO.item ;
  INNER JOIN ( ;
     SELECT WOH.wono, WOD.partno, SUM(WOD.qtyaloc) AS MatAllocated, MIN(WOH.reqdte) AS MinReqDate ;
     FROM ppwohd WOH LEFT JOIN ppwodt WOD ON WOH.wono = WOD.wono ; 
     WHERE [highlight #EF2929]xxxx[/highlight] GROUP BY 1, 2) DT ON ITM.item = DT.partno ;
 WHERE [highlight #EF2929]yyyy[/highlight] ;
 INTO CURSOR csrTemp

hth

MK

 

[Olaf Doschke]

In the end only the developers of the HXTT drivers can help you with what SQL queries they support, especially if their driver doesn't make use of the VFP runtimes at all

I think that is the major reason your queries also might need to look quite different and work as multiline etc.

You know, that's the problem with third party ODBC drivers, when they don't have a server acting, the must implement the whole query engine. It's much easier to develope a MSSQL ODBC driver, because it's endpoint is a server to send query strings to, which can be T-SQL and are then executed by that Server side. The HXTT drivers actually implement the file structure of DBF and CDX and FPT and maybe even not completely support all data types. If they are not basing access on the VFP runtime DLLs, as I see they also exist and have their main usage on Linux, they can't support VFP syntax and rules and will likely even not support all the VFP functions you can make use of in VFP queries with the native VFP drivers, especially the OLEDB provider.

It's also one of the problems of another third party tier to DBCs and DBFs, Symantecs Advanced Database drivers, that once they access DBFs of a DBC they can't do full support of any triggers calling stored procedures involved in DBC code using the full language of VFP and not just SQL. It would be a wonder if HXTT drivers would even support simpler VFP functionalities, you might try that and see if you get it running:

SELECT *, DateTime() as tRecordqueryTime FROM sometable

If the DateTime function is supported, try the next difficulty level of TTOC conversion:

SELECT *, TTOC(DateTime()) as cRecordqueryTime FROM sometable

or via CAST

SELECT *, CAST(DateTime() as C(22)) as cRecordqueryTime FROM sometable

I bet these won't work, as the HXTT drivers do not base on VFP runtimes and don't support VFP language and syntax, but their own SQL dialect. If even single functions are not present there's no reason to think the drivers would implement VFPs overall query composition capabilities, eg where what subqueries are possible.

That's the downside of such drivers from third-party development. They might add features, too, but asking for our help becomes useless when the VFP language specification doesn't apply even in SQL commands. HXTT drivers mainly solve the problem of access to the file structure but don't seem to give you the full-blown VFP query language and engine and that would easily explain why nothing anybody gave you works.

Bye, Olaf.