Help With A Subquery Join

Dec 10, 2004

I have a query that produces the following results:


wbs2 wbs3 Name AmtBud BTD BillExt
100 100 Traffic Impact 10000 13563.75 0
140 Highway/Signal 0 0 0
140 100 Highway/Signal 0 0 0



and another query that produces the follwing results:


wbs2 FeeType
100 SC
140 HM&SC
150 HM
160 HM&SC
180 SC
190 SC



So, I want to join both queries to produce the ending result:

wbs2 wbs3 Name AmtBud BTD BillExt FeeType
100 100 Traffic Impact 10000 13563.75 0 SC
140 Highway/Signal 0 0 0 HM&SC
140 100 Highway/Signal 0 0 0 HM&SC



Here are my queries:
first query:

SELECT CASE WHEN WM_Template.WBS2 = '500' THEN '1750.00' + WM_Template.WBS2 ELSE '2000.20' + WM_Template.WBS2 END AS WBS2,
WM_Template.WBS3 AS WBS3,
CASE WHEN WM_Template.WBS2 = '260' THEN 'Pre Con Mtg / Inspections/ Punch List' WHEN WM_Template.WBS2 = '250' THEN 'All Environmental Permits & Approvals'
WHEN WM_Template.WBS2 = '150' THEN 'Project Admin / Clerical / Status Reports ' ELSE WM_Template.LongName END AS LongName,
(ISNULL(LB.AmtBud, 0) + ISNULL(EB.AmtBud, 0)) AS AmtBud, SUM(CASE WHEN LedgerAR.TransType <> 'CR ' AND
LedgerAR.Period <= '200408' THEN Ledgerar.amount * - 1 ELSE ' 0 ' END) AS BTD, SUM(CASE WHEN LedgerAR.TransType <> 'CR ' AND
LedgerAR.Period = '200408' THEN Ledgerar.amount * - 1 ELSE ' 0 ' END) AS BillExt
FROM PR LEFT OUTER JOIN
LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3 LEFT OUTER JOIN
EB ON EB.WBS1 = PR.WBS1 AND PR.WBS2 = EB.WBS2 AND PR.WBS3 = EB.WBS3 LEFT OUTER JOIN
LedgerAR ON LedgerAR.WBS1 = PR.WBS1 AND LedgerAR.WBS2 = PR.WBS2 AND PR.WBS3 = LedgerAR.WBS3 LEFT OUTER JOIN
LedgerAP ON LEdgerAP.WBS1 = PR.WBS1 AND LedgerAP.Wbs2 = PR.WBS2 AND LedgerAP.WBS3 = PR.WBS3 LEFT JOIN
WM_DA_Template ON WM_DA_Template.WBS2 = PR.WBS2 AND PR.WBS3 = WM_DA_Template.WBS3 LEFT OUTER JOIN
WM_Template ON WM_DA_Template.WM_Key = WM_Template.WM_Key
WHERE (PR.WBS2 <> '9001') AND (PR.WBS2 <> '98') AND (PR.WBS2 <> 'zzz') AND (PR.WBS3 <> 'zzz') AND (PR.WBS2 <> '') AND (WM_Template.WBS2 <> '')
AND WM_Template.WBS2 <> '210' AND pr.wbs1 = '001-298'
GROUP BY WM_Template.WBS2, WM_Template.WBS3, WM_Template.LongName, lb.amtbud, eb.amtbud


second query

