BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Is possible do that?          BorlandTalk.com Forum Index -> Delphi Databases (Desktop) View previous topic :: View next topic   Author Message Sebastian Gautier Guest Posted: Tue Jan 06, 2004 1:16 pm    Post subject: Is possible do that? Hi I'm trying to calculate the total stock by product and country in my tables, suggestions would be appreciated; I have 2 tables with this structure: Tbl1 Stock, Country, Product_Name Tbl2 Country, Cod_Country I need do the following with an sql A result is qty,product_name for ctry Product_name Ctry 1, Ctry 2, Ctry 3 A 1500 3000 B 200 500 C 2000 100 Thanks in advance Sebastian Gautier Back to top Sebastian Gautier Guest Posted: Tue Jan 06, 2004 2:59 pm    Post subject: Re: Is possible do that? No, the countries are 1.. n (may be 50) If i hand write a code for every country, the Performance is poor with this configuration? the Tbl1 may have 25000 records (historicaly 20.000 average) tbl2 50 records (average) My Network Conf is 3 Pc's PIII 800 Mhz server is a Celeron 1.1 Mhz 256 Ram OS Server 2000 I'm using Dbase IV tables Thanks. "Wayne Niddery [TeamB]" <wniddery (AT) chaffaci (DOT) on.ca> escribió en el mensaje news:3ffae037$1 (AT) newsgroups (DOT) borland.com... Quote: Sebastian Gautier wrote: I'm trying to calculate the total stock by product and country in my tables, suggestions would be appreciated; I have 2 tables with this structure: Tbl1 Stock, Country, Product_Name Tbl2 Country, Cod_Country I need do the following with an sql A result is qty,product_name for ctry Product_name Ctry 1, Ctry 2, Ctry 3 A 1500 3000 B 200 500 C 2000 100 Is there exactly 3 countries or could there be any number of countries? SQL cannot handle a dynamic number of columns well. As long as the number of countries never changes then you can do: select tbl1.product_name, (select sum(t1.stock) from tbl1 t1 where t1.country = 'abc' and t1.product_name = tbl1.product_name), (select sum(t2.stock) from tbl1 t2 where t2.country = 'def' and t2.product_name = tbl1.product_name), (select sum(t3.stock) from tbl1 t3 where t3.country = 'ghi' and t3.product_name = tbl1.product_name) from tbl1 If there are a lot of countries this gets tedious and may have a performance problem, as well as you have to format the SQL to specify every country (and I'm not even including the needed join on Tbl2). If you can accept having a separate row for each country/product combination then the SQL is *much* simplified and will be faster, you can assemble into multiple countries per row in your application for display: select t1.product_name, t2.country, sum(t1.stock) from t1 join t2 on t2.cod_country = t1.country group by t1.product_name, t2.country This even includes the join on tbl2. -- Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com) RADBooks: http://www.logicfundamentals.com/RADBooks.html "It is error alone which needs the support of government. Truth can stand by itself." - Thomas Jefferson Back to top Rick Roen Guest Posted: Tue Jan 06, 2004 3:15 pm    Post subject: Re: Is possible do that? I am unclear which field contains the quantity? Also not sure what Cod_Country is? In general you would use a "Sum" and "Group By" and "Union" clause to do this type of thing: Select <Item number>, <Product name>, Sum( Quantity ) Country0, Cast( 0 as Numeric( 6,0 ) ) Country1, Cast( 0 as Numeric( 6,0 )) Country2 from Table where Country = 'Counrty 0 name' Group by Item number, Product name UNION ALL Select <Item number>, <Product name>, Cast( 0 as Numeric( 6,0 ) ) Country0, Sum( Quantity ) Country1, Cast( 0 as Numeric( 6,0 )) Country2 from Table where Country = 'Counrty 0 name' Group by Item number, Product name UNION ALL Select <Item number>, <Product name>, Cast( 0 as Numeric( 6,0 ) ) Country0, Cast( 0 as Numeric( 6,0 )) Country1, Sum( Quantity ) Country2 from Table where Country = 'Counrty 0 name' Group by Item number, Product name These are three queries that are combined with the Union clause. The CAST columns create a field to act as a place holder where there will be no value. You could also do a single query with a group by Country, but the results would be in row form, i.e. not with three result columns: A, Country0, Total A, Country1, Total A, Country2, Total B, Country0, Total B, Country1, Total Rick "Sebastian Gautier" <sgautier (AT) terra (DOT) cl> wrote Quote: Hi I'm trying to calculate the total stock by product and country in my tables, suggestions would be appreciated; I have 2 tables with this structure: Tbl1 Stock, Country, Product_Name Tbl2 Country, Cod_Country I need do the following with an sql A result is qty,product_name for ctry Product_name Ctry 1, Ctry 2, Ctry 3 A 1500 3000 B 200 500 C 2000 100 Thanks in advance Sebastian Gautier Back to top Wayne Niddery [TeamB] Guest Posted: Tue Jan 06, 2004 4:19 pm    Post subject: Re: Is possible do that? Sebastian Gautier wrote: Quote: I'm trying to calculate the total stock by product and country in my tables, suggestions would be appreciated; I have 2 tables with this structure: Tbl1 Stock, Country, Product_Name Tbl2 Country, Cod_Country I need do the following with an sql A result is qty,product_name for ctry Product_name Ctry 1, Ctry 2, Ctry 3 A 1500 3000 B 200 500 C 2000 100 Is there exactly 3 countries or could there be any number of countries? SQL cannot handle a dynamic number of columns well. As long as the number of countries never changes then you can do: select tbl1.product_name, (select sum(t1.stock) from tbl1 t1 where t1.country = 'abc' and t1.product_name = tbl1.product_name), (select sum(t2.stock) from tbl1 t2 where t2.country = 'def' and t2.product_name = tbl1.product_name), (select sum(t3.stock) from tbl1 t3 where t3.country = 'ghi' and t3.product_name = tbl1.product_name) from tbl1 If there are a lot of countries this gets tedious and may have a performance problem, as well as you have to format the SQL to specify every country (and I'm not even including the needed join on Tbl2). If you can accept having a separate row for each country/product combination then the SQL is *much* simplified and will be faster, you can assemble into multiple countries per row in your application for display: select t1.product_name, t2.country, sum(t1.stock) from t1 join t2 on t2.cod_country = t1.country group by t1.product_name, t2.country This even includes the join on tbl2. -- Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com) RADBooks: http://www.logicfundamentals.com/RADBooks.html "It is error alone which needs the support of government. Truth can stand by itself." - Thomas Jefferson Back to top Tony J Hopkinson Guest Posted: Tue Jan 06, 2004 7:27 pm    Post subject: Re: Is possible do that? On Tue, 6 Jan 2004 10:16:52 -0300, "Sebastian Gautier" <sgautier (AT) terra (DOT) cl> wrote: Quote: Hi I'm trying to calculate the total stock by product and country in my tables, suggestions would be appreciated; I have 2 tables with this structure: Tbl1 Stock, Country, Product_Name Tbl2 Country, Cod_Country I need do the following with an sql A result is qty,product_name for ctry Product_name Ctry 1, Ctry 2, Ctry 3 A 1500 3000 B 200 500 C 2000 100 Thanks in advance Sebastian Gautier If there can be a variable number of countries, then you need to use a db that's supports crosstab. Access does for instance. If not then you can get Product_Name Country Qty (cod_country ???) A Ctry1 1500 A Ctry2 3000 B Ctry1 200 fairly easily Then reorganize internally in a dynamic array or just straight into a grid if you are not going to anything with it except display. Number of rows = Number of distinct product_names and number of cols noumber of distict countries + 1 for the product_name column. Back to top Sebastian Gautier Guest Posted: Wed Jan 07, 2004 1:57 pm    Post subject: Re: Is possible do that? Hi experts, I have found the form to show the data that I need (using decision cube), but simultaneously need to print them. Exists the way to export them? thanks in advance Sebastian Gautier Quote: Hi I'm trying to calculate the total stock by product and country in my tables, suggestions would be appreciated; I have 2 tables with this structure: Tbl1 Stock, Country, Product_Name Tbl2 Country, Cod_Country I need do the following with an sql A result is qty,product_name for ctry Product_name Ctry 1, Ctry 2, Ctry 3 A 1500 3000 B 200 500 C 2000 100 Thanks in advance Sebastian Gautier Back to top Mike Shkolnik Guest Posted: Wed Jan 07, 2004 2:05 pm    Post subject: Re: Is possible do that? SMExport allow to export dataset or DecisionCube into different fiel formats (text, CSV, HTML, XML, Excel, Word, RTF, PDF etc) http://www.scalabium.com/sme Also there is SMReport Autogenerated - allow to print/preview dataset or DecisionCube http://www.scalabium.com/smr -- With best regards, Mike Shkolnik E-mail: [email]mshkolnik (AT) scalabium (DOT) com[/email] WEB: http://www.scalabium.com "Sebastian Gautier" <sgautier (AT) terra (DOT) cl> wrote Quote: Hi experts, I have found the form to show the data that I need (using decision cube), but simultaneously need to print them. Exists the way to export them? thanks in advance Sebastian Gautier Hi I'm trying to calculate the total stock by product and country in my tables, suggestions would be appreciated; I have 2 tables with this structure: Tbl1 Stock, Country, Product_Name Tbl2 Country, Cod_Country I need do the following with an sql A result is qty,product_name for ctry Product_name Ctry 1, Ctry 2, Ctry 3 A 1500 3000 B 200 500 C 2000 100 Thanks in advance Sebastian Gautier Back to top Sebastian Gautier Guest Posted: Wed Jan 21, 2004 4:00 pm    Post subject: Re: Is possible do that? Thank a lot very usefull Sebastian Gautier "Brian Bushay TeamB" <BBushay (AT) Nmpls (DOT) com> escribió en el mensaje news:g63svv025qa8j6gc3jbkq02cra6nj5h5un (AT) 4ax (DOT) com... Quote: I have found the form to show the data that I need (using decision cube), but simultaneously need to print them. Exists the way to export them? Assuming you are using QuickReport (it comes with Delphi but doesn't install be default with D7) there is an example of printing from a descion cube on the quickreport website. -- Brian Bushay (TeamB) [email]Bbushay (AT) NMPLS (DOT) com[/email] Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         BorlandTalk.com Forum Index -> Delphi Databases (Desktop) All times are GMT Page 1 of 1   Jump to: Select a forum C++ Builder Newsgroups----------------C++ BuilderC++ Builder (ActiveX)C++ Builder (Announcements)C++ Builder (Command Line Tools)C++ Builder DatabasesC++ Builder Databases (ADO)C++ Builder Databases (Desktop)C++ Builder Databases (InterBase Express)C++ Builder Databases (Multi-Tier)C++ Builder Databases (SQL Servers)C++ Builder (Graphics)C++ Builder (IDE)C++ Builder (Internet)C++ Builder (Internet Socket)C++ Builder (Internet Web)C++ Builder (Jobs)C++ Builder (Language)C++ Builder (Language C++)C++ Builder (Compatibility with MFC)C++ Builder (Native API)C++ Builder (Non-Technical)C++ Builder (Students)C++ Builder (Third Party Tools)C++ Builder (Upgrade)C++ Builder (VCL)C++ Builder (VCL Components Usage)C++ Builder (VCL Components Development)C++ Builder (VCL Usage)C++ Builder (WinAPI)C++ Builder Installation Delphi Newsgroups----------------Delphi (General)Delphi ActiveX Controls UsageDelphi ActiveX Controls DevelopmentDelphi As400Delphi DatabasesDelphi Databases (ADO)Delphi Databases (dbExpress)Delphi Databases (Desktop)Delphi Databases (InterBase Express)Delphi Databases (Multi-Tier)Delphi Databases (SQL Servers)Delphi DeploymentDelphi GraphicsDelphi IDEDelphi InternationalizationDelphi InternetDelphi Internet Isapi-WebBrokerDelphi Internet WebSnapDelphi Internet WinsockDelphi JobsDelphi Language BASMDelphi Language ObjectPascalDelphi Model Driven Architecture (General)Delphi Model Driven Architecture (Third Party)Delphi MultimediaDelphi Native APIDelphi Non-TechnicalDelphi ObjectPascalDelphi OLE AutomationDelphi OO designDelphi OpenToolsAPIDelphi Reporting-ChartingDelphi RTLDelphi StudentsDelphi TeamSourceDelphi Thirdparty ToolsDelphi Thirdparty Tools (General)Delphi Thirdparty Tools (IntraWeb)Delphi Thirdparty Tools (RaveReports)Delphi UpgradeDelphi VCLDelphi VCL ComponentsDelphi VCL Components UsingDelphi VCL Components WritingDelphi WebServices SOAPDelphi WinAPIDelphi XMLDelphi Installationcomp.lang.pascal.delphi.components.misccomp.lang.pascal.delphi.components.usagecomp.lang.pascal.delphi.components.writingcomp.lang.pascal.delphi.databasescomp.lang.pascal.delphi.misccomp.sources.delphi JBuilder Newsgroups----------------JBuilderJBuilder AnnouncementsJBuilder Applet IssuesJBuilder As400JBuilder CompilerJBuilder CORBA/RMIJBuilder DatabasesJBuilder DB SwingJBuilder DebuggerJBuilder DeploymentJBuilder DocumentationJBuilder EnterpriseJBuilder HandheldJBuilder IDEJBuilder JavaJBuilder JavaAPIJBuilder Java LanguageJBuilder Java SwingJBuilder Java BeansJBuilder Java Beans UsageJBuilder Java Beans DevelopmentJBuilder JBCLJBuilder JDataStoreJBuilder JobsJBuilder LinuxJBuilder MacintoshJBuilder & MobileSetJBuilder MultiLingual AppsJBuilder Non-TechnicalJBuilder OpenToolsJBuilder Servlets (JSP)JBuilder StudentsJBuilder Team DevelopmentJBuilder Thirdparty ToolsJBuilder UpgradeJBuilder Installation InterBase Newsgroups----------------InterBaseInterBase Bug DiscussionInterBase GeneralInterBase IBConsoleInterBase OpenSourceInterBase ReplicationInterBase SQLInterBase ThirdpartyInterBase Installation Miscellaneous Borland Newsgroups----------------AppCenterAppServerEnteraVisiBroker  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.