Ooh … Begitu! I see, I see …

Icon

Setelah kelayapan di internet, cari-cari info dari sana-sini dan bongkar pasang tips (terutama programming), akhirnya baru sadar … ternyata begitu caranya … [all about tricks and tips I have found during my work as programmer.]

get rows of dynamic latest date(s)

…. sorry for not updating for such a long time. 😛
just got a mail from a friend recently. probably this is some idea to share:


yo, Byeol. got a problem here. I have a table (FB) where I need to store every month’s stock of any available Product:

CREATE TABLE TSTOCK (
	THEDATE			DATE NOT NULL,
	PRODUCTID		VARCHAR(30) NOT NULL,
	COST			DOUBLE PRECISION DEFAULT 0 NOT NULL,
	LASTSTOCK		DOUBLE PRECISION DEFAULT 0 NOT NULL
);
ALTER TABLE TSTOCK ADD PRIMARY KEY (THEDATE, PRODUCTID);

the problem is … how to get only the last stocks of every available Product? the last date of each product might be different, like a certain product might be not updated this month but its last date was last month, while the others last dates are within this month. so basically, what I need is calling each Product’s last date row.
thanks in advance!

– Jack

hmmm …. Jack, you meant this one?

select s.* from TSTOCK s
where s.THEDATE = (select first 1 THEDATE from TSTOCK 
	where (PRODUCTID = s.PRODUCTID)
	order by THEDATE desc)

anyone got a better idea??

Advertisements

Filed under: Firebird, , , ,

get current date time in Firebird?

a friend asked me:

hi, do you know how to get current date time directly from firebird sql? I need to make sure that every client that append or insert any data would use or access the same date time resource, I would like to use sql date time directly, because some of the clients would be from any other time zone. thank you in advance for your help.

hmmm ….

it should be around this way, I guess?

select 
    current_date, 
    current_time, 
    current_timestamp 
from rdb$database

oh … before I forget, you need to check the timestamp information in the firebirdsql.org at this url to make sure about the version of your database: http://www.firebirdsql.org/index.php?op=guide&id=ib6_newfeatures

and this one is a good summary about Date Literals in Firebird:
Firebird Date Literals (Excerpt from Chapter 10 of The Firebird Book © Helen Borrie 2004)

hope this helps. 🙂

Filed under: Firebird

SQL tips for ‘select limit’ in Firebird DB

a friend asked me about how to do the similar things like this one (of MySQL), but in Firebird DB:

select limit ... from foo

something like that. and as far as I know, the equivalent SQL should be like this one, at least this works in Firebird 1.5:

    select 
        first [totalRowsToDisplay] [skip [totalRowsToSkip]] * 
    from 
        foo

so, for example I want to select the first 15 rows from table TSRDet, it would be served by this SQL selection:

select first 15 * from TSRDet

but now I want to skip the first 3 rows, and display the next 15 rows from the same table. how should I do that? well, just add the previous ‘skip [totalRowsToSkip]’ things:

select first 15 skip 3 * from TSRDet

pretty much simple right? and now I only want some columns to be displayed:

    select first 15 skip 3 
        SRCode, 
        Num, 
        ProductID, 
        QTY, 
        Price 
    from 
        TSRDet

quite simpler than we thought, right? I hope this helps. 🙂

added October 6, 2009:
sorry for long inactivity due to illness.
a visitor (Csaba Gabor) kindly made a question in the comment section, asking this:

by Csaba Gabor:

suppose you want to show the last 15 rows (not including the final 3 rows) in the table.

I am essentially asking here about negative values for First (and or Skip) or reversing the default ordering.

my answer suggestion:
it depends on which column you want to index, in this case I picked up the SRCode column.

and basically there are two solutions, I guess (CMIIW). this is the first (common) suggestion:

    select first 15 skip 3 
        SRCode, 
        Num, 
        ProductID, 
        QTY, 
        Price 
    from 
        TSRDet
    order by SRCode desc

but it will display the rows in SRCode descending order. you might dislike the result’s ordering.

so if we want to keep SRCode in ascending order for the rows, then what comes to my mind is something similar like this one.

    select 
        SRCode, 
        Num, 
        ProductID, 
        QTY, 
        Price 
    from 
        TSRDet
    where
        SRCode in (select first 15 skip 3 SRCode 
                from TSRDet 
                order by SRCode desc)
    order by SRCode

wonder if this answers your question. :thinking:

Filed under: Firebird

Date Time simple tips for Firebird store procedure

okay, first … I would like to admit that I’m quite a newbie in Firebird database. I used MySQL back then but few months ago my employer wanted to migrate to Firebird and up till now, we still remain using Firebird 1.5 for certain reasons. quite a headache for me because this one was totally new for me. I decided to put these things up, since some of my friends were facing some similar problems with me and these are the recent info I have collected so far from many searching in the forums out there, and perhaps any of you would like to submit more advice or perhaps drop any useful tutorial URLs in the comments part (and thank you in advance for spending your times doing that).

here we go. this is one of the problems I encountered using Firebird 1.5. I used to cast date format in Delphi into ‘yyyy-mm-dd’ format when I tried to filter any sql selection using date. somehow it worked, but sometimes it didn’t work properly, usually when I used it inside a store procedure script. so I always wondered about the proper way to cast the values into sql date format correctly. and then I accidentally came to Firebird Really Useful (Tips & Techniques) at http://www.firebirdsql.org/index.php?op=useful, and found a really good article about Some Store Procedure to Massage Dates.

thanks to Markus Ostenried who provided the article.
here is the trick to solve the casting into sql date format:

 CREATE PROCEDURE Proc_EncodeDate(AYear INTEGER,
   AMonth INTEGER, ADay INTEGER)
 RETURNS(Result DATE)
 AS
 BEGIN
   Result = cast(ADay || '.' || AMonth || '.' || AYear as DATE);
   suspend;
 END

by the way, should I share this simple tips too, from the same Firebird Really Useful page. just in case you want to know. this is a tips to extract the values for the year, month and day from a date timestamp format.

 CREATE PROCEDURE Proc_DecodeDate(ADate TIMESTAMP)
 RETURNS(AYear INTEGER, AMonth INTEGER, ADay INTEGER)
 AS
 BEGIN
   AYear  = extract(Year from ADate);
   AMonth = extract(Month from ADate);
   ADay   = extract(Day from ADate);
   suspend;
 END

and another one from the same author of the tips, I decided to quote it too, simply for those who have been questioning about how to get correct day numbers in a month. here we go:

 CREATE PROCEDURE Proc_DaysOfMonth(AYear INTEGER, AMonth INTEGER)
 RETURNS(Result INTEGER)
 AS
   DECLARE VARIABLE WorkDate DATE;
 BEGIN
   Result = 31;
   WHILE (Result > 28 AND WorkDate IS NULL) DO
   BEGIN
     EXECUTE PROCEDURE Proc_EncodeDate(AYear, AMonth, Result)
       RETURNING_VALUES(WorkDate);
     WHEN ANY DO Result = Result - 1;
   END
 END

pretty interesting for simple date procedures, right? 🙂 the source article itself does have great date and time store procedures useful sources. the article even provides the procedures to calculate the German “Feiertage” (feast days). you gotta check them out at http://www.firebirdsql.org/index.php?op=useful&id=ostenried_1. hopefully the URL still works. I believe there are more interesting date and time Firebird sql tips on forums out there, probably Hebrew, Arabian, Chinese or Japanese calendar for a challenge? if you happen to know any links out there to be checked out, don’t hesitate to drop me a link. thank you for reading. 🙂

Filed under: Firebird