Henry Olive

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

Thank you so much Vandrovnik, Attila

I wish everyone a healthy day. I have a DBGrid and one of a column has Pick List I fill the pick list like first proc. and read the picklist values in second proc. procedure TForm1.DBGrid2ColEnter(Sender: TObject); if DBGrid2.SelectedField <> CDS1.FieldByName('WSNAME') then Exit; while not MyQry.Eof do begin DBGrid2.Columns[ColIndex].PickList.AddObject(MyQry.Fields[1].AsString, Pointer(MyQry.Fields[0].AsString)); MyQry.Next; end; procedure TForm1.DBGrid2ColExit(Sender: TObject); var Inx:Integer; begin if DBGrid2.SelectedField <>CDS1.FieldByName('WSNAME') then Exit; Inx:=DBGrid2.Columns[2].PickList.IndexOf('WSNAME'); //problem is here i get always -1 here CDS1.FieldByName('WSNO').AsString:=IntToStr(Inx); end; what is wrong ? Thank You

Thank you so much Lajos, EMailx45

Thank you so much FPiette , Lajos Sorry for my poor english I'm adding WSNO (String) and WSNAME (String) fields values in to a picklist (by a query) with below code DBGrid2.Columns[ColIndex].PickList.AddObject(MyQry.Fields[1].AsString,Pointer(MyQry.Fields[0].AsString)); so, in the pick list just WSNAME field values are visible ( MyQry.Fields[1] ) When user choose a WSNAME in the pick list, i need to get WSNO and WSNAME values (choosen by user) ; P.S = If i was dealed same thing with a combobox then i would write below code WSNO:=IntToStr(Integer(Combobox1.Items.Objects[Combobox1.ItemIndex])); Thank You

I wish everyone a healthy day I'm getting multiple rows in singleton select err.msg. UPDATE ORDERS O SET O.DELVTOTAL= (SELECT(OD.QTY - OD.REMQTY) * OD.UPRICE FROM ORDETAIL OD WHERE O.ID=OD.ID) WHERE EXISTS (SELECT 1 FROM ORDETAIL OD WHERE O.ID = OD.ID AND O.STATUS='Partial Delivery') what is wrong ? Thank You

Yes, Thank so much Attila