Query/View Question

Apr 18, 2006


I am trying to create a view that returns data from three tables and can't seem to get it to return the data that I want. I am no SQL expert, so hopefully someone can give me some insight into what I need to do.

The tables are basically set up like this:





TABLE 1


PrimaryKey


Textfield1


Textfield2


Textfield3





TABLE 2


PrimaryKey


Table1ForeignKey


Table3ForeignKey


Textfield1





TABLE 3


PrimaryKey


Textfield1


Textfield2


Textfield3


Table 1 and Table 3 are each joined to Table 2 on their respective Primary/Foreign Key fields.

I want the view to return all of the records from Table 1, even if there are no matching records in Table 2.

From Table 2 I only want the latest record for each record in Table 1.

I want the view to look something like this:






Table 1
PrimaryKey

Table1
Textfield1

Table2
Textfield

Table3
Textfield

In other words, I want to return one record in the view for each record in table 1, and I want the data from table 2 in each of those records to represent the last record added to table 2.

Can anyone enlighten me on the query necessary to get this view?

View 5 Replies


ADVERTISEMENT

Different Query Plans For View And View Definition Statement

Mar 9, 2006

I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?

View 10 Replies View Related

Query View Vs Query Tables Directly

May 19, 2008

Greetings,

I recently started working with a database that uses several views, none of which are indexed. I've compared the execution plans of querying against the view versus querying against the tables and as best I can tell from my limited knowledge the two seem to perform equally. It seems to me that having the view is just one more thing I need to keep track of.

I've done some google searches but haven't found anything that really tells me which performs better, querying the view or the tables directly. Generally speaking which is better?

Thanks in advance for your replies.

View 3 Replies View Related

Query For A View

Jun 28, 2005

Hello,I have a View called View1 with the field ID, F1, F2, F3. Now I need to check if (Total F1 < Total F2 + Total F3) per ID, if yes fetch all records (so if condition matches, I need to bring rows, not only totals) how can I write my view query to handle this?Thanks,

View 1 Replies View Related

Help With A Query/view

Apr 25, 2005

Hi all!

I have a table that looks like this:

m_id | s_id1 | s_id2 | dt
m1 | 1 | 2 | 2001-01-01
m2 | 3 | 2 | 2001-01-02
m3 | 4 | 1 | 2001-01-03
m4 | 2 | 3 | 2001-01-04
m5 | 1 | 2 | 2001-01-05
m6 | 5 | 3 | 2001-01-06
...

I need to create a view that displays ONE m_id for every s_id, and that m_id should be the latest one...

ie:
s_id | m_id
1 | m5
2 | m5
3 | m6
4 | m3
5 | m6

I've been trying hard for a while, but I simply can't get it do display only one row per s_id...

any helponthis one would be greatly apreciated...

cheers.

View 3 Replies View Related

Query View

Oct 5, 2006

Hi all, I ran a query, pasting in my sql text. I received this error.

Msg 8163, Level 16, State 3, Procedure All_Participants, Line 7

The text, ntext, or image data type cannot be selected as DISTINCT.

Msg 208, Level 16, State 1, Procedure Distinct_Proposals, Line 7

Invalid object name 'Proposal_Details'.

But, I cannot see the msg numbers in my script. How do I find Msg 8163? Excuse the ignorance....only my second day using this :P

Thanks,

Steve

View 3 Replies View Related

How To Query A View From The Master DB?

Apr 7, 2004

Is there a way to access your views in x database from the master database?

I'm getting this error

Invalid object name 'v_StatisticsScalars'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'v_StatisticsScalars'.

View 4 Replies View Related

Query Problem --with A View

Feb 17, 2006

