Convert Subqueries To Join

Aug 27, 2007

Hi all,
Recently saw some posts about how bad it is using subqueries (nested queries) and things like all the queries can be written with JOIN instead. However, for a query like the followings, I cannot figure out how to do it properly.

The data looks like the following, it contains 3 columns, id, which is the primary key of the table (unique), group, as which group this entry belongs to, and the article type, where 0 is not an article.

id grp article
1 1 0
2 1 0
3 1 0
4 1 3
5 1 2
6 1 0
7 2 0
8 2 3
9 2 1
10 2 0
11 2 0
12 2 0
13 3 5
14 3 2
15 3 0
16 3 0
17 3 1
18 3 1

What the query is trying to achieve is for each group, get the latest (maximum) id, and the article type (bear in mind "0" is not one of the allowed type). so for data above, the result will look something like this...

id grp article
5 1 2
9 2 1
18 3 1

The following is the query I come up with, the problem is, I don't know how to get the same result without using subquery. I don't even see how it is possible. Guys, please share some light with me, and educate me on this.

select a.id, a.grp, b.article
from
(
SELECT max(id) as id, grp
from list a
where article != 0
group by grp
) a
join list b
on a.id = b.id


thanks in advance!!!

View 6 Replies


ADVERTISEMENT

How Would You Convert A Hash Join Into A Merge Join?

May 6, 2008

I read that merge joins work a lot faster than hash joins. How would you convert a hash join into a merge join? (Referring to output on Execution Plan diagrams.)
THANKS

View 3 Replies View Related

CONVERT WHERE CLAUSE TO OUTER JOIN

May 28, 2008

I have a SQL query where am using WHERE clause as a result of which the NULL values are getting filtered...can u please help me to tranform this query into LEFT OUTER JOINS so as to avoid this filtration....my query is


SELECT A.JOINT_ID,A.SIZE_NBMM,A.ISFIELDJOINT,A.WELDTEST_CRI_ID,C.LINE_ID,D.TESTLEVELNO,E.COMPLETE FROM EALPS_DRWREVSPLJTS

A,EALPS_DRW_REV_SPL B,EALPS_DRW_REV_LINE C,EALPS_WELDTESTCRIT D,EALPS_ACTV_SEQ E WHERE B.SPOOL_ID=A.SPOOL_ID AND

B.LINE_ID=C.LINE_ID AND D.WELD_TEST_CRIT_ID=A.WELDTEST_CRI_ID AND E.ACTIVITY_CODE='VT' AND E.JOINT_ID=A.JOINT_ID



Thanks and Regards,
parama.

parama laha

View 6 Replies View Related

Help Me Convert This Stored Proc To Do A Join

May 4, 2007

Hello,

I am using this sql script to achieve paging functionality for a site. IT works great for the most part, except when I need to return a result set that includes other tables.

How can I add another parameter to this to allow joined table to be a paramter? I'd like to pass in the join table name and columns to join on, or a custom select statement I guess?


CREATE PROCEDURE GetSortedPage(
@TableName VARCHAR(50),
@PrimaryKey VARCHAR(25),
@SortField VARCHAR(100),
@PageSize INT,
@PageIndex INT = 1,
@QueryFilter VARCHAR(100) = NULL
) AS
SET NOCOUNT ON

DECLARE @SizeString AS VARCHAR(5)
DECLARE @PrevString AS VARCHAR(5)

SET @SizeString = CONVERT(VARCHAR, @PageSize)
SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1))

IF @QueryFilter IS NULL OR @QueryFilter = ''
BEGIN

EXEC(
'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' NOT IN
(SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField
)
EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName)

END
ELSE
BEGIN

