A complex Select Statement

[Doug Easterbrook]

for the most part, I like to create my where clause before I use it — so that I can inspect thins ig debugging

we have converted to the practice of using statement (STA:) commands becaise it is easier to read.

eg

search is character

begin statement
STA: WHERE
STA: city_country_name= '[ccountryhold]' AND
STA: ctime_zone=[ctimezonehold] AND 
STA: city_acs=1 AND
STA: city_Longnum >= [clonghold-3] AND
STA: city_Longnum<= [clonghold+3] AND
STA: city_latnum>=[clathold-3] AND
STA: city_latnum<=[clathold+3]
end statement

get statement search

Omnis chroma-codes things nicely, it is easy to read, and I can quickly reorganize the statements or add brackets (if I have compound conditions with OR’s ) etc.

I also find it easier to see when I have not properly quoted things (eg, like if a mistake was made on countryhold



one of the things mentioned in much earlier posts when you were starting at SQL was to make helpers for quoting things or establishing the proper format for dates or boolean.

these can be helpful.   

assume that.  TS is a helper object that is a task var.   so

TS.$sqlCHAR() would return a properly quoted or escape character string
TS.$sqlBOOLEAN() returns TRUE or FALSE for the sql statement (Thats what postgres expect, I think SqlLite is the same)
TS.$sqlDATE() returns a proper date formatted string.  (in postgres it looks like.  DATE ‘2020-08-01’, sqllite may look similar)
TS.$sqlBINARY() returns a properly escaped binary field.

etc

do the STA: blocks end up not quoting in the actual statement, but uses a function that can easily be adapted or changed if you discover something you need to fix, without having to change all your sql code.



begin statement
STA: WHERE
STA: city_country_name= [TS.$sqlChar(countryhold)]AND
STA: ctime_zone=[ctimezonehold] AND 
STA: city_acs=1 AND
STA: city_Longnum >= [clonghold-3] AND
STA: city_Longnum<= [clonghold+3] AND
STA: city_latnum>=[clathold-3] AND
STA: city_latnum<=[clathold+3]
end statement



we’ve found this approach much easier to see code, debug it, find/replace for sql statements (they are in STA: blocks) and the TS.$sql helpers ..   well, we’ve had to update them over the years in minor ways as we learned more about the SQL engine.


plus, if you ever change from one sql engine to another, the formatting of dates and such may be different, so it gives you some insulation from database engine changes.






Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978

> On August 15, 2020, at 12:40 PM, Das Goravani <goravanis at gmail.com> wrote:
> 
> 
> This code is giving a SQL error when it is included in a select statement as the where clause
> con('WHERE ',city_country_name," = '",ccountryhold,"' AND ",ctime_zone," = ",ctimezonehold,' AND ',city_acs,' = 1',' AND ',city_Longnum,' >= ',clonghold-3,' AND ',city_Longnum,' <= ',clonghold+3,' AND ',city_latnum,' >= ',clathold-3,' AND ',city_latnum,' <= ',clathold+3)
> 
> 
> How do you do complex queries?  I’ve never done one before now. 
> 
> Is there something wrong with the above, it gives a SQL error… so I guess there is something wrong but I’m surprised.. I thought this was the right way to go.
> 
> Let me ask you a question.. you see the first query field is city_country_name.. isn’t it right to put the fields just like that.. without their row.. this is packing tvfcCitiesrow, but I dont need to preface the search fields with the row I dont think.. correct?  The way I have it is correct.  It’s being compared to ccountryhold which is a string but I have it quoted, the rest are numeric.. 
> 
> When I put the above string into #S1 for then inclusion in my select statement, it finds the values for all fields and makes a thus wrong string, it says like 
> 
> USA=USA instead of FIELD=USA
> 
> To get it to stop doing that, I could put the search fields into the text bits.. so that they are not interpreted.. I think… now we’re getting a bit beyond my experience. Do I do that?  Put the fields into the text bits and put square brackets on them.. 
> 
> How do you do a long search like this?  Long query?  
> 
> What’s giving me a SQL error?  It won’t pack SQLText so I can’t see what it’s generating.. 
> 
> How do you do complex queries?  I’ve never done one before now. 
> 
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com