Henry Olive

I wish everyone a healthy day. I'm trying to move from Interbase to Firebird I cant create below IB Procedure in Firebird ( I made copy in IB-IBConsole and Paste in FB-FlameRobin ) (The proc works well in IB) I'm getting, SQL error code = -104 Unexpected end of command - line 26, column 3 (*Which is SUSPEND*) What is wrong ? Thank You CREATE PROCEDURE "CUSTITEMMOVEMENT" ( "CUSTNO" INTEGER ) RETURNS ( "RNO" INTEGER, "TNAME" VARCHAR(5), "TDATE" DATE, "DOCNO" VARCHAR(12), "QTY" NUMERIC(18, 2), "NETPRICE" NUMERIC(18, 4), "ITEMNO" VARCHAR(20), "ITEMNAME" VARCHAR(40) ) AS BEGIN FOR SELECT IM.RNO,IM.TNAME,IM.TDATE,IM.DOCNO,IM.QTY, IM.NETPRICE,IM.ITEMNO, IT.ITEMNAME FROM ITEMMOVEMENTS IM JOIN ITEMS IT ON IT.ITEMNO=IM.ITEMNO WHERE (IM.CUSTNO= :"CUSTNO") ORDER BY IM.TDATE, IM.DOCNO INTO :"RNO",:"TNAME",:"TDATE",:"DOCNO",:"QTY",:"NETPRICE",:"ITEMNO",:"ITEMNAME" DO SUSPEND; /* Line 26 is here */ END ;

Thank you so much Ruslan, Serge Ruslan, with your suggestion i got same error. Serge, Your suggestion is OK now i can create SP. again Thank you

I wish everyone a healthy day. I'm planning to move from Interbase to Firebird What is the best admin tool for Firebird ( Free or not Free ) Thank You

I wish everyone a healthy day UPDATE TABLE1 T1 SET T1.FIELD1=(SELECT SUM(T2.FIELD1) FROM TABLE2 T2 WHERE T1.ID=T2.ID), T1.FIELD2=(SELECT SUM(T2.FIELD2) FROM TABLE2 T2 WHERE T1.ID=T2.ID) WHERE EXISTS (SELECT 1 FROM TABLE1 T1 WHERE T1.ID= T2.ID); This query works w/o any error but doesnt change the values, what is wrong ? Thank You

Thank you so much Mvanrijnen, Danny

Oops, Thank you so much Lars

Thank you so much everybody answered

I wish everyone a heathy day. I have a Memo.Text, if this memo.text starts with SELECT then I'll open myquery like below MyQuery.OPEN if this memo.text starts INSERT or UPDATE or DELETE or CREATE or ALTER then I'll make ExecSql like below MyQuery.EXECSQL; I wrote below code if Pos('SELECT', UpperCase(Trim(Memo1.Text))) > 0 then MyQuery.Open else MyQuery.ExecSql; but if a procedure has FOR SELECT then naturally i'm getting error msg. because the query looks for EXECSQL but finds OPEN Any advice ? Thank You

Thank you so much FrostBrutal, Lajos, Remy

Thank you so much aehimself, Uwe, Frost.Brutal, Stano, haentschman I'll use this SQL routine in my development not for user. I Think for me the most important thing is if memo1.text starts with SELECT or not if it starts with SELECT then i know it is a Regular Query ( Query.OPEN ) other wise i'll need to use EXECSQL so if it is CTE query or ALTER or CREATE, or DELETE ...... then i'll use EXECSQL My Code will be like below Str := UpperCase(SplitString(Memo1.Text, ' ')[0]); if Str ='SELECT' then Query1.Open else Query1.ExecSql;

Thank you so much aehimself. I think i need to find the FIRST WORD of the memo1.text The other words are not important. Because all commands starts with SELECT, CREATE,ALTER,INSERT,DELETE,UPDATE.... May be i should have asked the question like below How can i get THE FIRST WORD of a memo1.text ? Thank you again.

Thank you so much David, Lars David i'm so sorry instead off OPEN i mistakely wrote EXECSQL

I wish everyone a healthy day. Table-1 (PK = CustNo & ItemNo) CUSTNO...ITEMNO...PRICE 1....................AA..............100 1....................BB...............150 2....................AA..............100 2....................CC..............200 I want to Insert Table-1's all record into a new Table-2 and i dont have any Generator for LINENO. Is it possible to add LINENO field a counter number by Insert Sql ? ( The result for Table-2 should be like below ) Table-2 ( PK=CustNo & LineNo ) CUSTNO...LINENO...ITEMNO...PRICE 1...........................1............AA.............100 1...........................2............BB..............150 2...........................1............AA.............100 2...........................2............CC.............200 Thank You

I wish everyone a healthy day. MyDBGrid Looks Like Below ( I used Grouping in Client DataSet ) LINENO...ASSYNO....ITEMNO ------------------------------- 1..................ASSY1...........Item1 2..........................................Item2 3..........................................Item3 Now i need to get which LineNo's MAINASSY is empty ? According to above sample LineNo1'S mainassy is not empty but LineNo2 and LineNo3 are empty I need something like in OnDrawColumnCell event if DBGrid1.Columns[1].Text = '' then ..... Thank You

Thank you so much for your kind reply Dany I solved my problem with below code If Table1.FieldByName('ASSYNO').Text <> '' then .... *In case of you use GROUPING in a ClientDataset* with FieldByName(...).TEXT, you can check if there is a value in Dataset but No Text in DBGrid's dataset's same field Again thank you so much.

Thank you so much Lajos Field.Value cant be null or Empty because in Dataset it has a value , but it is empty in DBGrid cause of CDS Grouping The field's ONGETTEXT event there is below code if gbFirst in BOM.GetGroupState (1) then Text := Sender.AsString else Text := ''; Dataset Looks like below ASSYNO.....ITEMNO --------------------- Assy1..............ItemNo1 Assy1..............ItemNo2 Assy1..............ItemNo3 But DBGrid looks like (Cause of Grouping) ASSYNO.....ITEMNO --------------------- Assy1..............ItemNo1 ..........................ItemNo2 ..........................ItemNo3 I need Empty Columns that is Line2 and Line3

Thank you so much Serge Unfortunatelly there is no recursive CTE in Interbase ( I'm planning to move Firebird ) I decided to create a Generator Thank you again

Thank you Hanetchman i just wondered if it is possible to insert SQL ( not proc) with one of a field with auto counter (like generator, but w/o created any generator) Thank you so much Danny for your detailed explanation i really appriciate for the time you spent for me.

I wish everyone a healthy day. I have a Master-Detail tables (Components : SQLDataset+DSProvider+ClientDataset+D.Source) 2 table's comman field is ID field, Detail Table Primary Keys are ID,LINENO, In my Detail table i have a SQL Command *w/o any ORDER BY clause* But i want to show the detail table's datas in a DBGrid with sorted (ITEMNO) Even though SQLDataset's SortFieldNames=ITEMNO but it doesnt sort How can i show detail table's data in a dbgrid with ITEMNO sorted **without add CommandText ORDER BY ITEMNO clause.** (P.s : If i add commandtext ORDER BY ITEMNO it works ) Thank You

I solved the problem I wrote just ITEMNO in the sortfieldname instead of OD.ITEMNO ( SortFieldName requires field name *with Alias* in the CommandText) Thank You

I wish everone a healthy day. Master Table looks like below ID.....STATUS 1..... 'Planned' Detail Table looks like below ID......ITEMNO....QTY 1........AAA..............4 1........BBB...............2 1........CCC...............1 ItemCard Table looks like below ITEMNO.....RESERVED AAA...............4 BBB................2 CCC................1 When i change Master Table's STATUS from 'Planned' to 'Finish' then I want to update ItemCard table's Reserved fields (Dicrease reserved qtys as Detail table's quantities). that is after Status =Finish then ItemCard table results should be like below ITEMNO.....RESERVED AAA...............0 BBB................0 CCC................0 Could someone please help me. Thank You

Thank you so much Stano and Serge I used Trigger as Serge's suggestion

Thank you so much Stano No need any JOIN ? ( ItemCard & DetailTable ) How can i get ITEMNO params ('AAA','BBB','CCC') ? If you have time could you please write the SQL ? Thank you

I wish everyone a healthy day. I have below INTERBASE Trigger. Eventhough i had no problem creating the trigger it doesnt work as expected ( I fill RESERVQTY ( value=2) when running the program by myself, after post when i check RESERVQTY's value in the Database i see the value =2 but trigger Doesnt write ITEMS table's ORDRESERVE field anything, Should have been 2 ) What am i doing wrong ? Thank You CREATE TRIGGER "ORDETRESERVE_INS" FOR "ORDETAIL" ACTIVE AFTER INSERT POSITION 0 AS begin IF (New.RESERVQTY > 0) THEN UPDATE ITEMS SET ORDRESERVE = (COALESCE(ORDRESERVE,0) + New.RESERVQTY) WHERE ITEMNO = New.ITEMNO; end;

Thank you so much CorneliusDavid, SkyzoFrame