I have a View which does what I need it too-select distinct d.deptid, d.[description], d.supervisor, d.approvedby, case when c1.cnt = c2.cnt then 'reviews are complete' else 'reviews still in progress' end as deptstatusFrom department dLeft join (Select Count(*) cnt, deptid from employee group by deptid) c1 on (c1.deptid = d.deptid)Left join (Select Count(*) cnt, deptid from employeeeval join employee on (employeeeval.employeeid = employee.employeeid) group by deptid) c2 on (c2.deptid = d.deptid)Brings back data as follows:Cast -  Cast - 00999 - 09246 - reviews still in progressCMP- Copper Metal Products - 03315 - 09246 - reviews are completeCNS- Copper Narrow Strip - 07530 - 09246 -  reviews still in progressCRW- Copper Rod and Wire - 01950 - 09246 - reviews still in progressSo I did the following: select e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName, CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status, d.deptstatusfrom vw_DeptStatus d Left OUTER JOIN Employee e ON e.deptid = d.deptid LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u ON u.Department = e.DeptID WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') -- and (ev.PersonalScore is null)ORDER BY e.DeptID, e.LastNameNow this isn't bringing back what I want--If run the query w/out joining it up w/the view it brings back the following:
Cast - Atkins, Carl - 09935 - Chris Burke - Not StartedCast - Bridges, James - 09929 - Chris Burke - In Progress CNS - Cunningham, Kenton - 02100 - Kahle Rummer - Not StartedCNS - Mitchell, Bill - 06200 - Kahle Rummer - In ProgressNow what I really need it to do is (below are the results from my View joined in with my query) -- return all the dept's--regardless if they are finished.  If they are complete I only need ONE row, not all the rows for that dept. (highlighted in RED) I added these rows in-- Doesn't have to be NULL but maybe a blank string.Cast - Charlton, Maurice -  01313 - Chris Burke - In Progress - reviews still in progressCast  - Dorsey, Steve - 02455 - Chris Burke - In Progress  - reviews still in progressCMP - NULL - NULL - Fred Grove - NULL - reviews are completeCNS  - Bennett, Mark -  09911 - Kahle Rummer -  In Progress  - reviews still in progressCNS - Buckingham, Mark - 00964 - Kahle Rummer  - In Progress - reviews still in progressCRW - Eubanks, Kellie - 07599 - Rick Cramer - In Progress - reviews still in progressCRW - Luikart, Tyler- 09908 - Rick Cramer - In Progress - reviews still in progressMicroMll - NULL - NULL - Tim Cross - NULL - reviews are completeI hope this makes sense to someone -- if you have any questions just ask me.  Another note I need to bring this back in a DS --for Crystal Reports.  So the outline looks like this:
Dept ID- Supervisor - Reviews still in Progress   Any unfinished Reviews for that dept
Dept ID- Supervisor- Reviews Complete   No data shown since COMPELTEDept ID- Supervisor - Reviews still in Progress   Any unfinished Reviews for that dept

View 3 Replies View Related

Query Results To A View

Nov 15, 2004

I have a table in a database that has very old and not very relational and I want to create a quick view to show the information in a better way. Let's say that the table has 4 fields : id , child1, child2, child3. I want to create a view from this table that will show two fields : id and child. So, my table currently looks like this:

id child1 child2 child3

1 sam bob chris

and i would like it like this......

id child

1 sam

1 bob

1 chris

Can anybody help me? Thanks in advance,

Bob

View 3 Replies View Related

View Data In Query

Apr 9, 2008

Hi,
I have an accounting table with 5 year of data for an account, the table headers look as following

Acctno___Year___db_1,db_2,db,db_3,db4,….db_12,Cr_1,cr_2,cr_3…,cr-12

I want to with select statement to convert the data to query the header must be the following:-

Years1___Year2___year3___year4____year5
Db_1
Db_2
Db_3
.
.
.

Thanks for any help

View 3 Replies View Related

Converting Query To A View?

Jan 23, 2015

I getting an error when trying to convert the following into a view. I can't take the credit for the code as I copied it from with a program.

Create view vwMAW_KnowledgeDB AS
Select * from don0001
left join (select donclass.donor from donclass where classification = 9 and code in ('KB')) inc0 on inc0.donor = don0001.donor where inc0.donor is not null

quote:Error; Msg 4506, Level 16, State 1, Procedure vwMAW_KnowledgeDB, Line 2

