Search Results

Search found 6407 results on 257 pages for 'reorder columns'.

Page 79/257 | < Previous Page | 75 76 77 78 79 80 81 82 83 84 85 86  | Next Page >

• Oracle BI Server Modeling, Part 1- Designing a Query Factory

  - by bob.ertl(at)oracle.com

    Welcome to Oracle BI Development's BI Foundation blog, focused on helping you get the most value from your Oracle Business Intelligence Enterprise Edition (BI EE) platform deployments.  In my first series of posts, I plan to show developers the concepts and best practices for modeling in the Common Enterprise Information Model (CEIM), the semantic layer of Oracle BI EE.  In this segment, I will lay the groundwork for the modeling concepts.  First, I will cover the big picture of how the BI Server fits into the system, and how the CEIM controls the query processing. Oracle BI EE Query Cycle The purpose of the Oracle BI Server is to bridge the gap between the presentation services and the data sources.  There are typically a variety of data sources in a variety of technologies: relational, normalized transaction systems; relational star-schema data warehouses and marts; multidimensional analytic cubes and financial applications; flat files, Excel files, XML files, and so on. Business datasets can reside in a single type of source, or, most of the time, are spread across various types of sources. Presentation services users are generally business people who need to be able to query that set of sources without any knowledge of technologies, schemas, or how sources are organized in their company. They think of business analysis in terms of measures with specific calculations, hierarchical dimensions for breaking those measures down, and detailed reports of the business transactions themselves.  Most of them create queries without knowing it, by picking a dashboard page and some filters.  Others create their own analysis by selecting metrics and dimensional attributes, and possibly creating additional calculations. The BI Server bridges that gap from simple business terms to technical physical queries by exposing just the business focused measures and dimensional attributes that business people can use in their analyses and dashboards.   After they make their selections and start the analysis, the BI Server plans the best way to query the data sources, writes the optimized sequence of physical queries to those sources, post-processes the results, and presents them to the client as a single result set suitable for tables, pivots and charts. The CEIM is a model that controls the processing of the BI Server.  It provides the subject areas that presentation services exposes for business users to select simplified metrics and dimensional attributes for their analysis.  It models the mappings to the physical data access, the calculations and logical transformations, and the data access security rules.  The CEIM consists of metadata stored in the repository, authored by developers using the Administration Tool client.     Presentation services and other query clients create their queries in BI EE's SQL-92 language, called Logical SQL or LSQL.  The API simply uses ODBC or JDBC to pass the query to the BI Server.  Presentation services writes the LSQL query in terms of the simplified objects presented to the users.  The BI Server creates a query plan, and rewrites the LSQL into fully-detailed SQL or other languages suitable for querying the physical sources.  For example, the LSQL on the left below was rewritten into the physical SQL for an Oracle 11g database on the right. Logical SQL   Physical SQL SELECT "D0 Time"."T02 Per Name Month" saw_0, "D4 Product"."P01  Product" saw_1, "F2 Units"."2-01  Billed Qty  (Sum All)" saw_2 FROM "Sample Sales" ORDER BY saw_0, saw_1       WITH SAWITH0 AS ( select T986.Per_Name_Month as c1, T879.Prod_Dsc as c2,      sum(T835.Units) as c3, T879.Prod_Key as c4 from      Product T879 /* A05 Product */ ,      Time_Mth T986 /* A08 Time Mth */ ,      FactsRev T835 /* A11 Revenue (Billed Time Join) */ where ( T835.Prod_Key = T879.Prod_Key and T835.Bill_Mth = T986.Row_Wid) group by T879.Prod_Dsc, T879.Prod_Key, T986.Per_Name_Month ) select SAWITH0.c1 as c1, SAWITH0.c2 as c2, SAWITH0.c3 as c3 from SAWITH0 order by c1, c2   Probably everybody reading this blog can write SQL or MDX.  However, the trick in designing the CEIM is that you are modeling a query-generation factory.  Rather than hand-crafting individual queries, you model behavior and relationships, thus configuring the BI Server machinery to manufacture millions of different queries in response to random user requests.  This mass production requires a different mindset and approach than when you are designing individual SQL statements in tools such as Oracle SQL Developer, Oracle Hyperion Interactive Reporting (formerly Brio), or Oracle BI Publisher.   The Structure of the Common Enterprise Information Model (CEIM) The CEIM has a unique structure specifically for modeling the relationships and behaviors that fill the gap from logical user requests to physical data source queries and back to the result.  The model divides the functionality into three specialized layers, called Presentation, Business Model and Mapping, and Physical, as shown below. Presentation services clients can generally only see the presentation layer, and the objects in the presentation layer are normally the only ones used in the LSQL request.  When a request comes into the BI Server from presentation services or another client, the relationships and objects in the model allow the BI Server to select the appropriate data sources, create a query plan, and generate the physical queries.  That's the left to right flow in the diagram below.  When the results come back from the data source queries, the right to left relationships in the model show how to transform the results and perform any final calculations and functions that could not be pushed down to the databases.   Business Model Think of the business model as the heart of the CEIM you are designing.  This is where you define the analytic behavior seen by the users, and the superset library of metric and dimension objects available to the user community as a whole.  It also provides the baseline business-friendly names and user-readable dictionary.  For these reasons, it is often called the "logical" model--it is a virtual database schema that persists no data, but can be queried as if it is a database. The business model always has a dimensional shape (more on this in future posts), and its simple shape and terminology hides the complexity of the source data models. Besides hiding complexity and normalizing terminology, this layer adds most of the analytic value, as well.  This is where you define the rich, dimensional behavior of the metrics and complex business calculations, as well as the conformed dimensions and hierarchies.  It contributes to the ease of use for business users, since the dimensional metric definitions apply in any context of filters and drill-downs, and the conformed dimensions enable dashboard-wide filters and guided analysis links that bring context along from one page to the next.  The conformed dimensions also provide a key to hiding the complexity of many sources, including federation of different databases, behind the simple business model. Note that the expression language in this layer is LSQL, so that any expression can be rewritten into any data source's query language at run time.  This is important for federation, where a given logical object can map to several different physical objects in different databases.  It is also important to portability of the CEIM to different database brands, which is a key requirement for Oracle's BI Applications products. Your requirements process with your user community will mostly affect the business model.  This is where you will define most of the things they specifically ask for, such as metric definitions.  For this reason, many of the best-practice methodologies of our consulting partners start with the high-level definition of this layer. Physical Model The physical model connects the business model that meets your users' requirements to the reality of the data sources you have available. In the query factory analogy, think of the physical layer as the bill of materials for generating physical queries.  Every schema, table, column, join, cube, hierarchy, etc., that will appear in any physical query manufactured at run time must be modeled here at design time. Each physical data source will have its own physical model, or "database" object in the CEIM.  The shape of each physical model matches the shape of its physical source.  In other words, if the source is normalized relational, the physical model will mimic that normalized shape.  If it is a hypercube, the physical model will have a hypercube shape.  If it is a flat file, it will have a denormalized tabular shape. To aid in query optimization, the physical layer also tracks the specifics of the database brand and release.  This allows the BI Server to make the most of each physical source's distinct capabilities, writing queries in its syntax, and using its specific functions. This allows the BI Server to push processing work as deep as possible into the physical source, which minimizes data movement and takes full advantage of the database's own optimizer.  For most data sources, native APIs are used to further optimize performance and functionality. The value of having a distinct separation between the logical (business) and physical models is encapsulation of the physical characteristics.  This encapsulation is another enabler of packaged BI applications and federation.  It is also key to hiding the complex shapes and relationships in the physical sources from the end users.  Consider a routine drill-down in the business model: physically, it can require a drill-through where the first query is MDX to a multidimensional cube, followed by the drill-down query in SQL to a normalized relational database.  The only difference from the user's point of view is that the 2nd query added a more detailed dimension level column - everything else was the same. Mappings Within the Business Model and Mapping Layer, the mappings provide the binding from each logical column and join in the dimensional business model, to each of the objects that can provide its data in the physical layer.  When there is more than one option for a physical source, rules in the mappings are applied to the query context to determine which of the data sources should be hit, and how to combine their results if more than one is used.  These rules specify aggregate navigation, vertical partitioning (fragmentation), and horizontal partitioning, any of which can be federated across multiple, heterogeneous sources.  These mappings are usually the most sophisticated part of the CEIM. Presentation You might think of the presentation layer as a set of very simple relational-like views into the business model.  Over ODBC/JDBC, they present a relational catalog consisting of databases, tables and columns.  For business users, presentation services interprets these as subject areas, folders and columns, respectively.  (Note that in 10g, subject areas were called presentation catalogs in the CEIM.  In this blog, I will stick to 11g terminology.)  Generally speaking, presentation services and other clients can query only these objects (there are exceptions for certain clients such as BI Publisher and Essbase Studio). The purpose of the presentation layer is to specialize the business model for different categories of users.  Based on a user's role, they will be restricted to specific subject areas, tables and columns for security.  The breakdown of the model into multiple subject areas organizes the content for users, and subjects superfluous to a particular business role can be hidden from that set of users.  Customized names and descriptions can be used to override the business model names for a specific audience.  Variables in the object names can be used for localization. For these reasons, you are better off thinking of the tables in the presentation layer as folders than as strict relational tables.  The real semantics of tables and how they function is in the business model, and any grouping of columns can be included in any table in the presentation layer.  In 11g, an LSQL query can also span multiple presentation subject areas, as long as they map to the same business model. Other Model Objects There are some objects that apply to multiple layers.  These include security-related objects, such as application roles, users, data filters, and query limits (governors).  There are also variables you can use in parameters and expressions, and initialization blocks for loading their initial values on a static or user session basis.  Finally, there are Multi-User Development (MUD) projects for developers to check out units of work, and objects for the marketing feature used by our packaged customer relationship management (CRM) software.   The Query Factory At this point, you should have a grasp on the query factory concept.  When developing the CEIM model, you are configuring the BI Server to automatically manufacture millions of queries in response to random user requests. You do this by defining the analytic behavior in the business model, mapping that to the physical data sources, and exposing it through the presentation layer's role-based subject areas. While configuring mass production requires a different mindset than when you hand-craft individual SQL or MDX statements, it builds on the modeling and query concepts you already understand. The following posts in this series will walk through the CEIM modeling concepts and best practices in detail.  We will initially review dimensional concepts so you can understand the business model, and then present a pattern-based approach to learning the mappings from a variety of physical schema shapes and deployments to the dimensional model.  Along the way, we will also present the dimensional calculation template, and learn how to configure the many additivity patterns.

  Read the article

