Is WHERE RECORD = ? possible

[Joshua]

Is the following query possible? If yes, what is missing from my format. If not, what are my alternatives to a achieve the same goal?

ExecuteSQL ("SELECT Name FROM Customerdb WHERE ROW = 1"; ""; ""; "")

or

ExecuteSQL ("SELECT Name FROM Cutomerdb WHERE RECORD = 1"; ""; ""; "")

 

Thanks in advance.

Edited July 23, 2015 by Joshua

[eos]

What exactly are you trying to do?

[Kris M]

For the first item you should look at

http://help.filemaker.com/app/answers/detail/a_id/12061/~/specifying-offset-and-fetch-first-clauses-in-filemaker-pro-using-the-executesql

For the second question EOS is on the right track because the right answer is contingent on your response. Their are many ways to slice that cake.

[Joshua]

Thank you for your response. On a particular script that I'm working on... I need to pull certain information one record at a time. Because some of the records will have the field blank the work around seems inefficient especially when I have to repeat it several times in different parts of the script:

 

Set Variable [$CustomerIDs; Value:ExecuteSQL ("SELECT CustomerIDs FROM Customerdb"; ""; ""; "")]

Set Variable [$CustomerID; Value:GetValue ($CustomerIDs; MainPage::RecordNum)]

Set Variable [$CustomerName; Value: ExecuteSQL ("SELECT Name FROM Customerdb WHERE CustomerID = ?"; ""; ""; $CustomerID)]

 

What I was wondering is it possible to do the following:

Set Variable [$CustomerName; Value: ExecuteSQL ("SELECT Name FROM Customerdb WHERE ROW = ?"; ""; ""; MainPage::RecordNum)]

 

[eos]

 

On a particular script that I'm working on... I need to pull certain information one record at a time.

Gee … now everything is clear. 

Set Variable [$CustomerName; Value: ExecuteSQL ("SELECT Name FROM Customerdb WHERE ROW = ?"; ""; ""; MainPage::RecordNum)]

What you mean is probably along the lines of

ExecuteSQL ( "
  SELECT Name
  FROM Customerdb 
  OFFSET " & Get (RecordNumber ) /* MainPage::RecordNum ? */ - 1 & " ROWS
  FETCH FIRST 1 ROW ONLY 
  " ; "" ; ""
)

OTOH, why not use

Set Variable ( $listOfCustomerNames ;  ExecuteSQL ( " SELECT Name FROM Customerdb " ; "" ; "" ) 
# [ or, if in context: List ( Customerdb::Name ) ]
Set Variable [ $currentCustomerName ; GetValue ( $listOfCustomerNames ; Get ( RecordNumber ) ) ] 

Hard to tell what you need without knowing any details.

[beverly]

eos has presented two very viable options!

@Joshua the ExecuteSQL function in FM is not 'full-featured'. You cannot use all the available SQL functions that you might find in MySQL or MS SQL or Oracle or ....

In case you haven't seen this, it might help you see what you can/cannot do with ExecuteSQL:

http://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/

The blog article was just a start. I recommend that you get the PDF and the sample files.

beverly

[Joshua]

Ok, thank you.