Column names in each view or function must be unique. Column name 'donor' in view or function 'vwMAW_KnowledgeDB' is specified more than once.

View 1 Replies View Related

Urgent - View Query

May 29, 2007

Dear Friends
I have 3 form with these names :

Clients <----- Projects <------- Files

I am using this query in my [ web ] page :

select clients.id ,
(select count(*) from projects where projects.clients_id = clients.id) as TotalProjects

from clients



I want to compute SUM for all relevant files for each client's projects.
how i can add it to select select statement ??


thank you.
m.o

View 5 Replies View Related

A Query And View Of Result

Oct 8, 2007

Table 1
-------
NAME GROUPID
AAA 1
BBB 2

Table 2
-------
NAME GROUPID
CCC 1
DDD 1
EEE 2

Name & GroupID are column names.

I want to query these tables and get a result like that:

AAA CCC
DDD
BBB EEE
-----------
NOT
-----------
AAA CCC
AAA DDD
BBB EEE

Is it possible? If so, how?

Thanks in advance.

View 1 Replies View Related

SQL View / Joins Query

Jul 23, 2005

Hi,I have a view(A) and I am trying to do a join on another table (B) toinclude only rows where date values in view A is greater than in tableB. I also want the view to pick up rows in viewA based on date values.Here is what I have so far:SELECT *FROM viewA vwleft JOIN tableB tb ONvw.id = tb.id and(vw.date1 > tb.date1 orvw.date2 > tb.date2 orvw.date3 > tb.date3)WHERE vw.date4 > getdate()-1Not matter what kind of join I use I can get both the rows from theview where dateA > getdate()-1 AND where date1-3 are greate than intableB. Dates 1 - 4 seperate date fields. Could someone please tellme what I am doing wrong.Thanks.

View 3 Replies View Related

Odd Query Plan For View

Mar 28, 2006

