Re: [firebird-support] Connection rejected by remote interface

2017-07-17 Thread Aldo Caruso aldo.car...@argencasas.com [firebird-support] 

Hi Mark,

the version number is 2.5.2.

Unfortunately I cannot upgrade it to a more recent one because the 
OS is Ubuntu 14.04, and I have to wait until a complete migration to 
Ubuntu 16.04 until I can upgrade Firebird to a newer version.


Thanks,

Aldo Caruso


El 17/07/17 a las 12:01, Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support] escribió:


On 17-7-2017 14:08, Aldo Caruso aldo.car...@argencasas.com
[firebird-support] wrote:
> I have the same problem running Firebird super server on Linux (
> Ubuntu 14.04 ). Sadly, the only solution I found was to restart 
Firebird

> service.
>
> I wrote a bash script that attempts to connect to the database. If
> it could not establish a connection, it restarts Firebird service. This
> script is run periodically by cron service.

That would suggest the listener crashed. Which version are you using?

Mark
--
Mark Rotteveel

Re: [firebird-support] Re: How to get different Maximum Data for every single date in the table?

2017-07-17 Thread Vishal Tiwari vishuals...@yahoo.co.in [firebird-support] 
@Mark: I guess, asking for help, is never be like demanding. It is more over 
expecting cooperation according to me.
But anyways no more talk on that issue...
 

On Monday, 17 July 2017 9:32 AM, Vishal Tiwari  
wrote:
 

 Hi Mark,
Kindly don't get me rude here. I always respected everyone in this group.
Till today firebird is the only database I love a lot.
About asking for the help, it was not actually demanding.
In past, me and SET had lot of valuable conversation here.
I just know two ppl here, Ms. Helen and SET.
Believe me my friend, if I knew you, definitely I would have call you as well.
Anyways, I apologies, if i hurt you or anyone here, but that was never my 
intention.

Hope, it clarifies all misunderstandings.

With Love and Regards.
Vishal 

On Monday, 17 July 2017 8:05 AM, "Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support]"  wrote:
 

     On 17-7-2017 13:31, Vishal Tiwari vishuals...@yahoo.co.in 
[firebird-support] wrote:
> HI SET,
> 
> Please help.

I find it rather rude to expect (almost demand) someone to just help 
you, and to then ignore the fact that there are also other people here 
who would also might help you. Not to mention that you do this just an 
hour after posting your original question.

