I opened up a new SQL Window inside EDB Manager and tried executing the following:

SELECT
Current_Date as Today

I received an Error:
ElevateDB Error #700 An error was found in the statement at line 3 and column 1 (Missing FROM)

So I created a dummy table with one field and one record

/*-------------------------------------------------------*/
SCRIPT
BEGIN

EXECUTE IMMEDIATE 'CREATE TABLE "dummy"
(
"id" SMALLINT
)
VERSION 1.00
READWRITE
UNENCRYPTED
INDEX PAGE SIZE 8192
BLOB BLOCK SIZE 1024
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';

EXECUTE IMMEDIATE 'INSERT INTO "dummy" VALUES (1)';

END
/*-------------------------------------------------------*/

I modified my original SQL as follows...

SELECT
Current_Date as Today,
FROM dummy

and it works great. Even though I'm not returning any real data from the "dummy" table.

Being inquisitive and wanting to see how well ElevateDB does "Date Math" I tried the following...

SELECT
Current_Date + INTERVAL '-4' DAY AS FourDaysAgo,
Current_Date + INTERVAL '-3' DAY AS ThreeDaysAgo,
Current_Date + INTERVAL '-2' DAY AS TwoDaysAgo,
Current_Date + INTERVAL '-1' DAY AS OneDayAgo,
Current_Date as Today,
Current_Date + INTERVAL '1' DAY AS OneDayFromNow,
Current_Date + INTERVAL '2' DAY AS TwoDaysFromNow,
Current_Date + INTERVAL '3' DAY AS ThreeDaysFromNow,
Current_Date + INTERVAL '4' DAY AS FourDaysFromNow
FROM dummy

and it works AWESOME. I tested going backwards until one day before March 1, 2011 testing leap year. I tested going forward until one day after Feb 28, 2012. I tested December 31st plus 1 Day and Jan 1st minus 1 Day. It's all good.

Now I know that using a dummy table this way probably isn't considered EDB best practices but it did allow me to get the information I was looking for.  This method also works for selecting literal values. So, what is the proper way to select this type of information?

Michael Riley
GySgt USMC Retired
www.zilchworks.com

Michael,

So, what is the proper way to select this type of information?

Generally you will use expressions like CURRENT_DATE in the context of a
query against a table. If you really need it "stand-alone", you can use
this e.g.

select current_date as today from information.tables

Regards Uli

Michael


AFAIK there's no way to get a result from a query without a table (but something is itching at the back of my mind so I may have forgotten). Best practice will depend on what you want to do with the answer. If its use it in some other query then just plonk the sql for the date calc in there. If its for internal use in an app then use Delphi's built in stuff.

Roy Lambert [Team Elevate]

>>
Generally you will use expressions like CURRENT_DATE in the context
of a query against a table. If you really need it "stand-alone", you
can use this e.g.

select current_date as today from information.tables
<<

Uli,

When I execute the above query it returns accurate information. However
in my environment I get back four rows.

Is there a way to LIMIT this query to just one row?

Michael Riley
GySgt USMC Retired
www.zilchworks.com

Michael,

> Is there a way to LIMIT this query to just one row?

Sorry, I didn't pay attention to that. Use this instead:

select current_date as today from information.tables range 1 to 1

But as Roy indicated: in this case it would be probably better to use
Delphi code to get what you want.

Regards Uli

Uli,

>>
-->>Is there a way to LIMIT this query to just one row?

Sorry, I didn't pay attention to that. Use this instead:

select current_date as today from information.tables range 1 to 1

But as Roy indicated: in this case it would be probably better to use
Delphi code to get what you want.
<<

Thanks. I also agree with Roy. I didn't start out with that intent it
just happened. I just started using EDB three days ago, I've commited
myself to using EDB in my Delphi Apps. I've also got a pretty strong MS
SQL background.

So, if I did it (or could do it) with MS SQL Query Analyzer I'm
naturally going to try it with EDB Manager.


Michael Riley
GySgt USMC Retired
www.zilchworks.com

P.S. Until 1:00 AM this morning I had been visiting the EDB Forums in
my web browser. I tried downloading Grabit but it blue screened on
first use. I'm now using XanaNews. This sure does make it easier.

Roy,

>>
AFAIK there's no way to get a result from a query without a table
(but something is itching at the back of my mind so I may have
forgotten). Best practice will depend on what you want to do with the
answer. If its use it in some other query then just plonk the sql for
the date calc in there. If its for internal use in an app then use
Delphi's built in stuff.
y Lambert [Team Elevate]
<<

I didn't start out trying to accomplish this, it just happened. I have
a very heavy MS SQL background. So, because I'm new to EDB I keep
trying things inside the EDB Manager.

-- With MS Sql This Works
SELECT GETDATE()

I was trying to mimmick this behavior inside EDB Manager. I found the
equivilent EDB CURRENT_DATE and naturally wanted to test it out.