I have a SQL 2000 table containing 2 million rows of Trade data. Hereare some of the columns:[TradeId] INT IDENTITY(1,1) -- PK, non-clustered[LoadDate] DATETIME -- clustered index[TradeDate] DATETIME -- non-clustered index[Symbol] VARCHAR(10)[Account] VARCHAR(10)[Position] INTetc..I have a view which performs a join against a security master table (togather more security data). The purpose of the view is to return allthe rows where [TradeDate] is within the last trading days.The query against the view takes over around 30 minutes. When I viewthe query plan, it is not using the index on the [TradeDate] column butis instead using the clustered index on the [LoadDate] column... Theodd thing is, the [LoadDate] column is not used anywhere in the view!For testing purposes, I decided to do a straight SELECT against thetable (minus the joins) and that one ALSO uses the clustered index scanagainst a column not referenced anywhere in the query.There is a reason why I have not posted my WHERE clause until now. Thereason is that I am doing what I think is a very inefficient clause:WHERE [TradeDate] >= fGetTradeDateFromThreeDaysAgo(GetDate())The function calculates the proper trade date based on the specifieddate (in this case, the current date). It is my understanding that thefunction will be called for all rows. (Which COULD explain theperformance issue...)However, this view has been around for ages and never before caused anysort of problems. The issue actually started the day after I had torecreate the table. (I had to recreate the table because some columnswhere added and others where renamed.)On a side note, if I replace the WHERE clause with a hard-coded date(as in 'WHERE [TradeDate] >= '20060324'), the query performs fine butSTILL uses the clustered index on the [LoadDate] column.

View 4 Replies View Related

Query To View With Many Columns

Jul 31, 2007

Hi All,
I have a simple question. If I have a view that query from joined multiple tables and return a lot of columns (more than 100). If I run a simple query just return several columns (e.g. 4-5 columns), will SQL Server query all columns first from all joined table? or can SQL Server query only necessary column from related table?


Does anyone have idea how to join table that can reflect both left and right join?
Table A Table B
Column1 Column2 Column3 Column4 Column1 Column2 Column 3 Column5
A Jan 5 xxx A Jan 1 yyy
B Feb 3 C Mar 4
B Mar 4 C Apr 3
C Apr 2 D May 2
E Mar 1

Result Table
Column1 Column2 Column3 Column4 Column 5
A Jan 6 (= 5+1) xxx yyyy
B Feb 3
B Mar 4
C Mar 4
C Apr 5 (= 2+3)
D May 2
E Mar 1

So the result table is a join on column1 and column2 (both are primary key), with column3 is a sum aggregate. Table A has additional column4 and Table B has additional column5, so quite difficult to union (In fact, there are a lot of column differences between table).

Thanks for the help.

View 3 Replies View Related

View And Query Optimization

Mar 23, 2006

I'm have made a series of tables and views to create some reports. I have included boiled-down versions of them below. The report query that I want to run (at the bottom) takes a long time to execute and sometimes times out. Creating a rank within the query is doing the most damage, but it can still take a long time to execute without it. The usage table has almost 70k records and there are over 10k users. Does anyone have suggestions for making these views/queries more efficient?

==========================

TABLE TGroupAudit
AuditID, intOldGroupID, intNewGroupID, strUserID, dtJoinDate

==========================

TABLE people
strUserID, name

==========================

TABLE usage
UsageID, strUserID, cost, seconds, gmttime, accesstype

==========================

CREATE VIEW VUsageSubtotals AS
SELECT TOP 100 PERCENT VUserGroupDates.strUserID, people.name, VUserGroupDates.intGroupID, usage.accesstype, SUM(usage.cost) AS subtotal, MONTH(usage.gmttime) AS [month], VUsageTotals.total
FROM VUserGroupDates INNER JOIN
people ON VUserGroupDates.strUserID = people.strUserID INNER JOIN
usage ON people.strUserID = usage.strUserID INNER JOIN
VUsageTotals ON VUserGroupDates.strUserID = VUsageTotals.strUserID AND
VUserGroupDates.intGroupID = VUsageTotals.intGroupID AND MONTH(usage.gmttime) = VUsageTotals.[month]
WHERE (usage.gmttime BETWEEN VUserGroupDates.dtJoinDate AND VUserGroupDates.dtLeaveDate)
GROUP BY VUserGroupDates.strUserID, VUserGroupDates.intGroupID, usage.accesstype, MONTH(usage.gmttime), VUsageTotals.total

==========================

CREATE VIEW VUsageTotals AS
SELECT VUserGroupDates.strUserID, people.name, VUserGroupDates.intGroupID, SUM(usage.seconds) AS totaltime, SUM(usage.cost) AS totalcost, MONTH(usage.gmttime) AS [month]
FROM VUserGroupDates INNER JOIN
people ON VUserGroupDates.strUserID = people.strUserID INNER JOIN
usage ON people.strUserID = usage.userid
WHERE (usage.gmttime BETWEEN VUserGroupDates.dtJoinDate AND VUserGroupDates.dtLeaveDate)
GROUP BY VUserGroupDates.strUserID, people.name, VUserGroupDates.intGroupID, MONTH(usage.gmttime)

==========================

CREATE VIEW VUserGroupDates AS
SELECT strUserID, intNewGroupID AS intGroupID, dtJoinDate,
ISNULL ((SELECT MAX(TGA2.dtJoinDate) FROM TGroupAudit TGA2
WHERE TGA2.dtJoinDate < TGA1.dtJoinDate AND TGA2.strUserID = TGA1.strUserID), GETDATE()) AS dtLeaveDate
FROM TGroupAudit TGA1

==========================

Here's a sample query that gets run for a report:

SELECT TOP 30 PERCENT WITH TIES V1.*,
(SELECT COUNT(DISTINCT V2.subtotal)
FROM VUsageSubtotals V2 WHERE V1.subtotal <= V2.subtotal
AND V1.intGroupID = V2.intGroupID
AND V2.month = 3) AS rank
FROM VUsageSubtotals V1
WHERE strUserID IN ('a1, a2, b5, b7, b9, d12')
AND V1.month = 3
AND accesstype = 'STANDARD'
GROUP BY strUserID, intGroupID, subtotal, month, total, accesstype

The purpose of the report is an accounting one. It is desired to know how much each user used each accesstype during a month. Usage is further split up by the user's groups (which may never change or could several times during a month) for billing purposes.

View 5 Replies View Related

View The SQL Query Out-put In Horizontal Format.

Jan 10, 2005

I have the following SQL query that I like to view the out put in horizontal format:

Select ID, First_name, Last_name from ABC

Instead of getting out-put like

Id First_name Last_name
1Jim Smith
2Tom Jones

I like to see the out-put like:

Id1 2
First_nameJimSmith
Last_nameTomJones

Please advice.

Thanks in advance,


Abrahim

View 1 Replies View Related

Query To Create View / Report?

Apr 27, 2012

I am running MSFT SQL 2008 with a CRM system. In that CRM system we have defined "COMMUNICATIONS" that have a number of different "TYPES" These communications are tied to Companies, which in our business is a resident of our community. The resident has the ability to request a 1 time service (TRAVEL) that would be recorded with one "COMMUNICATION" , or a reoccuring service (DAILY CARE) that would be recorded with two "COMMUNICATIONS" (Start/Stop). THe communication ID does appear to be sequential, but may not be entered sequentially.

we would like to be able to create a timeline for a specific resident around 3 specific types of transactions. (AWAY, RETURN, CS) There should be some strong predictive value to these data points.

ABSENCES
AWAY = Start of an absence from the building
RETURN = End of an absence from the building
A person during their reisdency will have multiple Absences
An absence could start on the same day a previous absence ends
Absences vary in length
CS
CS0-CS10 (each is a different TYPE) of communicatio

[code]....

Query in DESIGN View

SELECT TOP 100 PERCENT comm_trantype AS Type, comm_trandate AS Date, CmLi_Comm_CompanyID AS CompID, Comm_CommunicationId AS CommID, ROW_NUMBER()
OVER (PARTITION BY CmLi_Comm_CompanyID
ORDER BY Comm_CommunicationId, comm_trandate) AS Seq
FROM CRM_CSLDB.dbo.vCommunication

[code]....

Results from QUERY in DESIGN VIEW

TYPE DATECOMPIDCOMMIDSEQ
AWAY2011-02-24 00:00:00.00051747531
RETURN2011-03-31 00:00:00.00051747542
AWAY2011-03-28 00:00:00.00064740681
RETURN2011-04-30 00:00:00.00064752972

[code]....

RESULTS FROM QUERY in SELECT TOP 11

TypeDateCompIDCommIDSeqAwayDaysReturnDate
AWAY2011-06-20 00:00:00.00016977564182011-06-28 00:00:00.000
AWAY2011-12-23 00:00:00.00015059050441122012-04-13 00:00:00.000
AWAY2011-10-09 00:00:00.0003534839461162011-10-25 00:00:00.000
AWAY2012-01-27 00:00:00.0003983890363942012-04-30 00:00:00.000
AWAY2012-03-09 00:00:00.0004064900615202012-03-29 00:00:00.000

[code]....

QUERY in DESIGN VIEW

SELECT TOP (100) PERCENT dbo.vCommunication.comm_trantype AS csTrans, dbo.vCommunication.comm_trandate AS csTranDate,
dbo.vCommunication.CmLi_Comm_CompanyID AS CompanyID, dbo.vCommunication.Comm_CommunicationId AS Comm_ID, dbo.CSL_resident.Name,
Comm_CommunicationId AS CommID, ROW_NUMBER() OVER (PARTITION BY CmLi_Comm_CompanyID
ORDER BY Comm_CommunicationId, comm_trandate) AS Seq
FROM dbo.vCommunication INNER JOIN

[Code] .....

QUERY RESULTS IN SELECT

csTranscsTranDateCompanyIDComm_IDNameCommIDSeq
CS12009-07-27 00:00:00.0001176147Harrison Bailey 761471
CS32007-08-27 00:00:00.0002673777Dorothy Wheeler 737771
CS42011-12-02 00:00:00.0002685087Dorothy Wheeler 850872
CS52012-01-01 00:00:00.0002685446Dorothy Wheeler 854463

[Code] ....

View 12 Replies View Related

Cross Join/? Query In A View Help

Feb 4, 2004

Hey All...
Got a View question.
Have 2 tables:
#1 Currencies
|CCY_Name|CCY_Code|

#2 Rates
|CCY1|CCY2|CCY3|...etc|Active|
-> where the Columns CCY# = the Records in #1

How do I build a View to Select the ONE record in #2 where Active=Y, having the CCY_Name from #1 based on #2.CCY1 (Column NAME) = #1.CCY_Code (Record).

Thanks

robbied111

View 2 Replies View Related

Urgent !! How To See View Sql In Query Analyzer

Feb 10, 2004

I need to see the sql for a view in query analyzer.
how can i do that ??

View 2 Replies View Related

Rookie Question About Query/View

Jul 23, 2005

I have much MSACCESS expeience and am new to SQL so here goes...I am trying to create calculated fields in a view, but can't seem tomore complex calculations to work.I can create a new field which does simple arithmetic on two otherfields and rename it so it becomes part of the new view to be queriedby other views, but when I use more complicated constructs I can'tseem to get them to work.For starters:How do you create a new field that has a number based on an IFconstruct based on another field.Example:field1 has state abbrevs and field 2 should have a "1" if field1="NY".In access I do field2:IIF(field1="NY",1,0),How do I accomplish this in MS SQL.Can it be done in the view?Thanks,Brad

View 7 Replies View Related

Cursor, Query, View And Recordset

Jul 20, 2005

Could someone help me by answering the questions below?What's a cursor?What's difference between Query and View?Is a RecordSet just part of a table? Can it be part of a query of view?If the content in a table changed, is it necessary for a old recordset torenew itself by do "Requery()"?Thanks for your help!

View 4 Replies View Related

View With A Query To An Oracle Database

Jul 20, 2005

Hi,In our environment a database view is based on columns in a table from anoracle database.This is a linked server I believe.I am told that since we use this construction no where clause is possible.So we get to many rows in our database which we have to filter later on.How can we do this differently ?? Since it takes a lot of time to go throughthis process.Bye,Arno de Jong, The Netherlands.

View 2 Replies View Related

Will This Query Be Optimized For A Partitioned View?

Jul 20, 2005

Hello :-)My question is: If I query a partitioned view, but don't know the valuesin the "where x in(<expression>)" clause, i.e.: select * from viewAwhere intVal in(select intVal from tbl1) . Compared to: select * fromviewA where intVal in(5,6).Of course "intVal" is partitioning column.Will this result in an optimized query that searches only the relevanttables?*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Can Not See Columns In Query Design View

Nov 3, 2006

Even though I select "Column Names" in Design View when creating a query (or view), only "* (All Columns)" appears in the table box.

In InfoPath, when I connect a combo-box, err drop down box, to the database, I am unable to connect directly to a table... no tables are shown. If I select a different database, these problems do not exist.

I can not find any setting to allow these columns to be shown in the design view or any setting that will "expose" the tables in InfoPath.

I tried creating a new database and exporting the data, tables and data, from the troubled DB to the new DB; however, the new DB exhibited the same behaviour. The system tables, Master and Model, have the same behaviour. Please help me with your ideas and suggestions... thank you very much for your time.

This database was upsized from Access 2003 to SQL Server 2000 SP4.

rogge

View 7 Replies View Related

Optimization Of Query On Partitioned View

May 27, 2008

Schema below.

The execution plan shows that this query is correctly optimized to check only the underlying Employee_2008 table.

select * from Employee where ReportingYear = '2008'

This query is not optimized and checks both Employee_2008 and Employee_2007:

declare @ry varchar(4)
set @ry = '2008'
select * from Employee where ReportingYear = @ry

How can I get second query to be optimized correctly?

Schema:

CREATE TABLE [dbo].[Employee_2007](
[EmployeeID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Employee_2008](
[EmployeeID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL
) ON [PRIMARY]

CREATE VIEW [dbo].[Employee]
AS
SELECT
'2007' ReportingYear,
EmployeeID,
Name
FROM Employee_2007

UNION ALL

SELECT
'2008' ReportingYear,
EmployeeID,
Name
FROM Employee_2008

View 3 Replies View Related

Indexed View Not Used By The Query Optimizer

Aug 20, 2007

Hello All,
I have a series of Stored Procedure that has a query taking a join of 5 tables. These tables are quiet large with couple of them having around 10 million rows. As this is a DSS application having periodic data loads, I thought of creating Indexed View on top of these tables. Now the problem is that the Indexed View is not directly used by the optimizer. I need to change my queries and put a WITH (NOEXPAND) query hint to make sure the indexed views are used. This is inspite getting dramatic improvement in the query timings (from 64 secs down to 3 secs) after using the Indexed Views. I would like to know what can be the possible reason for the optimizer not using the Indexed View by itself. Is it because my Indexed View caters to multiple queries or I am missing out on something basic.

Thanks in Advance,
Mitesh Shah

View 4 Replies View Related

Refering Aliases In The Same View/ Query

Dec 6, 2007

HI,
I have a view where I want to add Rundate and Prev Month Rundate as computed columns to simplify my joins and calculations.
Rundate will be a select from another table that has a list of rundates for each month like this.
SELECT MAX(fm_dateend)
FROM dbo.tbl_FiscalMonth
WHERE fm_dateend <= getdate()) AS smalldatetime) AS CurrRunDate
Now, I want to add Prev Month Rundate on the basis of CurrRunDate but it does not accept CurrRunDate. The query is like this

