SQL Statement Not working the way I want it to

[dgrewe]

I have a MSSQL statement called from VFP that is Kicking my rear to find the problem. Any help would be welcomed. I'm not a MSSQL fan.

The Resulting table is suppose to return all the other inmates in a Jail facility that had potential contact with other inmates.
This does not involve a prisons that have community rooms like exercise yards, mess halls, ETC.

The query runs, but it returns every person in the database tables in the facility.

Anyone see anything obvious I'm missing.

Any field ending in "ID" is an INTEGER.
Field ADDTIME is Datetime()
the function sqlconv() changes the VFP DateTime to a MSSQL/ORACLE backend statement.
All other fields are CHAR.

I modified the code so it would align and look nice for this forum, there may be a comma out of place in the example because of it

lcBookid = ALLTRIM(STR(lnBookID))
lcSelect = "cellhistid, book_id, newcellid, addtime"
lcWhere = ;
"BOOK_ID = " + lcBookID + ;
" AND addtime <= " + SQLConv(m.vdate2,2) + ;
" AND NEWCELLID IN " + ;
	" (SELECT  jfaciltyid          FROM jfacilty  WHERE unit IN " + ;       
	" (SELECT  DISTINCT(unit)      FROM jfacilty  WHERE jfaciltyid IN " + ;
	" (SELECT  DISTINCT(newcellid) FROM jcellhist WHERE newcellid > 0 " + ;
		" AND book_id = " + lcBookID + ")) " + ;
" AND jsection IN "+;
	" (SELECT  DISTINCT(jsection)  FROM jfacilty  WHERE jfaciltyid IN " + ;
	" (SELECT  DISTINCT(newcellid) FROM jcellhist WHERE newcellid > 0 " + ;
		" AND book_id = " + lcBookID + ")) " + ;
" AND facility IN "+;
	" (SELECT  DISTINCT(facility)  FROM jfacilty  WHERE jfaciltyid IN " + ;
	" (SELECT  DISTINCT(newcellid) FROM jcellhist WHERE newcellid > 0 " + ;
		" AND book_id = " + lcBookID + ")) )"

SQLProc("SELECT " + lcSelect, ;
        "FROM   JCELLHIST",;
        "WHERE  " + lcWhere, ;
        "ORDER BY addtime, book_id",;
        "INTO TABLE cellhist")

David W. Grewe Dave

 

[Mike Lewis]

Dave,

A couple of questions ...

First, what is SQLPROC()? I don't recognise that function. Is it a wrapper for SQLEXEC()?