EXEC(
'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
(SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' AND ' + @PrimaryKey + ' NOT IN
(SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField + ')
ORDER BY ' + @SortField
)
EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName + ' WHERE ' + @QueryFilter)

END

RETURN 0

View 6 Replies View Related

Help Me! Convert SQL Syntax To LINQ With Multi Join

May 5, 2008

Hi...
I have 3 tables:SportTeams (TeamID, TeamName)SportAthletes (TeamID, AthleteID, AthleteName)SportMedals (AthleteID, Medal)
I want to have a brief medal list (TeamID, G, S, B). I can write query in systax:
Select a.TeamID, a.TeamName_en,g.G,s.S,b.B from SportTeams aLeft Join(Select c.TeamID,Count(*) as G from SportMedals b Inner Join SportAthletes c On b.AthleteID = c.AthleteIDWhere Medal = 'G'Group By c.TeamID) g On a.TeamID = g.TeamIDLeft Join(Select c.TeamID,Count(*) as S from SportMedals b Inner Join SportAthletes c On b.AthleteID = c.AthleteIDWhere Medal = 'S'Group By c.TeamID) s On a.TeamID = s.TeamIDLeft Join(Select c.TeamID,Count(*) as B from SportMedals d Inner Join SportAthletes c On d.AthleteID = c.AthleteIDWhere Medal = 'B'Group By c.TeamID) b On a.TeamID = b.TeamIDOrder By g.G desc, s.S desc, b.B desc, a.TeamID asc
But I can't write it in LINQ syntax (I am beginner with C#, LINQ)
Can you help this sample to LINQ systax?
Thanks!

View 5 Replies View Related

T-SQL (SS2K8) :: Convert Unique Identifier From String To Use In A Join / Where Clause

Jan 6, 2015

I am wanting to get the job name based on sys.sysProcesses.[Program_name] column. Why is this query not returning any results even though the 2nd substringed guids are found the the sysJobs table?

SELECTCASE
WHEN RTRIM([program_name]) LIKE 'SQLAgent - TSQL JobStep (Job %' THEN J.Name
ELSE RTRIM([program_name])
END ProgramName
, Val1.UqID
, Val1.UqIDStr

[Code] ......

View 6 Replies View Related

Transact SQL :: How To Convert Row Specific Values Into Columns In Join Query

Aug 18, 2015

I am using stored procedure to load gridview,i want to show row specific values in coloumns , as i an working on daily timetable of college and There are three tables Week_Day,Daily_Timetable & Subject.Daily_Timetable has data which has week_day,class_id,Subject_id,Period_No.

Each day has 6 periods and each period is mapped with subject in daily timetable.From below sql i am getting 6 rows of monday.

But i want to show in a row weekname,period1_subject_id(Period_No=1),period2_subject_id(Period_No=2),period3_subject_id.......upto
period6_subject_id.

Please see my query below:-

SELECT     Week_Day.Week_Day_name, Subject.Subject_Code,  Daily_Timetable.Period_No
FROM         Week_Day LEFT JOIN
                      Daily_Timetable ON Week_Day.Week_Day_Id = Daily_Timetable.Week_Day_Id and Daily_Timetable.Class_Id=6  LEFT JOIN
                      Subject ON Daily_Timetable.Subject_Id = Subject.Subject_Id order by  Week_Day.Week_Day_Id ,Daily_Timetable.Period_No

View 4 Replies View Related

Performance Related Doubt On The Following Items --&&> LEFT/INNER JOIN Vs IN And Cost Of CONVERT

May 28, 2008

Hi,

1.
Right now in my queries I am using lots of LEFT Joins and INNER JOINs... and I was suggested to look at 'IN'... But with IN I did face some performance issues previously and stopped using it... but I have got new doubts on which query will give me better performance...

A query using LEFTJoin or a query using IN/NOT-IN


2.
This question is about CONVERT...

I have a stored proc which is used for updating a table... and multiple columns [of the same table] and corresponding values are sent to the proc [only a subset of the columns might be sent for updates everytime and the columns to update is not fixed for each run of the SP]...

I have to construct a UPDATE String out of it using string concatenation to finally be able to use "sys.sp_executesql" on that update statement...

This results in me having to use CONVERT() lots of times... and one of the columns among them on which I am doing a CONVERT is of the type XML...

So the question is as follows...
a. Is it preferrable to construct a single UPDATE statement string and execute it using "sys.sp_executesql"
b. Or Is it preferrable to give multiple UPDATE statments... i.e. one update statement for each column [Depending on whether that column has to be updated for that run or not]


i.e. The question essentially is:

Does a single update query constructed using lots of CONVERTS [Basically on INT and XML types]
give more performance over using multiple UPDATE statments on the table
Or is it the other way round..

Thanks,
Pratap.

View 5 Replies View Related

Question: Has Anyone Written A Tool To Convert From ANSI-89 To ANSI-92 Join Syntax?

Oct 25, 2007

A question for everyone:
With the introduction of SQL 2005, we now have to use ANSI-92 T-SQL Syntax and I was wondering if anyone had written a tool to convert queries from old ANSI SQL to the new syntax.

We have some code that has to change for the outer joins, but we also have a lot of code that should change for the inner joins. It doesn't seem that difficult to write something that parses an old piece of code and at least suggests a new version. Especially if the conversion code wasn't SQL code.

Thanks, in advance,
Brian

View 7 Replies View Related

Subqueries

Apr 28, 2004

Trying to find which Employees have NOT entered a record (timecard).

Table #1 Employee_Hours
Table #2 Employee (List active)

Table #1
SELECT EID, DATEPART(wk,[Date]) AS Week, MAX([Date]) AS WeekEnding
FROM Employee_Hours
WHERE ([Date] Between '1/1/04' AND '12/31/04')
GROUP BY EID, DATEPART(wk,[Date])
HAVING MAX([Date]) = '1/24/04' --Pay period

Table #2
SELECT EID, DisplayName
FROM Employee
WHERE (Active=1)

I tried subqueries and temp tables but can't get the results I need. The first query will return the employees who entered their time for the given pay period.

Thanks

View 1 Replies View Related

Use Of Subqueries

Jun 15, 2004

Hi,

I have the following sql statement:
INSERT INTO prg_lvl VALUES (
'Data Recovery',
'Data Recovery',
7,
'ENG',
0,
16,
'Menu=w_data_recovery_stk;',
( select max(menu_id) + 1 from prg_lvl ),
'K');
that works for Sybase and I wanna make it works for SQL Server.

The problem is the use ot the subquery (select max(menu_id) + 1 from prg_lvl).

Could I use subqueries in SQL Server in general or is there is any other method to overcome this problem

Thanks,
fady

View 3 Replies View Related

Subqueries

Oct 14, 2005

Hi,

I would like to know

What is Corelated SubQueries??

Thanks in Advance

View 3 Replies View Related

Subqueries

Jan 14, 2008

I am very new to SQL Server, working with it in class. I am trying to do procedures involving subqueries using the Northwind database, and am having a rough time.

For instance, I have the following code, which lists all orders for the month of November, 1996:

SELECT Customers.CustomerID, Customers.CompanyName, CAST(Orders.OrderDate AS CHAR (11) )AS OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
AND OrderDate BETWEEN '11/1/1996' AND '11/30/1996' ;

I have been asked to do the same thing, but using a subquery. I came up with the following:

SELECT CustomerID, CompanyName FROM Customers a
WHERE EXISTS (SELECT OrderDate FROM Orders b WHERE a.CustomerID = b.CustomerID
AND OrderDate BETWEEN '11/1/1996' AND '11/30/1996' ) ;

However, it does not list the Order Date, as I want it to, and the information is not correct (it is missing a few orders from November '97 that should show up. Can someone tell me what I am doing incorrectly?

Please note that I do not need anyone to give me the code, but simply give me an idea of what I am doing incorrectly, or let me know how it can be done, so that I may do it. =-)

View 16 Replies View Related

Are Sub-subqueries Possible?

Apr 12, 2006

Hi all,I've seen mention that you can use nested subqueries down to as manylevels as you like but whenever I run the following:select * from table1where tab1ID in(select tab1ID from table2 where tab2ID in(select tab2ID from Table3 where Tab3ID=N))I get the error "incorrect syntax near the keyword 'where'"Can anyone confirm that sub-subqueries are possible? If so is thesyntax I'm using correct?I'm running SQL 2000 through a Delphi 5 app.RegardsJon

View 2 Replies View Related

Help With SubQueries?

Jul 20, 2005

Guys,Got this query...---------------------------SELECT TOP 5 Tbl_admin_hotels.HotelName,(SELECT COUNT(Tbl_marketing_history.HotelID)FROM Tbl_marketing_historyWHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)AS CountTotal,(SELECT MAX(DateSent)FROM Tbl_marketing_historyWHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)AS LastSentFROM Tbl_admin_hotelsWHERE NOT Tbl_admin_hotels.HotelID = 99ORDER BY CountTotal DESC---------------------------Within the table Tbl_marketing_history there is also a 'Subject' column thatI require, the row to grab this Subject column from should relate to the rowI'm selecting in the SubQuery to grab 'DateSent'.I've tried and tried to grab multiple columns but failed miserably? Cananyone help pleaseeee!Cheers, Ash

View 12 Replies View Related

Should I Do Two Subqueries?

Sep 27, 2007

In this stored procedure, I want to grab all the information for Sales Representatives that made less then 6 visits. I have done that in the subquery. But I also want to be able to display their visits by Dates where any number of visits was made. Meaning, i want to be able to show Everyday where a visit was made, but i only want the records that are less then 6 visits. So I want nulls to show up for the dates where no visits were made. So I can track the people that didnt make visits, on days other made visits (which means they should have visits too)

So basically my report looks like this:
09-10-2007 09-11-2007 09-14-2007
Tom Smith 1 3 4
Janice Cooper 3 5 5
Troy Slater 5 4 2
Kelly Short 4 4 4


When i want it to look like this:

09-10-2007 09-11-2007 09-12-2007 09-13-2007 09-14-2007
Tom Smith 1 3 null null 4
Janice Cooper 3 5 null null 5
Troy Slater 5 4 null null 2
Kelly Short 4 4 null null 4

Its an exception report, so i want to show what was not done , on days that are supposed to have things done, i cant show this, if dates are not displayed before there were no visits less then 6. I want to display all relevant dates, where any number of visits was mad. But only show the visits that were less then 6.

Here's my stored procedure that already has the less then 6 visits showing. But doesnt always show the dates. What should i do?






Code Block
SELECT dbo.Qry_Visits.Status,
dbo.Qry_Visits.Customer_code,
Qry_Sales_Group.Name,
dbo.Qry_Sales_Group.SR_Name,
dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,
dbo.Qry_Date_Dim.Date_Dimension_Date,
dbo.Qry_Date_Dim.Day_Of_Month,
dbo.Qry_Sales_Group.Region,
dbo.Qry_Visits.period_code,
dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,
dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,
dbo.Qry_Date_Dim.Date_Dimension_Year,
dbo.Qry_Date_Dim.Date_Dimension_Period,
CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,
dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code],
B.VisitsTotal