• Seeking on a Heap, and Two Useful DMVs

  - by Paul White

  So far in this mini-series on seeks and scans, we have seen that a simple ‘seek’ operation can be much more complex than it first appears.  A seek can contain one or more seek predicates – each of which can either identify at most one row in a unique index (a singleton lookup) or a range of values (a range scan).  When looking at a query plan, we will often need to look at the details of the seek operator in the Properties window to see how many operations it is performing, and what type of operation each one is.  As you saw in the first post in this series, the number of hidden seeking operations can have an appreciable impact on performance. Measuring Seeks and Scans I mentioned in my last post that there is no way to tell from a graphical query plan whether you are seeing a singleton lookup or a range scan.  You can work it out – if you happen to know that the index is defined as unique and the seek predicate is an equality comparison, but there’s no separate property that says ‘singleton lookup’ or ‘range scan’.  This is a shame, and if I had my way, the query plan would show different icons for range scans and singleton lookups – perhaps also indicating whether the operation was one or more of those operations underneath the covers. In light of all that, you might be wondering if there is another way to measure how many seeks of either type are occurring in your system, or for a particular query.  As is often the case, the answer is yes – we can use a couple of dynamic management views (DMVs): sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats. Index Usage Stats The index usage stats DMV contains counts of index operations from the perspective of the Query Executor (QE) – the SQL Server component that is responsible for executing the query plan.  It has three columns that are of particular interest to us: user_seeks – the number of times an Index Seek operator appears in an executed plan user_scans – the number of times a Table Scan or Index Scan operator appears in an executed plan user_lookups – the number of times an RID or Key Lookup operator appears in an executed plan An operator is counted once per execution (generating an estimated plan does not affect the totals), so an Index Seek that executes 10,000 times in a single plan execution adds 1 to the count of user seeks.  Even less intuitively, an operator is also counted once per execution even if it is not executed at all.  I will show you a demonstration of each of these things later in this post. Index Operational Stats The index operational stats DMV contains counts of index and table operations from the perspective of the Storage Engine (SE).  It contains a wealth of interesting information, but the two columns of interest to us right now are: range_scan_count – the number of range scans (including unrestricted full scans) on a heap or index structure singleton_lookup_count – the number of singleton lookups in a heap or index structure This DMV counts each SE operation, so 10,000 singleton lookups will add 10,000 to the singleton lookup count column, and a table scan that is executed 5 times will add 5 to the range scan count. The Test Rig To explore the behaviour of seeks and scans in detail, we will need to create a test environment.  The scripts presented here are best run on SQL Server 2008 Developer Edition, but the majority of the tests will work just fine on SQL Server 2005.  A couple of tests use partitioning, but these will be skipped if you are not running an Enterprise-equivalent SKU.  Ok, first up we need a database: USE master; GO IF DB_ID('ScansAndSeeks') IS NOT NULL DROP DATABASE ScansAndSeeks; GO CREATE DATABASE ScansAndSeeks; GO USE ScansAndSeeks; GO ALTER DATABASE ScansAndSeeks SET ALLOW_SNAPSHOT_ISOLATION OFF ; ALTER DATABASE ScansAndSeeks SET AUTO_CLOSE OFF, AUTO_SHRINK OFF, AUTO_CREATE_STATISTICS OFF, AUTO_UPDATE_STATISTICS OFF, PARAMETERIZATION SIMPLE, READ_COMMITTED_SNAPSHOT OFF, RESTRICTED_USER ; Notice that several database options are set in particular ways to ensure we get meaningful and reproducible results from the DMVs.  In particular, the options to auto-create and update statistics are disabled.  There are also three stored procedures, the first of which creates a test table (which may or may not be partitioned).  The table is pretty much the same one we used yesterday: The table has 100 rows, and both the key_col and data columns contain the same values – the integers from 1 to 100 inclusive.  The table is a heap, with a non-clustered primary key on key_col, and a non-clustered non-unique index on the data column.  The only reason I have used a heap here, rather than a clustered table, is so I can demonstrate a seek on a heap later on.  The table has an extra column (not shown because I am too lazy to update the diagram from yesterday) called padding – a CHAR(100) column that just contains 100 spaces in every row.  It’s just there to discourage SQL Server from choosing table scan over an index + RID lookup in one of the tests. The first stored procedure is called ResetTest: CREATE PROCEDURE dbo.ResetTest @Partitioned BIT = 'false' AS BEGIN SET NOCOUNT ON ; IF OBJECT_ID(N'dbo.Example', N'U') IS NOT NULL BEGIN DROP TABLE dbo.Example; END ; -- Test table is a heap -- Non-clustered primary key on 'key_col' CREATE TABLE dbo.Example ( key_col INTEGER NOT NULL, data INTEGER NOT NULL, padding CHAR(100) NOT NULL DEFAULT SPACE(100), CONSTRAINT [PK dbo.Example key_col] PRIMARY KEY NONCLUSTERED (key_col) ) ; IF @Partitioned = 'true' BEGIN -- Enterprise, Trial, or Developer -- required for partitioning tests IF SERVERPROPERTY('EngineEdition') = 3 BEGIN EXECUTE (' DROP TABLE dbo.Example ; IF EXISTS ( SELECT 1 FROM sys.partition_schemes WHERE name = N''PS'' ) DROP PARTITION SCHEME PS ; IF EXISTS ( SELECT 1 FROM sys.partition_functions WHERE name = N''PF'' ) DROP PARTITION FUNCTION PF ; CREATE PARTITION FUNCTION PF (INTEGER) AS RANGE RIGHT FOR VALUES (20, 40, 60, 80, 100) ; CREATE PARTITION SCHEME PS AS PARTITION PF ALL TO ([PRIMARY]) ; CREATE TABLE dbo.Example ( key_col INTEGER NOT NULL, data INTEGER NOT NULL, padding CHAR(100) NOT NULL DEFAULT SPACE(100), CONSTRAINT [PK dbo.Example key_col] PRIMARY KEY NONCLUSTERED (key_col) ) ON PS (key_col); '); END ELSE BEGIN RAISERROR('Invalid SKU for partition test', 16, 1); RETURN; END; END ; -- Non-unique non-clustered index on the 'data' column CREATE NONCLUSTERED INDEX [IX dbo.Example data] ON dbo.Example (data) ; -- Add 100 rows INSERT dbo.Example WITH (TABLOCKX) ( key_col, data ) SELECT key_col = V.number, data = V.number FROM master.dbo.spt_values AS V WHERE V.[type] = N'P' AND V.number BETWEEN 1 AND 100 ; END; GO The second stored procedure, ShowStats, displays information from the Index Usage Stats and Index Operational Stats DMVs: CREATE PROCEDURE dbo.ShowStats @Partitioned BIT = 'false' AS BEGIN -- Index Usage Stats DMV (QE) SELECT index_name = ISNULL(I.name, I.type_desc), scans = IUS.user_scans, seeks = IUS.user_seeks, lookups = IUS.user_lookups FROM sys.dm_db_index_usage_stats AS IUS JOIN sys.indexes AS I ON I.object_id = IUS.object_id AND I.index_id = IUS.index_id WHERE IUS.database_id = DB_ID(N'ScansAndSeeks') AND IUS.object_id = OBJECT_ID(N'dbo.Example', N'U') ORDER BY I.index_id ; -- Index Operational Stats DMV (SE) IF @Partitioned = 'true' SELECT index_name = ISNULL(I.name, I.type_desc), partitions = COUNT(IOS.partition_number), range_scans = SUM(IOS.range_scan_count), single_lookups = SUM(IOS.singleton_lookup_count) FROM sys.dm_db_index_operational_stats ( DB_ID(N'ScansAndSeeks'), OBJECT_ID(N'dbo.Example', N'U'), NULL, NULL ) AS IOS JOIN sys.indexes AS I ON I.object_id = IOS.object_id AND I.index_id = IOS.index_id GROUP BY I.index_id, -- Key I.name, I.type_desc ORDER BY I.index_id; ELSE SELECT index_name = ISNULL(I.name, I.type_desc), range_scans = SUM(IOS.range_scan_count), single_lookups = SUM(IOS.singleton_lookup_count) FROM sys.dm_db_index_operational_stats ( DB_ID(N'ScansAndSeeks'), OBJECT_ID(N'dbo.Example', N'U'), NULL, NULL ) AS IOS JOIN sys.indexes AS I ON I.object_id = IOS.object_id AND I.index_id = IOS.index_id GROUP BY I.index_id, -- Key I.name, I.type_desc ORDER BY I.index_id; END; The final stored procedure, RunTest, executes a query written against the example table: CREATE PROCEDURE dbo.RunTest @SQL VARCHAR(8000), @Partitioned BIT = 'false' AS BEGIN -- No execution plan yet SET STATISTICS XML OFF ; -- Reset the test environment EXECUTE dbo.ResetTest @Partitioned ; -- Previous call will throw an error if a partitioned -- test was requested, but SKU does not support it IF @@ERROR = 0 BEGIN -- IO statistics and plan on SET STATISTICS XML, IO ON ; -- Test statement EXECUTE (@SQL) ; -- Plan and IO statistics off SET STATISTICS XML, IO OFF ; EXECUTE dbo.ShowStats @Partitioned; END; END; The Tests The first test is a simple scan of the heap table: EXECUTE dbo.RunTest @SQL = 'SELECT * FROM Example'; The top result set comes from the Index Usage Stats DMV, so it is the Query Executor’s (QE) view.  The lower result is from Index Operational Stats, which shows statistics derived from the actions taken by the Storage Engine (SE).  We see that QE performed 1 scan operation on the heap, and SE performed a single range scan.  Let’s try a single-value equality seek on a unique index next: EXECUTE dbo.RunTest @SQL = 'SELECT key_col FROM Example WHERE key_col = 32'; This time we see a single seek on the non-clustered primary key from QE, and one singleton lookup on the same index by the SE.  Now for a single-value seek on the non-unique non-clustered index: EXECUTE dbo.RunTest @SQL = 'SELECT data FROM Example WHERE data = 32'; QE shows a single seek on the non-clustered non-unique index, but SE shows a single range scan on that index – not the singleton lookup we saw in the previous test.  That makes sense because we know that only a single-value seek into a unique index is a singleton seek.  A single-value seek into a non-unique index might retrieve any number of rows, if you think about it.  The next query is equivalent to the IN list example seen in the first post in this series, but it is written using OR (just for variety, you understand): EXECUTE dbo.RunTest @SQL = 'SELECT data FROM Example WHERE data = 32 OR data = 33'; The plan looks the same, and there’s no difference in the stats recorded by QE, but the SE shows two range scans.  Again, these are range scans because we are looking for two values in the data column, which is covered by a non-unique index.  I’ve added a snippet from the Properties window to show that the query plan does show two seek predicates, not just one.  Now let’s rewrite the query using BETWEEN: EXECUTE dbo.RunTest @SQL = 'SELECT data FROM Example WHERE data BETWEEN 32 AND 33'; Notice the seek operator only has one predicate now – it’s just a single range scan from 32 to 33 in the index – as the SE output shows.  For the next test, we will look up four values in the key_col column: EXECUTE dbo.RunTest @SQL = 'SELECT key_col FROM Example WHERE key_col IN (2,4,6,8)'; Just a single seek on the PK from the Query Executor, but four singleton lookups reported by the Storage Engine – and four seek predicates in the Properties window.  On to a more complex example: EXECUTE dbo.RunTest @SQL = 'SELECT * FROM Example WITH (INDEX([PK dbo.Example key_col])) WHERE key_col BETWEEN 1 AND 8'; This time we are forcing use of the non-clustered primary key to return eight rows.  The index is not covering for this query, so the query plan includes an RID lookup into the heap to fetch the data and padding columns.  The QE reports a seek on the PK and a lookup on the heap.  The SE reports a single range scan on the PK (to find key_col values between 1 and 8), and eight singleton lookups on the heap.  Remember that a bookmark lookup (RID or Key) is a seek to a single value in a ‘unique index’ – it finds a row in the heap or cluster from a unique RID or clustering key – so that’s why lookups are always singleton lookups, not range scans. Our next example shows what happens when a query plan operator is not executed at all: EXECUTE dbo.RunTest @SQL = 'SELECT key_col FROM Example WHERE key_col = 8 AND @@TRANCOUNT < 0'; The Filter has a start-up predicate which is always false (if your @@TRANCOUNT is less than zero, call CSS immediately).  The index seek is never executed, but QE still records a single seek against the PK because the operator appears once in an executed plan.  The SE output shows no activity at all.  This next example is 2008 and above only, I’m afraid: EXECUTE dbo.RunTest @SQL = 'SELECT * FROM Example WHERE key_col BETWEEN 1 AND 30', @Partitioned = 'true'; This is the first example to use a partitioned table.  QE reports a single seek on the heap (yes – a seek on a heap), and the SE reports two range scans on the heap.  SQL Server knows (from the partitioning definition) that it only needs to look at partitions 1 and 2 to find all the rows where key_col is between 1 and 30 – the engine seeks to find the two partitions, and performs a range scan seek on each partition. The final example for today is another seek on a heap – try to work out the output of the query before running it! EXECUTE dbo.RunTest @SQL = 'SELECT TOP (2) WITH TIES * FROM Example WHERE key_col BETWEEN 1 AND 50 ORDER BY $PARTITION.PF(key_col) DESC', @Partitioned = 'true'; Notice the lack of an explicit Sort operator in the query plan to enforce the ORDER BY clause, and the backward range scan. © 2011 Paul White email: [email protected] twitter: @SQL_Kiwi

  Read the article

