Re: [firebird-support] Creating a conditional "order by" statement
Thanks for your reply. I haven't tried it as I came across Tomasz's suggestion first, and that works a treat!
Re: [firebird-support] Creating a conditional "order by" statement
Brilliant! Thanks for your help. I learn something new every day.
Re: [firebird-support] Creating a conditional "order by" statement
Tuesday, October 24, 2017, 1:37:01 AM, j...@acousticdesign.co.uk wrote: > I have a stored procedure along the lines of > SELECT ID, Amount1, Amount2 .. > FROM Table1. > UNION > SELECT ID, Amount1, Amount2... > FROM Table2 > ORDER BY 1 > Is it possible to construct a conditional ORDER By clause that > orders by Amount1 under certain conditions, and order by Amount2 under other > conditions? Using DSQL, no, since direct DSQL statements are compiled in the BLR code of the stored procedure. You need to avail yourself of EXECUTE STATEMENT. It's the sort of thing that this command is for. > At the moment, the only way around it I have found (and I spent > most of the night trying!) is to use an If clause and duplicate nearly all > the SQL > IF (Condition1) THEN > SELECT ID, Amount1, Amount2 .. > FROM Table1. > UNION > SELECT ID, Amount1, Amount2... > FROM Table2 > ORDER BY 1 > ELSE IF (Condition2) THEN > SELECT ID, Amount1, Amount2 .. > FROM Table1. > UNION > SELECT ID, Amount1, Amount2... > FROM Table2 > ORDER BY 2 > This seems a crazy way of having to achieve my aim! Yep. > Any help gratefully received. create procedure my proc (orderingcolumn char /* pass '2' or '3' depending on user input */ ) returns ( id bigint, amount1 decimal, etc. [columns you want in the left-to-right order you want] ) as declare variable execstring varchar (1000); /* whatever max. length you need */ begin execstring = 'SELECT ID, Amount1, Amount2 ..' || ' FROM Table1' || ' UNION' || ' SELECT ID, Amount1, Amount2...' || ' FROM Table2' || ' ORDER BY ' || :orderingcolumm ; FOR execute statement execstring into :ID, :AMOUNT1, etc. [your output variables] DO SUSPEND; end Looking at this problem from another angle...your examples are just examples, of course...but on the surface it looks as if you would better serve your need by using a DSQL statement directly, rather than trying to bury it in a SP. Or you could define a view, which would be unordered, and supply the order by clause when selecting from it. Helen
Re: [firebird-support] Creating a conditional "order by" statement
On 23.10.2017 at 14:37, j...@acousticdesign.co.uk [firebird-support] wrote: > I have a stored procedure along the lines of > SELECT ID, Amount1, Amount2 .. > FROM Table1. > UNION > SELECT ID, Amount1, Amount2... > FROM Table2 > ORDER BY 1 > Is it possible to construct a conditional ORDER By clause that orders by > Amount1 under certain conditions, and order by Amount2 under other conditions? > Try something like this: select iif(your condition, Amount1, Amount2) as SORTCOL, ID, Amount1, Amount2, ... from Table1 union select iif(your condition, Amount1, Amount2) as SORTCOL, ID, Amount1, Amount2, ... from Table2 order by 1 If there are more than two possible candidates for the ordering column, you can switch from iif to case - when - end. have a good one Tomasz -- __--==--__ __--== Tomasz Tyrakowski==--__ __--==SOL-SYSTEM==--__ __--== http://www.sol-system.pl ==--__ __--==--__
RE: [firebird-support] Firing event when deleting in MON$ATTACHMENTS
I am sorry and I am confused. I registered event with 'DISCONNECT_USER' instead of 'DISCONNECT_USER]' I forgot the bracket and now it works for the 1st scenario and not for the second because I was wrong with using 'DISCONNECT_USER]'||:USR in the PS and certaintly doesn't work but It give me an idea. I will try from the client to loop through all users existing in the DB and register them with 'DISCONNECT_USER]'+Users[i] I don't know if I can register more than hundred users in the client in case the number of users will increase in the futur. Thanks.
RE: [firebird-support] Firing event when deleting in MON$ATTACHMENTS
> 2-POST_EVENT 'DISCONNECT_USER]'||:USR; (I splitt the event name using > "]" in the client in order to retrieve the event name part and the username > part) It is not fired What event is your application registered/listening for? 'DISCONNECT_USER]', or 'DISCONNECT_USER]'||:USR? Sean
Re: [firebird-support] Firing event when deleting in MON$ATTACHMENTS
Hi, > Yes in my delphi application I registered the event name > I tried POST_EVENT in a stored procedure but without DELETE FROM > MON$ATTACHMENTS statement > sometimes it is fired sometimes not. I didn't understand why > > > When I write the event name like this : > > 1-POST_EVENT 'DISCONNECT_USER'; > It is fired > > 2-POST_EVENT 'DISCONNECT_USER]'||:USR; (I splitt the event name using > "]" in the client in order to retrieve the event name part and the > username part) > It is not fired > > And when I back to 1 when it worked it is not fired at all Did you commit the transaction executing POST_EVENT in all scenarios? Events are delivered to the listening client upon transaction commit. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Firing event when deleting in MON$ATTACHMENTS
Hello, Yes in my delphi application I registered the event name I tried POST_EVENT in a stored procedure but without DELETE FROM MON$ATTACHMENTS statement sometimes it is fired sometimes not. I didn't understand why When I write the event name like this : 1-POST_EVENT 'DISCONNECT_USER'; It is fired 2-POST_EVENT 'DISCONNECT_USER]'||:USR; (I splitt the event name using "]" in the client in order to retrieve the event name part and the username part) It is not fired And when I back to 1 when it worked it is not fired at all Anyway I kept that idea of running POST_EVENT in SP and after waiting I execute the delete statement. I consider that my problem is solved Thank you very much to all
Re: [firebird-support] Firing event when deleting in MON$ATTACHMENTS
> Hello, > > I am deleting user connected with: > DELETE FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = :ID_CONNECTION; > > I need to use POST_EVENT in order to notify user that he has been disconnected > but don't found a trigger for MON$ATTACHMENTS to do it. Can you please help > me > ? > Thanks As Sean has mentioned, when you "delete" the attachment, your "communication channel" back to the user is basically gone. What you could do is to: * Run a stored procedure with the POST_EVENT * Wait X minutes * Execute the DELETE FROM MON$ATTACHMENTS statement The main problem here is that your "administration connection" need to post an event isolated to the targeted user, e.g. by including something unique for the user in the event name and the client application has proper events registered. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
[firebird-support] Re: Firing event when deleting in MON$ATTACHMENTS
Hello There is BEFORE_DISCONNECT trigger - try it. You cannot (as I think) stop disconnection - but you can reconnect in your application Tom
