Henry Olive

Thank you so much Attila, If you have time can you please correct my code.

I wish everyone a healthy day. How can i get get SUM of a calculated field ? I have a TOTAL field which is calculated. I want to get SUM of TOTAL and write the result in a Label or DBtext p.s : I know i can do it While Not CDS1.Eof ... I wonder is there a better way Thank You

Thank you so much Dany, var MyCalcField : Double (CDS1 =Client Dataset;) MyCalcField :=(CDS1A.asFloat + CDS1B.asFloat + CDS1C.asFloat) - (CDS1D.asFloat + CDS1E.asFloat); Now i want to get Sum of MyCalcField (Total result of MyCalField) and I want to do this process in Delphi CDS1 View : EmpName MyCalcField AAA...........................5,000 BBB............................6,000 I need to get 5000 + 6000 = *11,000 result* I know i can make this with a SQL and i can make scan the CDS1 (While not CDS1.Eof do ... ) I thought may be there could be a better way to do this in Delphi

I wish everyone healthy days. I want to get day count of a month for example, for january=31 for february=28 (in 4 years it is 29) for April=30 I tried SELECT EXTRACT (DAY FROM current_date) FROM rdb$database where extract(month FROM current_date)=2 but not correct. Thank You

Interbase 2007 CREATE PROCEDURE EMPTAXBASE ( TMONTH SMALLINT) AS declare variable EMPPORTION NUMERIC(12, 5); begin SELECT EMPPORTION FROM COMPANY INTO :EMPPORTION; UPDATE EMPLOYEE SET TAXBASE= Case When SHAREHOLDER='Y' then (BRUTSALARY * :EMPPORTION /100) * :TMONTH End end I'm getting 'Token Unknown end' error ( which is the LAST end, the other is End ) what am i doing wrong ? Thank You

Stano, Thank You SO SO MUCH for your help, i solved the problem There were 2 problems 1=Case should have been between Parentheses 2=At the end of statement there should have been ; (Case when aa then bb else cc end) ;

I created a simple S.Proc in EMPLOYEE Database If someone has 5 minutes time may be he can try to create below s.proc. This proc also gives error 'Token unknown - line 12, char -1 end' CREATE PROCEDURE TEST AS DECLARE VARIABLE MYCOUNTRY VARCHAR(40); begin SELECT COUNTRY FROM COUNTRY WHERE COUNTRY='Fiji' INTO :MYCOUNTRY; UPDATE CUSTOMER SET COUNTRY = Case When COUNTRY='USA' then :MYCOUNTRY When COUNTRY <> 'USA' then COUNTRY End end