• Syntax error in aggregate argument: Expecting a single column argument with possible 'Child' qualifier.

  - by Rushabh

  DataTable distinctTable = dTable.DefaultView.ToTable(true,"ITEM_NO","ITEM_STOCK"); DataTable dtSummerized = new DataTable("SummerizedResult"); dtSummerized.Columns.Add("ITEM_NO",typeof(string)); dtSummerized.Columns.Add("ITEM_STOCK",typeof(double)); int count=0; foreach(DataRow dRow in distinctTable.Rows) { count++; //string itemNo = Convert.ToString(dRow[0]); double TotalItem = Convert.ToDouble(dRow[1]); string TotalStock = dTable.Compute("sum(" + TotalItem + ")", "ITEM_NO=" + dRow["ITEM_NO"].ToString()).ToString(); dtSummerized.Rows.Add(count,dRow["ITEM_NO"],TotalStock); } Error Message: Syntax error in aggregate argument: Expecting a single column argument with possible 'Child' qualifier. Do anyone can help me out? Thanks.

  Read the article

• .NET ExcelLibrary Export Problems - .XLS corruption

  - by hamlin11

  The library is located here: http://code.google.com/p/excellibrary/ I'm using some basic code to create an .XLS file. When I open the file in Excel 2007, I get the following errors: I click yes, then I get: And just for fun, here's the XML error details (not very helpful) Here's the code that I'm using to generate the Excel file: Dim ds As New DataSet Dim dt1 As New DataTable("table 1") dt1.Columns.Add("column A", GetType(String)) dt1.Columns.Add("column B", GetType(String)) dt1.Rows.Add("test 1", "Test 2") dt1.Rows.Add("test 3", "Test 4") ds.Tables.Add(dt1) ExcelLibrary.DataSetHelper.CreateWorkbook("c:/temp/test1.xls", ds) Note: I added a reference to the DLL provided by the project download page and have an "Imports ExcelLibrary.Office.Excel" to link up with it Any ideas on what the corruption is and/or how to fix it? Thanks

  Read the article

• WPF DataBinding, CollectionViewSource, INotifyPropertyChanged

  - by plotnick

  First time when I tried to do something in WPF, I was puzzled with WPF DataBinding. Then I studied thorougly next example on MSDN: http://msdn.microsoft.com/en-us/library/ms771319(v=VS.90).aspx Now, I quite understand how to use Master-Detail paradigm for a form which takes data from one source (one table) - both for master and detailed parts. I mean for example I have a grid with data and below the grid I have a few fields with detailed data of current row. But how to do it, if detailed data comes from different but related tables? for example: you have a Table 'Users' with columns - 'id' and 'Name'. You also have another table 'Pictures' with columns like 'id','Filename','UserId'. And now, using master-detail paradigm you have to built a form. And every time when you chose a row in a Master you should get all associated pictures in Details. What is the right way to do it? Could you please show me an example?

  Read the article

• RDLC filtering nested tables

  - by aprescott

  I am creating an RDLC report where the dataset consists of several datatables. There is one parent table and several child tables. What I would like to do is display relevant data from each child table for each row in the parent table. Here is a simplified example: table1 = "Purchase" has columns PurhcaseID, PurchaseNumber, PurchaseDate table2 = "PurchasedItem" has columns PurchaseItemID, PurhcaseID, ItemDescription In my RDLC, I have a Purchase table grouped on PurchaseDate and would like to display the PurchasedItems for each Purchase. The current solution uses a subreport, but I do not like this because it leaves an ugly empty space when there is no data for the subreport display. (I would be fine with using a subreport if I could properly hide it without leaving an empty space.) I am not able to rewrite the stored procedure to return a single table, either. How are others dealing with this scenario?

  Read the article

• WPF DataGrid: Make cells readonly

  - by crauscher

  I use the following DataGrid <DataGrid Grid.Row="1" Grid.Column="1" Name="Grid" ItemsSource="{Binding}" AutoGenerateColumns="False" > <DataGrid.Columns> <DataGridTextColumn Header="Name" Width="100" Binding="{Binding Path=Name}"></DataGridTextColumn> <DataGridTextColumn Header="OldValue" Width="100" Binding="{Binding Path=OldValue}"></DataGridTextColumn> <DataGridTextColumn Header="NewValue" Width="100*" Binding="{Binding Path=NewValue}"></DataGridTextColumn> </DataGrid.Columns> </DataGrid> How can I make the cells readonly?

  Read the article

• WPF Toolkit: how to scroll datagrid to show selected item from code behind?

  - by Akash Kava

  I tried following, all of following fails on function ScrollIntoView and gives NullReferenceException. // doesnt work grid.SelectedItem = sItem; grid.ScrollIntoView(sItem); // doesnt work grid.SelectedItem = sItem; grid.Focus(); grid.CurrentColumn = grid.Columns[0]; grid.UpdateLayout(); grid.ScrollIntoView(sItem,grid.Columns[0]); // doesnt work grid.SelectedItem = sItem; grid.UpdateLayout(); grid.ScrollIntoView(sItem); The problem is, when I select row from codebehind, selection is not visible its somewhere down in bottom, unless user scrolls he feels that selection has vanished. I need to scroll datagrid to the point user can see the selection. I also tried "BringIntoView" as well but no luck.

  Read the article

• Sharing SharePoint Lists across sites without SharePoint Designer

  - by Ryan

  The problem I've been having is how to show a list from one sub-site to another. I don't have server admin privileges so I am unable to use SharePoint Designer to edit any part of the site. The situation: One parent page with two sub-sites. sub-site #1 has a list on it with several text columns, and one column to be used as a 'visible' column. sub-site #2 has to show a view of the list on sub-site #1 filtering on the 'visible' column The two solutions I've found are to create a linked list in SharePoint Designer directly, or use a Content Query Web Part, edit the part to show the correct columns, and use SharePoint Designer to edit the item style to actually display the proper information. Is there any way to accomplish this without using SharePoint Designer?

  Read the article

• Convert WMI CimType to System.Type

  - by Anonymous Coward

  I am trying to write a generic extension to turn a ManagementObjectCollection into a DataTable. This is just to make things easier for a startup script/program I am writing. I have ran into a problem with CimType. I have included the code I have written so far below. public static DataTable GetData(this ManagementObjectCollection objectCollection) { DataTable table = new DataTable(); foreach (ManagementObject obj in objectCollection) { if (table.Columns.Count == 0) { foreach (PropertyData property in obj.Properties) { table.Columns.Add(property.Name, property.Type); } } DataRow row = table.NewRow(); foreach (PropertyData property in obj.Properties) { row[property.Name] = property.Value; } table.Rows.Add(row); } return table; } } I have found the a method which I think will work at http://www.devcow.com/blogs/adnrg/archive/2005/09/23/108.aspx. However it seems to me like there may be a better way, or even a .net function I am overlooking.

  Read the article

