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")

View 1 Replies


ADVERTISEMENT

Exporting Data With Dynamic Fields

Jan 12, 2005

Hi,

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.

View 2 Replies View Related

Dynamic Data Set Based On Calculated Fields

Mar 26, 2008



Hi,

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:


=iif(Parameters!BorC.Value, First(Fields!MyField.Value, "dataset_B"), First(Fields!MyField.Value, "dataset_C"))

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!

Thanks.

View 1 Replies View Related

Problem Querying 2 Fields

Feb 7, 2006

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..

Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'TERM Glossary.LIKE '%c%' Visible= True'.

I looked around and found some info on using UNION ALL but i get the same error. So, both the below queries give me an error.

PHP Code:




 SELECT * FROM Glossary 
WHERE AKNOWAS LIKE '%c%' OR
 TERM LIKE '%c%' Visible= True ORDER BY TERM ASC 








PHP Code:




 SELECT * FROM Glossary WHERE 
Glossary.AKNOWAS LIKE '%c%' 
UNION ALL 
SELECT * FROM Glossary 
WHERE TERM LIKE '%c%' Visible= True ORDER BY TERM ASC 

View 2 Replies View Related

Querying The Size Of Table Fields?

Jun 5, 2008

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);
 
Thanks for the help! 

View 4 Replies View Related

Querying Large Text Fields Resulting In Timeout Error

May 16, 2007

Hello:

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.

Rgds
pooja

View 3 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

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.

View 4 Replies View Related

Dynamic Fields In SQL

Dec 31, 2003

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 :)

View 2 Replies View Related

Dynamic Table Name And Fields

Jan 13, 2007

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

View 3 Replies View Related

Dynamic Cursor/ Dynamic SQL Statement

Oct 24, 2004

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 ':'.

View 2 Replies View Related

Subreports: Parameter Value Dropdown Shows Sum And Count Fields But Not The Actual Data Fields.

Jan 28, 2008


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.

Thanks,
Sirisha

View 3 Replies View Related

Dynamic Filedlength Of Varchar Fields In Source

Nov 21, 2006

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...

Regards Andreas

View 3 Replies View Related

Reporting Services :: Dynamic Fields In SSRS

May 21, 2015

I have a SP like this:

ALTER PROCEDURE [dbo].[ReportGateWay]
(
@ISO bigint= 0,
@Gateway bigint= 0
)
AS
BEGIN
DECLARE @SQL nvarchar(max)

[Code] ....

I need to invoke this in SSRS. The problem is that when on creating dataset for this, I get an error which read:

You must have atleast one field for your dataset

What can be done in this case?

Here is the SQL Fiddle: [URL] ....

View 16 Replies View Related

Querying Hierarchies Of Data

Jan 24, 2004

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:

parent | child

1 | 2
2 | 3
2 | 4
3 | 6
3 | 7
4 | 8
5 | 9
8 | 10
8 | 11

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.

Thanks.

Jon

View 20 Replies View Related

Data Errors Querying To .xls

Jun 29, 2007

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$]'
)

Thanks a bunch for your time,

Chris

View 2 Replies View Related

Querying Data File Name

Aug 20, 2007

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?

View 3 Replies View Related

Update Fields With Data From Other Fields In Same Row

Jun 30, 2000

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.

View 2 Replies View Related

Statement To Identify Certain Fields

Sep 19, 2013

I am extract data from one of our Sql servers with the code below

Code:
Select SectDesc, PrGDesc, 'Product Description'=SetDescF, ProdSet, ProdSect, SectChar, Prdct.ProdGrp, OrigPr

From Prdct

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...

View 6 Replies View Related

Help With SQL If Statement And Adding Fields Together

Jul 23, 2005

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.

View 2 Replies View Related

Data Retrieving Or Querying Problem

May 10, 2007

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!
 
Regards,
Ivan
 
 

View 2 Replies View Related

Looping And Querying Data From XML Clob

Oct 5, 2013

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.

View 2 Replies View Related

Querying Data From Multiple Views

Jul 23, 2005

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

View 1 Replies View Related

Can&#39;t Query More Than 15 Fields In A Select Statement

Jul 14, 1999

An ADODB error spawn everytime I query more than 15 fields in a specific SQL Table. My sql statement is something like this:

set rs=server.createobject("ADODB.Recordset")
rs.open "select * from mytbl",application("mycon"),1,3

And the error is something like this:

"you can't query more than the maximum field "

View 1 Replies View Related

Convert Fields In Select Statement

Oct 31, 2006

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

View 4 Replies View Related

Excluding Fields In SELECT Statement

Oct 23, 2005

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

View 8 Replies View Related

SELECT Statement For Fields That Are Blank?

Jan 9, 2008

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.

Here is my code:





Code Block

try
{
string conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Environment.CurrentDirectory + @"MyDB.accdb;Jet OLEDB:Database Password=MyPassword;";

// 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
{
}

Thank you again for your help.

View 8 Replies View Related

SQL Server 2008 :: Querying Last Quarter Data

Feb 26, 2015

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.

View 1 Replies View Related

Design For Storing And Querying Historical Data

Aug 2, 2007

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?

Thanks.

View 5 Replies View Related

Transact SQL :: Querying Data With Latest Date

Jun 24, 2015

Below is the table information. I want the Code information with latest EDate only.

CREATE TABLE Test
(
EDate Datetime,
Code varchar(255),
Cdate int,
Price int
);

[Code] ....

Expected output :
 
2015-06-23 00:00:00.000 CL 20150701 73
2011-04-08 00:00:00.000 XP 20110501 37
2015-06-23 00:00:00.000 HO 20150701 22

View 13 Replies View Related

Data Warehousing :: Querying In Fact Table

May 2, 2015

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?

1. XML
2. Spatial
3. Clustered
4. Non-Clustered

View 2 Replies View Related

Dynamic SQL Statement

Apr 14, 2008

 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.

View 5 Replies View Related

Dynamic Sql Statement

Sep 30, 2003

I need to create a where clause dynamically reading the values from a temp table.

Example:

select * from #tmp_keyword

k_id keyword
1 like "%DBA%"
2 like "%MSSQL%"
3 like

View 1 Replies View Related

Dynamic SQL Statement Help

Apr 25, 2005

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

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved