Change SQL statement?

[keepingbusy]

Hi

The following SQL statement produces members records linked to a project:

Select * From MEMBERS WHERE MEMBERID In ;
  (Select MEMBERID From LKPJTYP Where PROJECTURN=m.mprojnumb) ;
  And ADD05 Like m.mcounty INTO TABLE tempinsert+'dbf'

I am trying to link a "Supervisor" to the members who are linked to a specific project.

I have tried assigning the Supervisors SURNAME and FORENAME to variables (m.surname and m.forename1) and changed the SQL statement:

Select * From MEMBERS WHERE MEMBERID In ;
  (Select MEMBERID From LKPJTYP Where PROJECTURN=m.mprojnumb) ;
  And SURNAME Like m.surname AND FORENAME1 LIKE m.forename1 ;
  INTO TABLE tempinsert+'dbf'

When I run this I get an error:

SQL: Column 'SURNAME' is not found

Strange that because the SURNAME field exists in the table.

So just to clarify:

I select a Supervisor from a list
A project is selected (e.g. FOOTBALL)
A list of members should be produced who are linked to the Supervisor (by MEMBERID) but all the linked members must be associated to the project 'FOOTBALL'

I would be grateful for some guidance on this please and if I haven't explained this enough, please let me know.

Thank you.

Lee

 

[JRB-Bldr]

Strange that because the SURNAME field exists in the table.
Your using m.surname is telling the SQL statement that you have a memory variable named 'surname' and it is to use that.
By using the m. prefix you are telling things to ignore any field by that name and look for a memory variable. So it doesn't matter if there is a field named SURNAME or not.

So, if I understand what you say above, you have something like the following:
Supervisor (a.k.a. 'Surname')
Project
Member

And you want to bring those together in your SQL Query results?

One way would be something like:

* --- Define specific criteria memory variables ---
m.mprojnumb = <something>
m.Surname = <something else>
m.Forename1 = <3rd something>
cResult = tempinsert+'dbf'
* --- Execute Query ---
SELECT MEMBERS.* ;
  FROM MEMBERS, LKPJTYP;
  WHERE LKPJTYP.MEMBERID = MEMBERS.MEMBERID;
  AND LKPJTYP.PROJECTURN = m.mprojnumb ;
  AND UPPER(m.Surname) $ UPPER(LKPJTYP.SURNAME) ;
  AND UPPER(m.Forename1) $ UPPER(LKPJTYP.FORENAME1);
  INTO TABLE (cResult)

Good Luck,
JRB-Bldr

 

[keepingbusy]

Hi JRB-Bldr

Still getting that same error.

Perhaps I need to ad a bit more detail to this problem.

The link between the MEMBERS table and the LKPJTYP (Projects) table is a numeric field called MEMBERID

The PROJECTURN is a numeric field that stores a number (e.g. 18) against the project description (e.g. FOOTBALL)

The Supervisor link to the member is a numeric field called LOCALYN and the field linked to the member is another numeric field called LOCTOMEM.

So as an example:

The Supervisor would have 18 as the LOCALYN
The member would have 18 as the LOCTOMEM
The members would have a unique MEMBERID numbers (e.g. 100, 105, 280 etc)
The project table field MEMBERID is a numeric field which would be the same as the MEMBERID if an associated project is present (e.g. FOOTBALL)

I hope that makes a bit more sense.

Thank you

Lee

 

[Olaf Doschke]

Are there fields MEMBERS.SURNAME and MEMBERS.FORENAME1? Your select asks for them to be present in the MEMBERS table.

Bye, Olaf.

 

[keepingbusy]

Hi Olaf

The answer to your question is yes.

Whilst trying other options, I used some old fashioned syntax to try and achieve the objective. I came up with this which works (and very quickly):

tempinsert=SYS(3)
m.mprojnumb = 11
m.mloctomem = 18

cResult = tempinsert+'.dbf'

SELECT * FROM MEMBERS WHERE LOCTOMEM=m.mloctomem ;
  INTO TABLE (cResult)
CLOSE DATABASES

tempfile=SYS(3)

USE MEMBERS SHARED
COPY STRUCTURE TO tempfile+'.dbf'
CLOSE DATABASES

USE (cResult) EXCL
GO TOP
SCAN
  STORE " " TO mfound
  m.memberid=0
  m.memberid=MEMBERID
  m.recnum=0
  m.recnum=RECNO()
	
  USE LKPJTYP SHARED ORDER MEMBERID
  GO TOP
  SEEK(m.memberid)
  IF PROJECTURN=m.mprojnumb
    DO FOUNDONE
  ENDIF

  USE (cResult) EXCL
  GO m.recnum
ENDSCAN

CLOSE DATABASES

* As a test look at the file here

USE tempfile+'.dbf' EXCLUSIVE
BROWSE
CLOSE DATABASES

* CLEAN UP ETC

RETURN

******************
PROCEDURE FOUNDONE
******************

USE tempfile+'.dbf' EXCLUSIVE
APPEND FROM MEMBERS FOR MEMBERID=m.memberid
CLOSE DATABASES
RETURN