• Subtotal error on calculated field in a Reporting Services Matrix

  - by peacedog

  I've got a Reporting Services report that has two row groups: Category and SubCategory. For columns it has LastYearDataA, ThisYearDataA, LastYearDataB, ThisYearDataB. I added two columns (one for A and one for B) to handle an expression calculation (to show a percentage different from LastYear to ThisYear for each). That's working. The problem comes in the SubTotal for each category. The raw numbers are totaling correctly. If SubCat1 has 10//5 for LastYear/ThisYear A, and SubCat2 has 5//1, then I get 15/5 for the totals. But I get the percentage reported in the total column as "50%", matching SubCat1. Percentages for each Subcategory are being calculated correctly (according to my backup math, anyway). But the sub total % always matches the first SubCategory in the group. Is this impossible to do in Reporting Services 2005?

  Read the article

• ASP.NET GridView second header row to span main header row

  - by Dana Robinson

  I have an ASP.NET GridView which has columns that look like this: | Foo | Bar | Total1 | Total2 | Total3 | Is it possible to create a header on two rows that looks like this? | | Totals | | Foo | Bar | 1 | 2 | 3 | The data in each row will remain unchanged as this is just to pretty up the header and decrease the horizontal space that the grid takes up. The entire GridView is sortable in case that matters. I don't intend for the added "Totals" spanning column to have any sort functionality. Edit: Based on one of the articles given below, I created a class which inherits from GridView and adds the second header row in. namespace CustomControls { public class TwoHeadedGridView : GridView { protected Table InnerTable { get { if (this.HasControls()) { return (Table)this.Controls[0]; } return null; } } protected override void OnDataBound(EventArgs e) { base.OnDataBound(e); this.CreateSecondHeader(); } private void CreateSecondHeader() { GridViewRow row = new GridViewRow(0, -1, DataControlRowType.Header, DataControlRowState.Normal); TableCell left = new TableHeaderCell(); left.ColumnSpan = 3; row.Cells.Add(left); TableCell totals = new TableHeaderCell(); totals.ColumnSpan = this.Columns.Count - 3; totals.Text = "Totals"; row.Cells.Add(totals); this.InnerTable.Rows.AddAt(0, row); } } } In case you are new to ASP.NET like I am, I should also point out that you need to: 1) Register your class by adding a line like this to your web form: <%@ Register TagPrefix="foo" NameSpace="CustomControls" Assembly="__code"%> 2) Change asp:GridView in your previous markup to foo:TwoHeadedGridView. Don't forget the closing tag. Another edit: You can also do this without creating a custom class. Simply add an event handler for the DataBound event of your grid like this: protected void gvOrganisms_DataBound(object sender, EventArgs e) { GridView grid = sender as GridView; if (grid != null) { GridViewRow row = new GridViewRow(0, -1, DataControlRowType.Header, DataControlRowState.Normal); TableCell left = new TableHeaderCell(); left.ColumnSpan = 3; row.Cells.Add(left); TableCell totals = new TableHeaderCell(); totals.ColumnSpan = grid.Columns.Count - 3; totals.Text = "Totals"; row.Cells.Add(totals); Table t = grid.Controls[0] as Table; if (t != null) { t.Rows.AddAt(0, row); } } } The advantage of the custom control is that you can see the extra header row on the design view of your web form. The event handler method is a bit simpler, though.

  Read the article