My Original goal was to build a stored procedure that creates an
Amortization schedule. I want to input StartDate, Principal, APR and
Term and have the sproc insert the results into a table. A typical 30
year mortgage would have 360 rows in the table.

I could easily do this in Delphi.
I could easily do this with MS Sql.
But beacuse I'm commited to using EDB, I want to do this inside an EDB
Stored Procedure.

I got as far as inputting the input parameters and decided to see if I
could select back those input parameters . I like to build things in
small increments and test as I go. I could not simply return the input
parameters.

This lead me to discover if I could select back internal functions like
CURRENT_DATE or literals (like I would do from MS SQL Query Analyzer).
Hence I wound up getting the EDB #700 error ... (Missing FROM). So I
created a one field, one row dummy table and it allowed me to move on.

I'm blogging about all of this here. http://capecodgunny.blogspot.com/

You may be thinking... why is he blogging. Because, I haven't been this
excited about learning in a long time. I don't mind putting in the
extra effort required to learn something, I mean really learn
something. I just hate not remembering stuff I have already learned.

Besides, I'm coming from an MS SQL background and I'm commited to
learning EDB. So, blogging what I learn keeps a running record of what
I'm learning and perhaps it might help the next MS SQL guy that wants
to start using EDB.

Michael Riley
GySgt USMC Retired
www.zilchworks.com

Michael


>P.S. Until 1:00 AM this morning I had been visiting the EDB Forums in
>my web browser. I tried downloading Grabit but it blue screened on
>first use. I'm now using XanaNews. This sure does make it easier.

Whatever you do do not use Outlook Express or MS Mail. OE was the reason I wrote my own email/news client:

1. it matched articles by the subject not the reference list
2. it crashed quite often - I have the whole DBISAM and ElevateDB ngs plus some others in a DBISAM app (due for re-writing to ElevateDB)

Roy Lambert [Team Elevate]

Michael

>-- With MS Sql This Works
>SELECT GETDATE()

Since I don't know MS SQL is that as a stand alone statement or something you bed into another piece of SQL ie to compare apples and apples would SELECT SELECT GETDATE() which I'm interpreting as the equivalent of CURRENT_DATE work in MS SQL?

>I was trying to mimmick this behavior inside EDB Manager. I found the
>equivilent EDB CURRENT_DATE and naturally wanted to test it out.
>
>My Original goal was to build a stored procedure that creates an
>Amortization schedule. I want to input StartDate, Principal, APR and
>Term and have the sproc insert the results into a table. A typical 30
>year mortgage would have 360 rows in the table.

Looking at your website I can understand that. If you have trouble one approach would be to code it fore MS SQL and then ask for help converting it. Another would be to write the pseudo code and again ask for help if needed to change it into real code.

Even though Tim's programming language is pretty good I think the extendibility with external functions is even better.  I'm happier writing Delphi code and have a few functions written in Delphi that are called as sql functions.

>I could easily do this in Delphi.
>I could easily do this with MS Sql.
>But beacuse I'm commited to using EDB, I want to do this inside an EDB
>Stored Procedure.
>
>I got as far as inputting the input parameters and decided to see if I
>could select back those input parameters . I like to build things in
>small increments and test as I go. I could not simply return the input
>parameters.

Not within SQL, but obviously you can inside Delphi (at least if you mean parameters as I do), but since you put them in unless you're changing them inside the SP there's no real need to get them out again..

Roy Lambert [Team Elevate]

Roy,

>>
-->>SELECT GETDATE()

Since I don't know MS SQL is that as a stand alone statement or
something you bed into another piece of SQL ie to compare apples and
apples would SELECT SELECT GETDATE() which I'm interpreting as the
equivalent of CURRENT_DATE work in MS SQL?
<<

Yes it is standalone SQL and perfectly acceptable behavior from within
MS SQL Query Analyzer.

>>
Looking at your website I can understand that. If you have trouble
one approach would be to code it fore MS SQL and then ask for help
converting it. Another would be to write the pseudo code and again
ask for help if needed to change it into real code.
<<

That's a good idea, thanks. I take it you are a golfer (Fore)

>>
Even though Tim's programming language is pretty good I think the
extendibility with external functions is even better.  I'm happier
writing Delphi code and have a few functions written in Delphi that
are called as sql functions.
<<

I'm only on day four here using EDB. I'll have to add "External
Functions" to my reading list. Care to share a simple glimpse of what
one of these "External Delphi Sql Functions" look like!

>>
-->>I got as far as inputting the input parameters and decided to see
-->>if I could select back those input parameters . I like to build
-->>things in small increments and test as I go. I could not simply
-->>return the input parameters.

Not within SQL, but obviously you can inside Delphi (at least if you
mean parameters as I do), but since you put them in unless you're
changing them inside the SP there's no real need to get them out
again..
<<

I have been doing this all from within EDB Manager. I figured if I can
make it work inside EDB Manager then plugging into Delphi should be as
simple as wiring up the TEDBStoredProc object.


Michael Riley
GySgt USMC Retired
www.zilchworks.com