Hey there,

I'm building some dynamic SQL in a formclass which at run-time fills in some values based on a list of values:

1. Active user's login-code
2. Combo-box which determines the 'where' clause
3. Combo-box which determines the 'order by' clause
4. Class-property with an additional 'where' clause

The construction is that there's a master-table with some description records, and a child-table matching these records to the users of the system. The child table contains information on whether or not the user wants to see this record, if it's a favorite record and the last time the user accessed this record.

The structure is like this:

tbl_master
----------
ID N 6
DESC C 50
INSTALLED L

tbl_child
---------
ID N 6
USER C 5
NOTSHOWN L
FAVORITE L
ACCESSED T

I used a JOIN-statement to make this work, however it has the odd-behaviour that it only shows the records which any of the users has already accessed. So there has to be an entry for a master record in the child table for the user (which has never accessed the record) to see the record. The statement is like this:

* Here the basics of the SQL statement are created, including the join to the usertable

lcSelect = "SELECT "+ lcTblMaster + ".id, desc, notshown FROM " +lcTblUser
lcSelect = lcSelect + " LEFT OUTER JOIN " + lcTblUser + " ON " + lcTblMaster + ".id = " + lcTblUser + ".id AND "
lcSelect = lcSelect + lcTblUser + ".user = '" + ALLTRIM(oApp.cUserName) + "'"
lcSelect = lcSelect + " WHERE installed == .T. AND "
  
* A case structure is used to decide which specifics are needed for the current view, in a general way
  
DO CASE
	CASE This.cmbRomView.Value = "Default" && All 'shown' records
		lcSelect = lcSelect + " NOT notshown == .t. "
	CASE This.cmbRomView.Value = "Favorites"
		lcSelect = lcSelect + " favorite == .t. "
	CASE This.cmbRomView.Value = "Alles"
    	           lcSelect = lcSelect + " 0=0 "
	OTHERWISE
    	           lcSelect = lcSelect + " 0=0 "		
ENDCASE

* Using a property on the form, specific filtering options can be set

IF LEN(This.csqlwhere) > 0
	lcSelect = lcSelect + " AND " + This.csqlwhere
ENDIF
  
* Set the displaying order based on the second combo

lcSelect = lcSelect + ' order by '

* For future expansion, this has already been changed into a CASE. Right now it seems a bit overkill
 
DO CASE
	CASE This.cmbSorteren.Value = 'desc'
		lcSelect = lcSelect + ' desc '
	CASE This.cmbSorteren.Value = 'lastaccess'
		lcSelect = lcSelect + ' accessed desc '
	OTHERWISE
		lcSelect = lcSelect + ' desc '
ENDCASE
  
* Specify the destination of the result

lcSelect = lcSelect + " Into Cursor crsViewTemp "

* Take the command, and place it on the listbox

ThisForm.lstView.RowSource = lcSelect

Changing the join-statements doesn't work. I tried creating the same thing with the view-designer, but it somehow keeps messing up. Does anyone have an idea what's wrong with this?