FROM dbo.Qry_Visits
INNER JOIN dbo.Qry_Sales_Group
ON dbo.Qry_Visits.[SR Code]
COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
COLLATE Latin1_General_CI_AS
INNER JOIN dbo.Qry_Date_Dim
ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)
INNER JOIN ( Select COUNT(Visits)as VisitsTotal,[Sales Responsible],CONVERT(VARCHAR,(Qry_Visits.time_log),110)TheDate,Qry_Visits.Status
FROM dbo.Qry_Visits
WHERE Qry_Visits.Status=2
GROUP by [Sales Responsible] , CONVERT(VARCHAR,(Qry_Visits.time_log),110),Qry_Visits.Status
HAVING SUM(Visits) < 6)B
ON dbo.Qry_Sales_Group.SR_Name COLLATE Latin1_General_CI_AS = B.[Sales Responsible] COLLATE Latin1_General_CI_AS AND
CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = B.TheDate

WHERE Qry_Visits.Status=2
ORDER BY dbo.Qry_Sales_Group.SR_Name, CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110)

View 5 Replies View Related

Using Subqueries

Dec 23, 2007



Hey everyone, I'm running into an issue where I have a select statement that retrieves all employee records from a employee table. In this statement I'm displaying first and last name, titlte and the employees manager id(which is the employees id). What I want is a query that instead of displaying the manager's id, display the manager's name. The query that I'm using below doesn't work. I was hoping that someone could help me out:
SELECT LastName, FirstName, Title, Extension,
(SELECT FirstName, LastName
FROM Employees
WHERE (ReportsTo = EmployeeID)) AS Reports
FROM Employees

