how can i feed a few data values to an select sql to get the results that i need 3

[titoneon]

Hi,
Sorry for the type of subject i typed but i will try to explain what i am trying to do here, probably it is something already out there do that too for dbt tables.
I don't know if anyone still remember the "Old Microsoft query", it works in win98 but won't work on win 7, so i am looking for something like that, where the user can "select the table they want to query" then use "the criteria field, "the value to type" for that criteria field and "OR", means i can add as many value i want, then can execute the SQL OR QUERY.
In my case actually i can do this SQL for the criteria w/o a problem cause i will use a particular field for it, "my problem is how to enter as many values as i want and be able to add them concatenated" so them execute the sql and obtain the results in a cursor
example let say
criteria field: Job_no
Values: 117353
OR: 117357
OR: and so on until i decide to stop, now i need to exec the sql and get all the rest of the information i need in my result, i know the sql could be something like this
Select Job_no, date, field3,field4,.... from table where Job_no="117353" or job_no="117357" or job_no="######" or job_no="#####" and so on into cursor junkresult
my problem resides on how can i programaticly add the values to create this (where job_no="######" or job_no ="######" or job_no ="######" and so on, ###### this represent values, i just did not typed
Actually i will need a form where the user can enter as many OR they want and after that execute the SELECT SQL.
Any help is very welcome, in advance thanks a lot

 

[TamarGranor]

Take a look at Stonefield Query:

http://www.stonefieldquery.com.

Tamar

 

[JRB-Bldr]

my problem is how to enter as many values as i want and be able to add them concatenated

You can dynamically build your SQL Command from Form selections that your user enters (as long as they are Valid entries).

Very roughly something like:

* --- User makes Query Entries into Form ---
* --- Then User clicks on 'Build Query' button Or 'Execute Query' button ---
* --- Query then Built and Executed ---
cFieldList = ''
FOR FldCntr = 1 to n
   cThisField = "Field" + STR(FldCntr,1)
   cFieldList = cFieldList + cThisField + ","
ENDFOR
nLen = LEN(cFieldList)
cFieldList = LEFT(cFieldList,nLen-1)  && Remove trailing comma

cTableName = "MyDataTable1"
cCriteriaString = "Customer = 'GEORGE' AND EnterDate = DATE()"
cOutputCursor = "ResultantData"

DIMENSION arySQLCmd(1)  && Create 'container' into which to build query string

arySQLCmd(1) = "SELECT " + cFieldList
arySQLCmd(1) = arySQLCmd(1) + " FROM " + cTableName
arySQLCmd(1) = arySQLCmd(1) + " WHERE " + cCriteriaString
arySQLCmd(1) = arySQLCmd(1) + " INTO CURSOR " + cOutputCursor

* --- Now Execute Query String ---
&arySQLCmd(1)

By using an Array, you eliminate many of the max character issues that might have been encountered if you were to use a Memory Variable.

BUT - The BIG problem with users working with ANY Query Builder is their knowledge (or lack thereof) of the data tables and how to use them to achieve their desired results.

Will they know what the various Fields represent (especially when field names abbreviated to 10 char names)?
Will they know how to and what field(s) to use to 'relate' multiple tables within the Query?
What Query selection criteria to utilize?
Will they know what needs to be combined algorithmically?
Etc.

And then if the query successfully executes, will the user know what to do with the resultant Cursor?

Good Luck,
JRB-Bldr

 

[titoneon]

Hello JRB-Bldr,
Thanks for getting back man, yes the users who are going to use this, will know, also we need the result of the cursor to get into excel.
i need the where clause to be as WHERE job_no= value OR job_no= value OR job_no= value OR job_no= value OR job_no= value OR as many time the user enter in a textbox the vsalue of the Job_nos they want to process but in what shot, in other words i need to enter all these job_no values at once and then execute the query, so that is what i need, so i guess it has to be so kind of textbox in a form where i can enter the job_no value, press enter, then enter gain the next value and so on once i have entered all of them then execute the sql, of course i will need probably a combobox to select the field in thi case the job_no but maybe could be aby other field or a combination of two fields but rarely won't happen the two fields combination
so i don't know if the "cCriteriaString = "Customer = 'GEORGE' AND EnterDate = DATE()"
will be the solution, actually we will need cCriteriaString = "job_no = '114358' OR JOB_NO = "117357" OR JOB_NO = "117358" OR JOB_NO= and so on ......
I don' know if explained myself correctly, sorry for my English
Thanks a lot let me know if this change the approach that you posted.

 

[JRB-Bldr]

i need the where clause to be as WHERE job_no= value OR job_no= value OR job_no= value OR job_no= value OR job_no= value OR as many time the user enter in a textbox the vsalue of the Job_nos they want to process but in what shot

If you do it the way I have done it and the way I have suggested above, its all dependent on your User Form and the options that you give them there.

If you give them a means of entering multiple job numbers then when the user clicks "Run" (or whatever), your code then builds the WHEN string as needed and incorporates it into the SQL Command string.

One possibility on the Form might be to have the users enter Job numbers into a Grid (maybe named 'JobNos'). Then when the SQL Command string is being 'composed' you could do something like:

cCriteriaString = ''
SELECT JobNos
SCAN FOR !EMPTY(JobNo)
   cThisJobNo = JobNos.JobNo
   * --- Check for Duplicate Entries ---
   IF !(cThisJobNo $ cCriteriaString)
      cCriteriaString = cThisJobNo + " OR "
   ENDIF
ENDSCAN
* --- Remove Trailing ' OR '
nLen = LEN(cCriteriaString)
cCriteriaString = LEFT(cCriteriaString,nLen-4)

* --- Note that you can also add other Criteria from User entries into other Form Objects (Textboxes, other Grids, etc.) as needed ---
< do whatever >

* --- Now Use The Criteria String As Shown Above ---

Good Luck,
JRB-Bldr

 

[mjcmkrsr]

Hi,

You may want to populate an array (laArray) with the values you search. Then

select ... where ASCAN(laArray, job_no) # 0 into ...

hth

M.

 

[TamarGranor]

It's better to populate a cursor than an array with the user's values. When you use ASCAN() in a query, you can run into issues around SET EXACT vs. SET ANSI. Also, it'll probably run faster with a cursor.

Tamar

 

[titoneon]

Hi Everyone,
1-this is what i am trying to do, i have a combobox displaying all the values from one field so
in the combo1 rowsource i have this code: select distinct job_no from nengtime into cursor cjobno order by job_no
this will display all the value in the combobox

2- What i also did was in the form load event i have this code: CREATE CURSOR jobnos (rec_id Int AUTOINC, job_no C(6))

3- what i would like to know is if it possible to select multiple values from the combobox while scrolling and be able to insert those values selected into the jobnos cursor ? if so can i get ideas

4- what i am trying to accomplish is to use sql select by join the nengtime and jobnos where job_no = jobnos.job_no
use something like this after i have all the job_no into the cursor JOBNOS
SELECT * FROM Nengtime JOIN Jobnos ON Nengtime.job_no=Jobnos.job_no WHERE Jobnos.rec_id>m.maxrec_id into cursor result
Thanks

 

[JRB-Bldr]

select multiple values from the combobox while scrolling

If the values for the variety of Job No's already exist, then it would be better and easier to create a Cursor/Table with those values Plus an extra Logical field (something like INCLUDE).

Maybe something like:

SELECT DISTINCT job_no, .F. AS Include;
   FROM nengtime;
   ORDER BY job_no
   INTO CURSOR cjobno READWRITE

Then use a Grid where the user could Check or Not to Include or Not individual JobNo's

So that when you 'compose' your SQL Query string, merely SCAN/ENDSCAN the grid cursor/table for INCLUDE.

Good Luck,
JRB-Bldr

 

[titoneon]

JRB-Bldr,
How should i write then my select sql in this case, cause the one i have posted does not work, i got error message
"job_no is not unique and must be qualified"

Also i have a add command button to insert into the cursor jobnos with this code:
x = Thisform.Combo1.Value
INSERT INTO Jobnos (job_no) VALUE ( m.x)

so each time i select from the combobox a value i click on the add button, so the values are inserted into the cursor but it gets repeated in the cursor even thou is not repeated in the combo box
Thanks

 

[TamarGranor]

This sounds like a job for a two-column mover rather than a single combobox. That's a control with two listboxes and some buttons in between. You put the list of choices in the left listbox and the user can move the ones she wants to the right listbox by double-clicking or using the buttons.

There's a set of movers in the FoxPro Foundation Classes and a form that demonstrates them in the Solution Samples. Search for "mover" in the Solution Samples and take a look at the "Use mover list boxes for selecting data" sample.

Tamar

 

[JRB-Bldr]

I can't say what your data fields are like but I was merely making a small modification (adding the INCLUDE field) to the code you posted:
select distinct job_no from nengtime into cursor cjobno order by job_no

But I do see a typo in the code I posted.

SELECT DISTINCT job_no, .F. AS Include;
   FROM nengtime;
   ORDER BY job_no
   INTO CURSOR cjobno READWRITE

I forgot to put a semi-colon after the ORDER BY job_no;

Just run your code from your Command window and make any necessary modifications until you get the Cursor results you want. Then when it works, put it into the Load Method of the VFP Form.

so each time i select from the combobox a value i click on the add button

No. Forget using the ComboBox at all.
If the cjobno Cursor is the RecordSource (type: Alias) for a Grid, then it will appear.

And, if when configuring the Grid you put a Checkbox into it's Column2 (where the Include field is displayed)
thread184-99913
thread184-887233
thread184-161180
then the user merely Checks (or leaves Un-Checked) those JobNo's that they want to Include in the SQL Query.
Their checking the Grid Column's box will set INCLUDE = True which can then be used in the SCAN/ENDSCAN to 'compose' your SQL Query string.

Good Luck,
JRB-Bldr

 

[JRB-Bldr]

Tamar's suggestion of using a Mover object is another alternative INSTEAD OF USING A COMBOBOX.

However if you use a Grid, I'd also recommend that you use the DynamicBackGround properties of the Grid Columns to change the color so as to more dramatically allow the user to SEE the Include's prior to the RUN.

I like to use something like: IIF(Include,RGB(248,254,14),RGB(255,255,255)) in ALL of the Column properties so that the Includes show up as a Bright Yellow

Good Luck,
JRB-Bldr

 

[mjcmkrsr]

Hi,

You may want to use a "Mover Combobox" or a grid with a selection field - if there aren't to may fields to display.
You may also have a look at the code below. You type the job_no(s) you want into the textbox, add it to the selection and execute the sql. Just copy the text between BOC and EOC into a .PRG file and run

hth

MarK

BOC
********************************************
*!* grid_search_from_array.prg
PUBLIC oform1

oform1=NEWOBJECT("form1")

AddProperty(oForm1,"gaArray[1]",0)

oform1.Show
Read Events
Close all
Clear All
RETURN


**************************************************
DEFINE CLASS form1 AS form
AutoCenter = .T.
Caption = "Grid with searching in cursor"
Height = 480
Width = 600
Borderstyle = 2

ADD OBJECT lblOData AS label WITH ;
Caption = "Original Data", ;
Left = 10, ;
Top = 30

ADD OBJECT lblSelection AS label WITH ;
Caption = "Selection", ;
Left = 300, ;
Top = 30

ADD OBJECT lblResults AS label WITH ;
Caption = "Results", ;
Left = 300, ;
Top = 246

ADD OBJECT grid1 AS grid WITH ;
ColumnCount = -1, ;
Left = 10, ;
Top = 48, ;
Width = 270, ;
Height = 408, ;
RecordSource = "curTemp"

PROCEDURE grid1.Init
WITH This.Column1
.ControlSource = "F1"
.Header1.Caption = "F1"
ENDWITH

ENDPROC

ADD OBJECT grid2 AS grid WITH ;
ColumnCount = -1, ;
Left = 300, ;
Top = 48, ;
Width = 270, ;
Height = 198, ;
RecordSource = "curTemp2"

ADD OBJECT grid3 AS grid WITH ;
Visible = .F., ;
ColumnCount = -1, ;
Left = 300, ;
Top = 264, ;
Width = 270, ;
Height = 192, ;
RecordSource = "curTemp3"

ADD OBJECT txtTextBox as Textbox WITH ;
Left = 12, Top = 5, Width = 60, Value = 0, InputMask = "9999"

ADD OBJECT cmdDoit AS CommandButton WITH ;
Left = 84, Top = 5, Height = 20, Width = 90, Caption = "-> Selection"

PROCEDURE cmdDoit.Click()

IF ThisForm.grid2.Visible = .F.
WITH ThisForm.grid2
.ColumnCount = -1
.RecordSource = "curTemp2"
.Visible = .T.
.Refresh()
ENDWITH
ENDIF

INSERT INTO curTemp2 VALUES (ThisForm.txtTextBox.Value)

WITH ThisForm.grid2
.Refresh()
ENDWITH

ENDPROC

ADD OBJECT cmdSearch AS CommandButton WITH ;
Left = 180, Top = 5, Height = 20, Width = 90, Caption = "SQL"

PROCEDURE cmdSearch.Click()

SELECT f1, f2 FROM curTemp WHERE f1 IN (SELECT f1 FROM curTemp2) ;
ORDER BY 1 ;
INTO CURSOR curTemp3

IF _tally > 0
WITH Thisform.grid3
.Visible = .T.
.ColumnCount = -1
.RecordSource = "curTemp3"
ENDWITH
ELSE
WITH Thisform.grid3
.Visible = .F.
.ColumnCount = -1
.RecordSource = "curTemp3"
ENDWITH

ENDIF

Thisform.Refresh()




ENDPROC

ADD OBJECT cmdReset AS CommandButton WITH ;
Left = 276, Top = 5, Height = 20, Width = 90, Caption = "Reset"

PROCEDURE cmdReset.Click()

SELECT curtemp2
USE
CREATE CURSOR curTemp2 (f1 I)

SELECT curtemp3
USE

WITH Thisform.grid2
.Visible = .F.
ENDWITH

WITH Thisform.grid3
.Visible = .F.
ENDWITH

Thisform.Refresh()

ENDPROC

PROCEDURE Resize
This.Grid1.Width = This.Width - 20
This.Grid1.Height = This.Height - 40
ENDPROC

PROCEDURE Destroy
CLOSE ALL
Clear Events
ENDPROC

PROCEDURE Load
CREATE CURSOR curTemp2 (f1 I)
CREATE CURSOR curTemp (f1 I, f2 C(20))
FOR i = 1 TO 10000
INSERT INTO curTemp VALUES ( Int(Rand()*5000)+1, "T" + SYS(2015) )
ENDFOR
LOCATE
ENDPROC

ENDDEFINE
*********************************************
EOC

 

[titoneon]

Hi Tamar, JRB-Bldr and mjcmkrsr
I want to thank you for all yours help, i will see all these ideas an trying to figure out how to put something together so i can i have what i want, just remember i am not very savy on VFP, i am the old Dos school, that is the reason why it is a little difficult for me to get things right away but thanks to you guys we can do it.
Again thanks a lot, i will be back with the solution if i got it.

 

[titoneon]

Hi ALL,
I got thanks to all you, i combined a few little thing from your advice and i got it.
You guys are a great team for helping people like me.
Have a great weekend

 

[mjcmkrsr]

Hi,

Thanks. Below the slightly improved code.
You double click on the F1 cell in the "Original data" grid to add this value into the "Selection" grid.
hth
MarK

**************************************************
* grid_search_from_array.prg
PUBLIC oform1

oForm1=NEWOBJECT("form1")

*!* AddProperty(oForm1,"gaArray[1]",0)


oForm1.Show
Read Events
Close all
Clear All
RETURN


**************************************************
DEFINE CLASS form1 AS form
AutoCenter = .T.
Caption = "Grid selecting from cursor"
Height = 480
Width = 600
Minheight = This.Height
MinWidth = This.Width


ADD OBJECT lblOData AS label WITH ;
Caption = "Original Data", ;
Left = 10, ;
Top = 30, ;
Anchor = 3

ADD OBJECT lblSelection AS label WITH ;
Caption = "Selection", ;
Left = 300, ;
Top = 30, ;
Anchor = 1 + 8 + 32

ADD OBJECT lblResults AS label WITH ;
Caption = "Results", ;
Left = 300, ;
Top = 246, ;
Anchor = 32 + 64

ADD OBJECT grid1 AS grid WITH ;
ColumnCount = -1, ;
Left = 10, ;
Top = 48, ;
Width = 270, ;
Height = 408, ;
Anchor = 1 + 2 + 4 + 128, ;
RecordSource = "curTemp"

PROCEDURE grid1.Init
LOCAL loControl
FOR EACH loControl IN This.Column1.Controls
IF UPPER(loControl.Name) = "TEXT1"
BINDEVENT(loControl, "DblClick", THIS, "DblClick")
ENDIF
ENDFOR

WITH This.Column1
.ControlSource = "F1"
.Header1.Caption = "F1"
ENDWITH

ENDPROC

PROCEDURE grid1.DblClick()

INSERT INTO curTemp2 VALUES (This.Value)

ThisForm.grid2.Refresh()

ENDPROC


ADD OBJECT grid2 AS grid WITH ;
ColumnCount = -1, ;
Left = 300, ;
Top = 48, ;
Width = 270, ;
Height = 198, ;
Anchor = 1 + 8 + 32 + 64, ;
RecordSource = "curTemp2"

ADD OBJECT grid3 AS grid WITH ;
Visible = .F., ;
ColumnCount = -1, ;
Left = 300, ;
Top = 264, ;
Width = 270, ;
Height = 192, ;
Anchor = 4 + 8 + 16 + 32, ;
RecordSource = "curTemp3"

ADD OBJECT txtTextBox as Textbox WITH ;
Left = 12, Top = 5, Width = 60, Value = 0, InputMask = "9999"

ADD OBJECT cmdDoit AS CommandButton WITH ;
Left = 84, Top = 5, Height = 20, Width = 90, Caption = "-> Selection"

PROCEDURE cmdDoit.Click()

*!* ThisForm.gaArray[ALEN(ThisForm.gaArray)] = ThisForm.txtTextBox.Value
*!* DIMENSION Thisform.gaArray[ALEN(Thisform.gaArray) + 1]

IF ThisForm.grid2.Visible = .F.
WITH ThisForm.grid2
.ColumnCount = -1
.RecordSource = "curTemp2"
.Visible = .T.
.Refresh()
ENDWITH
ENDIF

INSERT INTO curTemp2 VALUES (ThisForm.txtTextBox.Value)

WITH ThisForm.grid2
.Refresh()
ENDWITH

ENDPROC

ADD OBJECT cmdSearch AS CommandButton WITH ;
Left = 180, Top = 5, Height = 20, Width = 90, Caption = "SQL"

PROCEDURE cmdSearch.Click()
*!* IF ALEN(Thisform.gaArray) > 1
*!* DIMENSION Thisform.gaArray[ALEN(Thisform.gaArray) - 1]
*!* ENDIF

*!* FOR i = 1 TO ALEN(Thisform.gaArray)
*!* WAIT WINDOW + "You choose "+ TRANSFORM(ThisForm.gaArray) TIMEOUT .5
*!* ENDFOR

*!* SELECT f1 FROM curTemp WHERE ASCAN(ThisForm.gaArray, f1) # 0 ;
*!* GROUP BY 1 ;
*!* ORDER BY 1 ;
*!* INTO CURSOR curTemp2

SELECT f1, f2 FROM curTemp WHERE f1 IN (SELECT f1 FROM curTemp2) ;
ORDER BY 1 ;
INTO CURSOR curTemp3

IF _tally > 0
WITH Thisform.grid3
.Visible = .T.
.ColumnCount = -1
.RecordSource = "curTemp3"
ENDWITH
ELSE
WITH Thisform.grid3
.Visible = .F.
.ColumnCount = -1
.RecordSource = "curTemp3"
ENDWITH

ENDIF

Thisform.Refresh()
*!* DIMENSION Thisform.gaArray[1]
*!* Thisform.gaArray[1] = 0

ENDPROC

ADD OBJECT cmdReset AS CommandButton WITH ;
Left = 276, Top = 5, Height = 20, Width = 90, Caption = "Reset"

PROCEDURE cmdReset.Click()

SELECT curtemp2
USE
CREATE CURSOR curTemp2 (f1 I)

SELECT curtemp3
USE

WITH Thisform.grid2
.Visible = .F.
ENDWITH

WITH Thisform.grid3
.Visible = .F.
ENDWITH

Thisform.Refresh()

ENDPROC

PROCEDURE Destroy
CLOSE ALL
Clear Events

ENDPROC

PROCEDURE Load
CREATE CURSOR curTemp2 (f1 I)
CREATE CURSOR curTemp (f1 I, f2 C(20))
FOR i = 1 TO 10000
INSERT INTO curTemp VALUES ( Int(Rand()*5000)+1, "T" + SYS(2015) )
ENDFOR
LOCATE

ENDPROC

ENDDEFINE
*********************************************