• Registered: 2014-09-04
  • Posts: 98

Topic: sql in code question

this sql query wroks to fill and filter a table grid  - now in a form I need to
choose Truck company, Products, Origin and Destination  and then get the Flat rate and Surcharge  from the database - if there are multiple that match I just need the newest one.

SELECT FreightRateChart.id,
       TruckCompanies.Name,
       Products.Name,
       LoadOrigin.Name,
       LoadDestination.Name,
       FreightRateChart.FlatRate,
       FreightRateChart.Surcharge,
       strftime ( '%m/%d/%Y',FreightRateChart.Updated)
From FreightRateChart

LEFT OUTER JOIN TruckCompanies ON FreightRateChart.id_TruckCompanies = TruckCompanies.id
LEFT OUTER JOIN Products ON FreightRateChart.id_Products = Products.id
LEFT OUTER JOIN LoadOrigin ON FreightRateChart.id_LoadOrigin = LoadOrigin.id
LEFT OUTER JOIN LoadDestination ON FreightRateChart.id_LoadDestination = LoadDestination.id

WHERE TruckCompanies.Name LIKE '%{TruckSearch}%'
  AND Products.Name LIKE '%{ProdSearch}%'
  AND LoadOrigin.Name LIKE '%{OriginSearch}%'
  AND LoadDestination.Name LIKE '%{DestSearch}%'

ORDER BY FreightRateChart.id DESC;
  • Registered: 2014-02-14
  • Posts: 10,294

Re: sql in code question

Hello.


Try to add keyword LIMIT 1


....
WHERE TruckCompanies.Name LIKE '%{TruckSearch}%'
  AND Products.Name LIKE '%{ProdSearch}%'
  AND LoadOrigin.Name LIKE '%{OriginSearch}%'
  AND LoadDestination.Name LIKE '%{DestSearch}%'

ORDER BY FreightRateChart.id DESC

LIMIT 1;
Dmitry.
  • Registered: 2014-09-04
  • Posts: 98

Re: sql in code question

I had figured out the limit 1, 
I am struggling with how to take that long sql and wrap it into a SQLExecute with the 4 variables needed to get the two results.

Also can I get them both with one call to the database or do I need to run it twice.

here is the project the freight table shows the sql working to filter the list for editing etc.

Post's attachments

Attachment icon trucklog.zip 366.32 kb, 450 downloads since 2017-02-14 

  • Registered: 2014-09-04
  • Posts: 98

Re: sql in code question

if  I run this in sqlitestudio I get the correct answer

SELECT FreightRateChart.FlatRate 
FROM FreightRateChart 
LEFT OUTER JOIN TruckCompanies ON FreightRateChart.id_TruckCompanies = TruckCompanies.id 
LEFT OUTER JOIN Products ON FreightRateChart.id_Products = Products.id 
LEFT OUTER JOIN LoadOrigin ON FreightRateChart.id_LoadOrigin = LoadOrigin.id 
LEFT OUTER JOIN LoadDestination ON FreightRateChart.id_LoadDestination = LoadDestination.id 

WHERE TruckCompanies.Name LIKE 'Trucking Co4'
  AND Products.Name LIKE 'Gas' 
  AND LoadOrigin.Name LIKE 'Origin1' 
  AND LoadDestination.Name LIKE 'Dest1'

ORDER BY FreightRateChart.id DESC LIMIT 1;

but when I try in mvd with