Here's the data that I'm using:
LastName, First Name Title Extension Reprots To




Davolio
Nancy
Sales Representative
5467
13

Fuller
Andrew
Vice President, Sales
3457
0

Leverling
Janet
Sales Representative
3355
12


When I run this query I get a error saying "Only one expression can be specified in the select list when the subquery is not introduced with Exists"

Thanks in advance for your help.

View 5 Replies View Related

Aggregates Containing Subqueries...

Sep 20, 2007

So I already no this can't be done... but I need a suitable alternative (if one exists) so I don't have to competely re-write this already too huge query.
Anyways, in my select I have something like this:
sum( case when code in (1,2,3,4) then 0 else 1 end ) as total
which has now increase from four static values to a more dynamic format, that being a table with 47 values which may shrink or grow over time. Attempting the following fails:
sum( case when code in (select code_id from ExcludedCodes) then 0 else 1 end ) as total
because apparently you can't use selects or aggregates within an aggregate.
So any ideas on how I can get this working... is there no Array or List type variable I could just substitute in? I've already tried using a Table Variable, but that failed as well.
Please keep in mind, this is one line of a very large select containing many other fields and aggregates (on a fair amount of joins) which is used in at least four differerent reporting queries. If there is no quick and easy replacement trick I can do just let me know so I can start re-writing all of them (which is seriously going to make my head hurt).