• How to solve out of memory exception error in Entity FramWork?

  - by programmerist

  Hello; these below codes give whole data of my Rehber datas. But if i want to show web page via Gridview send me out of memory exception error. GenoTip.BAL: public static List<Rehber> GetAllDataOfRehber() { using (GenoTipSatisEntities genSatisCtx = new GenoTipSatisEntities()) { ObjectQuery<Rehber> rehber = genSatisCtx.Rehber; return rehber.ToList(); } } if i bind data directly dummy gridview like that no problem occures every thing is great!!! <asp:GridView ID="gwRehber" runat="server"> </asp:GridView> if above codes send data to Satis.aspx page: using GenoTip.BAL; namespace GenoTip.Web.ContentPages.Satis { public partial class Satis : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { gwRehber.DataSource = SatisServices.GetAllDataOfRehber(); gwRehber.DataBind(); //gwRehber.Columns[0].Visible = false; } } } } but i rearranged my gridview send me out of memory exception!!!! i need this arrangenment to show deta!!! <asp:GridView ID="gwRehber" runat="server"> <Columns> <%-- <asp:TemplateField> <ItemTemplate> <asp:Button runat="server" ID="btnID" CommandName="select" CommandArgument='<%# Eval("ID") %>' Text="Seç" /> </ItemTemplate> </asp:TemplateField>--%> <asp:BoundField DataField="Ad" HeaderText="Ad" /> <asp:BoundField DataField="BireyID" HeaderText="BireyID" Visible="false" /> <asp:BoundField DataField="Degistiren" HeaderText="Degistiren" /> <asp:BoundField DataField="EklemeTarihi" HeaderText="EklemeTarihi" /> <asp:BoundField DataField="DegistirmeTarihi" HeaderText="Degistirme Tarihi" Visible="false" /> <asp:BoundField DataField="Ekleyen" HeaderText="Ekleyen" /> <asp:BoundField DataField="ID" HeaderText="ID" Visible="false" /> <asp:BoundField DataField="Imza" HeaderText="Imza" /> <asp:BoundField DataField="KurumID" HeaderText="KurumID" Visible="false" /> </Columns> </asp:GridView> Error Detail : [OutOfMemoryException: 'System.OutOfMemoryException' türünde özel durum olusturuldu.] System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity) +29 System.Convert.ToBase64String(Byte[] inArray, Int32 offset, Int32 length, Base64FormattingOptions options) +146 System.Web.UI.ObjectStateFormatter.Serialize(Object stateGraph) +183 System.Web.UI.ObjectStateFormatter.System.Web.UI.IStateFormatter.Serialize(Object state) +4 System.Web.UI.Util.SerializeWithAssert(IStateFormatter formatter, Object stateGraph) +37 System.Web.UI.HiddenFieldPageStatePersister.Save() +79 System.Web.UI.Page.SavePageStateToPersistenceMedium(Object state) +105 System.Web.UI.Page.SaveAllState() +236 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1099

  Read the article

• Asp.Net GridView in DropDownList in-line databind

  - by oraclee

  Hi All; <asp:GridView ID="GridView1" runat="server"> <Columns> <asp:TemplateField> <EditItemTemplate> <asp:DropDownList ID="DropDownList1" runat="server"> </asp:DropDownList> </EditItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e) { } How To Make GridView1_RowEditing event in DataBind DropDownList1 ? I need Editing databind dropdownlist pls Help Thank You.

  Read the article

• Add Table to FlowDocument In Code Behind

  - by urema

  Hi, I have tried this..... _doc = new FlowDocument(); Table t = new Table(); for (int i = 0; i < 7; i++) { t.Columns.Add(new TableColumn()); } TableRow row = new TableRow(); row.Background = Brushes.Silver; row.FontSize = 40; row.FontWeight = FontWeights.Bold; row.Cells.Add(new TableCell(new Paragraph(new Run("I span 7 columns")))); row.Cells[0].ColumnSpan = 6; _doc2.Blocks.Add(t); But everytime I go to view this document the table never shows.....although the border image and document title that I add to this document before adding this table outputs fine. Thanks in advance, U.

  Read the article

• Using Lite Version of Entity in nHibernate Relations?

  - by Amitabh

  Is it a good idea to create a lighter version of an Entity in some cases just for performance reason pointing to same table but with fewer columns mapped. E.g If I have a Contact Table which has 50 Columns and in few of the related entities I might be interested in FirstName and LastName property is it a good idea to create a lightweight version of Contact table. E.g. public class ContactLite { public int Id {get; set;} public string FirstName {get; set;} public string LastName {get; set;} } Also is it possible to map multiple classes to same table?

  Read the article

• Processing a tab delimited file with shell script processing

  - by Lilly Tooner

  Hello, normally I would use Python/Perl for this procedure but I find myself (for political reasons) having to pull this off using a bash shell. I have a large tab delimited file that contains six columns and the second column is integers. I need to shell script a solution that would verify that the file indeed is six columns and that the second column is indeed integers. I am assuming that I would need to use sed/awk here somewhere. Problem is that I'm not that familiar with sed/awk. Any advice would be appreciated. Many thanks! Lilly

  Read the article

• Fill spinner from cursor in android

  - by Rickard

  Hi, i have searched for a awnser for this for a while today. It all looks so easy but i never get it to work. I want to fill a spinner with my cursor. I have been trying to use SimpleCursorAdapter for this as a lot of sites say i shall but i never get it to work. Show me just how easy it is :) Thanks for your time! My cursor Cursor cursor = db.query(DATABASE_TABLE_Clients, new String[] {"_id", "C_Name"}, null, null, null, null, "C_Name"); My spinner (Spinner) findViewById(R.id.spnClients); My Code Cursor cursor_Names = SQLData.getClientNames(); startManagingCursor(cursor_Names); String[] columns = new String[] { "C_Name" }; int[] to = new int[] { R.id.txt_Address }; SimpleCursorAdapter mAdapter = new SimpleCursorAdapter(this, android.R.layout.simple_spinner_dropdown_item, cursor_Names, columns, to); Spinner spnClients = (Spinner) findViewById(R.id.spnClients); spnClients.setAdapter(mAdapter);

  Read the article

