Subquery In FROM Clause

Jul 20, 2005

Let us suppose that we have a table:

CREATE TABLE
transactions(
currencyID_1 int,
value_1 money,
currencyID_2 int,
value_2 money
)

I need to calculate the totals by each currency.
It might be express with the construction like this:

SELECT currency, sum(value)
FROM(
SELECT
currencyID_1 AS currency, sum(value_1) AS value
FROM transactions
GROUP BY currencyID_1
UNION ALL
SELECT
currencyID_2 AS currency, sum(value_2) AS value
FROM transactions
GROUP BY currencyID_2
)
GROUP BY currency

But if I'm not wrong it is not able to use subquery in FROM clause.

I have two questions.

1) Why it is prohibited to use subselect in FROM clause?
2) How to solve my task with the most graceful way? (view? temporary
table?)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

View 3 Replies

Help With Subquery And Having Clause ..

Apr 28, 2008

Hey, I'm referencing a database about a fictitious IT company, and I'm trying to answer the following statement:

Determine who has logged the most hours. Create a SELECT statement to return their name and the max hours they have worked. NOTE: This is a tougher one but looking into using a Subquery and use the HAVING statement.

I've written this so far, and although it does give me the right answer (there is only one employee in the DB who has worked on projects) it is not entirely correct. Basically there is no subquery, and it doesn't really show who has worked the max hours:

SELECT
EMPLOYEE_NAME 'Employee Name', SUM(HOURS_LOGGED) 'Maximum Hours Logged'
FROM
PROJECT_EMPLOYEE PE
INNER JOIN
PROJECT_HOURS PH ON PH.EMPLOYEE_ID = PE.EMPLOYEE_ID
INNER JOIN
PROJECT P ON P.PROJECT_ID = PH.PROJECT_ID
GROUP BY
PE.EMPLOYEE_NAME

Here is the DB if needed:

CREATE TABLE project_employee (
employee_id INT,
employee_name VARCHAR(40),
employee_hire_date DATETIME,
employee_termination_date DATETIME,
employee_billing_rate INT,
CONSTRAINT project_employee_pk
PRIMARY KEY (employee_id)
);

CREATE TABLE project (
project_id INT,
project_name VARCHAR(40),
project_budget INT,
CONSTRAINT project_pk
PRIMARY KEY (project_id)
);

CREATE TABLE project_hours (
project_id INT,
employee_id INT,
time_log_date DATETIME,
hours_logged INT,
dollars_charged INT,
CONSTRAINT project_hours_pk
PRIMARY KEY (project_id, employee_id, time_log_date),
CONSTRAINT proj_hours_fkto_projectemployee
FOREIGN KEY (employee_id) REFERENCES project_employee,
CONSTRAINT proj_hours_fkto_project
FOREIGN KEY (project_id) REFERENCES project
);

INSERT INTO project
(project_id, project_name, project_budget)
VALUES (1001, 'Corporate Web Site',1912000);
INSERT INTO project
(project_id, project_name, project_budget)
VALUES (1002, 'Year 2000 Fixes',999998000);
INSERT INTO project
(project_id, project_name, project_budget)
VALUES (1003, 'Accounting System Implementation',897000);
INSERT INTO project
(project_id, project_name, project_budget)
VALUES (1004, 'Data Warehouse Maintenance',294000);
INSERT INTO project
(project_id, project_name, project_budget)
VALUES (1005, 'TCP/IP Implementation',415000);

INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (101, 'Jonathan Gennick','15-Nov-1961',null,169);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (102, 'Jenny Gennick','16-Sep-1964','5-May-1998',135);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (104, 'Jeff Gennick','29-Dec-1987','1-Apr-1998',99);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (105, 'Horace Walker','15-Jun-1998',null,121);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (107, 'Bohdan Khmelnytsky', '2-Jan-1998',null,45);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (108, 'Pavlo Chubynsky','1-Mar-1994','15-Nov-1998',220);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (110, 'Ivan Mazepa', '4-Apr-1998','30-Sep-1998',84);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (111, 'Taras Shevchenko', '23-Aug-1976',null,100);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (112, 'Hermon Goche', '15-Nov-1961','4-Apr-1998',70);
INSERT INTO project_employee
(employee_id, employee_name, employee_hire_date,
employee_termination_date, employee_billing_rate)
VALUES (113, 'Jacob Marley', '3-Mar-1998','31-Oct-1998',300);

INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1001,101,'01-JAN-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1003,101,'01-JAN-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1005,101,'01-JAN-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1002,101,'01-FEB-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1004,101,'01-FEB-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1001,101,'01-MAR-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1003,101,'01-MAR-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1005,101,'01-MAR-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1002,101,'01-APR-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1004,101,'01-APR-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1001,101,'01-MAY-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1003,101,'01-MAY-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1005,101,'01-MAY-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1002,101,'01-JUN-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1004,101,'01-JUN-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1001,101,'01-JUL-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1003,101,'01-JUL-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1005,101,'01-JUL-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1002,101,'01-AUG-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1004,101,'01-AUG-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1001,101,'01-SEP-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1003,101,'01-SEP-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1005,101,'01-SEP-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1002,101,'01-OCT-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1004,101,'01-OCT-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1001,101,'01-NOV-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1003,101,'01-NOV-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1005,101,'01-NOV-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1002,101,'01-DEC-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
VALUES (1004,101,'01-DEC-1998',3.00,507.00);

View 2 Replies View Related

Subquery For IN Clause

May 13, 2008

Hi,

Being ignorant I just tried to write a query with an IN clause that looked like this:

and cv.criteriaId in (select valueId from #locations)

SQL Server accepted this as valid and ran the query, returning no results.

However if I rewrite the query manually with the thirteen or so numbers that that query produces, like this:

and cv.valueID in (1001, 7600, 7601, 7602, 7603, 7604, ..etc..)

It runs fine and produces what I need.

How can I write my subquery so that the whole query produces the value set I'm expecting?

Cheers,
Matt

View 3 Replies View Related

Where Clause Subquery Problem

Mar 6, 2008

Hi,

I got the following table with records:

ID......HotelID......FromDate......ToDate.......CustID
1.......10001.......1/2/2008.......1/3/2008......10001
2.......10001.......1/4/2008.......1/5/2008......10002
3.......10001.......1/6/2008.......1/7/2008......10003
4.......10002.......1/5/2008.......1/6/2008......10004

The SQL Query is in Stored Procedure code as below:

SELECT DISTINCT HotelID
FROM hotelreservation AS HR
WHERE NOT ((HR.FromDate BETWEEN @FromDate AND @ToDate) OR (HR.ToDate
BETWEEN @FromDate AND @ToDate) OR (HR.FromDate < @FromDate AND
HR.ToDate > @ToDate))-- AND UserRdmID IS NULL
AND HR.HotelID != (SELECT DISTINCT HotelID FROM HotelReservation AS HR2 WHERE ((HR2.FromDate BETWEEN @FromDate AND @ToDate) OR (HR2.ToDate
BETWEEN @FromDate AND @ToDate) OR (HR2.FromDate < @FromDate AND
HR.ToDate > @ToDate)))

If i query FromDate = 1/6/2008 and ToDate = 1/7/2008, then the result i
get is only HotelID : 10002. BUT if i query FromDate = 2/2/2008 and
ToDate = 2/3/2008, then i dont get any result.What i wanted is, if the hotelid is reserved, then that hotelid must be ignored.
Please help. I'm struck. Thx.

View 2 Replies View Related

Subquery In WHERE Clause And Aggregate

Mar 4, 2014

So I wrote this code but keep getting an error.

List all resellers whose annual sales exceed the average annual sales for resellers whose Business Type is "Specialty Bike Shop". Show Business type, Reseller Name, and annual sales. Use appropriate subqueries.

-- 396 Rows

SELECT R.ResellerName
FROM dbo.DimReseller AS R
WHERE
R.ResellerKey IN (
SELECT F.ResellerKey
FROM dbo.FactResellerSales AS F
WHERE R.ResellerKey = F.ResellerKey AND R.BusinessType = 'Specialty Bike Shop'
GROUP BY F.ResellerKey
HAVING R.AnnualSales > AVG(R.AnnualSales)
)

View 15 Replies View Related

Giving Where Clause For Subquery

Jun 25, 2007

Hi all,

select u_emp,(select name from [@emp] where code=u_emp) name1
from ovpm
This query gives me the output having employee code and name.
I want to further filter the employee name by giving a where clause as
where name1='Carell'

Is it possible?
Please Help

View 2 Replies View Related

Count Subquery In Where Clause (3.1)

Apr 18, 2008

I am attempting to do something like this...

select * from applebucket ab where 2 = (select count(id) from apples ap where ap.applebucket_id = ab.id)

SSCE 3.1 throws the following

There was an error parsing the query. [ Token line number = x,Token line offset = y,Token in error = SELECT ]

Any help would be greatly appreciated!

View 3 Replies View Related

Handling Multiple Columns Returned By A Subquery With An IN Clause

Mar 25, 2008

Hi,

I would like to know how i can handle multiple columns returned by a subquery via IN clause in case of sql server 2005. I can do that in oracle by using the following statement:

DELETE FROM TEST1
WHERE (ID, ID1) NOT IN (SELECT ID,ID1 FROM TEST2);

Thanks and Regards
Salil

View 9 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.

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

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 View Related

Transact SQL :: How To Create UNION Clause With Two Queries That BOTH Have WHERE Clause

Nov 4, 2015

I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?

I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....

I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.

Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?

View 13 Replies View Related

GROUP By Clause Or DISTINCT Clause

Jul 23, 2005

Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1

View 3 Replies View Related

Filtering Results In The Where Clause Vs A Having Clause

Oct 25, 2007

I am working with a vendor on upgrading their application from SQL2K to SQL2K5 and am running into the following.

When on SQL Server 2000 the following statement ran without issue:

UPDATE dbo.Track_ID

SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed

WHERE Processed = 0 AND LegNum = 1

AND TrackID IN

(

SELECT TrackID

FROM dbo.Track_ID

GROUP BY TrackID

HAVING MAX(LegNum) = 1 AND

TrackID + 'x1' IN

(

SELECT

dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))

FROM dbo.Track_ID INNER JOIN dbo.transactions

ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id

GROUP BY dbo.Track_ID.TrackID

)

)
Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.

