Adapting relationship with SQL is slow

[Electric_Soul]

Hello

Knowing a realtionship is NOT case sensiive, I am trying to create and equivalent in SQL. But SQL is case sensitive. What I came up with is the first sql statement. But this statement is really slow, compared to the second statement, which doesn't give correct result, because of case differences.

Does anyone have a clue on how to create a good equivalent, without modifying records??

1.

BE_FileMakerSQL ( "

SELECT

sn."Seriennr.", sum(ap.Menge) SN from Seriennummern sn

INNER JOIN

Artikelposten ap on lower(sn."Seriennr.")=lower(ap."Seriennr.")

WHERE

lower (sn."Artikelnr.") = lower('" & "PN0605-01" & "')

GROUP bY

sn."Seriennr."

HAVING

sum(ap.Menge)>0

" )

2.

BE_FileMakerSQL ( "

SELECT

sn."Seriennr.", sum(ap.Menge) SN from Seriennummern sn

INNER JOIN

Artikelposten ap on sn."Seriennr."=ap."Seriennr."

WHERE

sn."Artikelnr." = '" & "PN0605-01" & "'

GROUP bY

sn."Seriennr."

HAVING

sum(ap.Menge)>0

" )

[Electric_Soul]

Let me guess. Filemaker is storing the indexes in either lower or upper case, depending on the language setting for a table field ?? right ?

but please correct me otherwise :)

[RalphL]

All those calculations in your SQL WHERE & GROUP BY statements are your problem. Why not add a column or field to tables that is an auto-entered calculation that makes the Lower Case calculation. Use these columns or fields in you SQL. This should not affect any thing in your database and users need not be aware of it. Change lower('" & "PN0605-01" & "') to 'pn0605-01'").

To see the Index put the cursor in the field then go to the Insert Menu and select From index..., Command I does this on a Mac.