View 4 Replies View Related

Effectively Using Subqueries

Dec 23, 2005

Hi, I have searched through this forum and haven't found the answer to a problem I am having so I figured I will post it.

My SQL statement:

Select * FROM tblData WHERE LastName=
(SELECT tblData.LastName FROM tblData GROUP BY tblData.LastName HAVING (Count(tblData.LastName)>1))"

What I am trying to do: I am trying to make an SQL filter that I can apply to my form in order to only show records with duplicate last names. The sub query returns names that are already in the table. I then compare what is found to be duplicate with the original table in order to just show only the duplicate records. Everything works fine as long as there is only one name thats duplicated.

When there are multiple duplicate names then I run into an error.

When the statement is put into a string and executed in VBA I get this error:

"Run-time error '3021': No current record."

When the statement is put into a query and run against the DB I get this error:

"At most one record can be returned by this subquery"

So yeah, any help would be greatly appreciated. Am I going about this all wrong or am I just forgetting something? Thanks for any help.

View 2 Replies View Related

Subqueries Performances

Jun 15, 2008

Hello,

Lets consider two tables : CUSTOMERS and ORDERS.

I would like to know which of the following query is the fastest:

select * from customers C
where exists (select 0 from ORDERS O
where C.Name like 'A%' and O.Charged = 1)

select * from customers C
where exists (select 0 from ORDERS O
where O.Charged = 1) and C.Name like 'A%'

I don't want to use a JOIN clause. I just want to know if there is a difference of efficiency when the condition on C.Name is inside or outside the sub query.

Thank you in advance for any advices,

regards,

mathmax

View 2 Replies View Related

Subqueries Performances

Jun 16, 2008

Hello,

I would like to know which of the following query is the fastest:

select * from customers C
where exists (select 0 from ORDERS O
where C.Name like 'A%' and O.Charged = 1 and O.Customers_Id = C.Id)


select * from customers C
where exists (select 0 from ORDERS O
where O.Charged = 1 and O.Customers_Id = C.Id) and C.Name like 'A%'

Because the condition C.Name like 'A%' is inside the sub query, I'm wondering if it will be evaluated for each record of the Orders table. Does anyone know if there is a difference of efficiency when this condition is inside or outside the sub query ?

Thank you in advance for any advices,

regards,

mathmax

View 1 Replies View Related

Insert Subqueries

Jul 6, 2006

I have tried this insert comand and it errors out telling me that icannot use subqueries this way. INSERT INTO tblPartLocation(PartLocation, Part)VALUES (999,(SELECT PartID FROM tblParts WHEREPartName = 'test'))how would i insert a value from a query?thanks for any help

View 2 Replies View Related

View With Many Subqueries

Apr 20, 2008

I need to build a view that has 4+ columns that are "counts" of relationships with other tables.

for example:



Code Snippet
select Name, Description, (select count(*) from PetOwner PO where PO.OwnerId = O.Id) as PetCount
from O Owner






I need to add a few more counts to this query, but I'm thinking that is a bad idea since the number of joins is going to get out of control if I need more counts.

