Re: [firebird-support] Insert error: count of column and variable list do not match
Thanks Martijin. That worked.
[firebird-support] Insert error: count of column and variable list do not match
I'm trying to insert data from a query into a table however I receive the following error message: SQL Message : -104 Invalid token Engine Code: 335544569 Engine Message : Dynamic SQL Error SQL error code = -104 Invalid command count of column list and variable list do not match The table is defined as: CREATE GLOBAL TEMPORARY TABLE tmp_gdlmove ( verid BIGINT, level_num SMALLINT, parentid BIGINT, times_assigned SMALLINT, journey CHAR(1) ) ON COMMIT DELETE ROWS; and the query I'm trying is defined as: INSERT INTO tmp_gdlmove (verid, level_num, parentid, times_assigned) VALUES((SELECT verid, level_num, parentid, count(*) as times_assigned FROM (SELECT DISTINCT ass.verid, org.level_num, ass.ORGID, och.parentid, org.org_nme FROM ASSIGNMENT ass INNER JOIN orgchart och on och.childid=ass.orgid INNER JOIN organisation org on och.parentid=org.orgid WHERE verid IN (3) AND level_num IN (1,2) and depth =1) GROUP BY verid, level_num, parentid)) The select portion of the query returns the same number of columns as I have listed in the insert statement so I'm unsure why I receive this message.
[firebird-support] Structure Chart - Moving subtree in a closure table
I'm using a closure table to manage an organisation structure. The organisation
table is defined as:
CREATE TABLE ORGANISATION
(
ORGID DOM_INTLARGE NOT NULL,
ORG_NME DOM_VARCHARMEDIUM NOT NULL,
LEVEL_NUM DOM_INTSMALL NOT NULL,
INUSE DOM_BOOLN,
CONSTRAINT PK_ORGANISATION PRIMARY KEY (ORGID),
CONSTRAINT UN_ORGNME UNIQUE (ORG_NME, LEVEL_NUM)
);
and the closure table as:
CREATE TABLE ORGCHART
(
PARENTID DOM_INTLARGE NOT NULL,
CHILDID DOM_INTLARGE NOT NULL,
DEPTH DOM_INTSMALL NOT NULL,
CONSTRAINT PK_ORGCHART PRIMARY KEY (PARENTID, CHILDID),
CONSTRAINT FK_ORG2CHCHILD FOREIGN KEY (CHILDID) REFERENCES ORGANISATION
(ORGID),
CONSTRAINT ORG2CHPARENT FOREIGN KEY (PARENTID) REFERENCES ORGANISATION
(ORGID));
If I use the statements in the following stored procedure to move a part in
the middle of the structure (where DEPTH =2 in the ORGCHART table) I get a
PRIMARY or UNIQUE KEY constraint "PK_ORGCHART" constraint error as the delete
statement hasn't removed all the required rows.
SET TERM ^ ;
CREATE PROCEDURE MOVEORGANISATION_OLD
(
IN_ORGTOMOVE BIGINT,
IN_WHERETOMOVE BIGINT
)
AS
BEGIN
DELETE FROM orgchart
WHERE childid IN (SELECT childid
FROM OrgChart
WHERE parentid = :IN_OrgToMove)
AND parentid IN (SELECT parentid
FROM OrgChart
WHERE childid = :IN_OrgToMove
AND parentid != childid);
INSERT INTO OrgChart (ParentId, ChildId, depth)
SELECT supertree.ParentId, subtree.ChildId, subtree.DEPTH + supertree.DEPTH + 1
FROM OrgChart supertree
CROSS JOIN OrgChart subtree
WHERE supertree.ChildId = :IN_WhereToMove
AND subtree.ParentId = :IN_OrgToMove;
END
^
SET TERM ; ^
However if I first store a list of the rows to be delete from ORGCHART table
into a temporary table defined as:
CREATE TABLE TMP_ORGCHANGE
(
ORGID DOM_INTLARGE,
PARENTID DOM_INTLARGE
);
and then use it in the where clause criteria of the delete statement
everything works as expected (see revise procedure below).
SET TERM ^ ;
CREATE PROCEDURE MOVEORGANISATION
(
IN_ORGTOMOVE BIGINT,
IN_WHERETOMOVE BIGINT
)
AS
BEGIN
/*List records to be removed into temporary table */
insert into TMP_ORGCHANGE (parentid, orgid)
select parentid, childid FROM orgchart
WHERE childid IN (SELECT childid
FROM OrgChart
WHERE parentid = :IN_OrgToMove)
AND parentid IN (SELECT parentid
FROM OrgChart
WHERE childid = :IN_OrgToMove
AND parentid != childid);
/* now delete records from ORGCHART */
delete from ORGCHART
where parentid in (select parentid from TMP_ORGCHANGE)
and childid in (select orgid from TMP_ORGCHANGE);
/* create the new links */
INSERT INTO OrgChart (ParentId, ChildId, depth)
SELECT supertree.ParentId, subtree.ChildId, subtree.DEPTH + supertree.DEPTH + 1
FROM OrgChart supertree
CROSS JOIN OrgChart subtree
WHERE supertree.ChildId = :IN_WhereToMove
AND subtree.ParentId = :IN_OrgToMove;
END
^
SET TERM ; ^
Is there something that I'm doing wrong in the first procedure that prevents
all relevant records from being deleted.
Here is a sample dataset to illustrate. If I pass the values 5,2 to the first
procedure the operation fails but passing them to the second procedure it
succeeds.
INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES
('1', 'Whole Organisation', '0', NULL, NULL);
INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES
('2', 'Div1', '1', '1', '1');
INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES
('3', 'Div2', '1', '1', '1');
INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES
('4', 'Dir11', '2', '2', '1');
INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES
('5', 'Dir21', '2', '3', '1');
INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES
('6', 'Spec111', '3', '4', '1');
INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES
('7', 'Spec112', '3', '4', '1');
INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES
('8', 'Spec211', '3', '5', '1');
INSERT INTO ORGANISATION (ORGID, ORG_NME, LEVEL_NUM, PARENTID, INUSE) VALUES
('9', 'Spec212', '3', '5', '1');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('1', '1', '0');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('2', '2', '0');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('1', '2', '1');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('3', '3', '0');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('1', '3', '1');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('4', '4', '0');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('2', '4', '1');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('1', '4', '2');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('5', '5', '0');
INSERT INTO ORGCHART (PARENTID, CHILDID, DEPTH) VALUES ('3', '5', '1');
INSERT
RE: [firebird-support] reset sysdba password without loose existing users
What you'll need to do is to download the firebird embedded edition and unzip it into a folder. If you are going to try to change the password from the command prompt you'll need to copy the isql.exe file from your original installation into the folder where you unzipped the embedded version. If you are going to use a tool like flamerobin copy the fbemded.dll to its folder. I'm not sure but you may need to rename it to fbclient.dll for flamerobin to use it. If you are using a tool like IBExpert or DBWorkbench then were it ask for the client dll point it to the firebird embeded client. You'll then be able to connect to the security database and issue the commands to change the password. ---In firebird-support@yahoogroups.com, wrote : Hi, What the person meant was that if a database is opened using a firebird server process, that process looks for a valid password for the sysdba user. This is the situation you are in now. But, If you take that Database and open it in embedded mode, meaning that it isn’t using the server process. The Embedded mode only looks for the user to exist. It doesn’t care about the password. Once you connect, you can then issue the appropriate command to change the password. Thanks, Ed Mendez From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Thursday, March 23, 2017 1:05 PM To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] reset sysdba password without loose existing users Hi, Thanks for your reply, I don't understand what you meansorry As i said, i don't have sysdba passwordand i don't want to loose all existing users account Regards -Message d'origine- De : firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com] Envoyé : jeudi 23 mars 2017 17:29 À : firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com Objet : Re: [firebird-support] reset sysdba password without loose existing users 23.03.2017 17:27, startx252000@... mailto:startx252000@... [firebird-support] wrote: > Anybody can help me to reset SYSDBA without loose all existing users in Firebird Attach in embedded mode with SYSDBA name and you can change the password with ALTER USER command. -- WBR, SD. ++ Visit http://www.firebirdsql.org http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links
[firebird-support] How can I maintain the hierarchy of the data in the results returned
I have the following tables in a relationship I’ve tried to draw below. The OrgChart table is a closure table to display the hierarchy of the organisation. The Organisation is 3 Levels deep. |--| |Guidance | |_| | | m |-| |---| |Assignment | --m |Organisation| |___| - | | | | m m | || | |OrgChart| m || |--| |Review | |_| When a guidance is issued it is assigned to all levels of the organisation for review. The lowest level (level 3 tier) reviews it and this is recorded in the review table. The Level 2 tier signs off the review when all the level 3 tiers under it has reviewed the guidance and so on. What I’d like to do is to write a query (or series of queries) so I can list the last review of each level in the organisation through the hierarchy. Guidance ref_num| Date last reviewed| Dept Name 100| 5/5/16 | Dept A 100| 1/4/16 | Dept AA 100| 1/3/16 | Dept AAA 100| 5/5/16 | Dept A 100| 1/2/16 | Dept AA2 100| 3/3/16 | Dept AAA2 Etc. The idea is to later scan the table to produce a report as below Ref_num | Level1 _ Dept | level 1_Review | Level2_Dept | Level2_Review | Level3_Dept | Level3_Review 100 | Dept A| 5/5/16 |Dept AA |1/4/16 | Dept AAA |1/3/16 100 | Dept A| 5/5/16 |Dept AA2 |1/2/16| Dept AAA2|3/3/16 I'd be grateful for any help. Here is the full definition of the tables: CREATE TABLE ASSIGNMENT ( ASSID DOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */, VERID DOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */, ORGID DOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */, ASSIGNED_DTE DOM_DATE NOT NULL /* DOM_DATE = DATE */, EMPID DOM_INTLARGE /* DOM_INTLARGE = BIGINT */, STEID DOM_INTSMALL /* DOM_INTSMALL = SMALLINT */ ); CREATE TABLE GUIDANCE ( GDLIDDOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */, VERID DOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */, REF_NUM DOM_VARCHARSMALL NOT NULL /* DOM_VARCHARSMALL = VARCHAR(15) */, CAPTION DOM_VARCHARLARGE NOT NULL /* DOM_VARCHARLARGE = VARCHAR(225) */, LINK DOM_VARCHARLARGE /* DOM_VARCHARLARGE = VARCHAR(225) */, GTYCAT DOM_INTSMALL NOT NULL /* DOM_INTSMALL = SMALLINT */ ); CREATE TABLE ORGANISATION ( ORGID DOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */, ORG_NMEDOM_VARCHARMEDIUM NOT NULL /* DOM_VARCHARMEDIUM = VARCHAR(30) */, LEVEL_NUM DOM_INTSMALL NOT NULL /* DOM_INTSMALL = SMALLINT */, PARENT DOM_INTLARGE /* DOM_INTLARGE = BIGINT */, INUSE DOM_BOOL /* DOM_BOOL = CHAR(1) CHECK (value is NULL or (value between 0 and 1)) */ ); CREATE TABLE ORGCHART ( OCHID DOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */, PARENTIDDOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */, CHILDID DOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */, DEPTH DOM_INTSMALL NOT NULL /* DOM_INTSMALL = SMALLINT */, ORGANISATION_ORGID DOM_INTLARGE /* DOM_INTLARGE = BIGINT */ ); CREATE TABLE REVIEW ( REVID DOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */, ASSID DOM_INTLARGE NOT NULL /* DOM_INTLARGE = BIGINT */, REVIEW_DTE DOM_DATE NOT NULL /* DOM_DATE = DATE */, GOTID DOM_INTSMALL NOT NULL /* DOM_INTSMALL = SMALLINT */, NOTEBLOB SUB_TYPE 0 SEGMENT SIZE 80, EMPID DOM_INTLARGE /* DOM_INTLARGE = BIGINT */ );
[firebird-support] Re: query to return the last entry in detail table for each entry in master table
Thanks liviuslivius. You've taught me not to try to understand new code whilst tired. :) setysvar - Now that I'm refreshed I understand code and am very appreciative
[firebird-support] Re: query to return the last entry in detail table for each entry in master table
Hi Set Thanks for helping me with this. Your answer has been a great help. However there is a problem with the result set. In the Assignment table I have the following data: (of interest is assignid 9 which is projectid 19) ASSIGNID ASSIGN_DATE MANAGER PROJECTID 1 03/03/2016 11 2 03/03/2016 12 3 03/03/2016 13 4 03/03/2016 14 5 03/03/2016 15 6 03/03/2016 16 7 05/02/2012 17 8 05/02/2012 18 9 05/02/2012 19 10 03/03/2012 20 In the review table I have the following: (Note that assignid 9 has two entries. One with a review date of 5/2/2012 and the other with a review date of 5/5/2015) REVIEWID ASSIGNID REVIEW_DATE PROGRESS_NOTE 1 1 03/03/2016 2 2 03/03/2016 3 3 03/03/2016 4 4 03/03/2016 5 5 03/03/2016 6 6 03/03/2016 7 7 05/02/2012 8 8 05/02/2012 9 9 05/02/2012 10 1 05/02/2012 11 10 03/03/2012 12 9 05/05/2015 using your queries give me the following result: PROJECTID ASSIGN_DATE REVIEW_DATE PROGRESS_NOTE 11 03/03/2016 05/02/2012 12 03/03/2016 03/03/2016 13 03/03/2016 03/03/2016 14 03/03/2016 03/03/2016 15 03/03/2016 03/03/2016 16 03/03/2016 03/03/2016 17 05/02/2012 05/02/2012 18 05/02/2012 05/02/2012 19 05/02/2012 05/02/2012 20 03/03/2012 03/03/2012 The result set has Projectid 19 (assignid 9) with the a review date of 05/02/2012 instead of 5/5/2015. I'm not sure which bit of the query to tweak to get the expected result. Can I enlist your help again please.
[firebird-support] Re: query to return the last entry in detail table for each entry in master table
Sorry just to add that I want the query to return projectid, assign_date, review_date and Progress_note
[firebird-support] query to return the last entry in detail table for each entry in master table
I have an assignment table (master table) and a review table (detail table). The review table holds one or more records for each record in the assignment table. I need a query to return the latest review for each record in the assignment table. Could someone advise me on a query to do this. The assignment table has the following fields: assignid, assign_date, manager, Projectid The review table has the following fields: reviewid, assignid, review_date, progress_note Thanks in advance.
RE: [firebird-support] Re: invalid request BLR at offset 667 bad parameter number
Thanks Ed. Yes, Orgid is declared as a BigInt in the Assignment Table and Vorgid is declared as a Bigint in the procedure. I'm still building the procedure bit by bit so I haven't placed in what will happen when LevelCheck is not 1. I want to get what I've done so far working for valid entries before proceeding.
[firebird-support] Re: invalid request BLR at offset 667 bad parameter number
I've tracked down the problem to the variable Vorgid inside the insert into statement. This is a variable that I have declared at the beginning of the procedure and I am using a select statement to set it's value. So the question is what is the correct way to use this variable with the Insert statement.
[firebird-support] invalid request BLR at offset 667 bad parameter number
I am getting a "invalid request BLR at offset 667 bad parameter number" when I try to define the stored procedure below. The procedure first checks that the value supplied in IN_ORGID relates to a department and then finds the organisational structure for the department (Ie: it's division and subdivision) and insert these into a table called assignment. I've placed a comment where I believe the error is occurring. SET TERM ^ ; Create PROCEDURE INSERTASS ( IN_GDLID DOM_INTLARGE, IN_ORGID DOM_INTLARGE, IN_ASSDTE DOM_Date, IN_EMPID DOM_INTLARGE, IN_STEID DOM_INTSMALL ) AS Declare LevelCheck integer; Declare POrgExist smallint; Declare Vorgid BigInt; BEGIN select count(orgid) from organisation where orgid = :In_Orgid and level_num =3 into LevelCheck; if (LevelCheck =1) then begin Vorgid =0; /* get the organisational structure for in_orgid (Division, Subdivision,)*/ for select p.parentid from orgchart p, orgchart c inner join organisation pn on pn.orgid = p.parentid inner join organisation cn on cn.orgid = c.childid where p.childid=10 and c.parentid= 10 and p.depth+c.depth+1 between 2 and 3 into Vorgid do begin /* Check whether the Level 1 or 2 (Division or Subdivision) info already exist in the assignment table for the guidance and site combination. For Division and Subdivision Site is always null. Specialty can be null or include a value*/ Select count(assid)from Assignment where gdlid=:in_gdlid and orgid = :IN_ORGID into PorgExist; if (PorgExist =0) then /* <--- Error appears to occur with the insert statement below --->*/ INSERT INTO Assignment (assid, GdlId, OrgId, Assigned_dte, empid, steid) VALUES (gen_id(AssKey_Gen, 1), :IN_GDLID, :Vorgid, :IN_ASSDTE, NULL, NULL); end end else exception nowork; END ^ SET TERM ; ^ COMMIT WORK;
Re: [firebird-support] storing a sequence value in a trigger
Thanks Martijn. Your suggestion worked perfectly
[firebird-support] storing a sequence value in a trigger
I have three tables Guidance, Assignment and Review. Assignment is a detail table to Guidance and Review is a detail table to Assignment. When a new record is entered into the Guidance table I want to an after insert trigger on the Guidance table to insert default values into Assignment and Review. When the trigger inserts a new record in the assignment table how do I store the primary key for that record in the trigger so I can use it when the trigger goes on to insert a new record in the Review table. I've tried defining the trigger as below SET TERM ^ ; CREATE OR ALTER TRIGGER AI_GDLID FOR GUIDANCE ACTIVE AFTER INSERT POSITION 20 AS DECLARE MyAssId BIGINT; BEGIN MyAssId = insert into assignment(assid,GDLID, OCHID,ASSIGNED_DTE) Values (< error occurs here) (GEN_ID(AssKey_Gen,1),new.GDLID,1,CURRENT_DATE) RETURNING assid END ^ COMMIT WORK ^ SET TERM ; ^ But I get the following error:Error at line 2: Dynamic SQL ErrorSQL error code = -104Token unknown - line 6, column 13insert I've also tried defining the trigger as: SET TERM ^ ;CREATE OR ALTER TRIGGER AI_GDLID FOR GUIDANCE ACTIVE AFTER INSERT POSITION 20ASdeclare Asskey bigint;BEGIN Asskey = select GEN_ID(AssKey_Gen,1)from rdb$database; insert into assignment(assid,GDLID, OCHID,ASSIGNED_DTE) Values (<--- Error occurs here) (Asskey,new.GDLID,1,CURRENT_DATE); insert into Review(ASSID, REVIEW_DTE, GOTID, NOTE) Values (asskey, CURRENT_Date, 1, 'New Guidance Added');END ^ COMMIT WORK ^SET TERM ;^ However I then get this error message: Error at line 2: Dynamic SQL ErrorSQL error code = -104Token unknown - line 7, column 12select What is the correct way to achieve what I want to do?
