Server Set Bup For Subquery
Jan 28, 2003
Hello,
Here is the below query I am running at server A and getting this below error.
If I am running the same query in Server B and running fine without any error.
My question is do I need to do any server configuration?. Or any solution for this issue.
I really appreciate your help.
Thanks,
Ravii
UPDATE Leap.dbo.CIB
SET credit_status = c.credit_status
FROM DNT02.oesd.dbo.credit_line c
WHERE c.effective_date = (SELECT MAX(c1.effective_date)
FROM DNT02.oesd.dbo.credit_line c1
WHERE c.account_num = c1.account_num
)
AND Leap.dbo.CIB.account_num = c.account_num
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'c' does not match with a table name or alias name used in the query.
View 14 Replies
ADVERTISEMENT
Apr 26, 2008
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...
View 1 Replies
View Related
Jul 20, 2005
I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO
View 3 Replies
View Related
Mar 6, 2008
I am getting an error as
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
while running the following query.
SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,
LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,
LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,
ManagerName=(SELECT E.FirstName+' '+E.LastName
FROM EmployeeDetails E
INNER JOIN LUP_EmpProject
ON E.Empid=LUP_EmpProject.Empid
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid
WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)
FROM EmployeeDetails
INNER JOIN LUP_EmpDepartment
ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid
INNER JOIN LUP_FIX_DeptDetails
ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid
AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date
FROM LUP_EmpDepartment
WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid
ORDER BY LUP_EmpDepartment.Date DESC)
INNER JOIN LUP_EmpDesignation
ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid
INNER JOIN LUP_FIX_DesigDetails
ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid
AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date
FROM LUP_EmpDesignation
WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid
ORDER BY LUP_EmpDesignation.Date DESC)
INNER JOIN LUP_EmpProject
ON EmployeeDetails.Empid=LUP_EmpProject.Empid
AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate
FROM LUP_EmpProject
WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid
ORDER BY LUP_EmpProject.StartDate DESC)
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid
WHERE EmployeeDetails.Empid=1
PLEASE HELP.................
View 1 Replies
View Related
May 14, 2008
Hi,
I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Any ideas as to why??
SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar,
RDR1.U_Variety,
(SELECT OITM.U_Variety
FROM OWOR INNER JOIN
WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN
OITM INNER JOIN
OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode
WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity,
OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet,
OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode
FROM OWOR AS t0 INNER JOIN
ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN
RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN
OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode
WHERE (t0.Status <> 'L')
Thanks
Jacquues
View 4 Replies
View Related
Jul 19, 2007
Hi guys,
A have a problem that I need understand.... I have 2 server with the same configuration...
SERVER01:
-----------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
********************************
SERVER02:
-----------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
OK, the problem is that if a run the below query in server01, i get error 512:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
But, if run the same query in the server02, the query work fine -.
I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.
Any idea WHY?
SELECT dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao ds_tipo_transacao_movimento ,
dbo.opf_header_operacao.ds_tipo_transacao ds_tipo_transacao_header,
'SD' ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente,
0 vl_entrada_compra_ctro ,0 vl_entrada_compra_premio,
0 vl_entrada_venda_ctro , 0 vl_entrada_venda_premio,
0 vl_saida_compra_ctro, 0 vl_saida_compra_premio,
0 vl_saida_venda_ctro, 0 vl_saida_venda_premio,
0 vl_lucro , 0 vl_prejuizo, 0 vl_naoexec_contrato,
0 vl_naoexec_premio,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_ganho) vl_aprop_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_perda) vl_aprop_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_ganho) vl_rever_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_perda) vl_rever_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_irrf) vl_irrf
FROM dbo.opf_saldo_ctb_opc_flx,
dbo.opf_header_operacao ,
dbo.opf_movimento_operacao
WHERE dbo.opf_saldo_ctb_opc_flx.dt_saldo = '6-29-2007 0:0:0.000'
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_saldo_ctb_opc_flx.no_contrato )
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_movimento_operacao.no_contrato )
and ( dbo.opf_movimento_operacao.dt_pregao = (select (o.dt_pregao) from dbo.opf_movimento_operacao o
where o.no_contrato = dbo.opf_movimento_operacao.no_contrato and o.dt_pregao <='6-28-2007 0:0:0.000' ) )
and (dbo.opf_saldo_ctb_opc_flx.ic_tipo_saldo = 'S')
group by dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao,
dbo.opf_header_operacao.ds_tipo_transacao ,
ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente
Thanks
Nilton Pinheiro
View 9 Replies
View Related
Jul 6, 2014
I am trying to add the results of both of these queries together:
The purpose of the first query is to find the number of nulls in the TimeZone column.
Query 1:
SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename
The purpose of the second query is to find results in the AAST, AST, etc timezones.
Query 2:
SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')
Note: both queries produce a whole number with no decimals. Ran individually both queries produce accurate results. However, what I would like is one query which produced a single INT by adding both results together. For example, if Query 1 results to 5 and query 2 results to 10, I would like to see a single result of 15 as the output.
What I came up with (from research) is:
SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST'))
I get a msq 102, level 15, state 1 error.
I also tried
SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')) as IVR_HI_n_AK_results
but I still get an error. For the exact details see:
[URL]
NOTE: the table in query 1 and query 2 are the same table. I am using T-SQL in SQL Server Management Studio 2008.
View 6 Replies
View Related
Nov 14, 2006
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))
View 10 Replies
View Related
Mar 14, 2002
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.
View 1 Replies
View Related
Mar 17, 2008
Hi guys,
I am trying to perform the following update:
update Table1 set Field1 = (select Field1 from Table2) where Table1.Field2 = Table2.Field2 (suppose that Field2 is PK)
but it seems it won't work on SQL Server Compact Edition!
The problem is that I want to update Table1 with Table2 records
I have the following table:
Table1
1 'someName' 'SomeLastName'
2 'someName1' 'SomeLastName1'
3 'someName2' 'SomeLastName2'
4 'someName3' 'SomeLastName3'
5 'someName4' 'SomeLastName4'
6 'someName5' 'SomeLastName5'
and another table
Table2
1 'someChangedName' 'someChangedLastName'
2 'someChangedName1' 'someChangedLastName1'
3 'someChangedName2' 'someChangedLastName2'
4 'someChangedName3' 'someChangedLastName3'
5 'someChangedName4' 'someChangedLastName4'
6 'someChangedName5' 'someChangedLastName5'
and I want to perform an update of table1 based on the table2 records that table1 to become:
Table1
1 'someName' 'SomeLastName'
2 'someName1' 'SomeLastName1'
3 'someName2' 'SomeLastName2'
4 'someName3' 'SomeLastName3'
5 'someName4' 'SomeLastName4'
6 'someName5' 'SomeLastName5'
I hope that I was clear enough .
thanks.
View 3 Replies
View Related
Mar 28, 2008
Hi Gurus,I have table (CallByCall) with following Data.Input :CallID Event DNIS UserID Time SeqID MobNo-----------------------------------------------------------------------------1 | 43 | 100 | |09:00:05 | 1 |98332107701 | 40 | | |09:01:05 | 2|1 | 41 | | |09:02:05 | 3|1 | 42 | | 25 |09:03:05 | 4|2 | 43 | 200| |09:10:05 | 1 |98339755362 | 40 | | |09:11:05 | 2|2 | 41 | | |09:12:05 | 3|2 | 42 | | 26 |09:13:05 | 4|3 | 43 | 300| |09:15:05 | 1 |98139785363 | 40 | | |09:15:55 | 2|3 | 41 | | |09:16:05 | 3|3 | 42 | | 28 |09:18:05 | 4|4 | 43 | 100| |09:45:05 | 1 |92239788964 | 40 | | |09:46:05 | 2|4 | 41 | | |09:47:05 | 3|4 | 42 | | 26 |09:48:05 | 4|Output:CallID DNIS UserID MinTime MaxTime MobNo--------------------------------------------------------------------------1 | 100 | 25 |09:00:05 |09:03:05 |98332107702 | 200 | 26 |09:10:05 |09:13:05 |98339755363 | 300 | 28 |09:15:05 |09:18:05 |98139785364 | 100 | 26 |09:45:05 |09:48:05 |9223978896In short I am getting DNIS and MobNo for Event 43 andUserId for Event 42.How can I achieve the above result?Is it possible with scalar query?Could any one help me for the same?Thanking in AdvanceSanjeev98332 10772
View 1 Replies
View Related
Oct 3, 2015
Below query. its getting more time to exec and got deadlock. So, query to avoid deadlock.
SELECT m1.Value AS InterfaceName, m1.MessageDateTime, m2.GroupId, COUNT(mError.Id) AS ErrorCount
FROM (
SELECT m1.Value, MAX(m1.MessageDateTime) as MessageDateTime FROM Message m1
WHERE m1.TypeId = 9 AND (m1.Value LIKE 'F02' )
GROUP BY m1.Value
[Code] ....
View 2 Replies
View Related
Jul 27, 2007
I am using SQL Server 2000. I have a somewhat large query and hope someone could help me with it.
(Man, there needs to be a way to use colors...) The bolded parts of the query need to be replaced by the underlined part. I can't say 'b.HW' because of the scope of the inline query and that they are all on the same level. I've been told I need to change to a nested subquery, but can't for the life of me figure out how to do that. Can someone please show me?
The current query:
SELECT x.SSN,x.RealName,x.BudgetCode,x.TH,b.HW,x.HP,z.HL,x.NHW,x.FSLAOT,y.HHW AS AH,y.NHH,CASE WHEN x.TH < x.HP THEN 'XX' WHEN x.TH > x.HP THEN x.NHW - x.HP - y.NHH WHEN x.TH <= x.HP THEN 0 END AS SOT,CASE WHEN x.TH > x.HP THEN x.NHW - x.HP - y.NHH + x.FSLAOT WHEN x.TH <= x.HP THEN 0 END AS AO
FROM(SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS TH,CASE WHEN SUM(a.hours) > 40 THEN (SUM(a.hours) - 40) * 1.5 WHEN SUM(a.hours) <= 40 THEN 0 END AS FSLAOT,CASE WHEN SUM(a.hours) >= 40 THEN 40 WHEN SUM(a.hours) < 40 THEN SUM(a.hours) END AS NHW,32 AS HP
FROM dbo.ActivitiesInCurrentFiscalYear aWHERE a.ItemDate BETWEEN startdate and enddate AND a.ScheduleType = 1 AND a.EmployeeType = 2GROUP BY a.SSN, a.RealName, a.BudgetCode) x LEFT OUTER JOIN
(SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS HHW,CASE WHEN SUM(a.Hours) >= 8 THEN 8 WHEN SUM(a.Hours) < 8 THEN SUM(a.Hours) END AS NHH
FROM dbo.ActivitiesInCurrentFiscalYear a, dbo.Holidays hWHERE a.ItemDate = h.HolidayDate AND a.ItemDate BETWEEN startdate and enddate AND a.EmployeeType = 2 AND a.ScheduleType = 1GROUP BY a.SSN, a.RealName, a.BudgetCode) y ON x.SSN = y.SSN AND x.RealName = y.RealName AND x.BudgetCode = y.BudgetCode LEFT OUTER JOIN
(SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS HLFROM dbo.ActivitiesInCurrentFiscalYear aWHERE a.ItemDate BETWEEN startdate and enddate AND a.EmployeeType = 2 AND a.ScheduleType = 1 AND (a.program_code = '0080' OR a.program_code = '0081')GROUP BY a.SSN, a.RealName, a.BudgetCode) z ON x.SSN = z.SSN AND x.RealName = z.RealName AND x.BudgetCode = z.BudgetCode LEFT OUTER JOIN
(SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS HWFROM dbo.ActivitiesInCurrentFiscalYear aWHERE a.ItemDate BETWEEN startdate and enddate AND a.EmployeeType = 2 AND a.ScheduleType = 1 AND NOT (a.program_code = '0080' OR a.program_code = '0081')GROUP BY a.SSN, a.RealName, a.BudgetCode) b ON x.SSN = b.SSN AND x.RealName = b.RealName AND x.BudgetCode = b.BudgetCode)
View 8 Replies
View Related
Jun 14, 2007
I have some relatively simple SQL that acts differently between SQL Server 2000 and 2005. Although it is easy to fix I'd like to know if this difference is expected (documented) or a bug, and if there is perhaps a setting/switch I can use to avoid a code review of hundreds of stored procs to look for similar scenarios. Executed the following script in SQL Server 2005 –CREATE TABLE #Floats(FloatID INT IDENTITY,FloatNumber FLOAT NOT NULL)DECLARE @sngCounter floatSET @sngCounter = 20WHILE @sngCounter >= 0BEGININSERT INTO #Floats ( FloatNumber ) VALUES( @sngCounter )SET @sngCounter = @sngCounter - 1ENDSELECT * FROM (SELECT TOP 100 PERCENT * FROM #Floats ORDER BY FloatNumber) AS FloatNumbersDROP TABLE #FloatsGOProduces the following resultset –FloatID FloatNumber----------- -----------1 202 193 184 175 166 157 148 139 1210 1111 1012 913 814 715 616 517 418 319 220 121 0In SQL Server 2000 resultset is this –FloatID FloatNumber----------- --------21 0.020 1.019 2.018 3.017 4.016 5.015 6.014 7.013 8.012 9.011 10.010 11.09 12.08 13.07 14.06 15.05 16.04 17.03 18.02 19.01 20.0
View 4 Replies
View Related
May 8, 2015
I am trying to get a date diff in a named calculation using inner join I am getting
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. #
I have checked online and have been able to get some of my codes right by using the where condition in place of the inner join, but I have not been able to work around the datediff one
(select
DATEDIFF(day,TDate,[DID])
FROM [dv].[dbo].[Dail] as D
inner join
[dv].[dbo].[ANT] as A
on
a.AID = d.AID)
View 0 Replies
View Related
Aug 27, 2015
I am attempting to Insert multiple rows in a table by passing DataTable to a Stored Proc. My code is the same as the examples on the net, however when I execute the code I receive the Subquery returned more than 1 value error.
I first created up a User-Defined Table Type
My stored proc is as follows:
ALTER PROCEDURE [KCC].[Insert_MaterialJobMtl]
@MtlTblMaterialType READONLY
AS
BEGIN
Set NOCOUNT ON;
[code]....
View 9 Replies
View Related
Dec 12, 2014
I have some data in the following format;
MYTABLE
DOC_NO // REV_NO // FILE_NAME
ABC123 // A // abc123.pdf
ABC123 // B // abc123_2.docx
ABC124 // A // abc124.xlsx
ABC124 // A // -
ABC125 // A // abc125.docx
ABC125 // C // abc125.jpg
ABC125 // C // abc125.docx
ABC125 // C // -
ABC126 // 0 // -
ABC127 // A1 // abc127.xlsx
ABC127 // A1 // abc127.pdf
I'm looking to select all rows where the DOC_NO and REV_NO appear only once.(i.e. the combination of the two values together, not any distinct value in a column)
I have written the sub query to filter the correct results;
SELECT DOC_NO, REV_NO FROM [MYTABLE]
GROUP BY DOC_NO, REV_NO
HAVING COUNT(*) =1
I now need to strip out the records which have no file (represented as "-" in the FILE_NAME field) and select the other fields (same table - for example, lets just say "ADD1", "ADD2" and "ADD3")
I was looking to put together a query like;
SELECT DOC_NO, REV_NO, FILE_NAME, ADD1, ADD2, ADD3 FROM [MYTABLE]
WHERE FILE_NAME NOT LIKE '-' AND DOC_NO IN
(SELECT DOC_NO, REV_NO FROM [MYTABLE]
GROUP BY DOC_NO, REV_NO
HAVING COUNT(*) =1)
But of course, DOC_NO alone being in the subquery select is not sufficient, as (ABC125 /A) is a unique combination, but (ABC125 /C) is not, but these results would be pulled in.
I also cannot simply add an additional "AND" clause on its own to make sure the REV_NO value appears in the subquery, because it is highly repetitive and would have to specifically match the DOC_NO)
What is the easiest way of ensuring that I only pull in the records where both the DOC_NO and REV_NO (combination) are unique, or is there a better way of putting this select together altogether?
View 9 Replies
View Related
Mar 25, 2015
I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100
SELECT * FROM Customer WHERE
CountyId IN
(
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?
View 4 Replies
View Related
Jun 19, 2006
Hi and thanks in advance for the help.
Here's what I'm trying to do, I need to select all the rows from one table, and only 1 row from a related table.
Table setup
Table1:
Field 1 = PK Ident
Table2:
Field1 = FK ident
I need to select all the rows that exist in Table 1, and I need 1 row out of table2 where Field1 is equal to the Table1.Field1 value (multiple records in table2 will exist with that same value.) I need the top row using a SELECT TOP 1
I was trying to do this with a subquery, but SQL is throwing an error asking me for EXISTS statments.
View 1 Replies
View Related
Aug 11, 2006
I have the following as a subquery in a larger stored procedure:
SELECT P.ProductId, P.ProductName, P.Category , (SELECT MAX(O.Orderdate) FROM dbo.[Orders] AS O WHERE O.ProductId=P.ProductId) As MostRecentOrder, ROW_NUMBER() OVER (ORDER BY MostRecentOrder DESC) AS RowNumber FROM dbo.[Products] AS P WHERE P.Category=@category
@category is an input parameter
I am getting an error pointing to the Order By clause stating that "MostRecentOrder" is an invalid column name. If I sort by P.ProductId or P.ProductName, it works fine. Any ideas?
Thanks
View 16 Replies
View Related
Jan 13, 2007
Hi All, This Subquery is kicking my ***. Maybe you can help. I want to query a query.I have the user enter a phrase from a textbox, then I want to group the results by element_label. This is what i have so far, but its not working. SELECT Element_ID, Element_Label, Element_Name, Question_ID, Question_Label, Question_Level, Question_Text, RelatedSRR
FROM qryforaspx
WHERE ([Question_Text] LIKE '%' + ? + '%')
IN
SELECT Element_Label FROM Description
Group by
Element_label Thanks,
View 2 Replies
View Related
Jun 20, 2005
Has anyone seen where subqueries collapse into a sum??? I have code like the following, which has been running fine for over a year:UPDATE Reports..DataStats SET Vendors_Cnt = (SELECT COUNT(*) FROM vVendors__AllRecords), Vendors_Audit_Cnt = (SELECT COUNT(*) FROM vVendors_InvAudit), Vendors_Rpts_Cnt = (SELECT COUNT(*) FROM vVendors_Inv12mo), Vendors_InvUnused = (SELECT COUNT(*) FROM vVendors_InvUnused),Vendors_InvOne = (SELECT COUNT(*) FROM vVendors_InvOne), Vendors_InvMulti = (SELECT COUNT(*) FROM vVendors_InvMulti), Vendors_InvUnpaid = (SELECT COUNT(*) FROM vVendors_InvUnpaid), Vendors_InvNewer = (SELECT COUNT(*) FROM vVendors_InvNewer), Vendors_Inv12mo = (SELECT COUNT(*) FROM vVendors_Inv12mo), Vendors_InvPrior = (SELECT COUNT(*) FROM vVendors_InvPrior), Vendors_InvSkipYear = (SELECT COUNT(*) FROM vVendors_InvSkipYear), Vendors_Known = (SELECT COUNT(*) FROM vVendors_Known), Vendors_Orphaned = (SELECT COUNT(*) FROM vVendors_Orphaned), Vendors_Active = (SELECT COUNT(*) FROM vVendors_Active), Vendors_Inactive = (SELECT COUNT(*) FROM vVendors_Inactive), Vendors_Excluded = (SELECT COUNT(*) FROM vVendors_Excluded)WHERE (AuditName = @AuditName)But now it is generating overflows....and is not equivalent to (ignoring the obvious UPDATE vs. return differences for illustration):SELECT COUNT(*) FROM vVendors__AllRecordsSELECT COUNT(*) FROM vVendors_InvAuditSELECT COUNT(*) FROM vVendors_Inv12mo SELECT COUNT(*) FROM vVendors_InvUnusedSELECT COUNT(*) FROM vVendors_InvOneSELECT COUNT(*) FROM vVendors_InvMultiSELECT COUNT(*) FROM vVendors_InvUnpaid SELECT COUNT(*) FROM vVendors_InvNewer SELECT COUNT(*) FROM vVendors_Inv12mo SELECT COUNT(*) FROM vVendors_InvPrior SELECT COUNT(*) FROM vVendors_InvSkipYear SELECT COUNT(*) FROM vVendors_KnownSELECT COUNT(*) FROM vVendors_OrphanedSELECT COUNT(*) FROM vVendors_Active SELECT COUNT(*) FROM vVendors_Inactive SELECT COUNT(*) FROM vVendors_ExcludedThis appears to have started around the beginning of May. Anyone else suffer after patches?
View 5 Replies
View Related
Jun 22, 1998
i have a table which i`m having difficulty setting up a subquery on.
cmpcode code grpcode
------------ --------- ------------
CORP 96020 01ADMIN
HON 96020 01ADMIN
LON 96020 04FOREIGN
LON 96020 01DIRECT
LON 96020 03ELLIOTT
LON 96020 02ACTIVE
NEW 96020 02INACTIVE
NEW 96020 01ADMIN
NEW 96020 03HOLECEK
SIN 96020 01ADMIN
what i would like to do is pull in only `codes` with a grpcode in (02active, 01direct). in the example above, i would only want the `lon` cmpcode to appear, since it`s both 01direct and 02active. since the grpcodes are on different lines, i`m not sure how to accomplish this. also, my key is cmpcode, code - not just code. here`s how i`ve been attempting to do it:
select
cmpcode,
code,
grpcode
from oas_grplist
where
elmlevel = 5 and
grpcode = `02ACTIVE` and
code in(select code from coda..oas_grplist where grpcode = `01direct`).
the problem with this is the subquery join is only based on joining code, and cmpcode needs to be included in the join.
any ideas?
thanks in advance, André
View 2 Replies
View Related
Sep 29, 2004
I have a SELECT statement with a subquery. I use an alias as I add the results of the subquery to the dataset. I then try to use the alias in the WHERE clause of the SELECT statement. I get an “Invalid column name “ message with this code:
select i.id as itemid,
(select top 1 ca2.itemid from itemassign ca2
inner join account a2 on ca2.accountid=a2.id
where a2.customerid=c.id and ca2.itemid=i.id)
as iaid
from item i
inner join customer c on i.customerid=c.id
where i.customerid=1 and iaid is null
order by i.id DESC
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'iaid'.
If I run the statement without the and condition in the WHERE clause it returns a valid result.
Any input on this will be very appreciated!
Thank you,
IRead
View 1 Replies
View Related
Jun 13, 2007
Sorry about my last post, it seemed to generate nothing but confusion. Hopefully I will explain the problem better this time.
Here are my tables with sample data:
Code:
abmtransmitter table:
Transmitter ID | Site_name | last_test_date | last_failed_test_date
A0001 Site01 2007-06-12 2007-06-10
A0002 Site02 2007-05-23 2007-06-06
A0003 Site03 2007-06-05 2007-06-12
A0004 Site04 2007-01-18 2007-05-18
AbmSignal Table:
Transmitter | Signal_id | Signal_date
A0001 trouble 2007-06-09
A0001 fail test 2007-06-10
A0001 test 2007-06-11
A0001 test 2007-06-12
A0002 test 2007-05-23
A0002 fail test 2007-05-30
A0002 fail test 2007-06-06
A0003 test 2007-06-05
A0003 fail test 2007-06-12
A0004 test 2007-01-18
A0004 fail test 2007-02-18
A0004 fail test 2007-03-18
A0004 fail test 2007-04-18
A0004 fail test 2007-05-18
I am trying to get a list of transmitters that have failed to send their scheduled communication test.
I only want a list of the transmitters who have failed two communications tests since the last successful test.
In the above data, the list would result with A0002 and A0004. A0001 passed it's most recent test, and A0003 has only failed one time since it's last successful test.
The following query does not look correct to me, but it does give me the results that *look* correct.
If it works, why does it work because I don't understand how the query on abmtransmitter is passing the value last_test_date to the subquery.
Or is this just a fluke and my result set looks correct but may not be?
'
Code:
Select Transmitter_id, Site_name from abmtransmitter where transmitter_id in
(select transmitter from abmsignal where signal_id = 'fail test' and signal_date > last_test_date
group by transmitter having count(transmitter) > 1) and last_failed_test_date > last_test_date Order by site_name
View 1 Replies
View Related
Aug 28, 2006
I have a subquery where I get 2 rows back which cause issues (since I can't return more then one). I am using this subquery in my function and I can't use a temp table in order to select the correct value. what other options do I have . My subquery returns 2 dates and I need to return from my function the max date.
View 1 Replies
View Related
Apr 24, 2008
Hi
Im using sql server 2000 and I have sql statement that needs to do a LIKE statement from values from another table. An example would be the below
select Name, PostCode from Customers
where Post LIKE (select PartialPostCode + '%' from areas where area_arid = '123')
However if the above sub query returns more than one row then it will error. So I thought I would create a function to return a string such as the below and put it into vvariable
@strPostCodesLike = 'PostCode LIKE 'WS1 %' OR PostCode LIKE 'WS2 %'
And tried to execute the following SQL statement
select Name, PostCode from Customers WHERE @strPostCodesLike
However the above does not work, as I would need to use dynamic sql to get it to work. I cant use dynamic sql unfortunately.
Any help would be much appreciated
Many thanks in advance
View 2 Replies
View Related
Aug 8, 2005
I am new to sql and i was reading about subquery and i think its the right tool for what i want to achieve
i have two tables
Products table, OrdersLine table
Products Table
ProdSku
ProdName
QOH
Cost
********
OrdersLine Table
OrderNum
ProdSku
Qty
I want to get the product Sku, Name, QOH, Cost and the Sum(Qty) from OrdersLine
this is what i have tried
SELECT dbo.Products.ProdSku AS Sku, dbo.OrdersLine.Qty AS Expr1
FROM dbo.Products INNER JOIN
dbo.OrdersLine ON dbo.Products.ProdSku = dbo.OrdersLine.ProdSku
WHERE (dbo.Products.ProdSku = '122345')
GROUP BY dbo.Products.ProdSku
please help!
View 9 Replies
View Related
Mar 9, 2006
I am cofuse with subquery.HOW does subquery work. the inner or outer query exicute first. what are different type of sub query. plz give example. i am more confuse with exits subquery
View 3 Replies
View Related
Jul 20, 2006
I have this subquery that I'm trying to get info from the two seperate tables to show up, but I only get one or the other.
-- This report is for the DC. This report is to look at what items
-- come in a kit and what the inventory levels looks like at the
-- time the report is run. 07/18/06 mjg
SELECT I.ItemID, D.ShortDesc, KI.ItemID, KD.ShortDesc
FROM timKitCompList KL
INNER JOIN timItem I ON KL.CompItemKey = I.ItemKey
INNER JOIN timitemdescription D ON I.ItemKey = D.ItemKey
WHERE EXISTS
(SELECT KI.ItemID AS KitItemNo, KD.ShortDesc AS KitItemDesc
FROM timKit K
INNER JOIN timItem KI ON K.KitItemKey = KI.ItemKey
INNER JOIN timitemdescription KD ON KI.ItemKey = KD.ItemKey)
Here are the error messages that I get:
Server: Msg 107, Level 16, State 2, Line 4
The column prefix 'KI' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 4
The column prefix 'KD' does not match with a table name or alias name used in the query.
View 4 Replies
View Related
Jun 13, 2007
Hi guys!
I need help. I have two tables (customers & orders). I want to build a query that lists all the customers who haven't placed an order in over a year. Below are the simplified tables (i've removed other fields that I don't think are relevant to this query). Do I need to do a subquery or something? Please help!
TABLES:
CUSTOMERS
custid (unique autonumber)
ORDERS
custid (int)
orderdate (date)
View 3 Replies
View Related
Jul 9, 2007
Hello,
I've the next query but he answer
"Subqueries are not allowed in this context. Only scalar expressions are allowed. "
INSERT INTO
tbl_Tickets (
vld_Onderwerp,
vld_Omschrijving,
vld_Aangemaakt,
vld_Type,
vld_Apparaat,
vld_Klant,
vld_Gebruiker
)
VALUES (
'Test ',
'hoi',
'7/9/2007 1:41:15 PM',
(
SELECT
fld_id
FROM
tbl_storing_types
WHERE
fld_Naam = 'Storing'
),
'54684 ',
'1',
'1'
)
I searched much fora but i can't find a clear solution.
Who can help me?
View 2 Replies
View Related
Aug 21, 2007
Have a table:
ID M1 M2 M3 M4 M5 M6
-------------------------------------
1 A1 B3 B4 D5
...
...
...
For any record, I am trying to pull out only the values in 6 specific columns (M1-M6) which correspond to a variable. If the value in any column equals the varaible, it should be included, otherwise it shouldn't
For example:
If x = "B" and ID = 1
I want to pull the record for ID #1 and return the two columns M2 and M3, because Left(ColumnX,1) = x (which has the value of "B") for both those columns. Columns M1, M4, M5, M6 would not be returned
I tried to code a subquery, but it didn't work
Thanks for any help
Dan B
View 3 Replies
View Related