I've created a view with all the counts and it works fine.

I want to index the view so the counts aren't calculated everytime the query is run, but I can't index if I use a subquery like this.


In the end I want the result set to have these columns.



Code Snippet
Column1, Column2, Count1, Count2, Count3




And I want it to be quick.

What's the best way to do this?

View 1 Replies View Related

Endless Subqueries

Aug 16, 2007

I have a table with two columns: OID and Cumulative (witch is the same type as OID)
Each OID can have one or more Cumulatives.

Example of data:
OID Cumulative
167 292
167 294
167 296
168 292
169 302
169 304


The cumulation of each OID don't stop at one cumulation, but can be endless (theoretical).
Example: 167->292->590
So the table would have on more row:
OID Cumulative
295 505


I would like to represent this strucuture in a tree view and I'm looking for a query that could give me a table with this structure:
OID Cumul1 Cumul2 Cuml3 Cuml4 .... Cumuln
in the way I can read the row and have as many child nodes as I have values in the columns. The number of columns depends on the row with most cumulations.

How can I do the query?
Is there a better way as my table with n columns?

Thanks for suggestions

View 1 Replies View Related

Convert ANsi-89 To Ansi-92 Outer Join

Oct 1, 2007

I've been using this syntax for years on SQL Server and now comes the time to convert to SQL 2005 (90 compatibility). This syntax returns four rows. Basically it returns one row for each servername/component/context/property/value even when there does not exist a property of 'fff' since it's a left join:



Code Block
select t1.* from tblconfiguration t1
,tblconfiguration t2
where t1.component = 'AdjProcessUtility'
and t1.servername *= t2.servername
and t1.component *= t2.component
and t1.context *= t2.context
and t1.property = 'proc'
and t2.property = 'fff'




Result:
SQLEDEV1 AdjProcessUtility DuplicatesReport Proc Adjustment.dbo.prcDuplicatesReport
SQLEDEV1 AdjProcessUtility ExtractAdjFile Proc Adjustment.dbo.prcAdjExtractMFFiles
SQLEDEV1 AdjProcessUtility ValidationProcess Proc prcAdjValidations
SQLEDEV1 AdjProcessUtility ValidationReport Proc Adjustment.dbo.prcValidationReport



When the converted (using SQL enterprise Mgr) runs it returns no rows:



Code Block
SELECT t1.*
FROM dbo.tblConfiguration t1 LEFT OUTER JOIN
dbo.tblConfiguration t2 ON t1.ServerName = t2.ServerName AND t1.Component = t2.Component AND t1.Context = t2.Context
WHERE (t1.Component = 'AdjProcessUtility') AND (t1.Property = 'proc') AND (t2.Property = 'fff')





I don't really see how to change this query to make it work. I've searched the web and I really don't see any examples of left joins which use more than one column.

Here's the table definition:



Code Block
CREATE TABLE dbo.tblConfiguration
(
ServerName VARCHAR(30) NOT NULL,
Component VARCHAR(255) NOT NULL,
Context VARCHAR(255) NOT NULL,
Property VARCHAR(255) NOT NULL,
CONSTRAINT PK_tblConfiguration PRIMARY KEY NONCLUSTERED( ServerName, Component, Context, Property ),
Value VARCHAR(255) NOT NULL
)




I use this table to define reports and there attribues. The rows repeat themselves except for the Property and Value columns
Here is some of the data:

SQLEDEV1 AdjProcessUtility ExtractAdjFile Proc Adjustment.dbo.prcAdjExtractMFFiles

SQLEDEV1 AdjProcessUtility ExtractAdjFile RunTime 13:25
SQLEDEV1 AdjProcessUtility ExtractAdjFile Schedule 2,3,4,5,6
SQLEDEV1 AdjProcessUtility ExtractAdjFile FixedRecLength 71
SQLEDEV1 AdjProcessUtility ExtractAdjFile WriteFileHeader Y
SQLEDEV1 AdjProcessUtility ExtractAdjFile WriteTempTable Y

SQLEDEV1 AdjProcessUtility ValidationProcess Proc prcAdjValidations
SQLEDEV1 AdjProcessUtility ValidationReport ReportClass ReportCSV
SQLEDEV1 AdjProcessUtility ValidationReport Ids Validation
SQLEDEV1 AdjProcessUtility ValidationReport RunTime 15:06
SQLEDEV1 AdjProcessUtility ValidationReport Schedule 2,3,4,5,6
SQLEDEV1 AdjProcessUtility ValidationReport DefaultFileName Adj_ValidationReport_MMDDYYHHMM.csv


etc.

Any help is greatly appreciated,
Sid

View 16 Replies View Related

Could Anybody Explain To Me Why Sqldatadpater Does Not Allow Subqueries?

Aug 17, 2007

Dear experts,
Recently i got an error msg looks like this: you cannot use subqueries within a sqldatadpter except the subquery is introduced with EXISTS.
Well, actually i was using IN.
I know I can revise my query sting to use INNER JOIN or such stuff just to remove the nested queries. But i'm realllllly curious why it's not allowed??
Really appreciate it if some expert can tell me.
Thanks in advance

View 1 Replies View Related

Insert With Multiple Subqueries

Sep 17, 2007

Hi
what i like to do is insert in one table 2 value from 2 different row.
exp:
table1: person
id name
1  bob
2 john
 so id like to make an insert that will result in this
table 2: person_knowed
idperson1: 1
idperson2: 2
so the wuery should look something like this:
 
insert into person_knowed(idperson1, idperson2)(select id from personwhere name = 'bob',select id from personwhere name = 'john')); 
anybody have an idea of how to acheive this?
 

View 1 Replies View Related

Subqueries In Insert Statment

Feb 26, 2008

Hello, How can I do something like:
Insert Into Displayed(snpshot_id, user_id, user_domain, ddisplay, iWidth, iHeight, disp_size, disp_format, watermark, frc_update, creditcost)
Values ((Select snpsht_id from SiteIndex Where user_domain like '%domain'), 1000, (Select domain_id from UserDomains Where user_domain like 'domain'), GetDate(), 480, 360, 2, 1, 1, 0, 3)
Im getting an error:
Subqueries are not allowed in this context. Only scalar expressions are allowed.
 
Thanks!

View 4 Replies View Related

Optimization: Subqueries And Backreferences?

May 17, 2000

I wonder if somebody could give me advice on SQL Server7-specific handling of subqueries and backreferences...

What is a) more efficient, and b) faster:

1. IF (DATEDIFF(minute, (
SELECT tLastHitTime
FROM (
SELECT *
FROM tblSession
WHERE tSessionID = 'abcd1234'
) as sess), getDate()) < 30)
SELECT *
FROM sess

or:

2. IF (DATEDIFF(minute, (
SELECT tLastHitTime
FROM tblSession
WHERE tSessionID = 'abcd1234'
) as sess), getDate()) < 30)
SELECT *
FROM tblSession
WHERE tSessionID = 'abcd1234'


In the first example, 2 SELECT statements (line 2 & line 8) refer to derived table by its alias 'sess'. In the 2nd example, both queries are independant, yet identical...

I wonder if SQLServer is "smart" enough to execute identical queries only once, and then reuse the resulset (e.g. derived table), or it requires explicit references (such as alias) in order to do so...

TIA,
Alex

View 2 Replies View Related

Multiple Colum Subqueries

Feb 16, 2007

hi guys,

i have a problem, i have a query which does a search based on a parameter, thing is i want to order them according to how relevant the results are such as 5 for beingan exact match, 0 for being no match. using a series of liek statements.

so that the most relevnat results are displayed at the top.

how do i achieve this - im a bit confused about this.

appreciate any and all help guys

thanks

andy

View 7 Replies View Related

Use Of Subqueries In An Aggregate Function

Jun 15, 2004

hi there,
I have a query that works on sybase and want to make it also works on SQL Server.
The problem is that in this query I 'm using a subquery in an aggregate function.
It seems that SQL Server unlike Sybase doesn't support the use of subquery in aggregate function.
How can I overcome this problem.

Thanks,
Fady

View 3 Replies View Related

Subqueries In Access/MSSQL

Jun 7, 2006

Hi,

To make a long story short, I'm trying to correlate the most inner query (two levels deep) with the most outer one, and it just doesn't work.
Is it because the correlation must be back-to-back, without any levels that stand before the two?

