Transact SQL :: Query Works Even If Column Not Exists In Subquery
Jul 23, 2015
When I execute the below queries it works perfectly where as my expectation is, it should break.
Select * from ChildDepartment C where C.ParentId IN (Select Id from TestDepartment where DeptId = 1)
In TestDepartment table, I do not have ID column. However the select in sub query works as ID column exists in ChildDepartment. If I do change the query to something below then definately it will break -
Select * from ChildDepartment C where C.ParentId IN (Select D.Id from TestDepartment D where D.DeptId = 1)
Shouldn't the default behavior be otherwise? It should throw error if column doesnt exists in sub query table and force me to define the correct source table or alias name.
create table TestDepartment
(
DeptId int identity(1,1) primary key,
name varchar(50)
)
create table ChildDepartment
(
Id int identity(1,1) primary key,
I have a subquery i wanted to add a as a fourth column to my Original Query(is the one below the subquery). How to combine the two queries to one statement?? I tried but was getting an error "Subquery returned more than 1 value "
select COUNT(*)FreeReduced from students s join Buildings b on s.Building_ID = b.Building_ID where s.Activeness =1 and (Eligibility = 3 or Eligibility =2) group by building_number,Building_Name
Below is the query in which i want to retrieve another column (exchange rate) from a particular date for the sub query.
Actually PurchaseOrderDet table get records related to purchaseorder but for each row in purchaseorderdet need info from the same table for all rows on a particular date.
how i can achieve this query without using RANK() and other functions.
"SELECT Supplier.Uniid AS SupplierID, Supplier.Name, PurchaseOrder.Uniid AS PurchaseID, PurchaseOrder.OrderNo, PurchaseOrder.FormDate, StockItem.Uniid AS StockID, StockItem.StockCode + N' - ' + StockItem.Description1 AS StockItem, PurchaseOrderDet.ExchangeRate, PurchaseOrderDet.UnitPrice, PurchaseOrderDet.Discount, SUM(PurchaseOrderDet.OrderQty) AS SumOfOrderQty,
I am using SQL Server 2008 - and what I want to do is set my variable @dh. If the @startDate and @endDate falls into the criteria for my if exists statement, I want to set @dh equal to datediff(h, logontime, logofftime) BUT if that criteria is not true, I want to set @dh = 24. How can I do that?
When i am running below snippet execution plan is showing constant scan instead of referring subquery table.
I want to know how this query working. and why in execution plan there is no scan /seek which will basically indicate that particular table is getting referred.
select count(*) from A where exists (select count(1) from B where A.a=B.a)
execution plan has to show scan or seek for subquery. Surprisingly, output is coming as expected.
DECLARE @Teams AS TABLE(Team VARCHAR(3)) INSERT INTO @Teams SELECT 'IND' UNION SELECT 'SA' UNION SELECT 'AUS' select Team from @Teams where Team > 'AUS'
[code]....
co-relation between comparison operators in WHERE Clause and the respective output.
Hi, I have 2 tables:tblStations StationIDStationtblStationUser RecordIDUserNameStationI'm trying to come up with a dataset that contains thetblStations.StationEXCEPT for where that Station exists in tblStationUser where theUserName = @varUserName.I've tried this but get 0 rows (I should get about 40):SELECT tblStations.StationFROM tblStationsWHERE NOT EXISTS(SELECT tblStationUser.Station FROM tblStationUser WHEREtblStationUser.UserName=@varUserName)ORDER BY StationI tried the subquery separately which returns the correct number ofrows.Any clues as to where I'm going wrong?Thanks!Kathy
Hi folks,A DTS package we have run for years now no longer works. The specificpart that is not working is a subquery in the SOURCE object of atransformation. The source is based on a Microsoft Data Link to aSybase database (DSN changed a couple months ago but the connectionstring was updated successfully for the new 12.51 version of ASE) andthe destination is a link to a local SQL Server 2000 database.The transformation has always worked and when I remove the subqueryeverything works OK. The problem is that I need the subquery!Does anyone have a clue what is going on?Here is the full query.select TableKey = RVSN_TYPE_ID,TableCode = RVSN_TYPE,RevisionDate = RVSN_DATE,RevisionReasonCode = RSN_CODE,RevisionGroup = RVSN_GRP_ID,RevisedField = (select L.FieldIDfrom tempdb.guest.lkpRevisedField Lwhere L.TableID = R.RVSN_TYPEand L.FieldName = R.CHNG_FLD),RevisedValue = OLD_FLD_VAL,RevisionTimestamp = RVSN_TIMESTAMPfrom RVSN R,tempdb.guest.MaxTimeStamp TSwhere R.RVSN_TIMESTAMP TS.Rtimestampand R.RVSN_TIMESTAMP is NOT NULLJohn H.
When I try to save this stored procedure in VS.NET to SQL Server 2000 database, I keep getting the following error and have no idea how to correct it: "ADO error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS"
ALTER PROCEDURE dbo.sProcRevenueForTerminalByAcocuntManager ( @fromDate datetime, @toDate datetime )
AS SET NOCOUNT ON RETURN ( SELECT TOP 100 PERCENT T2.TerminalCode AS GroupName1, T3.TerminalName AS GroupName2, T2.AccountManagerName AS DetailName, SUM(T1.InvoiceDEDTotalAmount) AS DetailRevenue FROM dbo.TBLINVOICEDED T1 INNER JOIN dbo.TBLCUSTOMERS T2 ON T1.CustomerId = T2.CustomerId INNER JOIN dbo.TBLTERMINALDATA T3 ON T2.TerminalCode = T3.TerminalCode WHERE (T1.InvoiceDEDDate >= @fromDate) AND (T1.InvoiceDEDDate <= @toDate) GROUP BY T2.AccountManagerName, T2.TerminalCode, T3.TerminalName HAVING (NOT (SUM(T1.InvoiceDEDTotalAmount) IS NULL)) ORDER BY T2.TerminalCode, T2.AccountManagerName )
Im having a slight problem deleting/ updating some specific rows out of my table
when i worked out the select statment i got the correct return on data however when i place the same logic into a delete or update it seems to apply the logic to everything any help would be much appreciated :)
the select query: select distinct id, max(timeexecuted) from table1 group by id
the delete query: delete table1 where exists (select distinct id, max(timeexecuted) from table1 group by id)
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 )
-- 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.
When our production site was deployed on the client's WinServer2003, my webservice is throwing a "server does not exist or access denied" exception. I'm using the same connection string (typed once) as i'm using in my web forms on the user visible sections of the site. the service also works fine on my XP testing machine. unfortunately, I'm not a 2003 admin. If anyone can help, i would greatly appreciate it, trying to find what is misconfigured on the client's server is driving me bonkers.
Hi, When i try to save my stored procedure.. i am getting the above error and this is my sproc 1 INSERT INTO Statement..ClientSources 2 ( 3 ClientId, 4 ClientSourceId, 5 SourceName 6 ) 7 Select Distinct 8 @ClientId, 9 SOURCE_NUM, 10 (Select CASE s.SOURCE_NUMWhen 1 Then SRC1NAME 11 WHEN 2 Then SRC2NAME 12 WHEN 3 THEN SRC3NAME 13 WHEN 4 THEN SRC4NAME 14 WHEN 5 THEN SRC5NAME 15 WHEN 6 THEN SRC6NAME 16 WHEN 7 THEN SRC7NAME 17 WHEN 8 THEN SRC8NAME 18 WHEN 9 THEN SRC9NAME 19 WHEN 10 THEN SRC10NAME 20 WHEN 11 THEN SRC11NAME 21 WHEN 12 THEN SRC12NAME 22 WHEN 13 THEN SRC13NAME 23 WHEN 14 THEN SRC14NAME 24 WHEN 15 THEN SRC15NAME 25 END 26 FROM 27 PlanDBF p 28 Where 29 p.PLAN_NUM = s.PLAN_NUM 30 ) as SourceName 31 FROM 32 SourceDBF s 33 Where 34 SOURCE_NUM NOT IN ( 35 SELECT DISTINCT 36 ClientSourceId 37 --SourceName 38 FROM 39 Statement..ClientSources 40 Where 41 ClientId = @ClientId 42 )
I am getting the error in Line number 35 .. the inserts works fine... and if use * instead of the field name or use more than 1 field name i get this error Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Any help will be appreciated. Regards Karen
I am getting error [[Msg 116, Level 16, State 1, Line 7 .Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.]] for the below script.
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value ----------- ----- colB 123 colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
I have a data with mutliple esn but different auditdate and opid. I will pull this data filtering by date and opid. My requirements is not to include the opid = 51 but need to get the desired opdesc for this esn that contains opid=51.
See below sample ddl and desired result. I dont want do include the opid = 51 because it will create a duplicate in transaction instead retain the opid 5
example: esn T9000000000019829505 has multiple rows with different auditdate and opid. retain the records for opid is equal to 5 but get the opdesc for opid is equal 51.
reate table #test (esn nvarchar(35), dateaudit datetime, opid int) insert into #test(esn,dateaudit, opid)values('352452060499834','2015-05-12 20:32:39.490',5) insert into #test(esn,dateaudit, opid)values('352452060499834','2015-07-06 17:35:14.210',5) insert into #test(esn,dateaudit, opid)values('T9000000000019829505','2015-01-14 15:18:45.620',5)
I have the below data and want to only show records where Response Column has Max date for the commencement date and expiry date under questions column.. For example for tenantcode 52090 the highest Tab value is 2 but this is not correct as there is no associated value in response.
We need to compare all the commencement and lease expiry dates and show only the commencement and lease expiry with the recent dates. Null should be ignored Instead of using Max Tab or Max Modified date would the below logic work? Not sure how to script this.
1. Filter to find Questions containing "Expiry Date"
2. summarise to find the max expiry date per Tenant Code
I have the following stored proc that I need to pull InsuranceID specific rows when @InsuranceID > 0 is sent. Below is the code and I think the issue is in the 2nd WHERE condition.
ALTER PROCEDURE [dbo].[ms_selJobSetupSelections] (@ActiveOnlybit = 1, @InsuranceIDint = 0) AS BEGIN SELECT J.JobSetupID ,J.JobSetupText
I have a stored procedure with a where clause like this: WHERE Q.EffectiveDate >= @FromEffectiveDate AND Q.EffectiveDate <= @ToEffectiveDate AND I.InsuredName LIKE '%' + isnull(@PreQuoteDesc,I.InsuredName) + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%' AND rsu.FirstName LIKE '%' + isnull(@OwnerFirstName, rsu.FirstName) + '%' AND rsu.LastName LIKE '%' + isnull(@OwnerLastName, rsu.LastName) + '%' AND Q.quoteID = isnull(@quoteID,Q.QuoteID) AND Q.QuoteStatusID = isnull(@quoteStatusID, Q.QuoteStatusID) AND rsu.AspNetUserID = isnull(@ASPNetUserID, rsu.AspNetUserID) ------------------------------------------------------------------- All is working well except for the line of business: ------------------------------------------------------------ AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%' AND isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%' --------------------------------------------------------------------------------- If the user checks just 'Property' results look like: Property Property Property, General Liability If the user checks just 'General Liability' the resultes look like: Genral Liablility General Liability General Liability, Inland Marine If the user checks both Property and General Liability all they get back is: Property, General Liability They should get back everything including just Property or just General Liability or both. So I tried to change the ANDs to ORs and it doesn't work. ----------------------------------------- AND ( isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%' OR isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%' ) I know this is incredibly hard to follow because its incredibly hard to write out. Is there anyone smart out there who can figure this out? Thanks
i like to know how CTE works in sql server. i need to understand the flow of CTE. here i am pasting one example of CTE which i need to understand how works
1) when CTE is declared then why fields name is used ;WITH interval_cte(StartTime, EndTime) AS ? what is the use of these field name.i do not understand how this line works
SELECT EndTime, DATEADD(mi, 30, EndTime) FROM interval_cte WHERE EndTime < @EndTime
it has no alias define DATEADD(mi, 30, EndTime) ?how end time is increasing in loop ?which line increase end time value ?
(select SUM(sales.Total) from sales where StudentHist.Curdate = max(sales.curdate)) AS 'Balance'Iam trying to write a subquery to calculate the total amount of sales until the Curdate in studenthist equals the Curdate in sales table..how to write this query??
with c1 As ( select 1 As '1' , 2 As '2' , 3 As '3' ) , c2 As ( select 4 As '4', 5 As '5', 6 As '6' union all select 1 , 2 ,3 from c1 -- >>>>> select from c1 here ) select * from c2 union all select * from c1 -- >>>>>> and select from c1 here
According to the query above , I try to reuse the subquery by put the subquery into 'with cte' name (c1) then i select this 2 times .
if I do this way , how many time this subquery (c1) execute ? if 1 time then this is the right way to reuse this subquery . if 2 times , it is not then what should i do to reuse this subquery ?
(for info purposes the relationship here is DiagnosisCode = Procedure01, 02, 03 etc)
Is it possible to right a query that shows all records in dbo_OP_APPOINTMENT_PROCEDURE_PIVOT where the DiagnosisCode in table PneumoniaCareBundleDiagnosisCodes appear in any of the Procedure01, 02, 03 fields etc.
If I was using an IF statement the logic i would right it as follows: IF dbo_OP_APPOINTMENT_PROCEDURE_PIVOT.Procedure01 IS IN PneumoniaCareBundleDiagnosisCodes.DiagnosisCode OR IF dbo_OP_APPOINTMENT_PROCEDURE_PIVOT.Procedure02
Hey all - got a problem that seems like it would be simple (and probably is : )
I'm importing a csv file into a SQL 2005 table and would like to add 2 columns that exist in the table but not in the csv file. I need these 2 columns to contain the current month and year (columns are named CM and CY respectively). How do I go about adding this data to each row during the transformation? A derived column task? Script task? None of these seem to be able to do this for me.
Here's a portion of the transformation script I was using to accomplish this when we were using SQL 2000 DTS jobs:
' Copy each source column to the destination column Function Main() DTSDestination("CM") = Month(Now) DTSDestination("CY") = Year(Now) DTSDestination("Comments") = DTSSource("Col031") DTSDestination("Manufacturer") = DTSSource("Col030") DTSDestination("Model") = DTSSource("Col029") DTSDestination("Last Check-in Date") = DTSSource("Col028") Main = DTSTransformStat_OK End Function *********************************************************** Hopefully this question isnt answered somewhere else, but I did a quick search and came up with nothing. I've actually tried to utilize the script component and the "Row" object, but the only properties I'm given with that are the ones from the source data.
SELECT A.EmpId,A.IncidentDate FROM EmployeePoints1 as A WHERE IncidentDate= (SELECT MAX(IncidentDate) FROM EmployeePoints1 WHERE EmpId = A.EmpId) AND (DATEADD(day,28,DATEADD(WEEK, DATEDIFF(WEEK, 0,A.IncidentDate), 0)) < DATEADD(WEEK, DATEDIFF(WEEK, 0,GetDate()), 0)) AND (A.IncidentCode = 'I' OR A.IncidentCode = 'A') LEFT JOIN EmployeeTotalPoints1 ON EmployeeTotalPoints1.EmpId = A.EmpId
SSMS does not like mine! THis is the error that I receive:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
And this is my syntax:
Select employeeID ,COUNT(case when rehirestatus IN (select rehirestatus from regionalemptable where rtrim(storename) = 'Location1') THEN userID ELSE 0 END) + COUNT(case when rehirestatus IN (select rehirestatus from globalemptable where rtrim(storename) = 'Location1') Then userID ELSE 0 End) FROM production GROUP BY employeeID ORDER BY employeeID
This is my query, from my nonono table I need to return the most recent date from attempted (column name) How could I modify this to do such
Select * FROM [nonono] td WHERE (NOT EXISTS (SELECT [First], [second] FROM [yesyesyes] AS d WHERE ([First] = td.[First]) AND ([second] = td.[second]) AND ([Worksite] = td.[Worksite])))
I am trying to bulk update about 50,000 rows in a SQL table. The values that the table MUST contain are:
1-3 days 4-7 days 8-10 days
Some of the rows contain a space between the number and the hyphen like:
1 - 3 days 4 - 7 days 8 - 10 days
What would be my best methodology of removing the space between numerics only? I have seen multiple examples of how to remove ALL whitespace, but I only want to remove the space between the numbers and the hyphen *IF* it exists. And the field type is varchar(200)
I am trying to determine the existence of at least one row in my Detail table using EXISTS in my SELECT list from my Main table.SELECT M.ID,EXISTS(SELECT 1 FROM Detail D WHERE D.ID = M.ID) as HasDataFROM Main MCan this be done this way?I was hoping that using EXISTS would find a row and move on thus increasing performance.
