Menu
Is there any way to get a column in real time, from a main query, and use it in a subquery?
Something like this: (Use A.item in the subquery)
SELECT item1, * FROM TableA A INNER JOIN ( select * from TableB B where A.item = B.item ) on A.x = B.x; Ok, here is the real thing:
I need to modify this existing query. It worked before, but now that the database changed, I need to do some modifications, add some comparisons. As you can see there are a lot of JOINS, and one of them is a subquery. I need to add a comparison from a column from the main query (from the table T0 for example) to the subquery (like this: T6.UnionAll_Empresa = T0.UnionALl_Empresa)
Select T0.UnionAll_Empresa,<STUFF> from [UNION_ALL_BASES]..OINV T0 with (nolock) inner join [UNION_ALL_BASES]..INV6 T1 with (nolock) on t0.DocEntry = t1.DocEntry and t0.UnionAll_Empresa = t1.UnionAll_Empresa inner join ( select t1.CompanyID, T2.CompanyDb, t1.OurNumber, T6.BankCode, T6.BankName, T3.[Description] Situation, T1.[Status], T5.Descrption nomeStatus, T1.Origin, T1.DocEntry, T1.DocType, T1.ControlKey, T1.CardCode, T4.[Description] ContractBank, T1.PayMethodCode, T1.DueDate, T1.DocDate, T1.InstallmentID, T1.InstallmentValue, T1.Correction, T1.InterestContractural, T1.FineContract, T1.ValueAbatment, T1.ValueDiscount, T1.ValueFineLate, T1.ValueInterestDaysOfLate, T1.OtherIncreases, T1.ValueInWords, T1.ValueDocument, T1.DigitalLine, T1.Document from [IntegrationBank]..BillOfExchange T1 with (nolock) inner join [InterCompany2]..CompanyHierarchy T2 with (nolock) on T1.CompanyID = T2.ID left join [IntegrationBank]..BillOfExchangeSituation T3 with (nolock) on T1.Situation = T3.ID inner join [IntegrationBank]..ContractBank T4 with (nolock) on T1.ContractBank = T4.ID inner join [IntegrationBank]..BoeStatus T5 with (nolock) on T1.[Status] = T5.ID inner join [UNION_ALL_BASES]..ODSC T6 with (nolock) on T4.BankKey = T6.AbsEntry and **T6.UnionAll_Empresa = T0.UnionALl_Empresa** --I need to do this where T1.[Status] <> 5 and T2.CompanyDb = **T0.UnionAll_Empresa** --I need to do this ) TBI on (T1.DocEntry = TBI.DocEntry and T1.InstlmntID = TBI.InstallmentID and TBI.DocType = T1.ObjType ) inner join [UNION_ALL_BASES]..OCTG T2 on T0.GroupNum = T2.GroupNum and T0.UnionAll_Empresa = T2.UnionAll_Empresa inner join [UNION_ALL_BASES]..OSLP T3 on T0.SlpCode = T3.SlpCode and T0.UnionAll_Empresa = T3.UnionAll_Empresa where not exists (select 1 from [UNION_ALL_BASES]..RIN1 A with (nolock) inner join [UNION_ALL_BASES]..ORIN B with (nolock) on A.DocEntry = B.DocEntry and A.UnionAll_Empresa = B.UnionAll_Empresa where A.BaseEntry = T0.DocEntry and B.SeqCode = ''1'' ) 
579
You may use the IN, ANY, or ALL operator in outer query to handle a subquery that returns multiple rows. Contents: Using IN operator with a Multiple Row Subquery. Using NOT IN operator with a Multiple Row Subquery.
A subquery cannot contain a BETWEEN or LIKE clause. A subquery cannot contain an ORDER BY clause. A subquery in an UPDATE statement cannot retrieve data from the same table in which data is to be updated. A subquery in a DELETE statement cannot retrieve data from the same table in which data is to be deleted.
¶ A CTE (common table expression) is a named subquery defined in a WITH clause. You can think of the CTE as a temporary view for use in the statement that defines the CTE. The CTE defines the temporary view's name, an optional list of column names, and a query expression (i.e. a SELECT statement).
You can user OUTER APPLY
SELECT * FROM tbl1 OUTER APPLY ( SELECT TOP 1 currency_id, SUM(taxrate) AS taxrate FROM tbl2 WHERE wuptr.currency_id = tbl1.currency_id GROUP BY tbl2.currencyid ) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
