[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
r18bby
Newbie

Joined: 30 Jan 2006
Posts: 2

 Posted: Tue Jan 31, 2006 2:53 am    Post subject: 'Simple' Base/Calc Problem - But canot resolve! All, I would love someone to be able to help me with, what I'm sure is a simple request, BUT [code]I've got a mental block on it, and cannot find a way to do it... In simple terms I would like a table of distances from one place to another, something like:- [code]From To Distance ------------------------------- London Paris 123 Paris Rome 456 Rome Milan 789[/code] Then, I would like to be able to enter in a 'spreadsheet', from(A1) and to(B1), and for the distance to be entered in via 'select Distance from Table where From=A1 and To=B1' Am I going mad, or is it not possible to perform this kind of select in Base/Calc...? Many thanks in anticipation Peter[code][/code]
DrewJensen
Super User

Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

Posted: Tue Jan 31, 2006 10:24 am    Post subject:

Step one - Create the database. Name it (for example) TravDist.odb

Step two - Create that table Distances, or maybe you want to create two tables.
Tables:
Locations( ID, Name)
Distances(ID, Name1ID, Name2ID, Distance)

Distances.Name1ID and Distances.Name2ID both link back to the Locations.ID field. This way you only one Location named Paris, but many distancesses for Paris (to Milan, to NY, to London.....)

Now as for the search the only tricky part is the order. In other words Paris to London, should I would imagine be the same as London to Paris, right? You can handle this either in the search routine, making order inisignificant, only the two names matter. Or you could create seperate entries in your Distances table for the different order. (this would not be my choice for a solution)

If we make the search handle the order then it would be. Using a basic macro this is gonna be fairly easy - A quick thought of the flow is
- see if we have an entry in the Locations table for the value in A1
 Code: IF Select * from Locations where Name = :A1 has 1 record    AND Select * from Locations where Name = :B1 has 1 record THEN         IF select * from "Distances" where "Name1ID" = :AI and "Name2ID" = :B1 then             Return "Distance"         else IF select * from "Distances" where "Name1ID" = :B1 and "Name2ID" = :A1                 Return "Distance"             else                 Return no record found error             endif         endif ELSE     Report the one of the other is not found ENDIF

Now it turns out we can acutally do better then this, and handle the ordering problem in our query. For the tables structure I used above you can write this query
 Code: SELECT    "Locations"."Name" AS "FromName",    "Locations_1"."Name" AS "ToName",    "Distances"."Distance" FROM "Distances" "Distances", "Locations" "Locations", "Locations" "Locations_1" WHERE ( "Distances"."Name1ID" = "Locations"."ID" AND "Distances"."Name2ID" = "Locations_1"."ID" ) AND    (  ( "Locations"."Name" = :Frm AND "Locations_1"."Name" = ? )    OR    ("Locations"."Name" = ? AND "Locations_1"."Name" = :Frm )  )

This is quite valid, so you would be prompted for the two names. In the basic macro we would turn this into a prepared statement and simply fill in the values from the spread sheet.

If you want to test this simply do this.

Create you database. Create the tables as shown and enter some data. My test data is

I recreated the query using query builder and it looks like this:

Sure enough you can enter either Paris / London or London / Paris and get the same results.

OK, now we need to tie this to Calc.

Question, how will you call it. Is it always A1 and B1 for the names or is it really
A(x) and B(x) where x is the current row?

Either way, I just don't know the API for Calc that well yet, so need to go off and actually write this with an eye to the docs...so will be a few minutes. If you answer before I get back with that..then I will adjust as dessired.

EDIT - well, a little search on the forum under Calc and I came up with this.

I created a spreadsheet and attached the following basic macro to it.
 Code: function getDistance( aFromTo as any) as string dim FromName as String dim ToName as String   FromName = aFromTo(1,1)   ToName = aFromTo( 1,2) dim conn as variant  conn = CreateUnoService("com.sun.star.sdb.DatabaseContext")._           getByname( "TravDist" )._           getConnection( "", "" )           dim prepStmt as variant dim SQL as String   SQL = "SELECT "   SQL = SQL & " ""Locations"".""Name"" AS ""FromName"", "   SQL = SQL & " ""Locations_1"".""Name"" AS ""ToName"", "   SQL = SQL & " ""Distances"".""Distance"" "   SQL = SQL & "FROM ""Distances"" ""Distances"", ""Locations"" ""Locations"", "   SQL = SQL & " ""Locations"" ""Locations_1"" "   SQL = SQL & "WHERE ( ""Distances"".""Name1ID"" = ""Locations"".""ID"" "   SQL = SQL & "AND ""Distances"".""Name2ID"" = ""Locations_1"".""ID"" ) "   SQL = SQL & "AND "   SQL = SQL & "   (  ( ""Locations"".""Name"" = ? AND ""Locations_1"".""Name"" = ? ) "   SQL = SQL & "   OR "   SQL = SQL & "   (""Locations"".""Name"" = ? AND ""Locations_1"".""Name"" = ? )  ) "   prepStmt = conn.prepareStatement( SQL )     prepStmt.setString( 1, FromName)   prepStmt.setString( 4, FromName)   prepStmt.setString( 2, ToName )   prepStmt.setString( 3, ToName ) dim rs as variant   rs = prepStmt.executeQuery   if not rs.isBeforeFirst then     ' no records found     getDistance = -1   else      rs.next      getDistance = rs.getInt( 3 )   endif   conn.dispose end function

In the spreadsheet I entered the strings
Paris in cell A3 and London in B3, then in C3 I enter = GETDISTANCE(A3:B3). When I recalc the sheet I get the value 145 in the cell.
In cell A4 is London and in B4 is Paris, C3 is = GETDISTANCE(A4:B4) and again the value displayed is 145.

This is not particularly efficient code, for a number of reasons but it should show the basic idea. I also don't do any error checking so watch that if you just try to copy and paste this code.

Drew
_________________