Henry Olive

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

Thank you so much EMailX45 I think i couldnt express my problem well i'm very sorry. You made JUST common table exprression SQL, NOT Stored Procedure JUST common table expression works w/o any problem in my side also I need to use this CTE in a Stored Proc to get expenses according to months Expect result should be like below ( for 12 months) with a stored procedure Month......Total (It is Expenses Total) 1................10,000 2................16,000 ... 12.............14,000

I made backup an old database then i restored it in new Interbase-2020 when i try to test the new Truncate function i got "Attempted Update During Read Only Transaction" error msg. I checked my database's properties in IbConsole they are like below ReadOnly=False, WriteMode=Synchronous What could be the problem ? Thank You

I saw the settings it was in InteractiveSQL section

Thank you Stano Where is the Transaction Settings ?

I wish everyone a healthy day. I upgraded my IB2007 to IB2020 After Install IB2020 i opened IBConsole and tried to connect my old database(IB2007) for BackUp & Restore but i got Unsupported on-disk structure for file ...... found 12, support 18 What is wrong ? Thank You

Thank you so much Jeff

Thank you so much everybody answered my question. I made old version backup w/o any problem C:\MyData.GBK but i cant see any Restore choice in New version IbConsole I tried to add BackUp alias in IBConsole on the left side IbConsole but you cant show adress of OLD database Thank You

Thank you so much Dany, Markus

I wish everyone a healthy day. Interbase-2007 OrderDetail Table ID....LINENO...ITEMNO.....QTY...UPRICE 300.....1200.........AAA.............4...........100 300.....1201.........BBB..............5...........120 I made Partial Shipments in different dates. and i changed the Order Detail UPRICE value *after transfer* the Order into Delivery table (for currency convert issues), DeliveryDetail Table ID....LINENO....ITEMNO....QTY...UPRICE......ORDETLINENO 44........300.............AAA..........1............80..............1200 44........301.............BBB...........4............50..............1201 52........420.............AAA..........3............80..............1200 56........450.............BBB...........1............50..............1201 I need a SQL to update Delivery Detail Table's UPRICE datas, according to Order Detail's UPRICE datas ORDETLINENO field= OderDetail's LINENO field.value After SQL, the result of Delivery Detail table's should be like below ID....LINENO....ITEMNO....QTY...UPRICE......ORDETLINENO 44..........300...........AAA..........1............100..............1200 44..........301...........BBB...........4............120..............1201 52..........420..........AAA...........3............100..............1200 56..........450..........BBB............1............120..............1201 Could someone please help ? Thank You

I wish everyone a healthy day. Interbase 2007 - D-XE I have a view which has all datas from the customers. (CustNo, TDate(Trans.Date) ,TName(Trans.Name/Buy, Sell), Debit, Credit, DueDate, Status etc.) I want to close (Status=Paid) all invoices of A customer which paid Is it possible to solve this issue with a s.proc ? (or a better way) Can someone please help me ? Thank You

Thank you so much FPiyette, Bob, EMailX45 View is like below (view gets datas from Invoice, Bank, Cash, Cheque) (I know releated table name via TName) like Sell=Invoice, B.Trs=Bank, Cash=Cash etc. ID......DocNo...TDate...........TName....CustNo....Debit........Credit.....DueDate.......Status 5...........0001....01/25/20.......Sell..............1...........100,00...........0,00......02/25/20 24........0025.....02/15/20.......Sell.............1...........100,00...........0,00......04/15/20 132.....1268.....02/25/20.......B.Trs............1.............. 0,00......100,00 customer paid 0001 invoice number invoice on 02/25/20 by bank transfer Now i want to update Invoice Table's(ID=5 ) Status field=Closed Thank You

I wish everyone a healthy day Memo1.Text has total 3 sql commands like below - Create Table A (ID Integer); - Insert into A Values(1); - Update A set ID=2 Where ID=1; I want to get each command & execute them one after another by a query for example for the first line (Create Table A ID Integer;) var X:integer; S,Command:string; begin S:=Memo1.Text; //suppose memo1.text in 1 line X:=Pos(';',S); Command:=Copy(S,1,X-1); MySqlDataset.CommandText:=Command; MySqlDataset.Execute; But i want to loop the memo and get 3 commands *or more* in order. Thank You

Thank you SO MUCH Kas I'm using Delphi XE I'm getting Too many actual parameters in below code (it doesnt accept Start ) Curr := Pos(';', Memo1.Lines.Text, Start);

Thank you so much Kas If you have time could you please show the codes?

Thank you so much Gunther I'm so sorry i forgot to mention that i use Delphi-XE XE doesnt recognise SPLIT.

I wish everyone a healthy day i want to UPDATE a stock quantities with below S.Proc i dont get any err. msg. but this sql doesnt update correctly ALTER PROCEDURE "UPDATEITEMQTY" ( "ITMNO" VARCHAR(20)) AS declare variable TRS NUMERIC(18, 5); /* Qty Transfer from last year */ declare variable INCOME NUMERIC(18, 5); /* Stock IN */ declare variable OUTGO NUMERIC(18, 5); /* Stock OUT */ BEGIN SELECT SUM(IM.TRS)TRS, SUM(IM.INCOME)INCOME, SUM(IM.OUTGO)OUTGO FROM ITEMMOVEMENTS IM /* IM=a VIEW & every item movements are CORRECT */ JOIN WHOUSE WH ON WH.WHNO=IM.WHNO WHERE ITEMNO=:"ITMNO" and WH.WHTYPE <>'Rework' INTO :TRS,:INCOME,:OUTGO; UPDATE ITEMS SET /* IB2007 has Coalesce problem so i wrote below code */ TRS= Case When :TRS >0 then :TRS else 0 end, INCOME= Case When :INCOME >0 then :INCOME else 0 end, OUTGO= Case When :OUTGO >0 then :OUTGO else 0 end, STOCK= (:TRS + :INCOME) - :OUTGO WHERE ITEMNO=:ITMNO; END Can someone help me please ? Thank You