Thank you so much Stano I noticed that you made a typo mistake I added *as* after End (Case's End) like : as MYRESULT End error is Token Unknown as

Thank you so much Stano I added 'as MyResult' after Case's End This time i get 'Token Unknown' as error

I wish everyone a healthy day SELECT EMPNO,EMPNAME, PRIORTAXBASE, BRUTSALARY, Case When SHAREHOLDER='Y' then (PRIORTAXBASE + BRUTSALARY) When RETIRED='Y' then (PRIORTAXBASE + BRUTSALARY)- ((PRIORTAXBASE + BRUTSALARY) * 4 /100) + When (SHAREHOLDER IS NULL or SHAREHOLDER='N') then (PRIORTAXBASE + BRUTSALARY) - ((PRIORTAXBASE + BRUTSALARY) * 0.18) end as TAXBASE /* Upper SQL works fine, Here below i want to use TAXBASE (upper result) like below */ Case When TAXBASE <= 30000 then TAXBASE * 20 When TAXBASE >30000 and TAXBASE <=50000 then TAXBASE * 30 .... end as TAXTOTAL from EMPLOYEE Thank You

Thank you so much Stano, I'm so sorry, i forgot to add my message that i use IB-2007 and IB-2007 doesnt have Common Table Expression In this case i think there is no chance WITHOUT a view or S.PROC Again i'm so sorry.

Thank you so much Stano, Jeff, EmailX45

I wish everyone a healthy day Interbase DELETE FROM MDETAIL MD JOIN ITEMS IT ON MD.ITEMNO=IT.ITEMNO WHERE MD.RNO=120 and IT.ITEMTYPE ='001'; I'm getting token unknown JOIN err.msg Thank You

Thank you so much Joachimd

My table's FILEPATH.Value:='\\MySRV\MyFiles\Oraxxo.Txt'; I want to open above file location in windows explorer I know how to open (run) the file, i just need source location in windows explorer Thank You

Thank you so much Gunther

Interbase AAA Part's BOM (AAA uses 2 pieces AAA-1) ITEMNO................CHILDITEMNO....Qty AAA.........................AAA-1.....................2 AAA-1 Part's BOM ITEMNO................CHILDITEMNO....Qty AAA.........................AAB..........................2 AAA.........................AAC..........................3 I was expecting below result ITEMNO....CHILDITEMNO...QTY AAA.............AAA-1......................2 AAA1...........AAB..........................4 AAA1..........AAC...........................6 but i get below result ITEMNO....CHILDITEMNO...QTY AAA.............AAA-1......................2 AAA1...........AAB..........................2 AAA1..........AAC...........................3 My Recursive S.Proc like below FOR SELECT B.ITEMNO, B.CHILDITEMNO, SUM(B.QTY) QTY FROM BOM B WHERE B.ITEMNO= :"ITMNO" GROUP BY B.ITEMNO,B.CHILDITEMNO INTO :"ITEMNO",:"CHILDITEMNO", :"QTY" DO BEGIN SUSPEND; FOR SELECT B1.ITEMNO, B1.CHILDITEMNO, SUM(B1.QTY) QTY FROM BOMRECURSIVE(:"CHILDITEMNO") B1 GROUP BY B1.ITEMNO,B1.CHILDITEMNO INTO :"ITEMNO",:"CHILDITEMNO", :"QTY" DO BEGIN SUSPEND; END END Could some please correct my s.proc ? Thank You

Perfect Thank you SO MUCH Serge

Thank you so much Stano if i change 2.select like below the query is very very very slow SUSPEND; FOR SELECT B1.ITEMNO, B1.CHILDITEMNO, (B.QTY * B1.QTY) QTY /* removed sum, added b.qty b1.qty FROM BOMRECURSIVE(:"CHILDITEMNO") B1 JOIN BOM B ON B.CHILDITEMNO=B1.ITEMNO /* added join */ INTO :"ITEMNO",:"CHILDITEMNO", :"QTY" DO BEGIN SUSPEND; END END

Interbase UPDATE INVDETAIL ID SET ID.UPRICELOCAL=(ID.UPRICE-(ID.UPRICE * ID.DISCPERC /100)) * I.CURRRATE WHERE EXISTS (SELECT 1 FROM INVOICE I where I.RNO = ID.RNO) I'm getting Column unknown I.CURRRATE err.msg. Thank You

Thank you SO MUCH Jeff it is Perfect Thank you Serge

I have an Interbase 2007 database and i'd like to move to Firebird 3,0 Is there any to Database Management Tool that can transfer ALL metadata & datas from IB to Firebird w/o any headache ? Thank You

I wish everyone a Healthy day Interbase 2020 I try to use a comman table expression in below stored procedure I created the s.proc w/o any problem but when i try to call the s.proc I get Multiple Rows in a Singletone Select err.msg What is wrong ? Thank You CREATE PROCEDURE EXPMONTHLY RETURNS ( TMONTH SMALLINT, MONTHNAME VARCHAR(8), TOTAL NUMERIC(18, 2) ) AS BEGIN WITH CTE (TMONTH,TOTAL) AS( SELECT TMONTH,SUM(SUBTOTAL) TOTAL FROM INVEXP WHERE TNAME='ExpInv' GROUP BY TMONTH UNION ALL SELECT TMONTH,SUM(TOTAL) TOTAL FROM SALARY WHERE TNAME = 'Sal.Pay' GROUP BY TMONTH ) SELECT CTE.TMONTH,M.MONTHNAME,SUM(CTE.TOTAL) TOTAL FROM CTE JOIN MONTHS M ON CTE.TMONTH=M.SMONTH GROUP BY CTE.TMONTH,M.MONTHNAME INTO :TMONTH,:MONTHNAME,:TOTAL; SUSPEND; END;

Thank you SO SO SO MUCH Dany I just added FOR , DO , SUSPEND and now it works perfectly I wish you a healthy, happy new year with your loved ones