SQL and Multiple WHERE clauses using a global variable

[eoneon123]

Thanks in advance, I'm an intermediate FM user and a beginner at SQL. Basically I've been struggling with using a SELECT statement with multiple WHERE clauses where one of the values is a $$global variable. I'm using a plug in (SQLrunner) but it doesn't seem to work when I just use FM native SQL function. When I use only one of the WHERE values below, it works fine. It also works fine if I give the WHERE values explicit numbers. But for some reason when I combine the two values, the function returns nothing. This is the same result whether I run it from the data viewer or via a script. I'm sure the answer is something simple but I can't seem to get my head around it.

ExecuteSQL (

"SELECT sortID

From Premises

WHERE sortID = ? and AncestorID = ? "

; "" ; "" ; (Max(Premises::sortID)); $$AncestorID)

)

While I'm at it, I'm also wondering how to convert my global variables into usable numerical values so I don't get an "incompatible data types" error message when trying to use those values in later script steps . I'm defining the global variable using a SQL SELECT statement, which returns the value from a number field. But the only way I can use that value is a subsequent SQL function is to first set a global number field with said variable, and then create another variable in a subsequent step drawing from that field. I think that's clear, but the script steps look something like this:

1. Set Variable [$$globalValue: Value ExecuteSQL...]

2. Set Field [global number field; $$globalValue]

3. Set Variable [second iteration of $$globalValue]

4. Subsequent SQL function

I've tried to figure this out but to no avail. Many thanks to any help thrown my way!!!

[eoneon123]

Well, I figured out the second part of this question; I just used a GetAsNumber($$GlobalValue) and the "incompatible" error went away. I also worked around the first problem by swapping the placement of (Max(Premises::sortID) and $$sortID. I don't know why this works (and I wish I did) but it does. But my next question might also speak towards that confusion. I want to use the result of this select statement as a numerical variable to be used in a subsequent calculation. But the result returns the dreaded "?". If I use a fixed value like, sortID - 1, the statement works. But when I use a dynamic value in place of "1" the statement fails. I'm sure the answer is right in front of me but I can't figure it out for the life of me. If anyone can shed some light, help a kid out.

Let([

~sql = " SELECT sortID - $$GlobalValue FROM Premises WHERE sortID >= ? AND TheoryID = ? "

];

ExecuteSQL ( ~sql; "," ; "¶" ; $$sortID ; $$TheoryID )

)

[Hatsu]

Which field (name) should this "sortID - $$GlobalValue" correspond to? I can't imagine what you want to achieve with this in the SQL statement.

[eoneon123]

I'm pretty new at this but I've been experimenting with displaying hierarchies inside portals where my sortID field is reordered when I add or delete records inside the portal. So far I have three levels: Lev 1, Lev 2 and Lev 3. Lev 1 records are parent records of Lev 2, while Lev 3 records are children of Lev 2. I keep track of this with three number fields: the recordID, the recordID of the parent record, and the recordID of the parent's parent's recordID (in case of Lev 3).

A record's relative sort position is determined by: (1) the row it's created from and (2) a selection of buttons in the corner of each portal row where I can select the Lev of the record to be created. In this way I can predetermine the order and ancestry of each record in order to maintain the desired presentation in a few scenarios. The scenario in question: if I delete a parent record, I want to have the option to delete that parent AND all of it's children. But this leaves a gap in the numbers in sortID field. This is where the above script came in. I wanted to get the found count of a set of records (parent and all of its children) so that I could UPDATE the records underneath the deleted records to reflect a sequential sequence of sortID numbers.

I've worked out everything except the "sortID - $$FoundCount" part. Which is to say, I know how to get the found count into a variable, but I don't know how to use the number result of that variable to subtract from the sortID in a subsequent UPDATE script step that updates the order of the rest of the records in the portal. This sounds so convoluted, but if I don't just put it out there as is, I'll never get around to doing so.

One note, I've seen several unlimited parent-child hierarchy set ups but none using SQL. I know that mine is crude (at best) but I'm just as interested in knowing why my use of variables is failing inside these sql statements.

[beverly]

Let([

// ~sql = " SELECT sortID - $$GlobalValue FROM Premises WHERE sortID >= ? AND TheoryID = ? "

~sql = " SELECT ( sortID - ? ) FROM Premises WHERE sortID >= AND TheoryID = ? "

];

// ExecuteSQL ( ~sql; "," ; "¶" ; $$sortID ; $$TheoryID )

Execute SQL ( ~sql; "," ; "¶" ; $$GlobalValue ; $$sortID ; $$TheoryID )

)

The $$ won't be evaluated INSIDE the quoted query. You need to pass it as another argument.

You are correct that you need to have the number values passed as numbers and the text values passed as text.

Beverly

[eoneon123]

Thank you, Beverly. I'm not sure why I couldn't figure that out myself.