• Sorting eigenvectors by their eigenvalues (associated sorting)

  - by fbrereto

  I have an unsorted vector of eigenvalues and a related matrix of eigenvectors. I'd like to sort the columns of the matrix with respect to the sorted set of eigenvalues. (e.g., if eigenvalue[3] moves to eigenvalue[2], I want column 3 of the eigenvector matrix to move over to column 2.) I know I can sort the eigenvalues in O(N log N) via std::sort. Without rolling my own sorting algorithm, how do I make sure the matrix's columns (the associated eigenvectors) follow along with their eigenvalues as the latter are sorted?

  Read the article

• Defining an Entity Framework 1:1 association

  - by Craig Fisher

  I'm trying to define a 1:1 association between two entities (one maps to a table and the other to a view - using DefinedQuery) in an Entity Framework model. When trying to define the mapping for this in the designer, it makes me choose the (1) table or view to map the association to. What am I supposed to choose? I can choose either of the two tables but then I am forced to choose a column from that table (or view) for each end of the relationship. I would expect to be able to choose a column from one table for one end of the association and a column from the other table for the other end of the association, but there's no way to do this. Here I've chosen to map to the "DW_ WF_ClaimInfo" view and it is forcing me to choose two columns from that view - one for each end of the relationship. I've also tried defining the mapping manually in the XML as follows: <AssociationSetMapping Name="Entity1Entity2" TypeName="ClaimsModel.Entity1Entity2" StoreEntitySet="Entity1"> <EndProperty Name="Entity2"> <ScalarProperty Name="DOCUMENT" ColumnName="DOCUMENT" /> </EndProperty> <EndProperty Name="Entity1"> <ScalarProperty Name="PK_DocumentId" ColumnName="PK_DocumentId" /> </EndProperty> </AssociationSetMapping> But this gives: Error 2010: The Column 'DOCUMENT' specified as part of this MSL does not exist in MetadataWorkspace. Seems like it still expects both columns to come from the same table, which doesn't make sense to me. Furthermore, if I select the same key for each end, e.g.: <AssociationSetMapping Name="Entity1Entity2" TypeName="ClaimsModel.Entity1Entity2" StoreEntitySet="Entity1"> <EndProperty Name="Entity2"> <ScalarProperty Name="DOCUMENT" ColumnName="PK_DocumentId" /> </EndProperty> <EndProperty Name="Entity1"> <ScalarProperty Name="PK_DocumentId" ColumnName="PK_DocumentId" /> </EndProperty> </AssociationSetMapping> I then get: Error 3021: Problem in Mapping Fragment starting at line 675: Each of the following columns in table AssignedClaims is mapped to multiple conceptual side properties: AssignedClaims.PK_DocumentId is mapped to <AssignedClaimDW_WF_ClaimInfo.DW_WF_ClaimInfo.DOCUMENT, AssignedClaimDW_WF_ClaimInfo.AssignedClaim.PK_DocumentId> What am I not getting?

  Read the article

