I have a DBISAM SQL Statement which is in the form:


DROP TABLE IF EXISTS MEMORY\SumAdv;
DROP TABLE IF EXISTS MEMORY\SumPE;
/* other drops follow */

SELECT DepotsID, SUM(Bags) as SumBags
INTO MEMORY\SumAdv
FROM Advances
WHERE DepotsID = %0:d
AND DateDone>=[SeasonStartDate]
GROUP BY DepotsID
;


SELECT DepotsID, SUM(Bags) as SumBags
INTO MEMORY\SumTrans
FROM Transfers
WHERE DepotsID = %0:d
AND DateDone>=[SeasonStartDate]
GROUP BY DepotsID
;

/* This continues, then there is a final SELECT which gathers up all the data from the memory tables. */

--

When I started my conversion process from DBISAM I was rewriting these whole scripts so that rather than using a MEMORY table I used a CREATE, and SELECTED in the data.

Roy helped with that which was great ... however almost as soon as I had done it I realised that it was the wrong way to solve the problem

I replaced my "CREATE" scripts with simple VIEWS ... which can then be called in by other SELECTS.

The above case is slightly more complicated.

I have a function to return the SeasonStartDate, but the user needs to supply the DepotsID. In DBISAM I just slap this in with a FORMAT(SQLStr, [DepotsID]); & the %0:d characters are all replaced.

I _think_ I can replicate the above behaviour in EDB with a Stored Proc? Is that correct & can anyone guide me to an example?

Adam


You know what you need better than I do BUT views can't be supplied parameters so whilst they can be useful they also have limitations. Personally I would only use a view where it is static or at least it doesn't need to change for a long period of time. Something you don't really want to be doing is constantly rewriting them otherwise you might as well use a query/script.

I tend to have a bit of a simplistic view (groan) on life and I would use a query/script UNLESS I wanted to use the view to manage security on the database or was reusing that particular view a lot (which I guess is what you're doing). Couple of links for you.

http://stackoverflow.com/questions/1004974/use-sql-view-or-sql-query

http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

I can see no reason why a stored procedure shouldn't work - after all its just a script but stored on the database. Use the scripts you've already converted as examples

Before you go too far down the route of stuffing as much into the database as you can make sure its not going to bite you later. That model came about with the idea of a single massive central database being accessed by apps written by all and sundry. It can be used by anyone but if you have a model of one app - one database or one app - several databases it may not be the best approach. Another consideration is the type / frequency of updates. Its easy enough to send a script to add a new view into the database or to change a view in the database but if you're doing it very often it may be better to just issue a new exe.

Roy Lambert [Team Elevate]

Roy,

<< I tend to have a bit of a simplistic view (groan) on life and I would use
a query/script UNLESS I wanted to use the view to manage security on the
database or was reusing that particular view a lot (which I guess is what
you're doing). Couple of links for you. >>

One other consideration is multiple client-side platforms or APIs.  If you
plan to use VCL, ODBC, and .NET access to the database, then it pays to keep
as much in the database as possible.  This is the lesson that I learned with
DBISAM - because everything was coded natively on the client side, I had to
make changes to both the ODBC and VCL interfaces every time something
changed.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


><< I tend to have a bit of a simplistic view (groan) on life and I would use
>a query/script UNLESS I wanted to use the view to manage security on the
>database or was reusing that particular view a lot (which I guess is what
>you're doing). Couple of links for you. >>
>
>One other consideration is multiple client-side platforms or APIs. If you
>plan to use VCL, ODBC, and .NET access to the database, then it pays to keep
>as much in the database as possible. This is the lesson that I learned with
>DBISAM - because everything was coded natively on the client side, I had to
>make changes to both the ODBC and VCL interfaces every time something
>changed.

Which, unless I much mistake is << a single massive central database being accessed by apps written by all and sundry>>

<grin>

Roy Lambert

Roy,

<< Which, unless I much mistake is << a single massive central database
being accessed by apps written by all and sundry>> >>

Sorry, I read your sentence incorrectly and thought that you were saying
something else.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Sorry, I read your sentence incorrectly and thought that you were saying
>something else.


sallright. After all with umpteen apps open on who knows how many PCs you probably integrate several sentences into one each time you try and read something <vbg>

Roy Lambert