I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?

UPDATE dbo.Track_ID

SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed

WHERE Processed = 0 AND LegNum = 1

AND TrackID IN
(

SELECT TrackID

FROM dbo.Track_ID

WHERE TrackID + 'x1' IN

(

SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID))

FROM dbo.Track_ID INNER JOIN dbo.transactions

ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id

GROUP BY dbo.Track_ID.TrackID

)
GROUP BY TrackID

HAVING MAX(LegNum) = 1

)

View 3 Replies View Related

Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause

May 14, 2008

2 examples:

1) Rows ordered using textual id rather than numeric id

Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
order by
v.id

Result set is ordered as: 1, 11, 2
I expect: 1,2,11

if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

2) SQL server reject query below with next message

Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.

Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
cross join (
select 1 id
union select 2 id
union select 11 id
) u
order by
v.id
,u.id

Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.

It reproducible on

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

and

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

In both cases database collation is SQL_Latin1_General_CP1251_CS_AS

If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.

Could someone clarify - is it bug or expected behaviour?

View 12 Replies View Related

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause

May 27, 2008

I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error

ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause

I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.

View 1 Replies View Related

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause

May 27, 2008

View 1 Replies View Related

Having Clause Without GROUP BY Clause?

Nov 20, 2004

Hi,

What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?

eg :

SELECT SUM(col1) from test HAVING col2 < 5

SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5

I want the equivalent query in MSSQLServer for the above Oracle query.

Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.

Thanks,
Gopi.

View 3 Replies View Related

Top Clause With GROUP BY Clause

Apr 3, 2008

How Can I use Top Clause with GROUP BY clause?

Here is my simple problem.

I have two tables

Categories
Products

I want to know Top 5 Products in CategoryID 1,2,3,4,5

Resultset should contain 25 Rows ( 5 top products from each category )

I hope someone will help me soon.
Its urngent

thanks in advance

regards
Waqas

View 10 Replies View Related

Diff In On Clause And Where Clause?????

Apr 4, 2007

hi..
i have basic question like

what is differance between conditions put in ON clause and in WHERE clause in JOINS????

see conditions that shown in brown color

select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
whered1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)

select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate,
d1.Audit, d1.ExpirationDate, d1.Indicator
from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2
on d1.SummaryID=d2.SummaryID
AND d1.ImplicitID = d1.ImplicitIDAND d1.ListType = d2.ListType
AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null)
AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null)
whered1.ImplicitID >= d2.ImplicitID AND
(d1.SourceID<>d2.SourceID
OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL)
OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL)
)

another thing...

if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove
d1.ImplicitID >= d2.ImplicitID from WHERE clause????

View 6 Replies View Related

SQL Inner Join Clause And The Where Clause

Jan 21, 2008

Hi everyone,
I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ?

Thanks

View 6 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