SELECT WM_Template.WBS2, CASE WHEN (SUM(isnull(LB.AmtBud * - 1, 0)) <> '0') AND (SUM(isnull(EB.AmtBud, 0)) <> '0')
THEN 'HM & SC' WHEN (SUM(isnull(LB.AmtBud * - 1, 0)) <> '0') AND (SUM(isnull(EB.AmtBud, 0)) = '0') THEN 'HM' WHEN (SUM(isnull(LB.AmtBud, 0))
= '0') AND (SUM(isnull(EB.AmtBud, 0)) <> '0') THEN 'SC' WHEN (SUM(isnull(LB.AmtBud, 0)) = '0') AND (SUM(isnull(EB.AmtBud, 0)) = '0')
THEN (CASE WHEN (SUM(isnull(LedgerAP.Amount, 0)) <> '0') THEN 'SC' END) WHEN (SUM(isnull(LB.AmtBud, 0)) = '0') AND (SUM(isnull(EB.AmtBud, 0))
= '0') THEN (CASE WHEN (SUM(isnull(LedgerAR.Amount * - 1, 0)) <> '0') THEN 'HM' END) WHEN (SUM(isnull(LB.AmtBud, 0)) = '0') AND
(SUM(isnull(EB.AmtBud, 0)) = '0') THEN (CASE WHEN (SUM(isnull(LedgerAP.Amount, 0)) <> '0') AND (SUM(isnull(LedgerAR.Amount * - 1, 0)) <> '0')
THEN 'HM & SC' END) ELSE 'N/A' END AS FeeType
FROM PR LEFT OUTER JOIN
LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3 LEFT OUTER JOIN
EB ON EB.WBS1 = PR.WBS1 AND PR.WBS2 = EB.WBS2 AND PR.WBS3 = EB.WBS3 LEFT OUTER JOIN
LedgerAR ON LedgerAR.WBS1 = PR.WBS1 AND LedgerAR.WBS2 = PR.WBS2 AND PR.WBS3 = LedgerAR.WBS3 LEFT OUTER JOIN
LedgerAP ON LEdgerAP.WBS1 = PR.WBS1 AND LedgerAP.Wbs2 = PR.WBS2 AND LedgerAP.WBS3 = PR.WBS3 LEFT JOIN
WM_DA_Template ON WM_DA_Template.WBS2 = PR.WBS2 AND PR.WBS3 = WM_DA_Template.WBS3 LEFT OUTER JOIN
WM_Template ON WM_DA_Template.WM_Key = WM_Template.WM_Key
WHERE (PR.WBS2 <> '9001') AND (PR.WBS2 <> '98') AND (PR.WBS2 <> 'zzz') AND (PR.WBS3 <> 'zzz') AND (PR.WBS2 <> '') AND (WM_Template.WBS2 <> '')
AND pr.wbs1 = '001-333'
GROUP BY WM_Template.WBS2


Any suggestions would be helpful.

Thank You.

View 2 Replies


ADVERTISEMENT

Join And Subquery

Jun 12, 2007

Please let me know this......
i was asked this question during my evaluations at my office.

When do you use and when do u use a subquery.....?please lt me kow the ideal condition for this.

I was asked to answer a specific reason for the above question.

thank you....
there is one more interesting question coming up....

Trust in Technology mate .....'Not in Human Beings'

View 5 Replies View Related

Join Or Subquery Problem

May 2, 2001

I have a query problem that baffles me.

I have one data table (tblPresultPeriods) and one lookup table (lkpStatus).

The query is:

select PPPeriodNumber as PeriodNumber, Max(PPstatus) as Status
from tblPresultPeriods
where ppmemberid = 133 and pptransseq > 0
group by ppperiodnumber

It returns (correctly):

PeriodNumber Status
------------ ------
A1 1
Q1 4
Q2 1


I want to do a join so that I get the lkpStatus.StatusDescription value to display.

The query is:

select PPPeriodNumber, Max(PPstatus)as status, StatusDescription
from tblPresultPeriods, lkpStatus
where tblPresultPeriods.PPstatus = lkpStatus.Statuscode
and ppmemberid = 133
and pptransseq > 0
group by PPPeriodNumber, PPstatus, StatusDescription
order by PPPeriodNumber, PPstatus, StatusDescription

but I get (not what I want):

PPPeriodNumber status StatusDescription
-------------- ------ --------------------------------------------------
A1 0 Initialize
A1 1 Enter Presults
Q1 0 Initialize
Q1 1 Enter Presults
Q1 4 Re-Review Presutls
Q2 0 Initialize
Q2 1 Enter Presults


because the of the additional group by & order by columns.


I assume this requires a sub-query, but I am baffeled.

Bobby

View 2 Replies View Related

Left Join And Subquery

May 1, 2004

