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
ADVERTISEMENT
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
View Related
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
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
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
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
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
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
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
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
Apr 26, 2008
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...
View 1 Replies
View Related
Jul 20, 2005
I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO
View 3 Replies
View Related
Mar 6, 2008
I am getting an error as
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
while running the following query.
SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,
LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,
LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,
ManagerName=(SELECT E.FirstName+' '+E.LastName
FROM EmployeeDetails E
INNER JOIN LUP_EmpProject
ON E.Empid=LUP_EmpProject.Empid
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid
WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)
FROM EmployeeDetails
INNER JOIN LUP_EmpDepartment
ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid
INNER JOIN LUP_FIX_DeptDetails
ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid
AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date
FROM LUP_EmpDepartment
WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid
ORDER BY LUP_EmpDepartment.Date DESC)
INNER JOIN LUP_EmpDesignation
ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid
INNER JOIN LUP_FIX_DesigDetails
ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid
AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date
FROM LUP_EmpDesignation
WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid
ORDER BY LUP_EmpDesignation.Date DESC)
INNER JOIN LUP_EmpProject
ON EmployeeDetails.Empid=LUP_EmpProject.Empid
AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate
FROM LUP_EmpProject
WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid
ORDER BY LUP_EmpProject.StartDate DESC)
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid
WHERE EmployeeDetails.Empid=1
PLEASE HELP.................
View 1 Replies
View Related
May 14, 2008
Hi,
I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Any ideas as to why??
SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar,
RDR1.U_Variety,
(SELECT OITM.U_Variety
FROM OWOR INNER JOIN
WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN
OITM INNER JOIN
OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode
WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity,
OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet,
OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode
FROM OWOR AS t0 INNER JOIN
ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN
RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN
OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode
WHERE (t0.Status <> 'L')
Thanks
Jacquues
View 4 Replies
View Related
Jul 19, 2007
Hi guys,
A have a problem that I need understand.... I have 2 server with the same configuration...
SERVER01:
-----------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
********************************
SERVER02:
-----------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
OK, the problem is that if a run the below query in server01, i get error 512:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
But, if run the same query in the server02, the query work fine -.
I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.
Any idea WHY?
SELECT dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao ds_tipo_transacao_movimento ,
dbo.opf_header_operacao.ds_tipo_transacao ds_tipo_transacao_header,
'SD' ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente,
0 vl_entrada_compra_ctro ,0 vl_entrada_compra_premio,
0 vl_entrada_venda_ctro , 0 vl_entrada_venda_premio,
0 vl_saida_compra_ctro, 0 vl_saida_compra_premio,
0 vl_saida_venda_ctro, 0 vl_saida_venda_premio,
0 vl_lucro , 0 vl_prejuizo, 0 vl_naoexec_contrato,
0 vl_naoexec_premio,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_ganho) vl_aprop_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_perda) vl_aprop_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_ganho) vl_rever_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_perda) vl_rever_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_irrf) vl_irrf
FROM dbo.opf_saldo_ctb_opc_flx,
dbo.opf_header_operacao ,
dbo.opf_movimento_operacao
WHERE dbo.opf_saldo_ctb_opc_flx.dt_saldo = '6-29-2007 0:0:0.000'
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_saldo_ctb_opc_flx.no_contrato )
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_movimento_operacao.no_contrato )
and ( dbo.opf_movimento_operacao.dt_pregao = (select (o.dt_pregao) from dbo.opf_movimento_operacao o
where o.no_contrato = dbo.opf_movimento_operacao.no_contrato and o.dt_pregao <='6-28-2007 0:0:0.000' ) )
and (dbo.opf_saldo_ctb_opc_flx.ic_tipo_saldo = 'S')
group by dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao,
dbo.opf_header_operacao.ds_tipo_transacao ,
ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente
Thanks
Nilton Pinheiro
View 9 Replies
View Related
Jul 6, 2014
I am trying to add the results of both of these queries together:
The purpose of the first query is to find the number of nulls in the TimeZone column.
Query 1:
SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename
The purpose of the second query is to find results in the AAST, AST, etc timezones.
Query 2:
SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')
Note: both queries produce a whole number with no decimals. Ran individually both queries produce accurate results. However, what I would like is one query which produced a single INT by adding both results together. For example, if Query 1 results to 5 and query 2 results to 10, I would like to see a single result of 15 as the output.
What I came up with (from research) is:
SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST'))
I get a msq 102, level 15, state 1 error.
I also tried
SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')) as IVR_HI_n_AK_results
but I still get an error. For the exact details see:
[URL]
NOTE: the table in query 1 and query 2 are the same table. I am using T-SQL in SQL Server Management Studio 2008.
View 6 Replies
View Related
Sep 21, 2007
Hi. I am fairly new to reporting. I have a report that currently displays queried data in both a table and a matrix. I'd like for the data that is displayed to be based off of my report parameters. I have three, all of which are multivalue. Currently, I am able to display the report, based on the only the first value of TWO of the three parameters. I tried using the dataset parameters, but was not successful with linking them to my report parameters. Please help! In addition, is there a way to set multivalue parameters to only display distinct values? Thanks, in advance, for any help.
View 3 Replies
View Related
Jan 17, 2008
Hi,
I need to set a label in my report when all the values in a certain multivalue parameter are selected. In other words, i would need to check whether this parameter has the value "Select All" selected or not.
How do i accomplish this?
Thanks in advance,
Claudio
View 3 Replies
View Related
Mar 27, 2007
We have a report that has a banner page. The user can select categories from a multi-select parameter and then this is passed into the report as a csv file. The actual report works doing this, however, we need the banner page to use this parameter's value (the multiple selected labels) to display a string of the selected values. For the textbox expression, we are using =Parameters!Cat.Value, but it always wants to reference Paramaters!Cat.Value(0). We were thinking of using a function in code to pass in the parameter and then concantenate each value into on string and return that string, but isn't there an easier way to do this?
Thanks for the information.
View 3 Replies
View Related
Jun 7, 2007
I have passed a list of parameters to a multivalue parameter in a SQL Report.
I can click the drop down and see the list of parameters that were passed.
In my select statement for the report, here is the condition I am using in the where clause.
AND (SUBSTRING(C.CHARTOFACCTSACCOUNTNO,9,3) IN (@RUs))
Nothing is returned in the result set if more than one value is passed to the multivalue parameter.
Shouldn't the above conidition have the effect that no matter how many account numbers there are, the last three digits in the account number will be compared to each element in my multivalue parameter and if there is a match, it will be selected on the report.
For example, in the multivalue dropdown list, there are the following values:
211
212
If I pass 211 to the report, data is returned. If I pass 211, 212 to the multivalue parameter no data is returned.
View 60 Replies
View Related
Apr 18, 2007
how do I create a multivalue parameter that has a default value of 'All' Or how to I I get the "Select All" option as the default parameter?
View 3 Replies
View Related
May 11, 2007
I have multivalued parameter in my report. I have text box to display heading of the report. the report heading is dependent on the parameter selected.
with single value parameter the text box displays the correct parameter but it cannot with multi valued parameter. Is there any way to display the last value of the parameter in the heading of the report in case of multivalued parameter?
Ex. multivalued parameter is "YEAR"
my report shows the data for 3 years say 2003,2004,2005. report header should show 2005(last of the parameter list) report.
regds,
View 6 Replies
View Related
Mar 20, 2007
Hello,
I have in my report a text box that lists the user selection. When the user select a few of the values I display the selection but when the user select "All" I would like the text box to say "Parameter: All".
Is there anyway to know if "All" was selected, I don't want to list all the values because sometime there are too many of them.
Thank you,
Itzhak
View 3 Replies
View Related
Dec 6, 2006
i am trying to build a report that uses multivalue parameters - the report is a simple listing report that lists out a list of cases as a table - the data is obtained from a view which just returns all details for all cases
there is no user input for the selection details - am going to be calling the report and passing over the parameter values directly to it.
there are several parameters for the report - they all default to "All"
for each of these parameters, i need to be able to either use the default value of "ALL" or to enter a list (comma separated ?)
q1) could i just enter the list as a string comma separated list (rather than as multivalued parameters) and then use this list to filter the data using the FILTERS tab on the dataset
q2) if q1 is not possible, if i use multivalue parameters, how do i get my list of parameters into the report as the application calling the report just has a list of values
thx
mark
View 11 Replies
View Related
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
Nov 28, 2007
Is there an easy way to handle a multivalue parameter where the values could have comma's? I have County and State in a single column and want to select one from the multivalue parameter but when the string is passed it is not being found because I am parsing the parameter on comma. Here is what the drop down list looks like.
Dade,CA
Marion,CA
Brown,CA
Thanks,
vmon
View 1 Replies
View Related
Oct 1, 2007
In SQL Server 2005 reporing services, I am using the syntax in the documentation for a multivalue parameter.
AND c.Track IN (@Track)
It works fine when I just select one value, but if I select 2 or more, I get an error about the commas being wrong. Is the syntax correct? Is there a way I can see the SQL generated by the report so I can see what is wrong?
Thanks,
Linda
View 17 Replies
View Related
Apr 1, 2008
Hi,
I'm calling a report based on values collected from an ASP.NET page. It worked fine until I tried to pass a multivalue integer value.
ReportParameter seems to take a string[] just fine, but not a int[].
For example...
int[] incidents = prevPage.IncidentIDs;
string[] assets = prevPage.Assets;
ReportParameter[] parameters = new ReportParameter[2];
parameters[0] = new ReportParameter("IncidentID", incidents);
parameters[1] = new ReportParameter("Assets", assets);
throws the error: cannot convert from 'int[]' to 'string'
but changing incidents to a string like so:
string[] incidents = prevPage.IncidentIDs;
string[] assets = prevPage.Assets;
ReportParameter[] parameters = new ReportParameter[2];
parameters[0] = new ReportParameter("IncidentID", incidents);
parameters[1] = new ReportParameter("Assets", assets);
results in "The 'IncidentID' parameter is missing a value"
I've verified that incidents has values.
Any help is appreciated.
Thanks,
Tonya
View 14 Replies
View Related
Mar 4, 2008
There is a select all option for each multivalue parameter. If this option is selected, I would like to display in the report that the parameter selected is "ALL" instead of using the Join(parameter!parametername.value,",") to display all the values. Any idea how I check to see if the select all at the beginning is checked?
View 13 Replies
View Related
May 22, 2007
Hello Friends,
I have a report (Say Report 1)which is asking for a multivalue parameter Period. The user selects three period from the dropdown list available for the Parameter. Lets say that the user select 0407,0507,0607. Now is this report 1 there is a particular field which is linked to another sub report. So if the top level Report 1 supplies the revenue for 3 regions say for the periods selected then clicking on any of the regions would open a second detailed report, say Report 2 for the three periods as selected.
Till this everything is fine.
Now the problem is that in this detailed Report 2 there would be a particular text box as "Click here to naviagate back to parent report". So that when the user clicks on this cell he is taken back to the parent report, Report 1 which was originally generated for the 3 periods.
Can anyone please advise as to how the Multivalue Parameter which was passed from Report 1 to Report 2 can again be looped back to Report 1 from Report 2.
Thanx Friends,
Raktim
View 2 Replies
View Related
Jun 16, 2006
I'm trying to output all the values selected through a multivalue parameter, but I am having difficulties. In the text box, the parameter contains only four member functions (value, label, count, ismultivalue). However, to output a value or label for the parameter you need to specify an index in the form of:
Parameters!Names.Value(0)
Ideally, I would like to be able to obtain all these values with a function call, but it doesn't look like there is one. Any ideas of how to get all the values?
Thank you!!
View 4 Replies
View Related
Dec 14, 2007
Greetings all
trying to get a multivalue parameter to accept either typed in data ex: 111111111,111111112 or if I want to return all id numbers type in %. Problem is when I test it by typing in 111111111,111111112 it throws an error saying " Incorrect syntax near ','.
I can enter 111111111 or % and get results, the error comes when I try to type in two or more id numbers. the parameter in the dataset looks like
where a.id_number LIKE (@id_number+ '%')
any suggesstions? Im sure there are threads out there but Im pressed to meet a deadline and wanted to see if there were any quick solutions
thanks
km
View 5 Replies
View Related
Jan 23, 2006
Hi!
I have a table with the following columns:
account_nr, account_totaling_members, account_type
the account_totaling_members column contains a pipe sperated list of accounts in a varchar: "1001|1002|1003"
I need to normalize this so that i get records like:
"10", "1001", "sum"
"10", "1001", "sum"
"10", "1002", "sum"
..and so forth
Does anyone have any idea how to accomplish this?
View 3 Replies
View Related
