Adding Product Of A Subquery To A Subquery Fails?

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


ADVERTISEMENT

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.

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

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.

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

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &&<, &&<= , &&>, &&>= Or When The Subquery I

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

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &&<, &&<= , &&>, &&>= Or When The Subquery I

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

Subquery Returned More Than 1 Value But Work FINE (field_xxx=subquery)

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

Transact SQL :: Adding Subquery As A Column

May 19, 2015

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

[Code] ....

View 3 Replies View Related

ORDER BY In Subquery Of A UNION Fails ???

Mar 1, 2006

Hi all,

I have the following UNION ALL statement that is my attempt to gather data for the past 5 weekdays (adding a "dummy" row for today's data).

I want the final output to end up in descending order, so for today, I would want today first, then Tuesday, then Monday, then Friday, then Thursday (provided there is data for each sequential day - if not, you get the idea, I want to select back to get the latest 5 days, most recent to oldest).

This select fails, because it doesn't like the ORDER BY in the subqueryselect
CASE
WHEN DATENAME(dw, GETDATE()) = 'Monday' THEN
'MON'
WHEN DATENAME(dw, GETDATE()) = 'Tuesday' THEN
'TUES'
WHEN DATENAME(dw, GETDATE()) = 'Wednesday' THEN
'WED'
WHEN DATENAME(dw, GETDATE()) = 'Thursday' THEN
'THUR'
WHEN DATENAME(dw, GETDATE()) = 'Friday' THEN
'FRI'
END AS Dow, 'N/A' AS Freight
UNION ALL
(select top 4
CASE
WHEN DATENAME(dw, [OrderDate]) = 'Monday' THEN
'MON'
WHEN DATENAME(dw, [OrderDate]) = 'Tuesday' THEN
'TUES'
WHEN DATENAME(dw, [OrderDate]) = 'Wednesday' THEN
'WED'
WHEN DATENAME(dw, [OrderDate]) = 'Thursday' THEN
'THUR'
WHEN DATENAME(dw, [OrderDate]) = 'Friday' THEN
'FRI'
END as DOW,
CAST(CONVERT(int, (Freight * 100)) as VARCHAR(10)) as Freight
from Northwind.dbo.orders where employeeid = 9 order by [OrderDate] desc )

I know you can't use an ORDER BY in a subquery, UNLESS the subquery also uses a TOP n (which this one does)...but does anyone know why this isn't liking my code?

I got the select to work the way I want it to by doing the following (really UGLY) code...SELECT U.DOW, U.Freight FROM
((select
GETDATE() as [OrderDate],
CASE
WHEN DATENAME(dw, GETDATE()) = 'Monday' THEN
'MON'
WHEN DATENAME(dw, GETDATE()) = 'Tuesday' THEN
'TUES'
WHEN DATENAME(dw, GETDATE()) = 'Wednesday' THEN
'WED'
WHEN DATENAME(dw, GETDATE()) = 'Thursday' THEN
'THUR'
WHEN DATENAME(dw, GETDATE()) = 'Friday' THEN
'FRI'
END AS Dow, 'N/A' AS Freight )
UNION ALL
(select h.OrderDate as [OrderDate], h.DOW, h.Freight FROM
(select top 4
[OrderDate] as [OrderDate],
CASE
WHEN DATENAME(dw, [OrderDate]) = 'Monday' THEN
'MON'
WHEN DATENAME(dw, [OrderDate]) = 'Tuesday' THEN
'TUES'
WHEN DATENAME(dw, [OrderDate]) = 'Wednesday' THEN
'WED'
WHEN DATENAME(dw, [OrderDate]) = 'Thursday' THEN
'THUR'
WHEN DATENAME(dw, [OrderDate]) = 'Friday' THEN
'FRI'
END as DOW,
CAST(CONVERT(int, (Freight * 100)) as VARCHAR(10)) as Freight
from Northwind.dbo.orders where employeeid = 9 order by [OrderDate] desc ) H)) U
order by OrderDate descbut am still confounded about why my original sub-select is rejected with such impunity.

My confusion seems likely related to understanding the set theory or basic concepts of the building of the select/Union rather than the way I am using the ORDER BY syntax, but I just can't seem to explain it to myself.

Thoughts?
Thanks!

View 14 Replies View Related

Insert Using Subquery Fails To Insert With No Error

Apr 10, 2008

I am working with parent child tables and want to populate the primary key on insert so that the user does not have to enter this for each record.  Here is my codeInsertCommand="INSERT INTO [Awards] ([UFID], [DateAwarded], [Amount], [AwardingAgency]) Select UFID, @DateAwarded, @Amount, @AwardingAgency from master where GatorlinkName = @LoginName"  <InsertParameters><asp:Parameter Name="LoginName" Type="String" />         <asp:Parameter Name="strusername" Type="String" />            <asp:Parameter Name="UFID" Type="String" />            <asp:Parameter Name="DateAwarded" Type="DateTime" />            <asp:Parameter Name="Amount" Type="Decimal" />            <asp:Parameter Name="AwardingAgency" Type="String" />        </InsertParameters> The UFID field is the only field that should be populated from SQL data the others are coming from a form view insert form.  When I run an insert I get no error but the insert does not happen. I know that the @LoginName works since I am using this same logic in my select statement.  Thanks in advance for your help,Ken 

View 3 Replies View Related

SQL And A Subquery

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

T-SQL Subquery

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

SubQuery Help!

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

MS SubQuery Changes???

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

Subquery Help

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

I Need Help With A Subquery!

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

Need Help With Subquery

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

Subquery

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

Doing LIKE On A Subquery

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

Sql Subquery

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

SubQuery

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

Subquery Help

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

Subquery? Or?

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

Need Help With Subquery.

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

Subquery?

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

How To Use The Subquery

Sep 27, 2007

Hi there,

1. Assuming I have below table:
Projectid,Dept,Budgeted,Approved,Status
A1,Audit,Yes, No, Started
B1,HR,No, Yes, Started
C1,IT,Yes, Yes, Not Started
D1,Audit,Yes, Yes, Dropped

2. Below are the 2 queries created
select dept, count(projectid) from table where budgeted = 'yes' group by dept
select dept, count(projectid) from table where budgeted = 'yes' and (approved = 'yes' or status = 'started') group by dept

3. How to join the above 2 queries for me to get the following result

Dept, Budgeted, Exp1
Audit, 2, 1
IT, 1, 1
HR, 0, 0

View 2 Replies View Related

Subquery - Can't Sum

Oct 16, 2007

Greetings, I am working on a SQL View - I previously posted in this forum and thought I had fixed my problem. Turns out that my view no longer works because I have multiple records where I want only one record.

I am working with 4 tables - here are the tables and my sample data:

USE YOURDATABASE
GO
CREATE TABLE ZTECSOP60100
(
SOPNUMBE char(21) NOT NULL,
PONUMBER char(17) NOT NULL,
ORD int NOT NULL,
RCPTCOST numeric (19,5) NOT NULL,
QTYONPO numeric (19,5) NOT NULL,
QTYRECVD numeric (19,5) NOT NULL
)

INSERT INTO ZTECSOP60100
values ('ORD001', 'PO2074', '16384', '0.00000', '0.00000', '1.00000')
INSERT INTO ZTECSOP60100
values ('ORD1003', 'PO2079', '16384', '425.00000', '0.00000', '5.00000')

USE YOURDATABASE
CREATE TABLE ZTECPOP10110
(PONUMBER char(17) NOT NULL,
ITEMNMBR char (31) NOT NULL,
ITEMDESC char (101) NOT NULL,
ORD int NOT NULL)
GO

INSERT INTO ZTECPOP10110
values ('PO2074', 'SERVICE1', 'Service item', '16384')
INSERT INTO ZTECPOP10110
values ('PO2079', 'SERVICE1', 'Service item', '16384')


USE YOURDATABASE
CREATE TABLE ZTECSOP30200
(
SOPNUMBE char(21) NOT NULL,
ORIGNUMBE char (21) NOT NULL,
SOPTYPE smallint NOT NULL,
)
GO

INSERT INTO ZTECSOP30200
values ('ORD001', '', '2')
INSERT INTO ZTECSOP30200
values ('INV001', 'ORD001', '3')
INSERT INTO ZTECSOP30200
values ('ORD1003', '', '2')
INSERT INTO ZTECSOP30200
values ('INV010', 'ORD1003', '3')

USE YOURDATABASE
CREATE TABLE ZTEC30310
(
PONUMBER char (17) NOT NULL,
UNITCOST numeric (19,5) NOT NULL,
TRXSORCE char (13) NOT NULL,
UMQTYINB numeric (19,5) NOT NULL,
EXTDCOST numeric (19,5) NOT NULL
)
GO


USE YOURDATABASE
INSERT INTO ZTEC30310
values ('PO2074', '125', 'RECVG00000001', '1', '125')
USE RELIA
INSERT INTO ZTEC30310
values ('PO2074', '125', 'POIVC00000001', '1', '125')
INSERT INTO ZTEC30310
values ('PO2079', '100', 'POIVC00000005', '1', '300')
INSERT INTO ZTEC30310
values ('PO2079', '75', 'RECVG00000007', '1', '375')
INSERT INTO ZTEC30310
values ('PO2079', '85', 'POIVC00000004', '1', '170')

Here is my VIEW -

CREATE view [dbo].[_tec_SOP_POP_link] as
select ZTECSOP60100.SOPNUMBE as SOP_ORDER_NUMBER,
ZTECSOP60100.PONUMBER as PONUMBER,
ZTECSOP60100.QTYRECVD as QtyReceivedSoFar,
ZTECSOP30200.sopnumbe as SOP_INV_NUMBER,
ZTECPOP10110.ITEMNMBR as ITEM_NUMBER,
ZTECPOP10110.ITEMDESC as itemdescription,
CASE WHEN ZTEC30310.ponumber = ZTECsop60100.ponumber AND ZTEC30310.TRXSORCE like 'RECVG%'
then ZTEC30310.UNITCOST else '0' end as ReceivedCost,
CASE WHEN ZTEC30310.ponumber = ZTECsop60100.ponumber AND ZTEC30310.TRXSORCE like 'POIVC%'
then ZTEC30310.UNITCOST else '0' end as VENDORINVOICE_Cost,
Sum (case when ZTEC30310.ponumber = ZTECsop60100.ponumber AND
ZTEC30310.trxsorce like 'POIVC%' then ZTEC30310.EXTDCOST/ZTEC30310.UNITCOST else 0 end) as QTYINVOICEDMATCHED,
Sum (case when ZTEC30310.ponumber = ZTECsop60100.ponumber AND
ZTEC30310.trxsorce like 'POIVC%' then ZTEC30310.extdcost else 0 end) as TOTALDOLLARSINVOICEDMATCHED
from ZTECsop60100 left outer join ZTECsop30200 on ZTECsop60100.sopnumbe = ZTECsop30200.ORIGNUMBE AND ZTECsop30200.soptype = 3
left outer join ZTECPOP10110 on ZTECSOP60100.PONUMBER = ZTECPOP10110.PONUMBER
left outer join ZTEC30310 on ZTECsop60100.ponumber = ZTEC30310.ponumber
where ZTECSOP60100.ORD = ZTECPOP10110.ORD
GROUP BY ZTECSOP60100.SOPNUMBE, ZTECSOP60100.QTYRECVD, ZTECSOP30200.sopnumbe, ZTECSOP60100.PONUMBER, ZTECSOP60100.QTYONPO,
ZTEC30310.TRXSORCE, ZTECPOP10110.ITEMNMBR, ZTECPOP10110.ITEMDESC, ZTEC30310.UNITCOST, ZTEC30310.umqtyinb, ZTEC30310.ponumber
GO

This View returns 5 Records when I want only 2 records.

I need 1 record for each SOP_ORDER_NUMBER AND PONUMBER combination.

Record 1
SOP_ORDER_NUMBER = 'ORD001'
PONUMBER = 'PO2074'
QtyReceivedSofar = '1'
SOP_INV_NUMBER = 'INV001'
ITEM_NUMBER = 'SERVICE1'
itemdescription = 'Service item'
ReceivedCost = '125'
VENDORINVOICED_COST = '125'
QTYINVOICEDMATCHED = '1'
TOTALDOLLARSINVOICEDMATCHED = '125'

Record 2
SOP_ORDER_NUMBER = 'ORD1003'
PONUMBER = 'PO2079'
QtyReceivedSofar = '5'
SOP_INV_NUMBER = 'INV010'
ITEM_NUMBER = 'SERVICE1'
itemdescription = 'Service item'
ReceivedCost = '75'
VENDORINVOICED_COST = '94' (calculated by taking TOTALDOLLARSINVOICEDMATCHED/QTYINVOICEDMATCHED)
QTYINVOICEDMATCHED = '5'
TOTALDOLLARSINVOICEDMATCHED = '470'

The unanticpated dilemna was multiple records in ZTEC30310 for TRXSORCE of POIVC or RECVG - I basically need to sum the records and return one record.

I attempted to fix by adding sum within my case statements but I get the SQL error message
'Cannot perform an aggregate function on an expression containing an aggregate or a subquery'

Any assistance would be appreciated......many thanks in advance...

View 2 Replies View Related

Subquery

Oct 18, 2007

Hi, I've read through quite a few of the other new sql user questions and saw similar questions, but nothing that quite fits.

I use a transit database. The tables that I need to query for this question are Clients, Booking, Bookinglegs

I need to return results on trip information where the creation date
was the same day as the trip date (ldate) However, I also need to show trips taken by the clients whose trips made the above criteria, but also had other trips the same day.



This query produces the results I need for the trips created the same day as the trip day:

Select c.clientid, b.ldate, b.credate, PU.reqtime, DO.reqlate

From Clients C, Booking B, Bookinglegs PU, Bookinglegs DO

Where c.clientid=b.clientid
AND b.bookingid=PU.bookingid
AND PU.legnum=0
AND DO.legnum=1
AND b.credate=b.ldate

I tried writing a case expression so that the results of this query would return a value of 'SAME DAY' and then adding an identical union query to it, except with the AND b.credate<b.ldate with a case expression that returned a value of 'Prior Booking'...this worked, however, I get every single booking for that day from the second query. Is there any way to do a subquery or a different statement that would produce results that were only contained in the first statement? I tried outer joins as well, but I'm really at a loss...

Thanks!

View 3 Replies View Related

Subquery Using MAX Subquery Need Help

Nov 27, 2007

Hi Team:

SELECT Distinct a.group_code, a.group_name + '-'+ a.group_note As full_name ,
b.station_nbr, b.beg_eff_date, b.end_eff_date,
Round( e.C02_pct,3)As C02_pct, e.sample_date, d.station_name
from group_detail a
Inner Join station_group b on a.group_code = b.group_code
Inner Join station_type c on b.station_nbr = c.station_nbr
Inner JOIN station d ON c.station_nbr = d.station_nbr
Inner JOIN gas_analysis e ON d.station_nbr = e.station_nbr
Where (a.group_type = 'NPN')and( b.end_eff_date >=@sampledateBegin)and ( (c.type_code = 'WHM')or ( c.type_code = 'CRP'))
and (e.sample_date Between @sampledateBegin and @sampledateEnd )
and e.seq_nbr =
(select max(x.seq_nbr)
From gas_analysis x
Where x.station_nbr = b.station_nbr
and x.sample_date= e.sample_date )
and e.C02_pct =
(Select max(x.C02_pct)
From gas_analysis x
Where x.station_nbr = b.station_nbr
and x.sample_date= e.sample_date
and x.seq_nbr= e.seq_nbr)
order by a.group_code




Group station_nbr Beg_date end_date C02 sample_date
0004 D01G000819 2007-09-01 3000-12-31 0.40 2007-10-02
0004 D01G000819 2007-09-01 3000-12-31 2.49 2007-10-09



I am trying to obtain the highest C02 in this case 2.49 but before getting the highest C02 it needs to meet the Select MAX seq_nbr within a given date. In other words I am trying to write a Subquery that first will obtain the MAX seq_nbr within a given date and based on the records pick the higest C02.

I appreciate the help. I hope my scope help understanding what I am trying to achieve.

View 2 Replies View Related

Subquery Help Please

Jan 18, 2008

I am trying to get the % of resolved for every calltype based on an input dates. Here is my table:

Table: Win4
SessionDate Calltype Status
---------------------------------------
1/2/2008 Email Resolved
1/3/2008 Email Unresolved
1/4/2008 Connectivity Resolved
1/5/2008 Connectivity Unresolved
1/6/2008 Connectivity Resolved
1/7/2008 General Resolved
1/8/2008 General Resolved
1/9/2008 General Unresolved


Here's my desired result given the parameter dates 1/2/08 to 1/9/2008:

Calltype #Count #Resolved
---------------------------------------
Email 2 1
Connectivity 3 2
General 3 1


Here's my code:

SELECT Calltype, count(Calltype) as #Count
FROM dbo.WIN4
Where Sessiondate >= '1/2/2008' and Sessiondate <= '1/9/2008'
Group by Calltype


Then problem is that I do not know how to count all the RESOLVED with alias #Resolved.

I appreciate any help or who could write the code for me.

Thanks!
Dennis S.

View 3 Replies View Related

Subquery Help Please

Jan 18, 2008

I am trying to get the % of resolved for every calltype based on an input dates. Here is my table:

Table: Win4
SessionDate Calltype Status
---------------------------------------
1/2/2008 Email Resolved
1/3/2008 Email Unresolved
1/4/2008 Connectivity Resolved
1/5/2008 Connectivity Unresolved
1/6/2008 Connectivity Resolved
1/7/2008 General Resolved
1/8/2008 General Resolved
1/9/2008 General Unresolved


Here's my desired result given the parameter dates 1/2/08 to 1/9/2008:

Calltype #Count #Resolved
---------------------------------------
Email 2 1
Connectivity 3 2
General 3 1


Here's my code:

SELECT Calltype, count(Calltype) as #Count
FROM dbo.WIN4
Where Sessiondate >= '1/2/2008' and Sessiondate <= '1/9/2008'
Group by Calltype


Then problem is that I do not know how to count all the RESOLVED with alias #Resolved.

I appreciate any help or who could write the code for me.

Thanks!
Dennis S.

View 2 Replies View Related

Subquery Help Again :(

Mar 24, 2008

I am getting results back from my Subquery, however they are not the right facility on the Patient Visit. Thoughts?


My full query:

DECLARE @pPatientVisitID INT
SELECT @pPatientVisitID = pv.PatientVisitID
FROM PatientVisit pv

SELECT cIPV.ImmunCode,
cIPV.ImmunDescription,
cIPV.Category,
pp.Last,
pp.First,
ISNULL(pp.Middle,'') AS Middle,
cIPV.ImmunDte,
cIPV.ListOrder,
cIPV.Last + ', ' + cIPV.First + case when cIPV.middle is null then + '' else ' ' + cIPV.middle end as Name,
dbo.cusCalcAgeYearsMonths(AgeYears,AgeMonths) AS AgeAtImmun,
pp.PatientID AS MedicalRecordNumber,
(SELECT fac.ListName
FROM cusvImmunPatVisits cIPV
INNER JOIN PatientVisit pv ON cIPV.PatientVisitID = pv.PatientVisitID
INNER JOIN DoctorFacility fac ON pv.FacilityId = fac.DoctorFacilityId
WHERE cIPV.PatientvisitId = @pPatientVisitID
) AS Facility

FROM cusvImmunPatVisits cIPV
INNER JOIN PatientProfile pp ON cIPV.PatientProfileID = pp.PatientProfileID

WHERE cIPV.ImmunDte >= ISNULL(NULL,'1/1/1900') AND cIPV.ImmunDte < dateadd(day,1,ISNULL(NULL,'1/1/3000'))
AND --Filter on patient
(
(NULL IS NOT NULL AND pp.PatientProfileID IN (NULL)) OR
(NULL IS NULL)
)

View 1 Replies View Related

Sum Subquery Help

Feb 5, 2008

How can I control or sum at a break level when I have additional detail I need to show?

I have a Travel Industry report that show an amount for a "Booking" of travel, but have each of the "Legs" of a trip. I need to show both on the report, but the Booking amount is duplicated for each "Leg". I need to do a Total by Passenger of all bookings, but the segment detail causes the value to repeat.

Hope this makes sense. Here is a sample:
Joe Smith Ticket#123 $500 Columbus - Las Vegas
Las Vegas - Columbus

sum(amount) should by $500, but I get $1000 because my data looks like this:

Joe Smith Ticket#123 $500 Columbus - Las Vegas
Joe Smith Ticket#123 $500 Las Vegas - Columbus

View 5 Replies View Related

Subquery With (IN)

Feb 24, 2008

Select EmployeeID from employees
Where EmployeeID in(Select * from Orders Where OrderID>1)

What is the problem with the above query I get this error.

Msg 116, Level 16, State 1, Line 2
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

View 12 Replies View Related







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