Second, what back end are you using? You mentioned "MSSQL"? Is that what I would call SQL Server? The reason I ask is that (as I'm sure you know), the syntax and rules for SQL varies according to the back end in question, so it's always useful to know what that is.

If the back end has an interactive command line intepreter (like the query analyser in SQL Server), try taking the actual line of code that you are sending to the server, and pasting it into the intepreter. If that produces the same result as you are seeing in VFP, that will at least point to the query itself as the source of the error.

Final point: Are you sure you want INTO TABLE in the query? Where do you expect the table to be created? Remember, it's the back end that is executing the query, not VFP.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[dgrewe]

1. SqlProc() is a wrapper for SQLEXEC. there is a Global memvar for the database back end and SQLPROC formats the VFP select statement for the back end the customer uses.

2. The customer can use VFP, MSSQL, ORACLE or MYSQL with the product. there are Wrapper functions that converts all VFP ODBC statement to the backend controlled my Memvars.

3. I have taken the string created by the lcWhere above and ran it in the MSSQL Server Managenet Studio and the Statement runs. It just returns every one in the same facility as a contact.

4. I added the words INTO TABLE for the example code to make it readable to people. The Wrapper SQLPROC() actually returns a Cursor. The programmer then can turn it into a Table, view, or whatever he needs.


David W. Grewe Dave

 

[Mike Lewis]

Dave,

I have taken the string created by the lcWhere above and ran it in the MSSQL Server Managenet Studio and the Statement runs. It just returns every one in the same facility as a contact.

So, you're saying that you get the same (incorrect) result when run directly in the back end as when you run it from Foxpro?

If that's the case, then the problem clearly lies in the syntax of the SELECT statement. Off-hand, I can't see anything wrong with it, but it's a pretty complex statement, and I haven't looked at it closely.

You might try stripping out all the VFP-related stuff, then showing the query to the folk over in the SQL Server forums here on Tek Tips. I've found them very good at spotting errors in SQL code.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[dgrewe]

Mike,
Yes MSSQL returns the exact same results in the Studio as VFP does when called in the program.

Further testing found another problem with it.

Run it once and select any ID returned in the cursor.
Change the lnBookId to the new number and run the Select statement again.

The first ID number does not show up in the second Cursor as a contact for the second person.

You would assume
if BookID#1 was a contact for BookID#2
then BOOKID#2 should have contact with BOOKID#1

I have meeting to go to so it might be tomorrow before I can post the actual MSSQL Studio Statement.



David W. Grewe Dave

 

[dgrewe]

Actual MSSQL query statement generated by the VFP code above

SELECT book_id , addtime , cellhistid ,  newcellid 
FROM  jcellhist 
WHERE  book_id=351 
AND addtime <= '10/02/2007 23:59:59' 
AND newcellid IN 
	(SELECT jfaciltyid          FROM jfacilty  WHERE unit IN 
	(SELECT DISTINCT(unit)      FROM jfacilty  WHERE jfaciltyid IN 
	(SELECT DISTINCT(newcellid) FROM jcellhist WHERE newcellid > 0 AND book_id=351)) 
AND jsection IN 
	(SELECT DISTINCT(jsection)  FROM jfacilty  WHERE jfaciltyid IN 
	(SELECT DISTINCT(newcellid) FROM jcellhist WHERE newcellid > 0 AND book_id=351)) 
AND facility IN 
	(SELECT DISTINCT(jfaciltyid) FROM jfacilty  WHERE jfaciltyid IN 
	(SELECT DISTINCT(newcellid)  FROM jcellhist WHERE newcellid > 0 AND book_id=351))) 
ORDER BY addtime , book_id

Problem#1, Way to many records are being returned.

Problem#2, one record being returned is book_id=352
replace 352 into the above query and book_id=351 is NOT returned.




David W. Grewe Dave

 

[dgrewe]

transcripe typing error in code above
correct code is

SELECT book_id , addtime , cellhistid ,  newcellid 
FROM  jcellhist 
WHERE  book_id=351 
AND addtime <= '10/02/2007 23:59:59' 
AND newcellid IN 
	(SELECT jfaciltyid          FROM jfacilty  WHERE unit IN 
	(SELECT DISTINCT(unit)      FROM jfacilty  WHERE jfaciltyid IN 
	(SELECT DISTINCT(newcellid) FROM jcellhist WHERE newcellid > 0 AND book_id=351))
AND jsection IN 
	(SELECT DISTINCT(jsection)  FROM jfacilty  WHERE jfaciltyid IN 
	(SELECT DISTINCT(newcellid) FROM jcellhist WHERE newcellid > 0 AND book_id=351))
AND [COLOR=red]jfaciltyid[/color] IN 
	(SELECT DISTINCT(jfaciltyid) FROM jfacilty  WHERE jfaciltyid IN 
	(SELECT DISTINCT(newcellid)  FROM jcellhist WHERE newcellid > 0 AND book_id=351)))

David W. Grewe Dave

 

[bborissov]

David,
Run the SQL Profiler and see what is the exact SQL Statement ran when you runs if from VFP. Copy and paste it in New query window in SSMS and run it. See what happens.
Also so many IN()s will slow down that query dramatically (If you have a lots of records). I prefer INNER JOINS. They are optimizable and executed only once, not for each record.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[dgrewe]

borislav
the above code is the SQLLog of the converted VFP code sent to the MSSQL back end, Only I cleanded it up and formated it for readability.
it actually looks like this

SELECT book_id,addtime,cellhistid,newcellid FROM jcellhist WHERE book_id=351 AND addtime<='10/03/2007 23:59:59' AND newcellid IN (SELECT jfaciltyid FROM jfacilty WHERE unit IN (SELECT DISTINCT(unit) FROM jfacilty WHERE jfaciltyid IN (SELECT DISTINCT(newcellid) FROM jcellhist WHERE newcellid >0 AND book_id=351)) AND jsection IN (SELECT DISTINCT(jsection) FROM jfacilty WHERE jfaciltyid IN (SELECT DISTINCT(newcellid) FROM jcellhist WHERE newcellid>0 AND book_id=351)) AND jfaciltyid IN (SELECT DISTINCT(jfaciltyid) FROM jfacilty WHERE jfaciltyid IN (SELECT DISTINCT(newcellid) FROM jcellhist WHERE newcellid>0 AND book_id=351)))

David W. Grewe Dave