• Databind gridview with LINQ

  - by Anders Svensson

  I have two database tables, one for Users of a web site, containing the fields "UserID", "Name" and the foreign key "PageID". And the other with the fields "PageID" (here the primary key), and "Url". I want to be able to show the data in a gridview with data from both tables, and I'd like to do it with databinding in the aspx page. I'm not sure how to do this, though, and I can't find any good examples of this particular situation. Here's what I have so far: <%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="LinqBinding._Default" %> <asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent"> </asp:Content> <asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent"> <h2> Testing LINQ </h2> <asp:GridView ID="GridView1" runat="server" DataSourceID="LinqDataSourceUsers" AutoGenerateColumns="false"> <Columns> <asp:CommandField ShowSelectButton="True" /> <asp:BoundField DataField="UserID" HeaderText="UserID" /> <asp:BoundField DataField="Name" HeaderText="Name" /> <asp:BoundField DataField="PageID" HeaderText="PageID" /> <asp:TemplateField HeaderText="Pages"> <ItemTemplate <asp:DropDownList ID="DropDownList1" DataSourceID="LinqDataSourcePages" SelectedValue='<%#Bind("PageID") %>' DataTextField="Url" DataValueField="PageID" runat="server"> </asp:DropDownList> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:LinqDataSource ID="LinqDataSourcePages" runat="server" ContextTypeName="LinqBinding.UserDataContext" EntityTypeName="" TableName="Pages"> </asp:LinqDataSource> <asp:LinqDataSource ID="LinqDataSourceUsers" runat="server" ContextTypeName="LinqBinding.UserDataContext" EntityTypeName="" TableName="Users"> </asp:LinqDataSource> </asp:Content> But this only works in so far as it gets the user table into the gridview (that's not a problem), and I get the page data into the dropdown, but here's the problem: I of course get ALL the page data in there, not just the pages for each user on each row. So how do I put some sort of "where" constraint on dropdown for each row to only show the pages for the user in that row? (Also, to be honest I'm not sure I'm getting the foreign key relationship right, because I'm not too used to working with relationships). EDIT: I think I have set up the relationship incorrectly. I keep getting the message that "Pages" doesn't exist as a property on the User object. And I guess it can't since the relationship right now is one way. So I tried to create a many-to-many relationship. Again, my database knowledge is a bit limited, but I added a so called "junction table" with the fields UserID and PageID, same as the other tables' primary keys. I wasn't able to make both of these primary keys in the junction table though (which it looked like some people had in examples I've seen...but since it wasn't possible I guessed they shouldn't be). Anyway, I created a relationship from each table and created new LINQ classes from that. But then what do I do? I set the junction table as the Linq data source, since I guessed I had to do this to access both tables, but that doesn't work. Then it complains there is no Name property on that object. So how do I access the related tables? Here's what I have now with the many-to-many relationship: <%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ManyToMany._Default" %> <asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent"> </asp:Content> <asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent"> <h2> Many to many LINQ </h2> <asp:GridView ID="GridView1" runat="server" DataSourceID="LinqDataSource1" AutoGenerateColumns="false"> <Columns> <asp:CommandField ShowSelectButton="True" /> <asp:BoundField DataField="UserID" HeaderText="UserID" /> <asp:BoundField DataField="Name" HeaderText="Name" /> <asp:BoundField DataField="PageID" HeaderText="PageID" /> <asp:TemplateField HeaderText="Pages"> <ItemTemplate> <asp:DropDownList ID="DropDownList1" DataSource='<%#Eval("Pages") %>' SelectedValue='<%#Bind("PageID") %>' DataTextField="Url" DataValueField="PageID" runat="server"> </asp:DropDownList> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:LinqDataSource ID="LinqDataSource1" runat="server" ContextTypeName="ManyToMany.UserPageDataContext" EntityTypeName="" TableName="UserPages"> </asp:LinqDataSource> </asp:Content>

  Read the article

• Drawing a dashed line across the tops of Flex Column Chart

  - by Anoop

  Hi all, Please find the below code <?xml version="1.0" encoding="utf-8"?> ]; ]]> This code is drawing a colum chart with two columns and drawing a line across the top of both columns. I have two requirements : the line need to be dashed as of now the line starts from top right corner of the first column to the same corner of the second column. How can i shift the line to the left, so that it starts from center of first column to center of second column. Regards, PK

  Read the article