Hi,
Iwant to make a left join between two tables connected with a specific field
but I also want to have some criteria on the right table.
I want to display all the rows from the first(left) table and only the rows from the secod (right) table that match the join and the criteria.
However If I have criteria on the second table the join behaves like an inner join displaying only the rows that exist in both tables.
In MS Access instead of the right table I use a query in which I enter the criteria and then I connect it with a left join in a new query in order to get the right results. How can I do itin MSSQLServer without using Views?
Must I use a subquery with Exists and what is the right syntax in order to achieve this?

Best Regards,
manolis Perrakis

View 1 Replies View Related

Multivalue Subquery In The On Of A Join

May 13, 2008

I know I can't do this but I dont know will work. any help is much appreciated. the code below in red will always return more than one value, and yes the ?? is actually data (dont ask i didnt do it).

I am basically coding this because of the ?? someone before me thought was a good idea.

inner join MHDDTest.dbo.Admin_tbl_Vendor f on
(case when len(b.vadr) > 0 then
(case when (b.vadr = '??') then
(select vendorNumber from MHDDTest.dbo.Admin_tbl_Vendor
where (substring(afunction,3,2) + object) = '42305' and substring(vendorNumber,1,5) = b.vend)
else
b.vend + '-' + b.vadr
end)
else
b.vend
end
= f.vendorNumber)

View 9 Replies View Related

Trying To Join Two Columns In Subquery

Jun 21, 2007

After working with Oracle (can I say that out loud here? ) databases for a couple of years, our company recently switched to SQL2005. So far I really like it, and the switch has been relatively easy for me. Except for one thing...

I'm trying to find out how to join a subquery on multiple columns. In Oracle it would like this:

select * from #temp1 where (id_1, id_2) not in (select id_1, id_2 from #temp2)

But this doesn't work in SQL-T. I've temporarily solved it by doing this:

select * from #temp1 where id_1+id_2 not in (select id_1+id_2 from #temp2)

I don't like concat solutions for joins though (it always feels 'unsafe' to me). Plus, I can't believe there's no way to solve this.

I've searched the FAQ and BOL but I just can't seem to find the solution. Please help. I feel stupid.

View 5 Replies View Related

Subquery In A Left Join

Apr 24, 2008

Hi, I have a long sql query and I have to left join two tables. The problem is that I need to do a subquery in the left join for the second table. Let me give an example.


My second table haves this data:
ID GAME CREATED
1 SOCCER 1-8-1980
1 BASKKET 1-9-2000
2 TENIS 1-10-2000
2 GOLF 1-12-2000
2 CARS 1-9-2010
3 BIKE 1-2-1930


My first table left joins the second one by the ID column. So, I want first to determine the max record of each ID of the second table. Ones I have the max record for each ID in table one i want to left join it with table A.
How do I have to build the subquery in the inner join? I believe is something like this:

SELECT * FROM
TABLE1
LEFT OUTER JOIN TABLE2 (SUBQUERY) TABLE2_ALIAS
ON TABLE1.ID = TABLE2_ALIAS.ID

Thanks in advance!

View 7 Replies View Related

Transact SQL :: Left Join With Subquery

Aug 24, 2015

SELECT A.EmpId,A.IncidentDate
FROM EmployeePoints1 as A
WHERE IncidentDate=
(SELECT MAX(IncidentDate)
FROM EmployeePoints1
WHERE EmpId = A.EmpId) AND (DATEADD(day,28,DATEADD(WEEK, DATEDIFF(WEEK, 0,A.IncidentDate), 0)) < DATEADD(WEEK, DATEDIFF(WEEK, 0,GetDate()), 0)) AND (A.IncidentCode = 'I' OR A.IncidentCode = 'A')
LEFT JOIN EmployeeTotalPoints1 ON EmployeeTotalPoints1.EmpId = A.EmpId

I am trying to left join another table but  I got

Incorrect syntax near the keyword 'LEFT'.

View 9 Replies View Related

Left Outer Join Versus Subquery

Sep 13, 2006

Hello Chaps,

I have got question about the performance of subquery and left outer join

My scenario is that  I have two tables Customer Mater and address book.

Customer Mater and address book  tables hold the record for both carrier and customer. and i want retrieve the record for customer and related carrier as well

i have two options

1.  either use left outer join (i.e. Join Customer master to Address Book 2 times, 1 for customer and 1 for carrier) by:

       Address book (as customer) Left outer Join Customer Master (on related Key)

      AddressBook (as carrier) Left Outer Join Customer master (on related Key)

