SQL Statement For Querying Data With Dynamic Fields
Sep 19, 2006
I am working on a project in which a customer wants to be able to list and search their inventory and display the items in a table/grid on a web page.
Each item in their inventory has a set of properties - for example, manufacturer, price, serial number, name, etc. The complicated part is that they want an admin to be able to modify/add/delete the set of properties. So for example, they could add the attribute "size."
Given that, I think what is needed is 3 tables - one to store the set of properties schema, one for the items, and one to store the actual properties for each item. I know i COULD use alter table statements to add and delete columns, but that doesn't seem like the "right" solution.
I would like to be able to write a query such that each row returns the item and all its properties - then i can easily bind to a datagrid. However, what would the query be to do this? I also need to be able to allow the customer to query for items with certain properties - i imagine the sql for that to be similar to this:SELECT * FROM items d WHERE d.Id IN(SELECT a.ItemId FROM attributes a WHERE a.Name = "Manufacturer" AND a.Value = "Samsung")
AND d.Id IN(SELECT a.ItemId FROM attributes a WHERE a.Name = "SerialNumber" AND a.Value = "3223")
I have one Query Builder application where user creates queries and generates output with required fields.Then user can export the same to excel (Using farPointSpread properties) But if no. of records goes beyond 10K it goes too slow or even user machine hangs up. I want to give proper export facility. Can it be given using DTS?
I tried DTS (.vbs) file. but for random field selection it did not work.
I have three data sets. Data Set A is bound to my table in my report. There are also Data Sets B and C. All three data sets have the identical columns. I have setup data set A so that all of its fields are "Calculated". The calculation for each field will either pick the field from data set B or data set C based upon a parameter called BorC. So the calcualted fields in data set A look like the following:
Problem is when I run the report I get internal error. Is it possible to populate one data set from the fields of another data set? If so is there some trick to doing this? Stripping this example down to one column in each of the data sets, and then just directly setting data set A from either B or C still gives me the same error. SO I set the one field in data set A to the following and it still doesn't work:
=First(Fields!MyField.Value, "dataset_B")
OR
=First(Fields!MyField.Value, "dataset_C")
This leads me to believe there is some fundamental problem with using another data set in a Calculated field. Since I think I have done this before I am convinced I am doing sommething wrong.
Any advice you have for me would be greatly appreciated!
I just want to know if the searched for item appears in either Field in my table. The search works when i just use 1 Field, but i always get the below error whenever i add the second field to the query..
Hello I am writing a stored procedure and instead of "hard coding" the size of the procedure parameters I would like to set them to the size of fields in my database. for example instead of declaring a parameter like this: @ProjectDescription varchar(500)
I would like to do something like this:
@ProjectDescription varchar(Select size of "ProjectDescription" From Projects);
I have designed a CV database with complete CV stored in a TEXT field. There is a keyword search which queries the TEXT field also. The query conditions are defined in T-SQL submitted through an ASP page. There is about 20,000 records now. Now while querying the database for keyword search I am receiving time out errors. Is there any solution other than Index server to rectify this situation. How can I speed up the query execution time. Please advise.
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
I want to make my table have dynamic fields. For example if my table includes 2 fields. ID & name. I want the user to be able to add another field (if he needs) with the datatype he determines and the field name. I want then to alter the table and add that field.ok using alter statement is not required cause it mat cause lose of data if error occurs in the middle of the trnsaction. any suggessions??? Thnak :)
I need to get the field values of a table (name will be dynamic).Then assign those values to properties in a class.Let's say I will get the table name dynamically.dim tblName as string = "tablea"The 2 tables can each have 25 fields or so.I need a way to select the amt and email field values from tblName. Without saying "select job_amt, job_email from ...Is there someway to get the values based on the column name. So if the column name has amt and email, then give me those values.Maybe loop through the datatable - then for each column -- if col.ColumnName.IndexOf("Amt") = 0 or col.ColumnName.IndexOf("email") = 0 thenthen drop that column from the datatable.ex of table structure<u>tablea</u>job_idjob_amtjob_email<u>tableb</u>dance_idamt_dancedance_email
I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement.
Using the examples given in Books Online returns compilation errors. See below.
Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement?
James
-- SQL ---------------
EXEC SQL BEGIN DECLARE SECTION; char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?"; char szLastName[] = "White"; char szFirstName[30]; EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE author_cursor CURSOR FOR select_statement;
EXEC SQL PREPARE select_statement FROM :szCommand;
EXEC SQL OPEN author_cursor USING :szLastName; EXEC SQL FETCH author_cursor INTO :szFirstName;
--Error-------------------- Server: Msg 170, Level 15, State 1, Line 23 Line 23: Incorrect syntax near ';'. Server: Msg 1038, Level 15, State 1, Line 24 Cannot use empty object or column names. Use a single space if necessary. Server: Msg 1038, Level 15, State 1, Line 25 Cannot use empty object or column names. Use a single space if necessary. Server: Msg 170, Level 15, State 1, Line 27 Line 27: Incorrect syntax near ';'. Server: Msg 170, Level 15, State 1, Line 30 Line 30: Incorrect syntax near 'select_statement'. Server: Msg 170, Level 15, State 1, Line 33 Line 33: Incorrect syntax near 'select_statement'. Server: Msg 102, Level 15, State 1, Line 35 Incorrect syntax near 'author_cursor'. Server: Msg 170, Level 15, State 1, Line 36 Line 36: Incorrect syntax near ':'.
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
I'm using SSIS to import data from a table (SQL) containing varchar fields. The problem is, that those varchar fields are changing over time (sometimes shrinking and sometimes expanding). I.e. from varchar(16) to varchar(20).
When I create my SSIS package, the package seem to store information about the length of each source-field. At runtime, if the field-length is larger then what the package expects an error is thown.
Is there anyway around this problem?
Oh, yeah... My destination fields are a lot wider then the source fields, so the problem is not that the varchar values doesn't fit in my destination table, but that the package expects the source to be smaller...
Hey all. I have a query where I am basically querying an organizational chart. The table storing this information is basically a two column table of parent/child pairs. So you might have:
Querying this table should return all child columns that flow up to the top, so querying 1 would return 1, 2, 3, 4, 6, 7, 8, 10, 11.
I used a sample from MSDN (http://www.sqljunkies.com/Article/D7CAED46-CCAC-4FF7-B528-B2E9A274B71C.scuk) that does just this, except that when querying a value that returns more than 1000 records - I am experiencing way too long response times. The MSDN sample uses temp tables and inserts values into a temp table as it moves through the records and finds a match.
Anyone have an ideas on another way to accomplish the same thing? This is an important part of my security model as users that login should only have access to data that falls within their parent/child heirarchy.
Hi all, I've just started playing with data importing from Excel.
Since I know how to do linked servers, that's where I started. I created a link, querried the spreadsheet and got some strange results where data appearing in the spreadsheet returns as NULL in QA.
So, I noticed that I could use OPENDATASOURCE and OPENROWSET and tried each of them. They're returning the same data (in different column orders) also with strangely intermittant NULL values instead of the data that I see in the source spreadsheet.
I bet that this is something easy once you know what's going on. Anyone know what's going on?
Not that I think it'll matter, but the queries I'm using for these three cases are:
select * from EXCEL_A...EPIQ202$ where SUBSCRIBER_ID = '50664582103'
select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=e:EPIQ202.xls;Extended Properties=Excel 8.0')...EPIQ202$
select* from openrowset( 'Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=E:EPIQ202.xls', 'SELECT * FROM [EPIQ202$]' )
Is there a way to query the name of the physical data file and/or log file associated with a given database. For example if I were to do an alter database: ALTER DATABASE X MODIFY FILE (NAME = xxxxx, size=10MB)
Is there something I can type in to extract the file name associated with 'X' or do I have to know it implicitly?
Pardon me if this question is too elementary. I am trying to create a trigger that will cause certain datafields to be updated with values from other data fields in the same row when a certain column, created specifically to fire the trigger, is updated. The purpose of this is to reduce data entry by field personnel.I think I have the create trigger statement correct, but I'm a little confused on the update statement.
In a nutshell, how can I write something like: UPDATE "TABLENAME" SET DATAFIELD1 = DATAFIELD2 WHERE RECORDNUMBER = (THE SAME RECORD NUMBER)
I do know that I have to ensure that sp_dboption Recursive Triggers value is set to false, thanks.
INNER JOIN qryPRDGroupDets on Prdct.ProdGrp=qryPRDGroupDets.PGCode
where supersed ='' And OrigPr Not Like '9%' And OrigPr Not Like '%MDM%' And LISTPR1>'0' And STANCOST>'0'
Which works fine, but what I need to do is reference the "OrigPr" field and mark it as "valid" or "Invalid", the "OrigPr" the field contains alpha numeric data e.g. A000, A001, A002 - ZZ99 and so on, amongst all of the potential different types of codes we have codes that end in treble Zero (0) e.g. A000 which are valid, but if they end in double 00 e.g. AA00 then this is invalid, the problem I have is I can't just add
Code: 'Marker'= Case When Right(OrigPr, 2) = '00' Then 'Invalid' ELSE 'Valid' End
For it will mark the A000 as invalid, is there a way of getting around this...
I have a query that I need a hand on. I am trying to add togther somefiends based on values of another.What I would like to add a billing total by saying more or less thefollowing:SELECT labor_hours, labor_cost, expidite_fee, flat_rate,include_repair_cost, include_cal, include_flat_rate, include_parts,cur_bill,(labor_hours * labor_cost) AS labor_total,(ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER BYdateCAL DESC),0)) AS cal_total,(ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROMrepair_partsID WHERE orderID=79559),0) +ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROMmisc_part_assocID WHERE orderID=79559),0)) AS parts_total,((labor_hours * labor_cost) + expidite_fee + flat_rate +ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER BYdateCAL DESC),0) +ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROMrepair_partsID WHERE orderID=79559),0) +ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROMmisc_part_assocID WHERE orderID=79559),0)) AS actual_total,(expidite_feeIF include_repair_cost = 1+ (labor_hours * labor_cost)IF include_flat_rate = 1+ flat_rateIF include_cal = 1+ ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDERBY dateCAL DESC),0)IF include_parts = 1+ ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROMrepair_partsID WHERE orderID=79559),0) +ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROMmisc_part_assocID WHERE orderID=79559),0)) AS billing_totalFROM view_inventoryWHERE orderID=79559I know the IF part is whacked, that's where I need the help. Is thistype of thing even possible? Or even efficent? Is it wise to subqueryfor totals (not like I have a choice based on the applicationrequirements)? help.
Hi, I am using visual web developer 2005 express edition and Microsoft SQL 2005 to develop a fast-food ordering website and I am having trouble in retrieving data from a database table into a form. Can some one please teach me the way to write the syntax for retrieving or selecting a inserted value from the database? I have only know the syntax to insert data value from a form which is something like this: 'Create a New Connection to our daabase Dim test As SqlDataSource = New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("Connectionstring1").ToString 'This is the SQL Insert Command test.InsertCommand = "Insert into Customer([Initial],[Correspondent_Name],[Correspondent_No],[Payment_Type]) VALUES (@Initial,@Correspondent_Name,@Correspondent_No,@Payment_Type)" 'Each of this insert a value into the appropriate command test.InsertParameters.Add("Initial", DropDownList4.SelectedValue) 'This is the selected "Initial" DropDownList value test.InsertParameters.Add("Correspondent_Name", TextBox10.Text) 'This is the Correspondent_Name value test.InsertParameters.Add("Correspondent_No", TextBox3.Text) 'This is correspondent_no value test.InsertParameters.Add("Payment_Type", "Pay upon delivery") 'This indicates that the payment is made with credit card test.Insert() But I have no idea the syntax to retrieve a data. Please help me with this matter as it is important to me. Thanks in advance!
Using a code snippet borrowed from a co-worker, I have put together a query that, among other things, pulls a list value out of an xml clob field and displays it in the query results. My query as it stands right now is below, followed by a snippet from the xml clob that I am pulling from.
select * from (Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date, to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag from ws_transactions Where Wtr_Service_Tag In ('20458749610')
[Code] ....
This query is only able to pull the first value in the list.
I have two questions...
[1]How can I edit this query to pull all of the list items when there are more than 1? I have another field, in a separate table, that I can pull from to get that number.
[2]This one may be more complex. As currently written, the query pulls a fixed number of characters from the xml clob and either returns not enough data, or too much because the values I need to pull could be of varying lengths. I have no way to query what those lengths might be.
Hello,I am relatively new to doing non-trivial SQL queries.I have to get data out of 8 diff views based on a parameter Name.There is a view having name-ssn pairs. All other views have SSN field.For a person there MAY NOT be data in all the views.I have to populate data into diff tables in a Report from differentviews.I would like to know what is the best way to approach it.So far I was trying an Inner join from the Name-ssn vies to all otherviews based on the SSN and test for the name field with the inputparameter.I am thinking there will be problem of Cross join if I dont have datain all views about a person.Or the best way is to write query for each view and have all of them ina stored procedure ?Any help will be appreciatedThanksBofo
The data I have has a 1 in for each Yes answer and a 2 for each no answer. I just want the select statement to show the word yes when there's a 1 and the word no when there's a 2. I don't need to update or change the database. Could anyone lead me in the right direction here? Thanks
Hi,Is there a way to exclude fields in a query other than just includingthe ones you want. If there are 20 fields and you want to see all but3, it would be a lot easier to exclude the 3.Thanks
I thought this was working, but apparently it was not. I was wondering how I would create a SELECT Statement for values that are blank (Equal to ""). I really could have swore that this was working, but I guess it wasn't.
// create an open the connection OleDbConnection conn = new OleDbConnection(conString); OleDbCommand command = new OleDbCommand(); command = conn.CreateCommand();
// create the DataSet DataSet ds = new DataSet();
// run the query command.CommandText = "SELECT ID AS [#], Company AS [Company], FName AS [First Name], LName AS [Last Name], Type AS [Type] FROM Table1 WHERE Tags = @P0;"; OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter = new OleDbDataAdapter(command); command.Parameters.Add("@P0", OleDbType.VarChar).Value = ""; adapter.Fill(ds);
// close the connection conn.Close();
bindingSource1.DataSource = ds.Tables[0];
DataGridView.DataSource = bindingSource1;
// set the size of the dataGridView Columns this.DataGridView.Columns[0].Visible = false; this.DataGridView.Columns[1].Width = 234; this.DataGridView.Columns[2].Width = 50; this.DataGridView.Columns[3].Width = 50; this.DataGridView.Columns[4].Width = 75;
//Sort on the Title Column DataGridViewColumn sortColumn = DataGridView.Columns[1]; ListSortDirection direction; direction = ListSortDirection.Ascending; DataGridView.Sort(sortColumn, direction);
//Set the Selected Property of the First Row to False DataGridView.Rows[0].Selected = false; } catch { }
We have this query that pulls number of days worked from the current Quarter to Date.
(SELECT COUNT(DISTINCT daysworked) AS 'Days Worked' FROM (SELECT CAST(DATEPART(MM, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(DD, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(YYYY, DATEADD(HOUR, -8,ActualEnd)) AS VARCHAR) AS daysworked, ActivityId AS totalcalls FROM PhoneCall AS p WHERE (DATEPART(QUARTER, DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(QUARTER, DATEADD(QUARTER, -1, GETDATE()))) AND (DATEPART(YEAR, DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(YEAR, DATEADD(QUARTER, -1, GETDATE()))) AND (OwnerId = x.SystemUserId)) AS tb) AS [Days Worked],
I need changing it to bring up LAST Quarter's data.
I am working on a project, which involves displaying trends of certain aggregate values over time. For example, suppose we want to display how the number of active and inactive users changed over time.
One issue is how to store historical data. First of all, should I create a separate database for each historical snapshot or should I use one database for all snapshots? Second, our database size is a couple of gigabytes and replicating the entire database on a daily basis is not feasible. An alternative solution is to back up aggregate values, but how do I back up results of aggregate queries, where the user can specify a date range in the WHERE-clause? Another solution is to create fact tables from our relational schema and back those up.
Another issue is how to query historical data. Using multiple databases to store historical snapshots makes it harder to query.
As you can see there are several design alternatives and I would like to know how this sort of problem is generally solved in the industry. Does SQL Server provide any support for solving this problem?
I have a Fact Table with a ID column as Primary key and clustered index is created. And also I have 4 dimensions FK's of data type INTEGER. And finally, I have one aggregation measure in the Fact Table.
Now, my situation is How can I improve the speed of querying the fact table by creating any of the below indexes?
I am trying to write a dynamic sql insert statement in c#, but given this is my first run at creating sql statements dynamically I am having issues. My reason for creating the sql statement dynamically is because I do not want to insert any items that are null. Will I have to have a separate string for each section of the statement? For example:sql1 = "Insert Into Table [test]";sql += "(Column1, Column2, Column3," ;sql2 = "Values" ;sql2 += "(field1, field2, field3"; and then a third section if I were to use parameters. My thinking here is how do you append to a string in numerous locations? I will populate the sql strings first with those columns and fields that I know will not be null, and then use if statements to add to each sql string if the field in question != null. in other words, little help.
Hi, I try to get the dynamic insert statement script.
See the below statement I'm getting syntax error. How can change this right way script?.
select * into pubs.dbo.employee_temp from pubs.dbo.employee where emP_id<>emP_id
Declare @cmd varchar(8000)
set @cmd =N'insert into employee_temp(emp_id,fname,minit,lname)'+char(13)+ 'values '+'('+select ''''+emp_id+''''+','+''''+fname+''''+','+''''+mini t+''''+','+''''+lname+''''+')' from pubs.dbo.employee EXECUTE sp_executesql @cmd