If you have no idea what I'm talking about, below is the query that does work, but doesn't produce the right results ("QUOTES" gives the same number for all records):

---------------

select printersGlobal.company, (select count(*) from (select subBidding.bid_company from bidding as subBidding where subBidding.bid_company = 'Printing Company 1' group by subBidding.bid_company, subBidding.project) group by subBidding.bid_company) as QUOTES,

count(date_picked) as wins

from printers as printersGlobal

left outer join bidding as biddingGlobal on printersGlobal.company = biddingGlobal.bid_company
group by printersGlobal.company
order by printersGlobal.company asc

----------------------

When I replace 'Printing Company 1' with printersGlobal.company (to correlate and produce dynamic and correct results), I get errors.

I would appreciate if anybody can point me in the right direction.

Thanks.

View 14 Replies View Related

Using Subqueries To Get Latest Value Per Type?

May 7, 2008

Hopefully someone can suggest a better solution than what I'm currently hobbling along with.

Basically, I've got a table that gets rows inserted (with a timestamp) whenever there is a change to one of the values of a particular "item". So, what I want is to return a dataset of the latest values of each particular item.

I'm guessing that what I'm trying to acheive is doable in some elegant and performant fashion. Something maybe involving a ROLLUP or WITH CUBE or some FANCY item.

But for the time being, I've got a less-elegant query that returns the correct data, just using subqueries.

Here's the T-SQL to run my scenario:


DECLARE @actionHistoryTable TABLE (
itemID int
,actionType int
,actionValue nvarchar(50) NULL
,actionTime datetime )
INSERT @actionHistoryTable VALUES( 1000, 1, 'fork', '1/1/2008')
INSERT @actionHistoryTable VALUES( 1000, 2, '27', '1/2/2008')
INSERT @actionHistoryTable VALUES( 1000, 3, '200', '1/12/2008')
INSERT @actionHistoryTable VALUES( 1000, 2, '1', '1/1/2008')
INSERT @actionHistoryTable VALUES( 1000, 3, '204', '1/1/2008')
INSERT @actionHistoryTable VALUES( 1000, 1, 'ball', '1/3/2008')
INSERT @actionHistoryTable VALUES( 1026, 2, '20', '1/10/2008')
INSERT @actionHistoryTable VALUES( 1026, 2, NULL, '1/5/2008')
INSERT @actionHistoryTable VALUES( 1026, 1, 'hotdog', '1/6/2008')
INSERT @actionHistoryTable VALUES( 1026, 3, '2511', '1/8/2008')
INSERT @actionHistoryTable VALUES( 1026, 3, '375', '1/7/2008')
INSERT @actionHistoryTable VALUES( 1026, 1, 'mustard', '1/5/2008')
INSERT @actionHistoryTable VALUES( 1013, 1, 'rock', '1/2/2008')
INSERT @actionHistoryTable VALUES( 1013, 1, 'paper', '1/21/2008')
INSERT @actionHistoryTable VALUES( 1013, 3, '10', '1/20/2008')

-- JUST DISPLAY THE TABLE FOR EXAMPLE
SELECT * FROM @actionHistoryTable

-- THIS ROLLS-UP THE LATEST VALUE FOR EACH ACTION_TYPE FOR EACH ITEMID
SELECT aht.itemID
,( SELECT TOP 1 aht2.actionValue
FROM @actionHistoryTable aht2
WHERE aht.itemID = aht2.itemID
AND aht2.actionType = '1'
ORDER BY aht2.actionTime DESC ) as 'latest type 1 value'
,( SELECT TOP 1 aht2.actionValue
FROM @actionHistoryTable aht2
WHERE aht.itemID = aht2.itemID
AND aht2.actionType = '2'
ORDER BY aht2.actionTime DESC ) as 'latest type 2 value'
,( SELECT TOP 1 aht2.actionValue
FROM @actionHistoryTable aht2
WHERE aht.itemID = aht2.itemID
AND aht2.actionType = '3'
ORDER BY aht2.actionTime DESC ) as 'latest type 3 value'
FROM @actionHistoryTable aht
GROUP BY aht.itemID



Is there a better way?
-Steve

View 8 Replies View Related







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