SELECT MAX(fm_dateend)
FROM dbo.tbl_FiscalMonth
WHERE fm_dateend < CurrRunDate) AS smalldatetime) AS PrevMonthRunDate

Can anyone help me to work around with the alias as I dont like to put a whole bunch of code inplace of CurrRunDate(Alias).

Thanks,
Shariq

View 1 Replies View Related

View Query Execution Plan

Sep 15, 2007



Hi,

I am developing an application (VB) that should present a query estimated execution plan.

Using the SQL Server Management Studio, I should execute the following commands to see the query's estimated execution plan:


SET SHOWPLAN_XML ON

go

MyQuery
go

SET SHOWPLAN_XML OFF

go

The query is not executed. The result is the query execution plan.


In my application, I call Connection.Execute to execute the 'SET SHOWPLAN_XML ON'. Then, I use a Resultset submit the query. The query is executed and the execution plan is not returned.

Does anyone have any ideas?

Thanks

View 2 Replies View Related

Specifying The Current Month In A Query/view

Jul 10, 2007

Hi, I'm dabbling in trying to retrieve some info from an SQL database and publish it in Sharepoint data view. I'm trying to create a View in the SQL database that retrieves all records for the current month. So that in July, it automatically shows me July records, and when August ticks over, it automatically displays August's records. I can hard code the dates in to the query no probs (e.g. a statement like ENTRYDATE >= '1/07/2007') but was hoping there is a way to specify current month. So maybe the statement looks like ENTRYDATE >= "CURRENTMONTH".



Thanks in advance!

View 9 Replies View Related

Translating A Query (View) From Access To SSE

May 2, 2007

Is it possible to create a view in SSE like I use to do with Access:



SELECT Sum(IIf([Active],1,0)) AS Expr1
FROM Table1;



Active is a boolean field



Many thanks for any kind of help.














View 3 Replies View Related

How To Write A Query To View OS And Db Authenticated Users ...

Aug 13, 2003

Hi,

we have a sqlserver 2000 db with a mixture of OS and NT authentication.How to write a query (or get info) on users.


The query should be able to tell me that these users are OS authentiacted and these users are db authenticated.


Thanks,
-copernicus

View 4 Replies View Related







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