Mark
-- 
Mark Rotteveel
  #yiv2900154328 -- #yiv2900154328ygrp-mkp {border:1px solid 
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv2900154328 
#yiv2900154328ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv2900154328 
#yiv2900154328ygrp-mkp #yiv2900154328hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv2900154328 #yiv2900154328ygrp-mkp #yiv2900154328ads 
{margin-bottom:10px;}#yiv2900154328 #yiv2900154328ygrp-mkp .yiv2900154328ad 
{padding:0 0;}#yiv2900154328 #yiv2900154328ygrp-mkp .yiv2900154328ad p 
{margin:0;}#yiv2900154328 #yiv2900154328ygrp-mkp .yiv2900154328ad a 
{color:#ff;text-decoration:none;}#yiv2900154328 #yiv2900154328ygrp-sponsor 
#yiv2900154328ygrp-lc {font-family:Arial;}#yiv2900154328 
#yiv2900154328ygrp-sponsor #yiv2900154328ygrp-lc #yiv2900154328hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv2900154328 
#yiv2900154328ygrp-sponsor #yiv2900154328ygrp-lc .yiv2900154328ad 
{margin-bottom:10px;padding:0 0;}#yiv2900154328 #yiv2900154328actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv2900154328 
#yiv2900154328activity 
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv2900154328
 #yiv2900154328activity span {font-weight:700;}#yiv2900154328 
#yiv2900154328activity span:first-child 
{text-transform:uppercase;}#yiv2900154328 #yiv2900154328activity span a 
{color:#5085b6;text-decoration:none;}#yiv2900154328 #yiv2900154328activity span 
span {color:#ff7900;}#yiv2900154328 #yiv2900154328activity span 
.yiv2900154328underline {text-decoration:underline;}#yiv2900154328 
.yiv2900154328attach 
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 
0;width:400px;}#yiv2900154328 .yiv2900154328attach div a 
{text-decoration:none;}#yiv2900154328 .yiv2900154328attach img 
{border:none;padding-right:5px;}#yiv2900154328 .yiv2900154328attach label 
{display:block;margin-bottom:5px;}#yiv2900154328 .yiv2900154328attach label a 
{text-decoration:none;}#yiv2900154328 blockquote {margin:0 0 0 
4px;}#yiv2900154328 .yiv2900154328bold 
{font-family:Arial;font-size:13px;font-weight:700;}#yiv2900154328 
.yiv2900154328bold a {text-decoration:none;}#yiv2900154328 dd.yiv2900154328last 
p a {font-family:Verdana;font-weight:700;}#yiv2900154328 dd.yiv2900154328last p 
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv2900154328 
dd.yiv2900154328last p span.yiv2900154328yshortcuts 
{margin-right:0;}#yiv2900154328 div.yiv2900154328attach-table div div a 
{text-decoration:none;}#yiv2900154328 div.yiv2900154328attach-table 
{width:400px;}#yiv2900154328 div.yiv2900154328file-title a, #yiv2900154328 
div.yiv2900154328file-title a:active, #yiv2900154328 
div.yiv2900154328file-title a:hover, #yiv2900154328 div.yiv2900154328file-title 
a:visited {text-decoration:none;}#yiv2900154328 div.yiv2900154328photo-title a, 
#yiv2900154328 div.yiv2900154328photo-title a:active, #yiv2900154328 
div.yiv2900154328photo-title a:hover, #yiv2900154328 
div.yiv2900154328photo-title a:visited {text-decoration:none;}#yiv2900154328 
div#yiv2900154328ygrp-mlmsg #yiv2900154328ygrp-msg p a 
span.yiv2900154328yshortcuts 
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv2900154328 
.yiv2900154328green {color:#628c2a;}#yiv2900154328 .yiv2900154328MsoNormal 
{margin:0 0 0 0;}#yiv2900154328 o {font-size:0;}#yiv2900154328 
#yiv2900154328photos div {float:left;width:72px;}#yiv2900154328 
#yiv2900154328photos div div {border:1px solid 
#66;min-height:62px;overflow:hidden;width:62px;}#yiv2900154328 
#yiv2900154328photos div label

Re: [firebird-support] Re: How to get different Maximum Data for every single date in the table?

2017-07-17 Thread Vishal Tiwari vishuals...@yahoo.co.in [firebird-support] 
Hi Mark,
Kindly don't get me rude here. I always respected everyone in this group.
Till today firebird is the only database I love a lot.
About asking for the help, it was not actually demanding.
In past, me and SET had lot of valuable conversation here.
I just know two ppl here, Ms. Helen and SET.
Believe me my friend, if I knew you, definitely I would have call you as well.
Anyways, I apologies, if i hurt you or anyone here, but that was never my 
intention.

Hope, it clarifies all misunderstandings.

With Love and Regards.
Vishal 

On Monday, 17 July 2017 8:05 AM, "Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support]"  wrote:
 

     On 17-7-2017 13:31, Vishal Tiwari vishuals...@yahoo.co.in 
[firebird-support] wrote:
> HI SET,
> 
> Please help.

I find it rather rude to expect (almost demand) someone to just help 
you, and to then ignore the fact that there are also other people here 
who would also might help you. Not to mention that you do this just an 
hour after posting your original question.

Mark
-- 
Mark Rotteveel
  #yiv0699682194 #yiv0699682194 -- #yiv0699682194ygrp-mkp {border:1px solid 
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv0699682194 
#yiv0699682194ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv0699682194 
#yiv0699682194ygrp-mkp #yiv0699682194hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv0699682194 #yiv0699682194ygrp-mkp #yiv0699682194ads 
{margin-bottom:10px;}#yiv0699682194 #yiv0699682194ygrp-mkp .yiv0699682194ad 
{padding:0 0;}#yiv0699682194 #yiv0699682194ygrp-mkp .yiv0699682194ad p 
{margin:0;}#yiv0699682194 #yiv0699682194ygrp-mkp .yiv0699682194ad a 
{color:#ff;text-decoration:none;}#yiv0699682194 #yiv0699682194ygrp-sponsor 
#yiv0699682194ygrp-lc {font-family:Arial;}#yiv0699682194 
#yiv0699682194ygrp-sponsor #yiv0699682194ygrp-lc #yiv0699682194hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv0699682194 
#yiv0699682194ygrp-sponsor #yiv0699682194ygrp-lc .yiv0699682194ad 
{margin-bottom:10px;padding:0 0;}#yiv0699682194 #yiv0699682194actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv0699682194 
#yiv0699682194activity 
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv0699682194
 #yiv0699682194activity span {font-weight:700;}#yiv0699682194 
#yiv0699682194activity span:first-child 
{text-transform:uppercase;}#yiv0699682194 #yiv0699682194activity span a 
{color:#5085b6;text-decoration:none;}#yiv0699682194 #yiv0699682194activity span 
span {color:#ff7900;}#yiv0699682194 #yiv0699682194activity span 
.yiv0699682194underline {text-decoration:underline;}#yiv0699682194 
.yiv0699682194attach 
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 
0;width:400px;}#yiv0699682194 .yiv0699682194attach div a 
{text-decoration:none;}#yiv0699682194 .yiv0699682194attach img 
{border:none;padding-right:5px;}#yiv0699682194 .yiv0699682194attach label 
{display:block;margin-bottom:5px;}#yiv0699682194 .yiv0699682194attach label a 
{text-decoration:none;}#yiv0699682194 blockquote {margin:0 0 0 
4px;}#yiv0699682194 .yiv0699682194bold 
{font-family:Arial;font-size:13px;font-weight:700;}#yiv0699682194 
.yiv0699682194bold a {text-decoration:none;}#yiv0699682194 dd.yiv0699682194last 
p a {font-family:Verdana;font-weight:700;}#yiv0699682194 dd.yiv0699682194last p 
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv0699682194 
dd.yiv0699682194last p span.yiv0699682194yshortcuts 
{margin-right:0;}#yiv0699682194 div.yiv0699682194attach-table div div a 
{text-decoration:none;}#yiv0699682194 div.yiv0699682194attach-table 
{width:400px;}#yiv0699682194 div.yiv0699682194file-title a, #yiv0699682194 
div.yiv0699682194file-title a:active, #yiv0699682194 
div.yiv0699682194file-title a:hover, #yiv0699682194 div.yiv0699682194file-title 
a:visited {text-decoration:none;}#yiv0699682194 div.yiv0699682194photo-title a, 
#yiv0699682194 div.yiv0699682194photo-title a:active, #yiv0699682194 
div.yiv0699682194photo-title a:hover, #yiv0699682194 
div.yiv0699682194photo-title a:visited {text-decoration:none;}#yiv0699682194 
div#yiv0699682194ygrp-mlmsg #yiv0699682194ygrp-msg p a 
span.yiv0699682194yshortcuts 
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv0699682194 
.yiv0699682194green {color:#628c2a;}#yiv0699682194 .yiv0699682194MsoNormal 
{margin:0 0 0 0;}#yiv0699682194 o {font-size:0;}#yiv0699682194 
#yiv0699682194photos div {float:left;width:72px;}#yiv0699682194 
#yiv0699682194photos div div {border:1px solid 
#66;min-height:62px;overflow:hidden;width:62px;}#yiv0699682194 
#yiv0699682194photos div label 
{color:#66;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv0699682194
 #yiv0699682194reco-category {font-size:77%;}#yiv0699682194 
#yiv0699682194reco-desc {font-size:77%;}#yiv0699682194 .yiv0699682194replbq 
{margin:4px;}#yiv0699682194 #yiv0699682194ygrp-actbar div

Re: [firebird-support] Rename table

2017-07-17 Thread Alain Bastien alainbast...@gmail.com [firebird-support] 
this was the solution I proposed in  first instance.

Else what's in a name ? , if not what the table contains ?

The VCL BATCHMOVE does it fast a seemlessly in one click, if field names
are the same or

if  different then use the EMS  components !!!




2017-07-17 11:48 GMT+04:00 liviuslivius liviusliv...@poczta.onet.pl
[firebird-support] :

>
>
> Hi,
>
> This whole discussion is strange to me.
> Rename table while are pending inserts to it?
> How. If statment like rename table exsist then after name has changed all
> applications must be recompiled to take this new name into account. This
> looks to me that discussion is not about real problem. Only academic
> discusion that will be good to have this possibility in engine.
>
> If change is realy needed then i suppose simplest solusion is
> Create table with same structure and run simple command
>
> Insert into new_table
> Select * from old_table
> Commit and use it.
>
>
> Regards,
> Karol Bieniaszewsli
>
> 
>

Re: [firebird-support] Re: How to get different Maximum Data for every single date in the table?

2017-07-17 Thread Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
On 17-7-2017 13:31, Vishal Tiwari vishuals...@yahoo.co.in 
[firebird-support] wrote:
> HI SET,
> 
> Please help.

I find it rather rude to expect (almost demand) someone to just help 
you, and to then ignore the fact that there are also other people here 
who would also might help you. Not to mention that you do this just an 
hour after posting your original question.

Mark
-- 
Mark Rotteveel

Re: [firebird-support] Connection rejected by remote interface

2017-07-17 Thread Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
On 17-7-2017 14:08, Aldo Caruso aldo.car...@argencasas.com 
[firebird-support] wrote:
> I have the same problem running Firebird super server on Linux ( 
> Ubuntu 14.04 ). Sadly, the only solution I found was to restart Firebird 
> service.
> 
>  I wrote a bash script that attempts to connect to the database.  If 
> it could not establish a connection, it restarts Firebird service. This 
> script is run periodically by cron service.

That would suggest the listener crashed. Which version are you using?

Mark
-- 
Mark Rotteveel

Re: [firebird-support] Connection rejected by remote interface

2017-07-17 Thread Aldo Caruso aldo.car...@argencasas.com [firebird-support] 
   I have the same problem running Firebird super server on Linux ( 
Ubuntu 14.04 ). Sadly, the only solution I found was to restart Firebird 
service.


I wrote a bash script that attempts to connect to the database.  If 
it could not establish a connection, it restarts Firebird service. This 
script is run periodically by cron service.


Aldo Caruso


El 14/07/17 a las 10:07, 'Dieter Blume' d...@blume-edv.de 
[firebird-support] escribió:


Dear Friends,

i am a software-developer, using Delphi 2006 and Firebird 2.5.

Yesterday, I tried to migrate from Firebird 2.5 to Firebird 3.0.

Everything works fine, but only on my server. When I tried to
connect to Firebird 3.0 using a remote PC, the following error
occured:

"Connection rejected by remote interface"

I used exactly the same components on server and client.
- IBConsole
- gds32.dll (copy of fbclient.dll from 32-bit-setup)
- my own application using connect-string "server:c:\database\test.gdb"

I deactivated the Windows-Firewall on the server with no success.

Any suggestions? I would be happy about your help.
Please excuse my worse english.

Best Regards

Dieter Blume
staatl. gepr. EDV-Betriebswirt / Wirtschaftsinformatiker

Dieter Blume EDV-Organisation e.K.
33129 Delbrück - von-Galen-Straße 38
Tel: 05250 997849 - Fax: 05250 997859
Mobil: 0171 5028601 oder 0174 8434424
eMail: d...@blume-edv.de
Internet: www.blume-edv.de
USt-IdNr.: DE235021893

[firebird-support] Re: How to get different Maximum Data for every single date in the table?

2017-07-17 Thread Vishal Tiwari vishuals...@yahoo.co.in [firebird-support] 
HI SET,
Please help.
Thanks In Advance.

With Best Regards.
Vishal
 

On Monday, 17 July 2017 4:04 PM, Vishal Tiwari  
wrote:
 

 Hi All,
As per the attache files or below specified table DDL, DML and SQL, I need to 
get below details:
Get Order_ID, Order_Date, Salesman_ID for different Salesmen :1. Who sold 
maximum Copy for a day2. Who sold maximum amount of book for a day3. Who got 
maximum order for a day
As per below SQL, I am able to fetch the result only in one record that is 
correct. But I need to get the data for every single date i.e. for all distinct 
date only, i.e. Date should not repeat in the row, which satisfy above three 
condition. The result could be for different Salesmen or same Salesman 
depending on the data in the table.
So, I need to get data for every distinct date in the table. Please help how to 
get it.
One option I see to put "Order_ID" column in Max() function to avoid Group By 
issue.

Kindly suggest, If possible please correct the above SQL and share.

Thanks In Advance.

With Best Regards.
Vishal

DDL:
CREATE TABLE ORDERED_BOOK_DETAILS(  ORDER_ID Varchar(50) NOT NULL,  ORDER_DATE 
Date NOT NULL,  SALESMAN_ID Varchar(10) NOT NULL,  BOOK_NAME Varchar(50) NOT 
NULL,  BOOK_PRICE Numeric(15,2) DEFAULT 0,  COPY_SOLD Integer DEFAULT 0,  
TOTAL_AMOUNT Numeric(15,2) DEFAULT 0,  PRIMARY KEY 
(ORDER_ID,ORDER_DATE,SALESMAN_ID,BOOK_NAME));

DML:
INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME, 
BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD001', '01.01.2010', 'S001', 
'The Immortals of Meluha', '250.00', '50', '12500.00');INSERT INTO 
ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME, BOOK_PRICE, 
COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD001', '01.01.2010', 'S001', 'The Secret of 
The Nagas', '300.00', '50', '15000.00');INSERT INTO ORDERED_BOOK_DETAILS 
(ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, 
TOTAL_AMOUNT) VALUES ('ORD001', '01.01.2010', 'S001', 'Oath of The Vayuputras', 
'350.00', '50', '17500.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, 
ORDER_DATE, SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES 
('ORD002', '01.01.2010', 'S002', 'The Magic of Thinking Big', '400.00', '400', 
'16.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, 
SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD003', 
'01.01.2010', 'S003', 'The Immortals of Meluha', '250.00', '10', 
'2500.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, 
BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD004', '01.01.2010', 
'S003', 'The Secret of The Nagas', '300.00', '10', '3000.00');INSERT INTO 
ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME, BOOK_PRICE, 
COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD005', '01.01.2010', 'S003', 'Oath of The 
Vayuputras', '350.00', '10', '3500.00');INSERT INTO ORDERED_BOOK_DETAILS 
(ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, 
TOTAL_AMOUNT) VALUES ('ORD006', '01.01.2010', 'S003', 'The Magic of Thinking 
Big', '400.00', '10', '4000.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, 
ORDER_DATE, SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES 
('ORD007', '02.01.2010', 'S001', 'The Immortals of Meluha', '250.00', '155', 
'38750.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, 
SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD007', 
'02.01.2010', 'S001', 'The Secret of The Nagas', '300.00', '150', 
'45000.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, 
SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD007', 
'02.01.2010', 'S001', 'Oath of The Vayuputras', '350.00', '150', 
'52150.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, 
SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD008', 
'02.01.2010', 'S002', 'The Magic of Thinking Big', '400.00', '100', 
'4.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, 
SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD009', 
'02.01.2010', 'S003', 'The Immortals of Meluha', '250.00', '100', 
'25000.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, 
SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD0010', 
'02.01.2010', 'S003', 'The Secret of The Nagas', '300.00', '10', 
'3000.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, 
BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD0011', 
'02.01.2010', 'S003', 'Oath of The Vayuputras', '350.00', '10', 
'3500.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, 
BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD0012', 
'02.01.2010', 'S003', 'The Magic of Thinking Big', '400.00', '10', '4000.00');

SQL:
Get Order_ID, Order_Date, Salesman_ID for different Salesmen

[firebird-support] How to get different Maximum Data for every single date in the table?

2017-07-17 Thread Vishal Tiwari vishuals...@yahoo.co.in [firebird-support] 
Hi All,
As per the attache files or below specified table DDL, DML and SQL, I need to 
get below details:
Get Order_ID, Order_Date, Salesman_ID for different Salesmen :1. Who sold 
maximum Copy for a day2. Who sold maximum amount of book for a day3. Who got 
maximum order for a day
As per below SQL, I am able to fetch the result only in one record that is 
correct. But I need to get the data for every single date i.e. for all distinct 
date only, i.e. Date should not repeat in the row, which satisfy above three 
condition. The result could be for different Salesmen or same Salesman 
depending on the data in the table.
So, I need to get data for every distinct date in the table. Please help how to 
get it.
One option I see to put "Order_ID" column in Max() function to avoid Group By 
issue.

Kindly suggest, If possible please correct the above SQL and share.

Thanks In Advance.

With Best Regards.
Vishal

DDL:
CREATE TABLE ORDERED_BOOK_DETAILS(  ORDER_ID Varchar(50) NOT NULL,  ORDER_DATE 
Date NOT NULL,  SALESMAN_ID Varchar(10) NOT NULL,  BOOK_NAME Varchar(50) NOT 
NULL,  BOOK_PRICE Numeric(15,2) DEFAULT 0,  COPY_SOLD Integer DEFAULT 0,  
TOTAL_AMOUNT Numeric(15,2) DEFAULT 0,  PRIMARY KEY 
(ORDER_ID,ORDER_DATE,SALESMAN_ID,BOOK_NAME));

DML:
INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME, 
BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD001', '01.01.2010', 'S001', 
'The Immortals of Meluha', '250.00', '50', '12500.00');INSERT INTO 
ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME, BOOK_PRICE, 
COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD001', '01.01.2010', 'S001', 'The Secret of 
The Nagas', '300.00', '50', '15000.00');INSERT INTO ORDERED_BOOK_DETAILS 
(ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, 
TOTAL_AMOUNT) VALUES ('ORD001', '01.01.2010', 'S001', 'Oath of The Vayuputras', 
'350.00', '50', '17500.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, 
ORDER_DATE, SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES 
('ORD002', '01.01.2010', 'S002', 'The Magic of Thinking Big', '400.00', '400', 
'16.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, 
SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD003', 
'01.01.2010', 'S003', 'The Immortals of Meluha', '250.00', '10', 
'2500.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, 
BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD004', '01.01.2010', 
'S003', 'The Secret of The Nagas', '300.00', '10', '3000.00');INSERT INTO 
ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME, BOOK_PRICE, 
COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD005', '01.01.2010', 'S003', 'Oath of The 
Vayuputras', '350.00', '10', '3500.00');INSERT INTO ORDERED_BOOK_DETAILS 
(ORDER_ID, ORDER_DATE, SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, 
TOTAL_AMOUNT) VALUES ('ORD006', '01.01.2010', 'S003', 'The Magic of Thinking 
Big', '400.00', '10', '4000.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, 
ORDER_DATE, SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES 
('ORD007', '02.01.2010', 'S001', 'The Immortals of Meluha', '250.00', '155', 
'38750.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, 
SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD007', 
'02.01.2010', 'S001', 'The Secret of The Nagas', '300.00', '150', 
'45000.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, 
SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD007', 
'02.01.2010', 'S001', 'Oath of The Vayuputras', '350.00', '150', 
'52150.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, 
SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD008', 
'02.01.2010', 'S002', 'The Magic of Thinking Big', '400.00', '100', 
'4.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, 
SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD009', 
'02.01.2010', 'S003', 'The Immortals of Meluha', '250.00', '100', 
'25000.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, 
SALESMAN_ID, BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD0010', 
'02.01.2010', 'S003', 'The Secret of The Nagas', '300.00', '10', 
'3000.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, 
BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD0011', 
'02.01.2010', 'S003', 'Oath of The Vayuputras', '350.00', '10', 
'3500.00');INSERT INTO ORDERED_BOOK_DETAILS (ORDER_ID, ORDER_DATE, SALESMAN_ID, 
BOOK_NAME, BOOK_PRICE, COPY_SOLD, TOTAL_AMOUNT) VALUES ('ORD0012', 
'02.01.2010', 'S003', 'The Magic of Thinking Big', '400.00', '10', '4000.00');

SQL:
Get Order_ID, Order_Date, Salesman_ID for different Salesmen :1. Who sold 
maximim Copy for a day2. Who sold maximum amount of book for a day3. Who got 
maximum order for a day

With Max_Copy (Order_ID, Order_Date,

Re: [firebird-support] Doubt (Problem) In Writing SQL

2017-07-17 Thread Vishal Tiwari vishuals...@yahoo.co.in [firebird-support] 
Hi SET,
Thank You for the reply. Not an issue, I could understand.
Before I execute this SQL, would like to learn something pretty interesting 
logic in the SQL you provided.
I got the intention of using Exists predicate but I couldn't understand the 
logic behind the below condition, would you please help me in understanding?
AND ( BSD.BOOK_NO    < BSD2.BOOK_NO
   OR ( BSD.BOOK_NO  = BSD2.BOOK_NO
    AND BSD.BOOK_ID  < BSD2.BOOK_ID ) ) )

 

Thanking You Master Trouble Shooter And With Best Regards.
Vishal

On Saturday, 8 July 2017 10:59 PM, "setysvar setys...@gmail.com 
[firebird-support]"  wrote:
 

  Den 05.07.2017 20:15, skrev Vishal Tiwari vishuals...@yahoo.co.in 
[firebird-support]:
  
 Hi SET,  
  Just for you my Friend... Please check... 
  CREATE TABLE BOOK_SELLING_DETAIL (   BOOK_NO Integer,   BOOK_ID Integer,   
PUBLISHER Varchar(50),   MAIN_BRACH Varchar(10),   PUBLISH_DATE Date,   
ORIGINAL_PRICE Float,   SELLING_PRICE Float ); 
  
  INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, PUBLISHER, MAIN_BRACH, 
PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") VALUES ('5', '222', 
'Willy', 'Branch1', '25.01.2010', '550.00', '500.00', 
'0086:0001'); INSERT INTO BOOK_SELLING_DETAIL (BOOK_NO, BOOK_ID, 
PUBLISHER, MAIN_BRACH, PUBLISH_DATE, ORIGINAL_PRICE, SELLING_PRICE, "DB_KEY") 
VALUES ('5', '333', 'Willy', 'Branch1', '25.01.2010', '450.00', 
'400.00', '0086:0002'); ... 
   For every Publisher, for its same Branch and for the same date find the 
Maximum Book_No value, if there are multiple records of the same maximum 
Book_No found then  find the Maximum Book_ID value for that maximum Book_No and 
get all the details of that Book_ID row.  
  SQL: 
  SELECT BSD.BOOK_NO, BSD.BOOK_ID, BSD.PUBLISHER, BSD.MAIN_BRACH, 
BSD.PUBLISH_DATE, BSD.ORIGINAL_PRICE, BSD.SELLING_PRICE FROM 
BOOK_SELLING_DETAIL BSD Inner Join  (     Select BSD1.BOOK_NO, 
Max(BSD1.BOOK_ID) As BOOK_ID, BSD1.PUBLISHER, BSD1.MAIN_BRACH, 
BSD1.PUBLISH_DATE from BOOK_SELLING_DETAIL BSD1     Inner join     (         
Select Max(BOOK_NO) As BOOK_NO, PUBLISHER, MAIN_BRACH, PUBLISH_DATE from 
BOOK_SELLING_DETAIL         Group By PUBLISHER, MAIN_BRACH, PUBLISH_DATE     ) 
BSD2     ON      BSD1.PUBLISHER = BSD2.PUBLISHER And BSD1.MAIN_BRACH = 
BSD2.MAIN_BRACH And BSD1.PUBLISH_DATE = BSD2.PUBLISH_DATE And BSD1.BOOK_NO = 
BSD2.BOOK_NO           Group By BSD1.PUBLISHER, BSD1.MAIN_BRACH, 
BSD1.PUBLISH_DATE, BSD1.BOOK_NO   ) BSD3 ON  BSD.BOOK_NO = BSD3.BOOK_NO And 
BSD.BOOK_ID = BSD3.BOOK_ID And BSD.PUBLISHER = BSD3.PUBLISHER And 
BSD.MAIN_BRACH = BSD3.MAIN_BRACH And BSD.PUBLISH_DATE = BSD3.PUBLISH_DATE  
Order By BSD.PUBLISHER, BSD.MAIN_BRACH, BSD.PUBLISH_DATE, BSD.BOOK_NO, 
BSD.BOOK_ID
| 
  |

   
 Hi Vishal!
 
 Thanks for the DDL and example values you provided. I didn't have the time and 
energy required to look at this before today, and quite frankly, I haven't 
looked carefully at your SQL. Though I wonder if your question can be rephrased:
 
 For each PUBLISHER, MAIN_BRACH and PUBLISH_DATE, find the record with the 
highest value for BOOK_NO. In case of duplicates, choose the one with the 
highest BOOK_ID.
 
 If so, try this (simpler) SQL:
 
 SELECT BSD.BOOK_NO, BSD.BOOK_ID, BSD.PUBLISHER, BSD.MAIN_BRACH, 
BSD.PUBLISH_DATE, BSD.ORIGINAL_PRICE, BSD.SELLING_PRICE
 FROM BOOK_SELLING_DETAIL BSD
 WHERE NOT EXISTS( SELECT *
   FROM BOOK_SELLING_DETAIL BSD2
   WHERE BSD.PUBLISHER    = BSD2.PUBLISHER
     AND BSD.MAIN_BRACH   = BSD2.MAIN_BRACH
     AND BSD.PUBLISH_DATE = BSD2.PUBLISH_DATE
     AND ( BSD.BOOK_NO    < BSD2.BOOK_NO
    OR ( BSD.BOOK_NO  = BSD2.BOOK_NO
     AND BSD.BOOK_ID  < BSD2.BOOK_ID ) ) )
 
 HTH,
 Set
  #yiv9611900168 #yiv9611900168 -- #yiv9611900168ygrp-mkp {border:1px solid 
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9611900168 
#yiv9611900168ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9611900168 
#yiv9611900168ygrp-mkp #yiv9611900168hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv9611900168 #yiv9611900168ygrp-mkp #yiv9611900168ads 
{margin-bottom:10px;}#yiv9611900168 #yiv9611900168ygrp-mkp .yiv9611900168ad 
{padding:0 0;}#yiv9611900168 #yiv9611900168ygrp-mkp .yiv9611900168ad p 
{margin:0;}#yiv9611900168 #yiv9611900168ygrp-mkp .yiv9611900168ad a 
{color:#ff;text-decoration:none;}#yiv9611900168 #yiv9611900168ygrp-sponsor 
#yiv9611900168ygrp-lc {font-family:Arial;}#yiv9611900168 
#yiv9611900168ygrp-sponsor #yiv9611900168ygrp-lc #yiv9611900168hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9611900168 
#yiv9611900168ygrp-sponsor #yiv9611900168ygrp-lc .yiv9611900168ad 
{margin-bottom:10px;padding:0 0;}#yiv9611900168 #yiv9611900168actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9611900168

Re: [firebird-support] Rename table

2017-07-17 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
Hi,
This whole discussion is strange to me.Rename table while are pending inserts 
to it?How. If statment like rename table exsist then after name has changed all 
applications must be recompiled to take this new name into account. This looks 
to me that discussion is not about real problem. Only academic discusion that 
will be good to have this possibility in engine. 
If change is realy needed then i suppose simplest solusion isCreate table with 
same structure and run simple command
Insert into new_tableSelect * from old_tableCommit and use it.

Regards,Karol Bieniaszewsli
null