2.or use 1 join for customer and use subquery to retrieve carrier name (from Customer table) as I only need that one

      Address Book AB (as Customer) Join Customer Master CM
      Subquery:  Select carrier_name From customer_master cm2  where cm2.carrier = ab.carrier

can any one tell one which is the best as far as performance is concern. My opinion is that the subquery is better becuase left outer join will have the overhread of extra columns... not pretty sure about it..

Any idea?

 

regards,

Anas

View 9 Replies View Related

Problem Joining Child Data (JOIN, Subquery, Or Something Else?)

Jun 21, 2006

The problem:I'm updating a report to be "multi-language" capable. Previously,any items that had text associated with them were unconditionallypulling in the English text. The database has always been capable ofstoring multiple languages for an item, however.Desired output:Given the test data below, I'd like to get the following resultsselect * from mytestfunc(1)Item_Id, Condition, QuestionText1876, NOfKids <= 10, This many children is unlikely.select * from mytestfunc(2)CheckID, Condition, QuestionText1876, NOfKids <= 10, NULLThe current SQL for my UDF:CREATE FUNCTION Annotated_Check (@Lang_ID int) RETURNS TABLE AS RETURN (SELECT tblCheck.Item_ID, tblCheck.CheckDescr AS Condition,tblQuestionText.QuestionTextFROM tblCheck LEFT OUTER JOIN tblQuestionText ON (tblCheck.Item_ID =tblQuestionText.Item_ID)WHERE ((tblQuestionText.LanguageReference = @Lang_ID) OR(tblQuestionText.LanguageReference IS NULL)))Test data:CREATE TABLE [dbo].[tblCheck] ([Item_ID] [int] NOT NULL ,[CheckDescr] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CreationDate] [datetime] NULL ,[RevisionDate] [datetime] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE [dbo].[tblCheck] ADDCONSTRAINT [DF__tblCheck__Creati__0D7A0286] DEFAULT (getdate()) FOR[CreationDate],CONSTRAINT [PK_Check] PRIMARY KEY CLUSTERED([Item_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE TABLE [dbo].[tblLanguage] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[Priority] [int] NULL ,[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[Spoken] [bit] NULL ,[CreationDate] [datetime] NULL ,[RevisionDate] [datetime] NULL) ON [PRIMARY]GOALTER TABLE [dbo].[tblLanguage] WITH NOCHECK ADDCONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblLanguage] ADDCONSTRAINT [DF__tblLangua__Creat__2CF2ADDF] DEFAULT (getdate()) FOR[CreationDate],UNIQUE NONCLUSTERED([Priority]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE TABLE [dbo].[tblQuestionText] ([Item_ID] [int] NOT NULL ,[LanguageReference] [int] NOT NULL ,[QuestionText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[SameAs] [int] NULL ,[CreationDate] [datetime] NULL ,[RevisionDate] [datetime] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE [dbo].[tblQuestionText] ADDCONSTRAINT [DF__tblQuesti__Creat__76969D2E] DEFAULT (getdate()) FOR[CreationDate],CONSTRAINT [PK_QuestionText] PRIMARY KEY CLUSTERED([Item_ID],[LanguageReference]) WITH FILLFACTOR = 90 ON [PRIMARY]GOINSERT INTO tblCheck (Item_ID, CheckDescr)VALUES(1876, 'NOfKids <= 10')INSERT INTO tblLanguage (ID, Priority, Name, Spoken)VALUES(1,1,'English', 1)INSERT INTO tblLanguage (ID, Priority, Name, Spoken)VALUES(2,2,'Espanol', 1)INSERT INTO tblQuestionText (Item_ID, LanguageReference, QuestionText)VALUES (1876, 1, 'This many children is unlikely.')Any tips or pointers will be appreciated. Thanks.

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

Can Any One Tell Me The Difference Between Cross Join, Inner Join And Outer Join In Laymans Language

Apr 30, 2008

Hello

Can any one tell me the difference between Cross Join, inner join and outer join in laymans language

by just taking examples of two tables such as Customers and Customer Addresses


Thank You

View 1 Replies View Related

Integration Services :: How To Perform Left Restricted Join In Merge Join Transformation

May 22, 2015

I have two xml source and i need only left restricted data.

how can i perform left restricted join?

View 2 Replies View Related

Transact SQL :: Difference Between Inner Join And Left Outer Join In Multi-table Joins?

Oct 8, 2015

I was writing a query using both left outer join and inner join.  And the query was ....

SELECT
        S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
        Production.Suppliers AS S LEFT OUTER JOIN
        (Production.Products AS P
         INNER JOIN Production.Categories AS C

[code]....

However ,the result that i got was correct.But when i did  the same query using the left outer join in both the cases

i.e..

SELECT
        S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
        Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';

The result i got was same,i.e

supplier     country    productid    productname     unitprice    categorynameSupplier QOVFD     Japan     9     Product AOZBW    97.00     Meat/PoultrySupplier QOVFD    Japan   10     Product YHXGE     31.00     SeafoodSupplier QOVFD     Japan   74     Product BKAZJ    10.00     ProduceSupplier QWUSF     Japan    13     Product POXFU     6.00     SeafoodSupplier QWUSF     Japan     14     Product PWCJB     23.25     ProduceSupplier QWUSF    Japan     15    Product KSZOI     15.50    CondimentsSupplier XYZ     Japan     NULL     NULL     NULL     NULLSupplier XYZ     Japan     NULL     NULL     NULL     NULL

and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.

View 5 Replies View Related

Warning - The Join Order Has Been Enforced Because A Local Join Hint Is Used

Dec 23, 2014

I have two select statements, in between select statement taking UNION ALL . I need to avoid the error

Warning: The join order has been enforced because a local join hint is used.

View 9 Replies View Related

'Left Outer Merge Join' Failing To Join Valid Row

Aug 10, 2007

Scenario:

OLEDB source 1
SELECT ...
,[MANUAL DCD ID] <-- this column set to sort order = 1
...
FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC


OLEDB source 2
SELECT ...
,[Bo Tkt Num] <-- this column set to sort order = 1
...
FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC

These two tasks are followed immediately by a MERGE JOIN

All columns in source1 are ticked, all column in source2 are ticked, join key is shown above.
join type is left outer join (source 1 -> source 2)

result of source1 (..dcd column)
...
4-400-8000119
4-400-8000120
4-400-8000121
4-400-8000122 <--row not joining
4-400-8000123
4-400-8000124
...


result of source2 (..tkt num column)
...
4-400-1000118
4-400-1000119
4-400-1000120
4-400-1000121
4-400-1000122 <--row not joining
4-400-1000123
4-400-1000124
4-400-1000125
...

All other rows are joining as expected.
Why is it failing for this one row?

View 1 Replies View Related

Multi-table JOIN Query With More Than One JOIN Statement

Apr 14, 2015

I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.

For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.

The carid (primary key) and carmodelname belong to the Cars table.
The makeid and orderdate belong to the OrderDetails table.
The productname and carcategory belong to the Product table.

The number of rows returned should be the same as the number of rows in OrderDetails.

View 2 Replies View Related

Select Command - Left Join Versus Inner Join

Aug 9, 2013

Why would I use a left join instead of a inner join when the columns entered within the SELECT command determine what is displayed from the query results?

View 4 Replies View Related

Merge Join (Full Outer Join) Never Finishes.

Jun 5, 2006

I have a merge join (full outer join) task in a data flow. The left input comes from a flat file source and then a script transformation which does some custom grouping. The right input comes from an oledb source. The script transformation output is asynchronous (SynchronousInputID=0). The left input has many more rows (200,000+) than the right input (2,500). I run it from VS 2005 by right-click/execute on the data flow task. The merge join remains yellow and the task never finishes. I do see a row count above the flat file destination that reaches a certain number and seems to get stuck there. When I test with a smaller file on the left it works OK. Any suggestions?

View 3 Replies View Related

Why Does My Query Timeout Unless Force Join To Hash Join?

Jul 25, 2007

I'm using SQL Server 2005.



A piece of software I wrote starting timing out on a query that left outer joins a table to a view. Both the table and view have approximately the same number of rows (about 170000).



The table has 2 very similar columns, one is a varchar(1) and another is varchar(100). Neither are included in any index and beyond the size difference, the columns have the same properties. One of the employees here uses the varchar(1) column (called miscsearch) to tag large sets of rows to perform some action on. In this case, he had set 9000 rows miscsearch value to "g". The query then should join the table and view for all rows where miscsearch is set to g in the table. This query takes at least 20 minutes to run (I stopped it at this point).

If I remove the "where" clause and join all rows in the two tables, the query completes in about 20 seconds. If set the varchar(100) column (called descrip) to "g" for the same rows set via miscsearch, the query completes in about 20 seconds.



If I force the join type to a hash join, the query completes using miscsearch in about 30 seconds.



So, this works:

SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER HASH JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC



and this works:

SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE descrip = 'g' ORDER BY balance DESC



But this does't:

SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC



What should I be looking for here to understand why this is happening?



Thanks,

john















View 1 Replies View Related

Page 2 - Within The INNER JOIN, How To Limit The Row To 1 Row Inside The INNER JOIN?

Apr 24, 2007

Awesome! I don't alway get the email notification of whoever reply to the posting. I think it only work after I log off of the forum.

Scott

View 2 Replies View Related

Changing From Implicit Join To Explicit Join

Dec 24, 2013

We are trying to migrate from sql 2005 to 2012. I am changing one of the implicit join to explicit join. As soon as I change the join, the number of rows returned are fewer than before.

Below is my Implict join query

INSERT #RIF_TEMP1 (rf1_row_no,rf1_rif, rf1_key_id_no, rf1_last_date, rf1_start_date)
SELECT currow.rf0_row_no, currow.rf0_rif, currow.rf0_key_id_no, prevrow.rf0_start_date, currow.rf0_start_date
FROM #RIF_TEMP0 currow , #RIF_TEMP0 prevrow

[Code] ....

and below is explict join query

INSERT #RIF_TEMP1 (rf1_row_no,rf1_rif, rf1_key_id_no, rf1_last_date, rf1_start_date)
SELECT currow.rf0_row_no, currow.rf0_rif, currow.rf0_key_id_no, prevrow.rf0_start_date, currow.rf0_start_date
FROM #RIF_TEMP0 currow LEFT JOIN #RIF_TEMP0 prevrow
ON (currow.rf0_row_no = prevrow.rf0_row_no + 1)

[Code] ....

the count returned from both the queries is different.

I am not sure what am I doing wrong. The count of #RIF_TEMP0 is always 32, it never changes, but the variable @countTemp is different for both the queries.

View 7 Replies View Related

Right Join Returns Same Results As Left Join

Feb 5, 2015

Why does this right join return the same results as using a left (or even a full join)?There are 470 records in Account, and there are 1611 records in Contact. But any join returns 793 records.

select Contact.firstname, Contact.lastname, Account.[Account Name]
from Contact
right join Account
on Contact.[Account Name] = Account.[Account Name]
where Contact.[Account Name] = Account.[Account Name]

View 3 Replies View Related

How To Join 3 Tables Using Left Or Right Join Keyword?

Aug 17, 2007

Hi guys,

I'll appreciate any help with the following problem:

I need to retrieve data from 3 tables. 2 master tables and 1 transaction table.

1. Master table TBLOC contain 2 records :
rcd 1. S01
rcd 2. S02

2. Master table TBCODE contain 5 records:

rcd 1. C1
rcd 2. C2
rcd 3. C3
rcd 4. C4
rcd 5. C5

3. Transaction table TBITEM contain 4 records which link to 2 master table:
rcd 1. S01, C1, CAR

rcd 2. S01, C4, TOY
rcd 3. S01, C5, KEY
rcd 4. S02, C2, CAR



I use Left Join & Right Join to retrieve result below (using non-ASNI method) but it doesn't work.

Right Join method:


SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A RIGHT JOIN TBCODE B ON A.CODE = B.CODE

RIGHT JOIN TBLOC C ON A.LOC = C.LOC

GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE



When I use Non-ASNI method it work:



SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A, TBCODE B, TBLOC C

WHERE A.CODE =* B.CODE AND A.LOC =* C.LOC

GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE

Result:

LOC CODE ITEM
-----------------------------
S01 C1 NULL
S01 C2 NULL
S01 C3 CAR
S01 C4 TOY
S01 C5 KEY
S02 C1 NULL
S02 C2 CAR
S02 C3 NULL
S02 C4 NULL
S02 C5 NULL


Please Help.

Thanks.






View 3 Replies View Related

Super Join - Is Merge Join The Answer?

Nov 7, 2006

Is there a way to do a super-table join ie two table join with no matching criteria? I am pulling in a sheet from XL and joining to a table in SQLServer. The join should read something like €œfor every row in the sheet I need that row and a code from a table. 100 rows in the sheet merged with 10 codes from the table = 1000 result rows.

This is the simple sql (no join on the tables):

select 1.code, 2.rowdetail
from tblcodes 1, tblelements 2

But how to do this in SSIS?

Thanks - Ken

View 2 Replies View Related

How Would You Convert A Hash Join Into A Merge Join?

May 6, 2008

I read that merge joins work a lot faster than hash joins. How would you convert a hash join into a merge join? (Referring to output on Execution Plan diagrams.)
THANKS

View 3 Replies View Related

Within The INNER JOIN, How To Limit The Row To 1 Row Inside The INNER JOIN?

Apr 17, 2007

There is a table called "tblvZipCodes" that contain a zipcode of all cities, area code that are located in that zip code.

The problem I have with the inner join is that there are more than 1 cities in one zipcode code. Is there a way to just return only the 1st row and not return the rest of the rows from the tblvZipCodes in the INNER JOIN query?

Thanks..


Code:

SELECT TOP 100 PERCENT dbo.tblPurchaseRaw.Year, dbo.tblPurchaseRaw.Make, dbo.tblPurchaseRaw.Model, dbo.tblPurchaseRaw.ModelType,
dbo.tblPurchaseRaw.Color, dbo.tblvZipCodes.ZIPCode, dbo.tblvZipCodes.City, dbo.tblvZipCodes.County, dbo.tblvZipCodes.State,
dbo.tblvZipCodes.AreaCode, dbo.tblvZipCodes.Region, dbo.tblaAccounts.Name, dbo.tblaAccounts.PhoneOne, dbo.tblaAccounts.AccountID,
dbo.tblPurchaseRaw.AcceptedID, dbo.tblPurchaseRaw.Series, dbo.tblPurchaseRaw.BodyStyle, dbo.tblaAccounts.WebSite,
dbo.tblaAccounts.SalesEmail, dbo.tblPurchaseRaw.EmailTo, dbo.tblPurchaseRaw.PhotoURL, dbo.tblPurchaseRaw.Mileage,
dbo.tblPurchaseRaw.RawID, dbo.tblvRegions.Name AS RegionName, dbo.tblPurchaseRaw.VIN, dbo.tblPurchaseRaw.Style,
dbo.tblPurchaseRaw.StockDate
FROM dbo.tblPurchaseRaw INNER JOIN
dbo.tblaAccounts ON dbo.tblPurchaseRaw.AccountID = dbo.tblaAccounts.AccountID INNER JOIN
dbo.tblvZipCodes ON dbo.tblPurchaseRaw.ZipCode = dbo.tblvZipCodes.ZIPCode INNER JOIN
dbo.tblvRegions ON dbo.tblvZipCodes.Region = dbo.tblvRegions.RegionID
WHERE (CONVERT(char, dbo.tblPurchaseRaw.StockDate, 101) <> '01/01/1900') AND (dbo.tblPurchaseRaw.SoldRawID IS NULL) AND
(dbo.tblPurchaseRaw.AcceptedID <> - 10) AND (dbo.tblPurchaseRaw.AcceptedID <> - 1)
ORDER BY dbo.tblvZipCodes.ZIPCode

View 14 Replies View Related

Inner Join To Outer Join Problem

Mar 1, 2008

hello, i am running mysql server 5 and i have sql syntax like this:
select
sales.customerid as cid,
name,
count(saleid)
from
sales
inner join
customers
on
customers.customerid=sales.customerid
group by
sales.customerid
order by
sales.customerid;
it works fine and speedy. but when i change inner join to right join, in order to get all customers even there is no sale, my server locks up. note: there is about 10000 customers and 15000 sales.
what can be the problem?
thanks,

View 10 Replies View Related







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