• What is syncobj in SQL Server

  - by hgulyan

  Hi. I run this script to search particular text in sys.columns and I get a lot of "dbo.syncobj_0x3934443438443332" this kind of result. SELECT c.name, s.name + '.' + o.name FROM sys.columns c INNER JOIN sys.objects o ON c.object_id=o.object_id INNER JOIN sys.schemas s ON o.schema_id=s.schema_id WHERE c.name LIKE '%text%' If I get it right, they are replication objects. Is it so? Can i just throw them away from my query just like o.name NOT LIKE '%syncobj%' or there's another way? Thank you.

  Read the article

• Are there alternatives to UltraWinGrid (for C#)?

  - by MPelletier

  I've been using the Infragistics UltraWinGrid for a while in a C# project, and while it's very spiffy, it is sometimes a bit heavy to run (and editing it in Visual Studio can be hazardous). I'm looking for a lighter alternative. Looks (always a big plus with Infragistics) are not as important as functionality. Namely, I'm looking for a beefed up DataGridView which: has data binding (duh!) has lock control over editing allows for sub-tables on opening a row can have multiple headers to group columns together (say header 1 is composed of "Group A" and "Group B", and header 2 has various columns under each group) has sorting by column (and can sort numbers properly, even if it does not have a stock method at first) has filtering by column (perhaps the most demanding spec) à la UltraWinGrid/Excel (text field with a way to specify if the filter is equal, not equal, greater, lesser, starts with, ends with, etc). supports check box, text box or data bound list/combo box cells allows cells to be merged (not the control cells of course!) can have events bound to each row (say double click)

  Read the article

• Replacement relevance sorting for MySQL fulltext in InnoDB tables?

  - by Giles Smith

  I am using MySQL InnoDB and want to do fulltext style searches on certain columns. What is the best way of sorting the results in order of relevance? I am trying to do something like: SELECT columns, (LENGTH(column_name) - LENGTH(REPLACE(column_name, '%searchterm%', ''))) AS score FROM table WHERE column_name LIKE '%searchterm%' ORDER BY score However this will become quite complicated once I start searching more than 1 column or using more than one keyword. I already have several joins happening so have simplified the query above. Can anyone suggest a better way? I don't want to use any 3rd party software like Sphinx etc

  Read the article

< Previous Page | 75 76 77 78 79 80 81 82 83 84 85 86  | Next Page >