flatrate := SQLExecute('SELECT FlatRate FROM FreightRateChart LEFT OUTER JOIN TruckCompanies ON FreightRateChart.id_TruckCompanies = TruckCompanies.id LEFT OUTER JOIN Products ON FreightRateChart.id_Products = Products.id LEFT OUTER JOIN LoadOrigin ON FreightRateChart.id_LoadOrigin = LoadOrigin.id LEFT OUTER JOIN LoadDestination ON FreightRateChart.id_LoadDestination = LoadDestination.id WHERE TruckCompanies.Name ''' +Form1.truckCombo.Text+ ''' AND Products.Name ''' +Form1.productCombo.Text+ ''' AND LoadOrigin.Name ''' +Form1.originCombo.Text+ ''' AND LoadDestination.Name ''' +Form1.destinationCombo.Text+ ''' ORDER BY FreightRateChart.id DESC LIMIT 1; ');

flatrate never gets set to a value

I don't think I am understanding the method of getting the variables into the sqlquery properly.

5 Reply by mathmathou (edited by mathmathou 2017-02-14 20:21:42)

  • Registered: 2015-02-24
  • Posts: 446

Re: sql in code question

Hello timlitw,


Just a wild guess, it's early in the morning and I'm having my first coffee smile


I don't see the logical operators in your second query. Seems you forgot the "LIKE" in the statement.


The ORDER BY / LIMIT command is ok if you assume that the latest value saved in database is the highest. But what if this is not the case ?
You could also try something different with the statement "HAVING MAX(X)" if you are dealing with a integer (an ID for example or a number) but you'll have to had a "GROUP BY" command to use it.


If you are dealing with other variables that numbers, a number stored as text for example, you can still use this value for sorting with the command CAST. This instruction will transtype (convert) a value from a type to another and help you sorting.
For example, if a number is stored as text, you can convert it and then use it for sorting with :
ORDER BY CAST(X AS INTEGER) ASC


Finally, is you query included in the script or embedded in a SQL Query button ? Because the way you pass on the variables will be different in both cases.


I'll have a look at your project when I reach my office. I know nothing better than a bit of code with another coffee to start a good day of work smile


Cheers


Math

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor
  • Registered: 2015-02-24
  • Posts: 446

Re: sql in code question

Hello again


OK, I've just checked  and this works :

flatrate := SQLExecute('SELECT FlatRate FROM FreightRateChart '+
                            'LEFT OUTER JOIN TruckCompanies ON FreightRateChart.id_TruckCompanies = TruckCompanies.id '+
                            'LEFT OUTER JOIN Products ON FreightRateChart.id_Products = Products.id '+
                            'LEFT OUTER JOIN LoadOrigin ON FreightRateChart.id_LoadOrigin = LoadOrigin.id '+
                            'LEFT OUTER JOIN LoadDestination ON FreightRateChart.id_LoadDestination = LoadDestination.id '+
                            'WHERE TruckCompanies.Name LIKE ''' +Form1.truckCombo.Text+ ''' '+
                            'AND Products.Name LIKE ''' +Form1.productCombo.Text+ ''' '+
                            'AND LoadOrigin.Name LIKE ''' +Form1.originCombo.Text+ ''' '+
                            'AND LoadDestination.Name LIKE ''' +Form1.destinationCombo.Text+ ''' '+
                            'ORDER BY FreightRateChart.id DESC LIMIT 1; ');

I tested it with "LIKE " and "=" and both works. Since you don't use the % in your query, you can use = instead of LIKE.

Also, the concatenation sign + helps making your query easier to read on multiple lines like in sqlstudio.

I added a 

     ShowMessage(flatrate);

just to make sure there was a value returned.

The result I get with value :
- Trucking Co4
- Gas
- Origin1
- Dest1

is 46,72.


Hope this helps


Cheers


Mathias


PS : using v 3.2

Post's attachments

Attachment icon trucklog.zip 346.69 kb, 468 downloads since 2017-02-14 

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor
  • Registered: 2014-09-04
  • Posts: 98

Re: sql in code question

Sorry -  I was away from project for a few days.  Thaks for your help!

  • Registered: 2014-09-04
  • Posts: 98

Re: sql in code question

It has changed a bit but is working - I found out the price was by the product category so I had to add categories to the products and fill the category field by the product selected. but can I make this more efficient by running the SQL query only once to get both values?  If so does it come back as an array? I guess I need to study arrays

    flatrate := SQLExecute('SELECT FlatRate FROM FreightRateChart '+
        'LEFT OUTER JOIN TruckCompanies ON FreightRateChart.id_TruckCompanies = TruckCompanies.id '+
        'LEFT OUTER JOIN ProductsType ON FreightRateChart.id_ProductsType = ProductsType.id '+
        'LEFT OUTER JOIN LoadOrigin ON FreightRateChart.id_LoadOrigin = LoadOrigin.id '+
        'LEFT OUTER JOIN LoadDestination ON FreightRateChart.id_LoadDestination = LoadDestination.id '+
        'WHERE (TruckCompanies.Name = '''+CalcShipping.truckCombo.Text+''') '+
        'AND (ProductsType.Name='''+CalcShipping.prodtypecombo.Text+''') '+
        'AND (LoadOrigin.Name='''+CalcShipping.originCombo.Text+''') '+
        'AND (LoadDestination.Name='''+CalcShipping.destinationCombo.Text+''') '+
        'ORDER BY FreightRateChart.id DESC LIMIT 1;');
    surcharge := SQLExecute('SELECT Surcharge FROM FreightRateChart '+
        'LEFT OUTER JOIN TruckCompanies ON FreightRateChart.id_TruckCompanies = TruckCompanies.id '+
        'LEFT OUTER JOIN ProductsType ON FreightRateChart.id_ProductsType = ProductsType.id '+
        'LEFT OUTER JOIN LoadOrigin ON FreightRateChart.id_LoadOrigin = LoadOrigin.id '+
        'LEFT OUTER JOIN LoadDestination ON FreightRateChart.id_LoadDestination = LoadDestination.id '+
        'WHERE (TruckCompanies.Name = '''+CalcShipping.truckCombo.Text+''') '+
        'AND (ProductsType.Name='''+CalcShipping.prodtypecombo.Text+''') '+
        'AND (LoadOrigin.Name='''+CalcShipping.originCombo.Text+''') '+
        'AND (LoadDestination.Name='''+CalcShipping.destinationCombo.Text+''') '+
        'ORDER BY FreightRateChart.id DESC LIMIT 1;');

    CalcShipping.PriceTon.Text := flatrate;
    CalcShipping.SurchargePercent.Text := surcharge;
  • Registered: 2015-02-24
  • Posts: 446

Re: sql in code question

Hello timlitw,


You are right, there is a way of getting multiple results from just one query, but this is not an array, this is a dataset.


IF and only IF you use the SAME query multiple times to get multiples results, then you can simplify like :


procedure .....
var
     MyResults : TDataSet;
     x : Integer;
     y, z : String;
begin
     SQLQuery('SELECT x, y, z FROM my_table WHERE my_conditions',MyResults);
     while not MyResults.Eof do
          begin
               x := MyResults.FieldByName('x').AsInteger;
               y := MyResults.FieldByName('y').AsString;
               z := MyResults.FieldByName('z').AsString;

               ..... your awesome code here to use the x, y and z variables you just got in one single query

               MyResults.Next;
          end;
     MyResults.Free;

end;

Don't forget the MyResults.Next or your application will hang and you'll get an error, and don't forget to Free the dataset at the end of the procedure or you might encounter memory leaks.


You can use this to get multiples results with one query but also multiple sets of multiple results if you , for example, specify a range in your conditions. The "while not MyResults.Eof" means as long as there is data, loop through them, so you can have multiple loops with just one query.

Test this by putting a memo on a form and writing a query that fetches all your company names (for example), and output these names to the memo in the loop. If you have 10 companies, you'll get 10 lines with 10 names in your memo. With just one query smile


have fun


Cheers


Mathias

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor
  • Registered: 2014-09-04
  • Posts: 98

Re: sql in code question

wow - thanks!