I am trying to perform an update based on the following query
select o.order_id,
from orders o, accounts a
where a.account_id=r.account_id and
a.event_type IN (2003, 3057, 4003, 7005)
order by o.order_id desc
I have tried several combinations but to no avail.
Can anybody provide a solution?
Thanks in advance
Declan
I've set up a correlated SQL query but i'm not entirely sure i've got it right.
The purpose of it is to get all the unapproved hours for a TimeCode where the CodeOwner matches @UserName.
It gets the unapproved hours for every code rather than the ones for the UserName.
SELECT ID, TimeCode, Date, Hours, Username, Comments, Approved,
(SELECT CodeOwner FROM lward.tblCodes WHERE tblCodes.CodeID = tblHours.TimeCode AND CodeOwner = @UserName) as CodeOwner
FROM lward.tblHours
WHERE Approved is NULL The Username in tblHours is used for storing who is logging some hours. CodeOwner is the Owner of a paticular time code.tblHoursID, TimeCode, Date, Hours, Username, Comments, ApprovedtblCodesCodeID, CodeName, CodeDescription, CodeOwner, CodeCategoryThe only relationship is CodeID and TimeCode, CodeID is the primary key and TimeCode is the foreign.
HiI was trying an example of subquery Create Table #Temp( PK_ID int identity(1,1), sName Varchar(50) )Create Table #TempAddress( PK_ID int identity(1,1), PersonID int, Address Varchar(100))Insert Into #Temp Values('Karan')Insert Into #Temp Values('Gupta')Insert Into #Temp Values('Karan')Insert Into #Temp Values('Karan')Insert Into #TempAddress Values(1,'Address1')Insert Into #TempAddress Values(2,'Address2')Insert Into #TempAddress Values(3,'Address3')select PK_ID from #Temp a where (select PK_ID from #TempAddress b where a.PK_ID = b.PersonID)Drop Table #TempDrop Table #TempAddressBut I am getting an errorMsg 4145, Level 15, State 1, Line 29An expression of non-boolean type specified in a context where a condition is expected, near 'Drop'. Am i doing something wrong.Kindly adviceRegardsKaran
View 1 Replies View RelatedHello,
I need to come up with a query (I really don't want to use cursors) that will perform the following:
I have a message table that houses multiple users with multiple messages...for every user, I need to delete every message after the 20th oldest message.
Here are the pertinent fields: userid,message_id,message_date
I really appreciate your help,Rob
This really a question of approach more than anything else.
The situation is as follows:
I have a set of data that should contain one row for every company for every date in a supplied date range. Any companies that do not have a row for all dates, or have null values in certain required fields, should be dropped from the series all together.
So that...Given the date range 11/1/2007 to 11/2/2007 and the data set
Code:
11/1/2007 CompanyA req1 req2 req3 req4
11/2/2007 CompanyA req1 req2 null req4
11/1/2007 CompanyB req1 req2 req3 req4
11/2/2007 CompanyB req1 req2 req3 req4
11/2/2007 CompanyC req1 null req3 req4
Only the two rows for CompanyB would ultimately be delivered.
On a tip I looked into correlated subqueries but that doesn't seem to fit. Does anyone else have an approach that might be best for a situation like this?
I would like to combine the following 3 select statements:
1.
select SECTION_ENGLISH_DESC, D_REGULATION.REG_ENGLISH_DESC, D_SECTION.REG_SURR_ID from D_SECTION INNER JOIN D_REGULATION on D_SECTION.REG_SURR_ID = D_REGULATION.REG_SURR_ID where D_SECTION.reg_surr_id in ('101')
2.
Select count(*) from F_INSPECTIONS where REG_SURR_ID = '101'
3.
select CASE COUNT(*)
WHEN 0 THEN 'Compliant'
ELSE 'Not Compliant'
END
from F_VIOLATIONS
where SECTION_SURR_ID = '201'
the first statement is the main "frame" for what i want to get back. It should loop through all the inspections for 1 regulation (101).
the second statement, i know, is redundant but thats fine. (i get the same number of inspections for the same regulation for each inspection).
The third statement should return weather the current section is compliant (for reg 101). So that example would be for a single section (201) which may be included in reglation 201.
(a regulation has many sections)
Thanks a lot,
Dave Benoit
Ajaya writes "SERIAL DATE STATUS
123 10/02/06 PENDING
123 01/06/06 DECLINE >
345 08/30/06 APPROVE>
345 10/05/06 APPROVE
567 08/14/06 APPROVE>
567 08/22/06 APPROVE
678 10/17/06 OTHER STATUS
678 10/17/06 APPROVE>
678 10/24/06 APPROVE
789 10/04/06 DECLINE
789 10/06/06 OTHER STATUS
789 10/06/06 APPROVE>
I am looking for a sql which extract
EARLIEST DATE IF STATUS = APPROVE OR DECLINE, ONLY NEED TO CONSIDER THESE TWO STATUSES.
for example serial = 789, I want to select the last row, since it is approved status.
The arrow in the right side , means i want to select that row from my sql.
If both statuses(decline and approve) is falls for a serial number, then I want to select the row with approved status with earliest date. example sir = 789
Please help"
Use a correlated subquery to show the titles that have sales. Show title name, title id and quantity for each table?
Above is the original question.
My understanding below
I think two tables sales and titles. The title_name, title_id is in the titles table. Quantity is in the sales table. My question is, how can i write a correlated subquery to show titles that have sales?Any feed back is would be thankfull.
I'm trying to take the 2nd block of SQL and implement it into the first so that I can have a correlated subquery. Can anyone help me with formatting this or at lease getting closer? I'm lost!
select * from st_Meta_Field MF
INNER JOIN st_field F ON MF.Field_ID = F.Field_ID
where F.Table_ID = 5 AND (F.Field_Name = 'XXX' OR F.Field_Name = 'YYY')
AND F.Record_State = 1 AND MF.Record_State = 1
select max(display_row),subset_value
from st_Meta_Field where table_id = 5 AND Display_Row < 500
group by Subset_Value
I'm using Transact SQL for SQL 2000 and I'm having difficulties with a correlated sub-query.
I have a table called 'Results' like this...
Company_ID Product Spend Flag_Top25
1 Product A $100
1 Product B $250
1 Product C $450
I want to create a flag to identify if a companies spending for each product is within the Top 25% of spending within the product category across all products.
For example, the code below would identify the Top 25% of Companies with spending on Product A.
SELECT TOP 25 PERCENT Company_ID
FROM Results
WHERE Product = 'Product A'
ORDER BY Spend DESC
I'm trying the following correlated sub-query to get this done and it is not working.
UPDATE Results A
SET A.Flag_Top25 = 'Top 25% Customer'
WHERE A.Company_ID in (
SELECT TOP 25 Percent B.Company_ID
FROM Results B
WHERE B.Spend > 0
and B.Product = A.Product
ORDER BY B.Spend DESC
)
I'm trying to take the 2nd block of SQL and implement it into the first so that I can have a correlated subquery. Can anyone help me with formatting this or at lease getting closer? I'm lost!
select * from st_Meta_Field MF
INNER JOIN st_field F ON MF.Field_ID = F.Field_ID
where F.Table_ID = 5 AND (F.Field_Name = 'XXX' OR F.Field_Name = 'YYY')
AND F.Record_State = 1 AND MF.Record_State = 1
select max(display_row),subset_value
from st_Meta_Field where table_id = 5 AND Display_Row < 500
group by Subset_Value
I have a table similar to the following:
ID ¦ Name ID ¦ Period From ¦ Period To ¦ Percentage ¦
---------------------------------------------------------------------------
Important - Each person can have more than one entry.
What I am trying to do is get the last percentage that each person obtained.
The only way I have been able to do this is by the following:
SELECT * FROM myTable
LEFT OUTER JOIN ( SELECT NameID, MAX(PeriodTo) as PeriodTo FROM myTable GROUP BY NameID) t1
ON myTable.NameID = t1.NameID
WHERE myTable.PeriodTo = t1.PeriodTo
I was wondering if there was another way of doing this, or whether this is an efficient method of doing this kind of query. Jagdip
Hi, the following query works in Oracle, how do I do it in SQL Server? Thanks.
UPDATE table1 a SET a.newid =
(SELECT b.newid
FROM table2 b
WHERE a.id = b.id)
Basically, if table 1 and 2 have the same value in the "id" column, then I update the "newid" column in table a to match that of "newid" in table b.
I've been through my textbook, online articles, youtube ... you name it! Every reference to a correlated subquery that I have found involves only one table.
Two quick questions:
1. Is the below considered a correlated subquery?
2. Can you use a JOIN in an embedded SELECT statement? I ask because I have errors near the WHERE keyword in both subqueries.
USE MyGuitarShop
SELECT EmailAddress,
(SELECT MIN(OrderDate) FROM Orders JOIN Customers WHERE Orders.CustomerID = Customers.CustomerID) AS OldestOrder,
(SELECT Orders.OrderID FROM Orders JOIN OrderItems WHERE Orders.OrderID = OrderItems.OrderID) AS OrderID
FROM Customers
GROUP BY Customers.EmailAddress
Hi,I have a history table with about 400 million rows, with a uniquecomposite nonclustered index on two columns (object id and time period)which is used for most of the queries into the table, and an identitycolumn for the clustered primary key.Many of my queries use correlated subqueries to pull unique historyrows from the history table for each of a set of objects from theobject table, for instance, pulling the earliest history row for eachobject in a set. These correlated subqueries reference the object tableand return the primary key of the history table, e.g.:select *from lp_object linner join lp_object_history hon h.lp_object_id = l.lp_idwhere l.lp_set_id = 'SOME_LITERAL'and h.lp_id = (select top 1 lp_idfrom lp_object_historywhere lp_object_id = l.lp_idand lp_some_column > 0order by lp_time_period)Now, if lp_some_column is not indexed, this query has no choice but toread the entirety of every single history row for every object in theset where lp_set_id = 'SOME_LITERAL', so that it can determine iflp_some_column > 0, and because the history table is clustered by theidentity column rather than the ID of the relevant object whose historywe're tracking, the reads take forever - they have to bop all aroundthe disk. The sets I deal with tend to have about 5K objects in themand about 200K associated history rows.I'm considering reclustering by the (object id, time period) index, butthen my queries will need an extra bookmark lookup step to get the rowdata from the identity value returned by the correlated subquery. Ithink it will still be faster, though, so I will probably build a copyof the table with the alternative clustering scheme to run someperformance tests.What I'm wondering is, if I were to dispense with the identity columnaltogether and replace it with a composite primary key of (object id,time period), would I be still be able to use my correlated subqueries?Because then there wouldn't be a single column that uniquely identifieseach row in the history table and I don't think SQL Server supportsmulticolumn correlated subqueries.Thanks for reading,Seth
View 4 Replies View RelatedHello All,I have a SQL Query with multiple correlated Subqueries in it. When itgets executed it runs rather slow due to the size of the QT table.Does anybody have any suggestions how to alter this query to make itrun faster, or any index suggestions to assist it with.Query is as follows:SELECT SH_ORDER, SH_CUST, SH_ADD_DATE, SH_CUST_REF, SH_DESC, SH_EXCL,(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (JU_PROC_GRP < 2)AND (QT_QUOTE_JOB = 0))AS [PREPCOST],(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (QT_QUOTE_JOB = 0)AND (JU_PROC_GRP > 1) AND (JU_CATEG = 1)) AS [MATCOST],(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (QT_QUOTE_JOB = 0)AND (JU_PROC_GRP > 1) AND (JU_CATEG = 3)) AS [OUTCOST],(SELECT SUM(QT_CHARGE) AS QT_CHARGE_SUMFROM QT INNER JOINJU ON QT_PROC_CODE = JU_PROC_CODEWHERE (QT_NUMBER = ' ' + SH_NOTE_2) AND (QT_QUOTE_JOB = 0)AND (JU_PROC_GRP > 1) AND((JU_CATEG = 0) OR (JU_CATEG = 2) OR (JU_CATEG = 4))) AS [LABCOST]FROM SHWHERE SH_ADD_DATE = '5/FEB/2004'thanks a lot for any helpJason
View 1 Replies View RelatedHi,
I am used to writing Sub-Correlated queries within my main queries. Although they work fine but i have read alot that they have performance hits. Also, as with time our data has increased, a simple SELECT statement with a few Sub-Queries tends to run slower which may be between 10-15 seconds. Following will be a simple example of what i mostly do:
SELECT DISTINCT C.CusID, C.Name, C.Age,
(
SELECT SUM (Price)
FROM CusotmerOrder
WHERE CusID_fk = CO.CusID_fk
) Total_Order_Price,
(
SELECT SUM (Concession)
FROM CusotmerOrder
WHERE CusID_fk = CO.CusID_fk
) Total_Order_Concession,
(
SELECT SUM (Price) - SUM (Concession)
FROM CusotmerOrder
WHERE CusID_fk = CO.CusID_fk
) Total_Difference
FROM Customer C
INNER JOIN CustomerOrder CO
ON C.CusID = CO.CusID_fk
......
WHERE (conditions...)
My question is what would be a better way to handle the above query? How can i write a better yet simple query with optimized performance. I would also mention that in some of my asp.net applications, i use inline queries assigned to SqlCommand Object. The reason i mention it that since these queries are written in some class files, how would we still accomplish what i have mentioned above. Kindly could any Query Guru guide me writing better queries. I shall be obliged...
Suppose I have this query, which shows each order and the price of the most expensive item in each order:
SELECT Ord.OrderID, Ord.OrderDate, MAX(OrdDet.UnitPrice) AS maxUnitPrice
FROM Northwind.dbo.[Order Details] AS OrdDet INNER JOIN Northwind.dbo.Orders AS Ord ON Ord.OrderID = OrdDet.OrderID
GROUP BY Ord.OrderID, Ord.OrderDate
I need to also show the ProductID that has MaxUnitPrice from the Order
Details. I can't just add ProductID to the select list because
I'd have to group by it, and then I'd get a row for each product,
instead of a row for each order... I think I need a correlated subquery
but can't work out how to do it!
I am running 2 versions of a correlated subquery. The two versiondiffer slightly in design but differ tremendously in performance....ifanyone can answer this, you would be awesome.The "bad" query attempts to build a result set using a correlatedsubquery. The part causing the error is that the correlated subqueryis part of a derived table (joining 3 tables). Trying to run the querytakes a long time and the more records in the source table, theperformance is exponentially worse. When i change the derived table toa fixed table, the query runs fast.I look at the Execution Plan in Query Analyzer and the majority of timeis taken by the Clustered Index Seek and by the Nested Loops/InnerJoin.************************************************** ************************************************** ******here is the "bad" query:************************************************** ************************************************** ******SELECT licenseKey, (SELECT TOP 1 mi.id FROM messages miINNER JOIN identities i ON i.id=mi.identityidINNER JOIN licenses l on i.licenseid=l.idWHERE l.licenseKey = t1.licenseKey AND category = 'usage'ORDER BY mi.created DESC) as messageidFROM licenses T1************************************************** ************************************************** ******here is the "good" query************************************************** ************************************************** ******SELECT licenseKey, (SELECT TOP 1 t2.id FROM temptable T2WHERE t2.licenseKey = t1.licenseKeyORDER BY t2.created DESC) as messageidFROM licenses T1************************************************** ************************************************** ******Thank you in advance
View 5 Replies View Relatedrecord_id Status Due_date549In Progress2004-06-02 00:00:00.000549Not Started2004-06-07 00:00:00.000549Not Started2004-06-08 00:00:00.000549Waiting 2004-05-31 00:00:00.000549Waiting 2004-06-04 00:00:00.000550Completed2004-05-05 00:00:00.000551Completed2004-05-06 00:00:00.000551Completed2004-05-07 00:00:00.000551Completed2004-05-10 00:00:00.000551Not Started1900-01-01 00:00:00.000552Not Started1900-01-01 00:00:00.000Hi I have this table with 3 columns.. What I want isDistinct(record_id),max(due_date) and Status.. I tried thisselect distinct(record_id),status,(due_date) from table1 where(due_date) in(select max(due_date) from table1 as A where a.record_id=record_idand a.due_date is not null group by a.record_id,status)So the result that I want isRecord Status Max(due_date)549Not Started2004-06-09 00:00:00.000550Completed2004-05-05 00:00:00.000551Completed2004-05-10 00:00:00.000Any help is appreciated..ThanksAJ
View 3 Replies View RelatedI have SQL query/dual sub-query in MS Access that is returning data from the left side of the query FROM correctly, but is only returning one record from the right side of the query FROM. Furthermore, it repeats the display of the one record and it repeats the entire results set with a different one record each time until all the records have been displayed. I expect that problems described as “Furthermore” will not exist by fixing the one record issue. I have tried using all the join types available in MS Access, but none change the result.
The desired output is:
Yellow Blue
11/23/201311/19/2013
11/19/210310/01/2012
10/01/210210/08/2010
10/08/201012/14/2007
The actual output is:
Yellow Blue
11/23/201311/19/2013
11/19/210311/19/2013
10/01/210211/19/2013
10/08/201011/19/2013
11/23/201310/01/2102
11/19/210310/01/2102
10/01/210210/01/2102
10/08/201010/01/2102
The same pattern is repeated 2 more times with Blue values of 10/08/2010 and then 12/14/2007.
Here is the SQL:
SELECT Long_List.Yellow,Short_List.Blue
FROM
(
SELECT DISTINCT BirthDate AS Blue
FROM (
SELECT DISTINCT BirthDate FROM citizens
[Code] .....
I have a 7 million line table named SecurityID with the following data:
Date, Security, Identifier1, Identifier2, Identifier3
I am trying to reduce it to a table newSecurityID in the following form:
FromDate, ToDate, SecurityId, Identifier1, Identifier2, Identifier3
This new table will have the first instance for each securityId with the identifying information. New rows will be added If any of the 3 identifying information changes. This isn't as simple as querying for the maximum and minimum value given each distinct group of identifiers because identifiers can change from an initial set and then change back to the initial values.
My plan was to first select all distinct (Security, Identifier1, Identifier2, Identifier3) into a temporary table. Then query the table SecurityID for the minimum date available which matches these 4 fields and find the corresponding maximum value.
This doesn't seem to working as I had planned as I am getting one row for each date rather than when identifiers change. Plus its taking a really long time to finish.
Any help will be appreciated!
Here is my code:
select distinct SecurityId, Identifier1, Identifier2, Identifier3 into #DistinctSecurityID from SecurityID
insert into
newSecurityID (FromDate, ToDate, SecurityId, Identifier1, Identifier2, Identifier3 )
select
FromDate=min( a.Date),
ToDate=isnull((
select min(b.Date)
from
SecurityId b
where
b.SecurityId=a.SecurityId
and b.Date>a.Date
and (a.Identifier1!= b.Identifier1
OR a.Identifier2!=b.Identifier2
OR a.Identifier3!= b.Identifier3)
),'20991231'),
#DistinctSecurityID.SecurityId,
#DistinctSecurityID.Identifier1,
#DistinctSecurityID.Identifier2,
#DistinctSecurityID.Identifier3
from
SecurityID a,
#DistinctSecurityID
where
#DistinctSecurityID.SecurityId=a.SecurityId
and #DistinctSecurityID.Identifier1=a.Identifier1
and #DistinctSecurityID.Identifier2=a.Identifier2
and #DistinctSecurityID.Identifier3=a.Identifier3
group by
#DistinctSecurityID.SecurityId,
#DistinctSecurityID.Identifier1,
#DistinctSecurityID.Identifier2,
#DistinctSecurityID.Identifier3,
a.SecurityId,
a.Date,
a.Identifier1,
a.Identifier2,
a.Identifier3
order by
a.SecurityId,
min(a.Date)
Note the following sql query. It contains two separate queries, an correlated subquery and outer query to work against the results of the subquery. Its purpose is twofold (1) get the TOP n ranked field entities using a certain value, (2) return all records for those entities.
SELECT MasterLoanID, NoteNumber, LendingOfficer,OriginalAmount, ReviewSampling FROM MasterLoanData WHERE Import_AsOfDate = '2006-05-31' AND BankID = '1' AND clientID = 1 AND LendingOfficer IN(SELECT TOP 3 LendingOfficer FROM MasterLoanData WHERE Import_AsofDate = '2006-05-31' AND ClientID = 1 AND BankID = '1' GROUP BY LendingOfficer ORDER BY SUM(OriginalAmount) DESC) ORDER BY LendingOfficer, Notenumber
Note that both queries need to filter the same fields -- import_AsofDate, BankID, and ClientID -- in order produce accurate results. Separate indexes exist for all three fields. Both queries work against the Sql Express database, however, when I combine them in a sql statement, Sql Express seems totally lost -- the query runs but never finishes -- I have to abort execution!
I've isolated the problem down to referencing of the import_AsofDate field in the outer query WHERE clause. If I remove that field reference from the outer WHERE clause, the query works quickly -- in seconds, however the results aren't accurate because I'm not getting a filter against the correct Import_AsofDate value. Note too that the same Access database executes the same query in seconds! Sql Express just seems totally confused by date reference contained in both WHERE clauses. I'd say that signifies a definite problem in Sql Express.
Has anyone experienced a similar problem with a correlated subquery and the same date field being referenced in both WHERE clauses of each query? I don't currently have a full fledged Sql Server database to test this query against, but seems as though it should work.
Rick
I'm having some performance issues with a TSQL query. It's a complex statement but the main issue is the correlated query.
How can I convert this
SELECT TOP 5
(SELECT SUM(lt2.col3)
FROM dbo.MyTable2 lt2
WHERElt2.col1 = lt.col1 AND lt2.col2 = lt.col2 AND lt2.id = lt.id ) AS Result
FROM dbo.MyTable1 t1
... to an inner join or a sql2012 window function?
By the way, I just added the TOP 5 myself while testing. It's not in the main query.
Any ideas how can I pass date range values from the where clause of an outer query to the inner correlated subquery ... without using a stored procedure because I am using Report Builder?
Using the simplified sql below I need the average freight charge between the dates for all of the ShipCountry's orders. (I have hard coded the dates for demo purposes only as it is these that I need referenced from the outer query's where clause.)
select
OrderDate,
ShipCountry,
ShipCity,
Freight,
/* how do I get to the outer query's date range ? */
(SELECT AVG(Freight) FROM Orders WHERE ShipCountry = O.ShipCountry AND OrderDate between '01-jan-1997' and '01-jan-2000') AS CountryAverageFreight
from
Northwind.dbo.Orders O
where
ShipCity = 'Paris' and OrderDate between '01-jan-1997' and '01-jan-2000'
Thanks
I have data in a table (@Outer) that I am matching to a lookup table (@Inner) which contains multiple "matches" where nulls can match any value. By sorting the inner table and grabbing the top record, I find the "best" match. I know the sort and the null matches work but I don't understand why the correlated sub query below doesn't understand that the OJ prefix refers to the outer table.DECLARE @Outer TABLE (
OuterID int IDENTITY (1, 1) NOT NULL,
MethodID int NULL,
CompID int NULL,
FormID int NULL,
InnerID int NULL
)
INSERT @Outer VALUES (2, 2, 2, NULL) -- OuterID = 1
INSERT @Outer VALUES (3, 2, 1, NULL) -- OuterID = 2
DECLARE @Inner TABLE (
InnerID int IDENTITY (1, 1) NOT NULL,
MethodID int NULL,
CompID int NULL,
FormID int NULL
)
INSERT @Inner VALUES (2, null, null) -- InnerID 1
INSERT @Inner VALUES (2, null, 1) -- InnerID 2
INSERT @Inner VALUES (2, 2, null) -- InnerID 3
INSERT @Inner VALUES (3, null, null) -- InnerID 4
INSERT @Inner VALUES (3, 2, null) -- InnerID 5
INSERT @Inner VALUES (4, 2, 1) -- InnerID 6
-- UPDATE Outer Table with best match from Inner table
UPDATE @Outer SET
InnerID = IJ.InnerID
FROM @Outer OJ
INNER JOIN
(
SELECT TOP 1 I.*
FROM @Inner I
WHERE IsNull(I.MethodID, OJ.MethodID) = OJ.MethodID
AND IsNull(I.CompID, OJ.CompID) = OJ.CompID
AND IsNull(I.FormID, OJ.FormID) = OJ.FormID
ORDER BY I.MethodID DESC, I.CompID DESC, I.FormID DESC
) IJ ON OJ.MethodID = IsNull(IJ.MethodID, OJ.MethodID)
AND OJ.CompID = IsNull(IJ.CompID, OJ.CompID)
AND OJ.FormID = IsNull(IJ.FormID, OJ.FormID) SELECT * FROM @Outer
The result should be OuterID 1 matched to Inner ID 3 and OuterID 2 matched to Inner ID 5.
Can anyone help me? Thanks in advance.
Hi all,
I copied and executed the following sql code in my SQL Server Management Studio Express (SSMSE):
--SELECTeg8.sql from SELECT-Using correlated subqueries of MSDN2 SELECT Examples--
USE AdventureWorks ;
GO
SELECT DISTINCT Name
FROM Production.Product p
WHERE EXISTS
(SELECT *
FROM Production.ProductModel pm
WHERE p.ProductModelID = pm.ProductModelID
AND pm.Name = 'Long-sleeve logo jersey') ;
GO
-- OR
USE AdventureWorks ;
GO
SELECT DISTINCT Name
FROM Production.Product
WHERE ProductModelID IN
(SELECT ProductModelID
FROM Production.ProductModel
WHERE Name = 'Long-sleeve logo jersey') ;
GO
=========================================
I got:
Results Messages
Name o row affected
========================================
I think I did not get a complete output from this job. Please help and advise whether I should search somewhere in the SSMSE for the complete results or I should correct some code statements in my SELECTeg8.sql for obtaining the complete results.
Thanks in advance,
Scott Chang
Original code (works in Sql Server 2000 but has error in Sql Server 2005) --
AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
Error message in Sql Server 2005 --
Server: Msg 8180, Level 16, State 1, Line 38
Statement(s) could not be prepared.
Server: Msg 107, Level 16, State 1, Line 38
The column prefix 'Tbl1001' does not match with a table name or alias name used in the query.
Corrected code --
AND SUBSTRING(CONVERT(CHAR,A_ED.EFFDT,121), 1, 10) <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
What's up with this?
This takes like 0 secs to complete:
update xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
but From linked server this takes 8 minutes????!!!??!:
update [xxx_servername_xxxx].xxx_DatabaseName_xxx.dbo.xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
What settings or whatever would cause this to take so much longer from the linked server?
Edit:
Note) Other queries from the linked server do not have this behavior. From the stored procedure where we have examined how long each query/update takes... this particular query is the culprit for the time eating. I thought it was to do specefically with this table. However as stated when a query window is opened directly onto that server the update takes no time at all.
2nd Edit:
Could it be to do with this linked server setting?
Collation Compatible
right now it is set to false? I also asked this question in a message below, but figured I should put it up here.
I am hoping someone can shed light on this odd behavior I am seeing running a simple UPDATE statement on a table in SQL Server 2000. I have 2 tables - call them Table1 and Table2 for now (among many) that need to have certain columns updated as part of a single transaction process. Each of the tables has many columns. I have purposely limited the target column for updating to only ONE of the columns in trying to isolate the issue. In one case the UPDATE runs fine against Table1... at runtime in code and as a manual query when run in QueryAnalyzer or in the Query window of SSManagementStudio - either way it works fine.
However, when I run the UPDATE statement against Table2 - at runtime I get rowsaffected = 0 which of course forces the code to throw an Exception (logically). When I take out the SQL stmt and run it manually in Query Analyzer, it runs BUT this is the output seen in the results pane...
(0 row(s) affected)
(1 row(s) affected)
How does on get 2 answers for one query like this...I have never seen such behavior and it is a real frustration ... makes no sense. There is only ONE row in the table that contains the key field passed in and it is the same key field value on the other table Table1 where the SQL returns only ONE message (the one you expect)
(1 row(s) affected)
If anyone has any ideas where to look next, I'd appreciate it.
Thanks
Hi SQL fans,I realized that I often encounter the same situation in a relationdatabase context, where I really don't know what to do. Here is anexample, where I have 2 tables as follow:__________________________________________ | PortfolioTitle|| Portfolio |+----------------------------------------++-----------------------------+ | tfolio_id (int)|| folio_id (int) |<<-PK----FK--| tfolio_idfolio (int)|| folio_name (varchar) | | tfolio_idtitle (int)|--FK----PK->>[ Titles]+-----------------------------+ | tfolio_weight(decimal(6,5)) |+-----------------------------------------+Note that I also have a "Titles" tables (hence the tfolio_idtitlelink).My problem is : When I update a portfolio, I must update all theassociated titles in it. That means that titles can be either removedfrom the portfolio (a folio does not support the title anymore), addedto it (a new title is supported by the folio) or simply updated (atitle stays in the portfolio, but has its weight changed)For example, if the portfolio #2 would contain :[ PortfolioTitle ]id | idFolio | idTitre | poids1 2 1 102 2 2 203 2 3 30and I must update the PortfolioTitle based on these values :idFolio | idTitre | poids2 2 202 3 352 4 40then I should1 ) remove the title #1 from the folio by deleting its entry in thePortfolioTitle table2 ) update the title #2 (weight from 30 to 35)3 ) add the title #4 to the folioFor now, the only way I've found to do this is delete all the entriesof the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =2), and then insert new values for each entry based on the new givenvalues.Is there a way to better manage this by detecting which value has to beinserted/updated/deleted?And this applies to many situation :(If you need other examples, I can give you.thanks a lot!ibiza
View 8 Replies View RelatedThe Folowing code is not working anymore. (500 error)
Set objRS = strSQL1.Execute
strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID)
objRS.Open strSQL1, objConn , 2 , 3 , adCmdText
If not (objRS.BOF and objRS.EOF) Then
objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1
objRS.update
End If
objRS.Close
The .execute Method works fine
strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID)
objConn.Execute strSQL1
W2003 + IIS6.0
Pls advice?
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE