>How can I make this SQL execute faster? 2.5 seconds is way too long.
>To solve the NATURAL (Slow) access of records via SELECT, I usually just
>create some appropriate indexes and retry, until FB is fast-as-lighting
>again. Some how, I'm tied up with this SQL which I cannot seem to get
>goin
>Here is another SQL that is running slow for me. I think I'm probably still
>missing
>something that you are trying to teach me. Can you spot my error?
>
>SELECT FIRST 25 SKIP 0 DISTINCT
> s.OBJ_GUID, s.CMTS, s.CTK, s.REC_DSC, s.REC_ACT, s.NOTE, l.NEW_VAL,
> f.FLD_DSPLY_NM,
> t.TBL_DSPLY_
> Hello,
>
> If I select some fields from mytable with ,where field = XX' or ,where field
> > XX', the indexed fields were read indexed (displayed from IBExpert). But
> If I select some fileds from mytable with 'where field is distinct from XX',
> the read is not indexed.
>
> It is already okay?
H
>Hi Svein,
>
>Here is the PLAN that goes with that SQL. I should have included it in the
>first message.
>
>PLAN SORT (SORT (JOIN (S INDEX (IDX_JET_SAV_PNT5), L INDEX (IDX_JET_CHG_LOG3),
>F INDEX
>(UNQ_JET_FLD_NMS_2), T INDEX >(UNQ_JET_TBL_NMS_1
>
>When I execute this the first time, it tak
>Hello everybody
>
>I have a control table, which could to have one row and just one row, never
>more.
>
>Which trigger I could to write for avoid inserts in that table if there is
>one row on them?
>
>Thanks in advance.
Very simple Walter. Add a PK field and then have a before insert trigger that
>Hi all,
>
>I have a problem with indexes in my firebird query.
>
>Below is my query.
>
>select a.objid, b.running_qty, b.running_qty2, b.running_totalcost,
>b.running_lastcost
>from mm_itrghd a, mm_itrgdt b where (a.objid=b.header_id) and
>(b.item_id=1200) and (b.wh_id=1) and
>((a.postdate<'2010-
>Thanks for your response. Actually, my indexes is not that many. I was just
>trying to find a
>best one for my queries. I will delete most of them after.
>
>Back to my problem.
>
>It seems that if i removed the "order by" clause, the query works very fast.
>So it must be
>that order by part th
>Database dialect1
>Creation date May 2, 2009 22:22:39
>
>The server has been rebooted a number of times since the database was
>last restored from a backup (which occurred more than a year ago).
Actually, more than two years ago. Out of curiosity, why do you stil
>I forgot to put "first 1" on my first post.
OK, the difference between including your ORDER BY and not including it is that
with ORDER BY you ask the question 'get me the record that meets these
criteria', whereas without ORDER BY you ask the question 'get me a record that
meets these criteria
>FB 1.5
>
>I have a number of records in a table, that includes a BLOB field, that I need
>to duplicate.
>Is there an easy way of doing that?
Hi Ed!
This question may be understood very differently. If you want to exactly
duplicate the records, I don't know how to do that. If you want to dupl
Does
With Recursive lc as
(
Select ID from BD_Locality where ID = :ID_Root
Union all
Select ID from BD_Locality, lc where Parent_ID = lc.id
),
RPL (BizType_Description, Loc_Description) as
(
select BizType_Description, Loc_Description from v_bd_biz_prim where BizType_ID
= :BizType_ID
and (Loc_ID
>If you override the default NULLs placement, no index will be used for
>sorting. In Firebird 1.5, that is the case with NULLS FIRST. In 2.0 and
>higher, with NULLS LAST on ascending and NULLS FIRST on descending sorts.
>
>but i need the index :(
Why do you need an index for sorting, Stephane? Ge
>> Select b2.ID from BD_Locality b2
>> join lc l on b2.Parent_ID = l.id
>
>I'm a little confused with the meaning of this join :)
In your recursive CTE, you were selecting ID without specifying whether it was
lc.ID or BD_Locality.ID. Theoretically, it has to be the latter, since the
prior would
Lester Caine wrote:
>> Do I end up with a list of fields each picking up one timeslot for a
>> particular
>> date? Or is there some way to use the 'timeslot' table that I used to use in
>> the
>> old two phase process?
>
>OK falling at first hurdle ...
>
>WITH
> Z as
> (
> SELECT
>CAST (
Svein Erling Tysvær wrote:
> Lester Caine wrote:
>>> Do I end up with a list of fields each picking up one timeslot for a
>>> particular
>>> date? Or is there some way to use the 'timeslot' table that I used to use
>>> in the
>>&g
This morning I got very surprised. I ran (something similar to)
UPDATE MyTable M
SET M.MyField = 'Y'
WHERE M.MyPK = :MyPK
repeatedly in Database Workbench (4.1.2.0) because a program I have gave me a
strange message.
Sometimes this updates one row in the table, sometimes not. Running
SELECT *
>I've tried recreating the PK and various other stuff, the only thing
>that seems to help is to use a constant rather than a parameter.
Actually, using
UPDATE MyTable M
SET M.MyField = 'Y'
WHERE M.MyPK+0 = :MyPK
and thus eliminate the use of the primary key index, also seems to work. Though
I'
I'm a complete novice regarding MERGE, never seen one, never used one. Though
try something like:
merge into elements as e
using (with newtbl1 as
(select distinct elm_prof , elm_mat from ELEMENTS),
Newtbl2 (num, elm_prof, elmmat) as
(select gen_id(gen_new,1), n
> Although Svein is almost always right about everything, in
> this case he's overgeneralized a good warning. Several
> things in the database metadata have limits on the number of
> times they can be modified, including the number of
> generators you can define, but resetting a generator leav
>If I a tables primary key is (username, title, start_date)...I'm guessing it
>will create an index on (username, title, start_date).
Correct.
>If I'm querying by username, start_date, will it still use the index OR
>do I need to add an extra index for username, start_date)?
It will probably u
> If you does the query in the same order, the primary's key index is used. By
> example:
...
> - title, start_date, username ---> don't use the primary's key index
> because the column "username" is not the first column.
I find this a strange example. Sure, if you have
SELECT
FROM MyTitle M
>1. Has anyone actually looked at the SQL monitor to see
>what values are being passed in the params?
Good idea, Helen. I hadn't, but wrote a test program. In this test program I
failed to reproduce the problem I described, whereas I successfully reproduced
it in Database Workbench (DBW). Hence
>Hi
>
>I'm trying yo fully understand how FB takes decisions to optimize a query in
>my DB.
>Please check this two queries. The results are the same, but the second query
>is much better that the >first one.
>It seems that the left outer join of the secondary table able FB to use their
>indexes.
>Now I want to delete all cars that *has* a record in "drivers", but the
>driver_name is null
>
>so far, I always do
>
>delete from cars
>where
>(
> select drivers.driver_name
> from drivers
> where
> cars.id_drivers = drivers.id
> )
> is null
>
>that works fine, but I just wander if there i
Walter wrote:
Hello everybody
In a table I have data about money received from the clients. Sometimes a
client pay more than a month, like this:
Client Date(mm/dd/) Months received
-
Alice 01/02/2011 2
Alice 03/05/201
> I would like to delete records if a determined count of record is reached.
> One trigger counts a generator for the primery key of the table named a. on
> before insert. One trigger after insert counts a second generator like this:
>ist=GEN_ID(TILOG_ALLG_ID_DEL, 1);
>if(ist >= 30) then
>
> I was under the impression that the query parser would recognize that
> the two expressions are identical. That's actually how it works in SQL
> Server 2008. In fact, there you HAVE to use that syntax; you CANNOT
> refer to the select list's column alias in the group by clause. I wasn't
> awa
> and (
> (select count(*) from tfachres where artnr = :artnr) > 0
> and a.dispo = 0
> or coalesce((select count(*) from tfachres where artnr = :artnr), 0) <= 0
> and bestandsnr is null
> )
>
> Not sure if the query optimizer is smart enough to eval the count
> subquery only once.
Why
>Flamerobin shows the plan as:
>PLAN JOIN (users NATURAL, group_users INDEX (INTEG_14))
>
>Also, I got the following tables:
>Group users key (composite): col1, col2, col5
>Users key: col2, col3
>
>When I do an inner join between the two tables I get:
>PLAN JOIN (users NATURAL, group_users INDEX (I
>I have a table called CUSTOMER, and another called HISTORY.
>HISTORY is linked to CUSTOMER by a foreign key, which
>means that you can only insert in HISTORY if there is a record in
>CUSTOMER in the foreign key fields, of course.
>
>Eventually my users get this error message when trying to inser
> I have check that. First, we have closed an application "A". After this,
> the 5-minutely executing isql-script does not work and hangs (dos-box) The
> transactions stops! Oldest for example 50 and next 51, a difference
> of 1 but next transaction does not count continuous. The differ
> CREATE TABLE posts
> (
> board_id,
> username,
> PRIMARY KEY (board_id, username)
>)
>
> SELECT COUNT(*) FROM posts WHERE board_id = 1;
>
> There are 123k rows in the posts table (just a sample) and all of them
> are of board_id = 1. This query takes about 0.5-1 sec to run (slow).
>
> I'm
> select
> ( select case when (a.mdz is null) then 'A'
>when (cast(a.mdz as timestamp) > cast('2011-09-30' as
> timestamp)) then 'B'
>when (cast(a.mdz as timestamp) < cast('2011-09-30' as
> timestamp)) then 'C'
>else 'D'
> end
>
>
> When I run the script I get the following error message:
> Project Umpires.exe raised exception class EIBInterBaseError with message
> 'unsuccessful metadata update object UMPIRECLUBLINKS is in use'.
>
> This message is not too meaningful to me!
OK, I'll try to translate:
Umpires.exe is doing D
> firebird 2.5.0.26074 on windows 7 home premium x64
>
> Below is a (manually obscured, simplified table) scenario that
> (hopefully) matches what I'm doing, with actual return count values.
>
> Is my SQL just woefully incorrect, or am I possibly experiencing a
> problem with fb 2.5, in apparen
> and t1.form_filename_src containing 'hello')
Sorry, I forgot that CONTAINING is case insensitive whereas LIKE is case
sensitive. Continue to use LIKE '%hello%' if you want it to be case sensitive.
Set
>I have a table TableA with the following data:
>
>LINE USED
> 1F
> 2F
> 3F
> 95 F
> 96 F
>
>and a table TableB with the following data:
>
>LINE
> 1
> 2
> 3
> 4
>
>and I need to put a 'T' on the USED column when the line's number is the
>same in both tabl
> Out of interest, does SELECT 1 work identically and if so whether it
> performs any quicker (if this were a very large table) than SELECT *
> in the sub-select?
>
> In other words, I'm unclear how the rows from the sub-select are
> 'materialised' prior to joining to TableA and whether * is nee
>> Conclusion: using select 1 ... instead of select * ... in the sub-select
>> doesn't
>> improve anything, while using select first(1) ... even slows things down a
>> bit.
>> IMHO, that proves FB handles the sub-selects in an efficient manner, i.e.
>> doesn't retrieve irrelevant data and exits t
>Just realized you can make it even easier:
>
> merge
>into emp
>using rdb$database
>on emp.fruits = 'mango'
>when not matched then insert (fruits) values ('mango')
An alternative (just an alternative available on all Firebird - and probably
many InterBase - versions, the MERGE c
>As far as I know that the result of LIST(DISTINCT ...) is ordered is only
>an implementation detail, so it shouldn't be something you depend on.
Is there any simple way to order such a list, or any plans to implement it?
Thinking a bit about the LIST answer, I tried
WITH TMP as
(select distinct
I observed something similar many years ago, though I don't remember the exact
error message. I think I solved it by simply using shorter aliases and removing
redundant spaces and words
(e.g. I would change from
from
mv$pdv1 pedidovend0_
left outer join
crt1 carteira1
>i want to union a query A to an other query B, if no any record in a,
>then automatically switch the 3rd query C to union the B. otherwise,
>the A can union to B.
>
>now i try to do it such as:
>
>select 0, 0, 0 from t1 /* C */
>
>where not exists(
> select f1, f2, f3 from t1
> whe
>> select f1, f2, f3 from t1
>> where f1 = 'xxx'
>> union
>> select 0, 0, 0 from rdb$database
>> where not exists(
>> select f1, f2, f3 from t1
>> where f1 = 'xxx')
>> union
>> select f3, f4, f5 from t2
>
>Thanks, this is just my expected result.
>
>but my query , like this,
>
>s
Hi Paul!
> I'm porting an application from FB1.5 to 2.x and a query that worked on 1.5
> doesn't work on 2.0 or 2.1 (I haven't tried 2.5)
>
> The query is:
>
> SELECT u.Id, u.Name, u.Email FROM Punter u
> WHERE u.ALIVE = 1
> /* and they haven't blacklisted this issue */
> AND u.Id not IN (SELECT
>In certain queries I wish nulls to be treated as empty strings. For example:
> Select Field1 || ' ' || Field2 AS MyOutputValue;
>
>In this example, I want the value for Field1 to be returned even if Field2 is
>null.
>
>My first thought is to define a default of '' for the Domains that govern all
>Thank you, Svein. I think I will implement the "Default" solution since my
>application
>allows the end user to create ad-hoc queries (of sorts). I think setting the
>default will
>produce fewer questions about why records are omitted. You are correct in
>assuming that is the same as empty.
>
>SELECT * FROM ARTICLE_CONTENTTREE_LINK WHERE ARTICLEID in
>(Select RELID FROM ARTICLE WHERE DEFINEID=112435)
>
>SELECT * FROM ARTICLE_CONTENTTREE_LINK
>WHERE EXISTS
> (Select RELID FROM ARTICLE WHERE DEFINEID=112435
>AND ARTICLE.RELID=ARTICLE_CONTENTTREE_LINK.ARTICLEID)
I don't think these two
> Thanks for the quick reply.
> I forgot to say that other users who have exactly the same setup do have
> this trigger, so somehow it must have been possible to create it.
> No idea though how.
Identical computers tend to work identically or fail completely. Are you sure
these other users have D
>Hi Tomasz. I already checked the source database for duplicated entries,
>and found none, but i did not tried to verify the database with gfix.
Exactly how did you check it for duplicate entries? The point is that if
there's a problem with an index or key, then doing
SELECT MyIndexedField, coun
>I have a customer that reports from time to time that my application is
>issuing the same Order Number. The order number's source is a generator.
>
>My application (using a very old version of IBO):
>
>1) Creates a Transaction object in code. (default AutoCommit is False)
>2) Creates a
>> Do you make FK over existing records?You probably already have old records
>> in second table which refer to nonexistent PK in first table.Delete all old
>> orphan records in second table, and then make FK.
> What is the best way to delete orphans? Years ago I had a similar problem
> with a
>A is invoice header
>B is invoice rows
>
>SO I need to
>- aggregate rows on the same B.cms_code (something like pn type)
>- summing B.pn_value
>- filering on A.invoice_Date
>
>With your code something strange happens I have value more greater than
>invoice total.
>
Marks code:
>SELECT SUM(b3)
>In Oracle I frequently use "insert into table_a select * from table_a"
>to build up some volume in a table. The first time I tried that in
>Firebird, it never finished - I had to kill it.
Doesn't sound quite logical that Firebird should behave this way, but I don't
doubt your observations and
>> I've got data in a table containing timestamps in a timestamp column. I
>> want to group these by month.
>>
>> How?
>>
>> Anything simpler/better than this:
>> substring(cast(T."TheTimestamp" as varchar(50)) from 1 for 7)
>
>select
> count(*)
> , extract(month from mytimestamp)
>from
> t
>
>Hi,
>
>I'm having trouble with this simple stored procedure:
>
>SET TERM !! ;
>CREATE PROCEDURE test_proc (d_date TIMESTAMP)
> RETURNS (Result CHAR(50))
>AS BEGIN
> Result = CAST(d_date AS CHAR(50));
> SUSPEND;
>END !!
>SET TERM ; !!
>
>If I execute this statement it works fine:
>
>"SELECT a.my
>CREATE TABLE TEST(ITEM VARCHAR(20) NOT NULL PRIMARY KEY, VAL BLOB SUB_TYPE
>BINARY);
>SELECT VAL FROM TEST WHERE ITEM = '...';
>
>INSERT INTO TEST(ITEM, VAL) VALUES('...', ...);
>SELECT FIRST 1 RIGHT(ITEM, 8) FROM TEST WHERE LEFT(ITEM, 8) = '...' ORDER BY
>ITEM DESC;
>
>These are sql statements
>> SELECT A2.ID FROM A2 JOIN I2 ON A2.ID = I2.ID WHERE A2.ALI = 'a0002'; /*
>> returns 2 from A2*/
>>
>> SELECT D2.ID, D2.YMD, D2.TB, D2.MB, D2.DB FROM D2 WHERE D2.ID = A2.ID AND
>> D2.YMD = '2011-12-21'; /* return 2 from D2 */
>>
>> SELECT D2.ID, D2.YMD, D2.TB, D2.MB, D2.DB FROM D2 JOIN I2 ON
>Can anyone explain why FB uses all 3 in the OR query yet only 2 in the AND
>query (See definitions below)?
I'll use a different example, not necessarily restricted to databases. Let's
say you have data of the worlds' population containing the following
information:
PIN
CountryOfResidence
Memb
>**
>now i do in loop with one single thread, one connection :
>
>Start transaction
>insert Into Table_A(ID) VALUES('<#randomchar>'); //
>insert Into Table_A(ID) VALUES('<#randomchar>'); // (100 000 loop)
>insert Into Table_A(ID) VALUES('<#randomchar>'); //
>commit transactio
Hi Michael!
You didn't ask, but there are a couple of things in your procedure that makes
me wonder whether it is actually what you want (sorted in order of importance):
> ELSE IF ((iHour > 0) AND (iHour < 11)) THEN
>sHour = '0' || iHour;
Probably you want iHour < 10 rather than 11
Does this statement return any rows?
SELECT EV.POSICAO_ID_FINAL
FROM EXCESSOS_VELOCIDADE EV
WHERE NOT EXISTS(SELECT * FROM POSICOES P WHERE P.COMUNICACAO_ID =
EV.POSICAO_ID_FINAL)
If so, that value must be added to POSICOES before you can create your foreign
key.
HTH,
Set
>>> Hi!
>>>
>>> I find myself repeatedly writing code such as
>>>
>>> SELECT (a+b)*c AS value1,
>>> some_function((a+b)*c) AS value2,
>>> some_other_function((a+b)*c) AS value 3
>>> FROM ...
>>
>>> Is there any other way to "reuse" a calculated value by referencing its
>>> alias
Hi Michael!
>I have a table containing some 33.879.139 records.
>
>If I do a simple select like this:
>
>
>Select Dato from Transaktioner
>Where
> VareFrvStrNr='090179'
> AND Art=11
> And Transaktioner.Afdeling_ID<>'9'
>
>it fecthes 11 records within 0.3 seconds.
>It will use the index on V
>Hello,
>
>when i execute several insert (or update or delete), is it possible to create
>a trigger that will be raise not on each
>insert/update/delete but at the end of these "bulk" inserts ? i can off course
>call the stored procedure from the program
>but for good database integrity i prefe
>> I'm trying with (to get all records from Feb 2nd)
>>
>> where
>> (MyTimeStamp between '02-01-2012 00:00:00' and '02-01-2012
>> 23:59:59')
>>
>> it uses the index, but I don't get all the records
>
>My mistake!... that seems to work OK... Now I just wonder if that is the
> *best* way to do
>Im trying a bit difficult select:
>
>select id, value
>from MyTable
>where id in (1, 1, 1, 2, 3, 3, 4)
>
>I want that for every value inside IN a row is output, so for example this
>would result something like:
>1 | 40
>1 | 40
>1 | 40
>2 | 21
>3 | 23
>3 | 23
>4 | 87
>
>But now I don't take duplic
>When trying to execute the following it produces an error:
>
>SET TERM ^ ;
>
>EXECUTE BLOCK
>AS
> DECLARE VARIABLE vMaxID BIGINT;
>BEGIN
> SELECT MAX(ID) FROM A_TABLE
> INTO :vMaxID;
>
> SET GENERATOR GEN_A_TABLE_ID TO :vMaxID;
>END
>
>SET TERM ; ^
>
>the error being
>
>Engine Code: 335544
>Hello,
>
>I need help optimizing the query shown below. PROJECT-related tables
>contain 12 rows each while COPY-related tables contain 14K rows each.
>Moreover, PROJECT."ASSIGNMENT_STATUS" != 'UNASSIGNED'
>filters out all but 10 joined rows.
>
>My question is how do I change the plan to force
>P
>Hello,
>
>i m in multi user database.
>
>I want to clean a table of all entries from one user (rec with field
>id_user=xxx)
>and insert new entries, BUT i must be sure that noone is not actually doing
>any
>insert on this table for this user. i can not lock the table because this will
>affect
> >Hello,
> >
> >i m in multi user database.
> >
> >I want to clean a table of all entries from one user (rec with field
> >id_user=xxx)
> >and insert new entries, BUT i must be sure that noone is not actually doing
> >any
> >insert on this table for this user. i can not lock the table because
>Hello,
>
>this is my query :
>
>Select
> MAINTABLE.ID
>from
> MAINTABLE
>Join SECONDTABLE on SECONDTABLE.ID_obj=MAINTABLE.ID_obj
>Join THIRDTABLE on THIRDTABLE.ID_obj=MAINTABLE.ID_obj
>where
> (MAINTABLE.name = 'jean' or
> SECONDTABLE.name = 'jean' or
> THIRDTABLE.name = 'jean')
>oops, now i have another probleme
>
>Select
> First 10
> MAINTABLE.ID
>from
> MAINTABLE
>where
> (MAINTABLE.name = 'jean')
>ORDER BY MAINTABLE.DATE
>
>UNION
>
>Select
> First 10
> SECONDTABLE.ID
>from
> SECONDTABLE
>where
> (SECONDTABLE.name = 'jean')
>Order by SECONDTABLE.DATE
>
>So, what I wanted to avoid is for the query optimizer to join all tables first
>and only
>then apply the != 'UNASSIGNED' filter. Instead, I wanted the optimizer to
>apply the filter
>to the PROJECT table first, then join (and find 10 matching rows) and only
>after that do
>the aggregation. An
Hi Arno!
Is Firebird intelligent enough to use an index for
PROJECT."ASSIGNMENT_STATUS" != 'UNASSIGNED'
when over 99% of the data contains 'UNASSIGNED'? I thought we had to wait for
Firebird 3 to see histograms and that 'not equal' would not be able to
efficiently use an index before that?
Ea
>hello,
>
>how to do this :
>
>select
> Id_contact,
> (select First 1 skip 0 Amount, currency from invoice where
> ID_invoice=id_contact and ...) as amount_last_invoice, >currency_last_invoice
>
>from
> contact
>
>off course i can do 2 time (Select Amount ...) as amount_last_invoice, and
>
>> How many records approx.?
>ok, the select count(*) return ... 4 300 000 record (for last 3 days)
What are the statistics of that database? I just counted 3.6 million records in
1 min 18 seconds (albeit that table contains 50 years of history, not 3 days,
so the system isn't anywhere as busy
>What I need is to find last items price from selected period.
>
>Here's how i do it now:
>
>select
>tblItems.item_ID,
>tblItems.Item_Name,
>(Select tblPrices.Price from tblPrices where tblPrices.DateTime =
> (select max(tblPrices.DateTime) from tblPrices
> where tblPrices.Item_id = tblItems.
>no one have any explanation ?
>
>what i don't understand is that in select IDObj From DESCRIPTION where
>ID='ID_NOT_EXIST'
>the speed is slow when no record are founded (so the number of field/size in
>table
>Description must not matter, only the size of the index) ! but it's not the
>case, i
>I gave all privileges to the trigger on its table but that did not help.
What do you mean by 'its table'? Grant the trigger rights to the table it
should insert/update, e.g.
GRANT ALL ON TABLE1_PRIVILEGES TO ;
HTH,
Set
>> Is your question theoretical for some kind of case study or DBMS
>> comparison thingy or did you hit a limit already? Just curious. ;-)
>
>In really, is a practical question.
>
>I have to classify some records (about 300k) in many categories (nearly 200).
>So, I thought
>do this directly in
> In one of your mails you wrote you use a transaction pool. I personally do
> not understand the use of that, since transactions with Firebird should be
> as short as possible. If you keep transactions open for a long time,
> performance can degrade over time with increasing users and load beca
> 3/ as i know readonly can stay open undefinitively without hurting the
> database
Provided the isolation level also is READ COMMITTED. If you use SNAPSHOT as
your isolation level, then even read-only transactions block garbage collection
(at least, I cannot imagine how to keep a stable view o
>Greetings,
>
>I'm trying to eliminate the need for the FreeUDFLib so I can have the option
>of running Firebird 32-bit or 64-bit.
>I believe I'm down to 4 UDFs - are there any replacement for the following:
>F_DAY (extracts the day from a passed date)
>F_MONTH (extracts the month from a passed da
On 16-3-2012 17:13, Kevin Stanton wrote:
> Greetings,
>
> I believe I'm down to just 3 UDFs and was wondering if there are any
> replacements for:
>
> F_ADDMONTH
> F_ADDYEAR
> F_AGEINMONTHS
>
>
> Also, is there a place where I can get a list of all functions for FB
> 1.56 (and beyond)? I've take
>hello,
>
>What the best way to join 2 tables across 2 databases ?
>
>actually i do on the 1st database a select First 100 ID from myTable1 where
>...
>and in the second database select * from MyTable2 where ID in
>()
>
>but the problem, the select * from MyTable2 where ID in
>() is very slow, e
>> Select
>> Field1,
>> (select ...) AS MyFIELD2
>> IIF(MyField is null, select , Null) As MYField3 From
>>
>>
>> it's not work, it's say unknow "MyField"
>> but if i replace MyFIELD2 by Field1 it's work ...
>>
>> is their any workaround to make this SQL work ?
>I don't see a My
>>try:
>>
>>select first 1 a.pfad
>>from t_pictures a where status <> 6 and
>>not exists (select 1 from t_pictures where status < 5 and kd_id = a.kd_id
>>and li_id = a.li_id)
>>into :pfad;
>>
>>(also first 1 will return just 1 record but it could be any record in the
>>set as you have no "order by"
>I've a table with 150 000 records.
>
> DEL = 1 - about 100 records
> DEL = 0 - about 149 900 records
>
> What can I do to speed up this query ? Without ORDER it runs in about 1.5 s.
Returning only 500 records, it is probably better not to use the index for
ANAME. You might change your query to:
>So, what all this means ? Using large index for sorting small resultset is
>ineffective ?
Something like that. At prepare time, Firebird doesn't know whether your query
will return 500 or 10 records. If it returns 10 records, then the index
is beneficial, if it returns 500, then it is
>SELECT cast((5411.000455/0.20) as numeric(16,6)) as mount FROM
>MON$ATTACHMENTS r
This particular query can be rewritten as
SELECT 5411.000455*cast(1/0.20 as integer) as mount FROM MON$ATTACHMENTS r
Though it will not work equally well with other numbers, at least not if
cast(1/0.
>My BIG concern is that my program (Delphi XE with good old IBX components) run
>with the exactly same release,
>on 4 different clients generating diferent results. 3 of these are in normal
>parameters (while working, the
>gap between OIT/NT and OAT/NT and OST/NT increases by a few transactions
>Hi,
>I have an application which executes a stored procedure in Firebird 1.5.6 on a
>fairly
>regular basis (multiple times per day).
>
>Immediately following a backup/restore of the database, the procedure runs
>fairly quickly
>(less than a second or two) at any time of day.
>
>Gradually, over
>Tools: Delphi 2006, IB Objects 4.9.14
>
>Query:
>SELECT (COUNT(*)+0.00)/2 as "LabelsCount"
>FROM LABELS
>
>previously worked. Now I get an error:
>
>Invalid expression in the select list (not contained in either aggregate
>function or GROUP BY clause
>
>However when I run the query from the SQL e
>>This query uses natural for both tables, when I expected it to use an
>>index for the join.
>>
>>select *
>>from table1 t1
>>join table2 t2 on t2.indexed_char14 starting with t1.indexed_char10
>>where t1.unindex_varchar containing 'foo'
>>
>>This uses an index, so the optimizer knows about it.
>>
>we have some workstations. Every ws need to show some fields of an table.
>Now I thought, I can configure that with the numbers of the column. I also
>could write
>down the column-Names in a configuration table, but sql statements should be
>slower.
No need for a configuration table, Olaf, you
> POSITION(sep || soughtval || sep in sep || searchedvaluelist || sep) > 0
>
> Adding sep before and after each string makes sure you don't get false hits,
> like '12'
> in '341256,234,567' where '12' appears inside '341256', but ',12,' doesn't.
Good idea, Kjell, but then why not use CONTAINING
>Hi all
Hi again, Bhavbhuti.
>I have Parent-Child tables that contain Price List and are updated with the
>same items
>in them but with either a later date or a later batch no. filled in, so
>typical child
>records would be as follows
>
>iID, iItemID, bRate, dDt, iBatchNo
>1, 123, 50.00, empt
Sorry, I didn't read the subject line, here's a modified query that excludes
those that have later dDts or iBatchNos:
SELECT lPLFI.*,
mI.cDesc,
mI.cPack,
mI.cCode
FROM lPriceListsFixedItem lPLFI
LEFT JOIN mItems mI
ON lPLFI.iItemID = mI.iID
WHERE lPLFI.iPID
>There's a semantic difference between NOT IN and the equivalent NOT EXISTS
>having to do
>with NULL values and empty result sets. I've understood it from time to time,
>but this
>is not one of those times. So it's not possible to fold all IN subqueries
>into EXISTS
>subqueries. Worse, the
1 - 100 of 564 matches
Mail list logo