I'm sure that there is a more efficient (and quicker way to code this) but it's working!

Lee

 

[keepingbusy]

Think I posted too soon. Just ran a few tests with more related records and I'm not achieving the desrired result.

I would appreciate any other guidance.

Thank you

Lee

 

[JRB-Bldr]

As to running the code that I suggested and still getting the same error message...

Is there a field LKPJTYP.SURNAME ?
If not then obviously it cannot be used and/or referenced in the SQL Query statement.
I included it because you used it in your original posting:
And SURNAME Like m.surname

Maybe the field is in the other table MEMBERS.
If so, then change the line to: MEMBERS.SURNAME

I'm not achieving the desrired result.

Either the SQL Query is running without throwing any error messages or not - which is it?

If no error messages then the general syntax is now correct.

Specifically in what manner is the result not desired ?

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Lee,

as I don't have your data I don't know how to test this code and give any guidance, sorry. This iis really bad code, sorry to say so, unreadable and incomplete. I don't know why you reopen (cResult) within the SCAN loop and can only guess you do so because FoundOne function or prg does close tables.

Please give example data and expected result and then we can talk about SQL.

Bye, Olaf.

 

[Olaf Doschke]

Sorry for my ignorance, Lee, I see the FoundOne procedure is at the end. I'd open tempfile before the loop and do this APPEND within the loop instead of a seperate procedure, don't CLOSE DATABASES ALL, even not CLOSE TABLES ALL. Why?

Bye, Olaf.

 

[Olaf Doschke]

Now that I had the time, looking at your second code you're not even doin, what your first SQL did, You do not filter on ADDD05 = m.country. Not to speak of surname and forename1.

Otherwise your SCAN loop does filter members, which are referenced in LKPJTYP.


Besides that. The error "Column 'SURNAME' is not found" has to be taken serious. Maybe the field has a CAPTION "SURNAME" but is named different. In a BROWSE you see field CAPTIONS. BROWSE NOCAPTIONS to browse with real field names or open members in the table designer.

If SQL says there is no colum surname, there is no column surname.

Bye, Olaf.

 

[keepingbusy]

jrbbldr

Is there a field LKPJTYP.SURNAME ?

No.

There are four fields in that table. The relevant ones being MEMBERID and PROJECTURN.

I included it because you used it in your original posting: And SURNAME Like m.surname

Yes, that was me just trying different options to see if I could find the correct syntax. I am not that ofay with SQL commands.

Maybe the field is in the other table MEMBERS.
If so, then change the line to: MEMBERS.SURNAME

There is a SURNAME field in the MEMBERS table and I am going to revisit this tonight.

Olaf

And ADD05 Like m.mcounty INTO TABLE

This was the original code I used for another search. I just gave that as an example (as per my post) to show what I had previously done and how I change it to try and achieve a different result.

I will take a step back and have another look at this. I am sure there is a simple SQL command that will achieve what I'm trying to do (Example):

The Supervisor would have 18 as the LOCALYN
The member would have 18 as the LOCTOMEM
The members would have a unique MEMBERID numbers (e.g. 100, 105, 280 etc)
The project table field MEMBERID is a numeric field which would be the same as the MEMBERID if an associated project is present (e.g. FOOTBALL)

Lee

 

[Olaf Doschke]

In general you talk about members of a project. I assume the people in the members table can take part in many projects, and each project has several members doing it and one supervisor.

This suggest the following table design:

a) members - the people
b) projects - the projects
c) projectmembers - a cross reference table linking memebers with projects

The supervisor could be supervisormemberid within the projects table.

How is your table design? I don't understnd "LOCTOMEM", I don't understand "LACALYN", I do understand memberid, but wonder, why you would have that as a field in projects, if it's not the supervisor member, you would have multiple records in projects, if there are multiple members in a project, thus it's not the projects table anymore, which would only earn this name, if there is one record per project.

Could it be, you just don't have the needed normalisation and therefore need more complicated queries than usually needed, if your data table design is normalized?

Bye, Olaf.

 

[JRB-Bldr]

I am not that ofay with SQL commands.
Then I'd suggest that you start there.

There are a lot of SQL Syntax references on the web.
A Google search for: SQL syntax turns up a LOT of 'finds'.

Additionally if you do a Google search for: VFP SQL you will get a number of 'finds' which might help you generally understand SQL syntax usage in VFP.

VFP has some SQL commands that are unique to VFP and which will not work in other databases such as SQL Server, but you need to understand the basics first.

The examples of your data that you have given do not give us the full 'picture' of your data table structure nor what you are attempting to do.

We have already tried to assist you in a number of ways based on our limited understanding of what you are working with (i.e. your data table structures, etc.) and what you are working towards. I'd suggest that you work on getting a better understanding of the SQL Query syntax and apply it to your specific needs.

Once you have the basics understood you should be able to get the syntax correct and, if not, then for a few simple suggestions to correct any specific syntax problems we are always here to assist you.

Good Luck,
JRB-Bldr

 

[keepingbusy]

Thanks to those who posted. Advice taken.