• Welcome, Guest. Please login.
 
September 17, 2019, 02:28:39 pm

News:

Welcome to the SQLitening support forums!


Might need a new Forum Category

Started by Mark Strickland, July 22, 2008, 12:43:25 pm

Previous topic - Next topic

Mark Strickland

Since we are starting to play with SQL I think possibly a new forum topic may be in order.  There have been several posts from people wanting to know how to get started with SQL.  Because of the non procedural nature of SQL it involves significantly different thinking when it comes to file access.

Here is an example of something I did in Cheetah and had to re-think in SQL.  It would be nice to have a place to post these kinds of "SQL Tips and Tricks" as they relate specifically to SQLite and SQLitening.

My Cheetah algorithm to search for a person in a list of names:
1) Find a record by a partial name entered by the user (PATIENT NAME)
2) Read the next 4 records in key sequence by PATIENT NAME
3) Read the previous 4 records in key sequence by PATIENT NAME
4) Display a list of 7 records with the one you searched for in the CENTER of the list

Because SQL is non procedural this is how I am getting the list.  I still have to process the list procedurally because if you are at the front or back of the file there may not be 4 records before or after the desired record and you will have to figure out what goes in the center of the result set.  It may be possible to do this in PURE SQL but I have not yet figured it out.


select * from

  (select 'A' as src,* from pat where upper(pat_lname) <= 'SCHEFFLER' and pat_number <> '00010965'
     order by pat_lname desc limit 4)
 
union

select * from
  (select 'B' as scr,* from pat where pat_number = '00010965')
 
union

select * from
  (select 'C' as scr,* from pat where upper(pat_lname) >= 'SCHEFFLER' and pat_number <> '00010965'
     order by pat_lname asc limit 4)

  order by pat_lname



The 'A', 'B', 'C' column names are to show the source of the record in the result set.

I also discovered some other interesting "features" of SQLite if index files are used or not used based on the index structure and the nature of the query.

If we had a place I would post the parts of a working example and the DB.  Others could post things they have found and the orginal topic could be kept pure for the development activity of SQLitening.


Food for thought.

Mark Strickland, CISSP
www.SimplyBASICsecurity.com

Paul Squires

Hi Mark,

Sure, no problem at all. I see the advantage of such a forum. I'll add it to the forum list shortly. If it proves to be little used or non-useful then I can always merge it back into the main SQLitening forum.

Thanks for the idea. Hope your pharmacy program conversion is going okay.  ;)