RE: [firebird-support] Grouping SQL counts
FB 1.5x I have a SQL statement that returns the results I want--giving me a count on the detail dataset (ClientRegHistList is a detail list on ClientRegHis, more info below) select C.RegDate, Count(Client_ID) from ClientRegHist C, ClientReghistList CL where (CL.ClientRegHist_ID = C.ClientRegHist_ID) and CL.RegType = 0 and (C.Company_ID = 128) group by 1; I have a second SQL that is exactly the same except changing the 0 to a 1 in line 5. I would like to combine the two datasets into o ne result set--any hints on how to do this? Thank you, Ed Dressel Huan is right in that your problem description is ambiguous, Ed, there are several possible answers to your question. However, I think there are a limited number of possible ways to read your question, so below are a couple of possibilities that may or may not work. My memory is poor, I haven't used Fb 1.5 for one or two years and I'm never use COUNT(fieldname) myself (I always use COUNT(*) or COUNT(DISTINCT fieldname), so no guarantees that all four of them will work. Even though not strictly speaking required, I also changed from SQL-89 to SQL-92 (explicitly using JOIN rather than joining in the WHERE clause). a) Getting the sum of 0 and 1: select C.RegDate, Count(Client_ID) from ClientRegHist C join ClientReghistList CL on CL.ClientRegHist_ID = C.ClientRegHist_ID where CL.RegType in (0, 1) and C.Company_ID = 128 group by 1; b) Counting each separately: select C.RegDate, CL.RegType, Count(Client_ID) from ClientRegHist C join ClientReghistList CL on CL.ClientRegHist_ID = C.ClientRegHist_ID where CL.RegType in (0, 1) and C.Company_ID = 128 group by 1, 2; c) Having all the lines of both queries in the same result set (you may not be able to separate them): select C.RegDate, Count(Client_ID) from ClientRegHist C join ClientReghistList CL on CL.ClientRegHist_ID = C.ClientRegHist_ID where CL.RegType = 0 and C.Company_ID = 128 group by 1 union all select C.RegDate, Count(Client_ID) from ClientRegHist C join ClientReghistList CL on CL.ClientRegHist_ID = C.ClientRegHist_ID where CL.RegType = 1 and C.Company_ID = 128 group by 1; d) Having one column for each in the result set: select C.RegDate, Count(case when CL.RegType = 0 then Client_ID else null end) as RegType0, Count(case when CL.RegType = 1 then Client_ID else null end) as RegType1, from ClientRegHist C join ClientReghistList CL on CL.ClientRegHist_ID = C.ClientRegHist_ID where CL.RegType in (0, 1) and C.Company_ID = 128 group by 1; Do any of these four answer your question? HTH, Set
[firebird-support] Re: FB 2.5.3 32bit and Db size limits test
Alex, what is limit of 2.083.888 kb ? Is it size of database file on disk ? Is it memory usage by Firebird process (what exact memory counter) ? Something else ? How do you insert blobs into database ? Regards, Vlad ---In firebird-support@yahoogroups.com, af_123xy@... wrote : Hi all i am new user of FB and i am making a tests to check a one db size limits to see if i can choose FB to support a new application. i will need to acquire in the DB only blob types to save images, jpg, pdf, tiff , etc of different dimension so i made a small application test on a W7/64 bit and FB 32bit and after reaching almost 13.000 images and reached a limit of 2.083.888 kb i've go an out of memory message inserting one more Blob record i were reading on the official website that on some Platform the size limit is some terabytes a part some limit imposed by filesystem do i should use FB 64 bit version for handle it or i am doing something wrong in the configuration? thank you for any suggestions Alex
Re: [firebird-support] FB 2.5.3 32bit and Db size limits test
Re: [firebird-support] FB 2.5.3 32bit and Db size limits test
I have a database of 70Gb, more than 70% images, maybe you have a problem in your system as said before. On 11/12/2014 09:56, Daniel Rail dan...@accra.ca [firebird-support] wrote: Hi, What version of Firebird(Classic, SuperClassic, SuperServer or Embedded)? What is the page cache/buffers(run GSTAT -H on the database)? Is it your application that is returning the our of memory error? Because for a 32-bit application, there is a 2GB memory limit, and if all those 13,000 images are loaded in memory within your application, that is most likely the problem. And, a 64-bit application doesn't have that limitation. Also, NTFS is a 64-bit filesystem(even for 32-bit applications), so the 2GB limit doesn't exist, it's above the TeraByte for a single file(but still depends on the size limit of the drive). Also, we do have customers that have databases that are over 20GB, with approximately 75% of the data being images. And, that was with Firebird 1.5 32-bit on NTFS, now they are all on Firebird 2.5.3 64-bit. -- Best regards, Daniel Rail Senior Software Developer ACCRA Solutions Inc. (www.accra.ca http://www.accra.ca) ACCRA Med Software Inc. (www.filopto.com http://www.filopto.com) At December 11, 2014, 3:58 AM, af_12...@yahoo.com mailto:af_12...@yahoo.com [firebird-support] wrote: on Win764 bit , it's a NTFS anyway i have to use FB 64 bit to get more then 2gb DB
RE: [firebird-support] Grouping SQL counts
Set Huan: Sorry for the lack of clarity--it was clear when I wrote it but when I read it now, I understand the lack of clarity. I wanted two columns count columns--Set, your answer d is perfect. Thank you both for taking the time--and Set for taking the time. Ed Dressel
[firebird-support] Re: FB 2.5.3 32bit and Db size limits test
Off topic, what is the benefit of storing the images actually in the database?
Re: [firebird-support] FB 2.5.3 32bit and Db size limits test
it is th size of DB
Re: [firebird-support] FB 2.5.3 32bit and Db size limits test
I had to use FB 64 instead of 32 bit version
Re: [firebird-support] FB 2.5.3 32bit and Db size limits test
on what filesystem it is installed and wich version of FB and operating system?
