Multiple Level Nested Corelated Query

Sep 30, 2005

My multiple level nested corelated query is not fetching correct
result. It work fine on small set of data, but fails on larger set of
data. Any clue?

Explaining data storing and discussing design would be tough for me
here, still to show you how complex I have created my life, here is the
query:

select
(
SELECT Top 1 RowNSBranchID FROM AssoExtBranchToNSBranchMstM AM
-- MMM
WHERE AM.RowExtSysID IN
(
SELECT RowID FROM ExternalSystemMstM WHERE ExtSysID =
(
SELECT ExtSysID FROM ExternalSystemMstM WHERE SF = 'Active' AND
RowID =
(
SELECT MAX(RowID) FROM ExternalSystemMstM WHERE MCStatus = 2 AND
ExtSysCode = UM.SystemCode
)
)
)
AND RowExtBranchID IN
(
SELECT RowID FROM ExternalBranchMstM
WHERE ExtBranchID =
(
SELECT ExtBranchID FROM ExternalBranchMstM
WHERE ROWID =
(
SELECT RowID FROM ExternalBranchMstM
WHERE ROWID =
(
SELECT MAX(ROWID) FROM ExternalBranchMstM WHERE MCStatus = 2 AND
ExtBranchCode = UM.UpBranchCode
AND RowExtSysID IN
(
SELECT RowID FROM ExternalSystemMstM WHERE ExtSysID =
(
SELECT ExtSysID FROM ExternalSystemMstM WHERE SF = 'Active' AND
RowID =
(
SELECT MAX(RowID) FROM ExternalSystemMstM WHERE MCStatus = 2
AND ExtSysCode = UM.SystemCode
)
)
)
)
AND (SF = 'Active')
)
)
)
AND AM.SF = 'Active'
order by AssoID desc,TrackID desc
) nsbranchid, UM.*

from
TmpInProcessData062005MstM UM

View 5 Replies


ADVERTISEMENT

Multiple Counts In One Nested Query: How?

May 12, 2008

I'd appreciate some help with the issue below - my SQL is a bit rusty and was never that hot to be frank. I'm using SQL Server 2000 (although have a test box with 2005 Express also). I've trawled MSDN and a few forums but can't find the solution (maybe I don't know what I'mm looking for!), so any help would be marvellous...

I have a table with a field called 'IRV' containing a string of comma-separated values. I want to be able to query a point in that string and count the number of times a given value appears. So...as an example, I want to count how many times '1' appears at position 7 in the IRV. I can create SQL to do this as follows:


SELECT COUNT(X) AS is1
FROM myIRVtable
WHERE (SUBSTRING(IRV, 7, 1) = '1')


So far so good. However, it is also possible that the value at position 7 in this string could be '2' (or '3', or '4', etc) - and rather than re-running the query again and again to get these values, I'd like to do it in one hit.

How can I combine all this together - anyone have any brilliant solutions?

View 4 Replies View Related

Case Expressions May Only Be Nested To Level 10.

Mar 11, 2008



Hi,

I get the following issue:

Server: Msg 125, Level 15, State 4, Line 16
Case expressions may only be nested to level 10.


I need to create a stored procedure that looks at two fields to generate a rating depending on each value. I have tried to use a case statement but am restricted to a certain number of nesting levels. Is there a way around this?




Code Snippetselect answerID,ram_severity, ram_probability,
case when ram_severity='0' and ram_probability='A' then 10
else case when ram_severity='0' and ram_probability='B' then 10
else case when ram_severity='0' and ram_probability='C' then 10
else case when ram_severity='0' and ram_probability='D' then 10
else case when ram_severity='0' and ram_probability='E' then 10
else case when ram_severity='1' and ram_probability='A' then 10
else case when ram_severity='1' and ram_probability='B' then 10
else case when ram_severity='1' and ram_probability='C' then 10
else case when ram_severity='1' and ram_probability='D' then 10
else case when ram_severity='1' and ram_probability='E' then 10
else case when ram_severity='2' and ram_probability='A' then 10
else case when ram_severity='2' and ram_probability='B' then 10
else case when ram_severity='2' and ram_probability='C' then 10
else case when ram_severity='2' and ram_probability='D' then 100
else case when ram_severity='2' and ram_probability='E' then 100
else case when ram_severity='3' and ram_probability='A' then 10
else case when ram_severity='3' and ram_probability='B' then 10
else case when ram_severity='3' and ram_probability='C' then 100
else case when ram_severity='3' and ram_probability='D' then 100
else case when ram_severity='3' and ram_probability='E' then 1000
else case when ram_severity='4' and ram_probability='A' then 10
else case when ram_severity='4' and ram_probability='B' then 100
else case when ram_severity='4' and ram_probability='C' then 100
else case when ram_severity='4' and ram_probability='D' then 1000
else case when ram_severity='4' and ram_probability='E' then 1000
else case when ram_severity='5' and ram_probability='A' then 100
else case when ram_severity='5' and ram_probability='B' then 100
else case when ram_severity='5' and ram_probability='C' then 1000
else case when ram_severity='5' and ram_probability='D' then 1000
else case when ram_severity='5' and ram_probability='E' then 1000
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
end
as rating
from table ua




Im sure there must be a way I just cant find it yet. Many thanks in advance

View 5 Replies View Related

Transact SQL :: Case Expressions May Only Be Nested To Level 10

Aug 5, 2015

I have a query which works absolutely fine when connecting to an actual server:

WITH CLAIMDATA AS(
SELECT DISTINCT
DB_NAME() AS DBName,
'UA' AS Client,
POLICY AS KMPONO,

[code]...

If i change the connector to REPLPROD (which is a linked server): From REPLPROD.XUNMDTAUAI.dbo.UPPOREP UP INNER JOIN REPLPROD.XUNMDTAUAI.dbo.UKKMREP UK

I get the error:

Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 125, Level 15, State 4, Line 1

Case expressions may only be nested to level 10.

View 7 Replies View Related

Stored Proc - Output Parameter Does Not Work With Nested Level

Apr 26, 2004

Anyone can help with this question: thanks

in a asp .net application, I call a stored procedure which have a output parameter.
the output parameter works find in sql session, but not in the asp .net application.

if I put select msg_out = "error message" in position A(see below for stored proc), it works fine
if I put them inside the if statement, the output parameter wont work in asp .net application, but fine in SQL session
The stored proc was created like this:

Create procedure XXXXXXX
(@msg_out varchar(80) OUTPUT
)
as
begin

while exists (*******)
begin
//position A
if certain condition
begin

select msg_out = "error message"
return 1
end

end


end


end

It seems to me that anything inside if - the second begin...end - it wont get executed.

Anyone has got a clue

Any help much appreciated!

View 5 Replies View Related

How To Work With Corelated Queries

Feb 4, 2008

hi all,
can plz any one let me to understand the fllow of following query

Use of corelated query To find the nth highest salary:-

Select Salary from Table1 T1 where N =
(Select Count(Distinct(Salary)) From Table1 T2 where
T2.Salary >= T1.Salary)

Where N is the desired no for which highest salary to be fetched.

where Table1 is

Name| Salary
asdf| 12345
qwe | 2351
sdf | 5684
gtr | 3210
mnnb| 321

I am clear of the funda that first outer query will be executed then Inner query ..
but how the comparison is going that is not clear to me plz if any one can help me to understand that.....


Rahul Arora
07 Batch
NCCE Israna,


######################
IMPOSSIBLE = I+M+POSSIBLE

View 8 Replies View Related

How Do I Avoid This Corelated Subquery?

Jul 20, 2005

Hello,I have a situation which would essentially use a co-related subquery.I am trying to avoid using a co-related subquery due to its slowperformanc and use a join statement instead.Here is what I am trying to do:Tables:========Limit-------RelID ProdID Days Amt1 Amt2----- ------ ---- ---- ------148 2 30 15000 30000148 2 7 1000 2500Temp Limits-----------RelID ProdID MaxDays Amt1 Amt2----- ------ ----- ------ ------148217500 10001482717500 5001482302500 200014829025000 75014821807500 -300I want the totals of Amt1 and Amt2 in the TempLimits table added toeach row in the Limits table where the Days in the Limits Table >=MaxDays in TempLimitsSo, when I run the query with the above dataI need to getRelID ProdID Days Amt1 Amt2----- ------ ---- ------ ------148 2 30 42500 (15000+7500+17500+2500) 33500(30000+1000+500+2000)148 2 7 26000 (1000+7500+17500) 4000(2500+1000+500)************************************************** **************************Here is the query that I have right now...update Limitset Amt1 = isnull(Amt1,0) + IsNull(temptable.temp_Amt1, 0), Amt2 = isnull(Amt2,0) + IsNull(temptable.temp_Amt2, 0)from Limit inner join(select Templimits.relationship_id relationship_id,Templimits.product_id product_id, sum(Templimits.Amt1) temp_Amt1,sum(Templimits.Amt2) temp_Amt2from TempLimitsWHERE TEMPLIMITS.MAXDAYS <= LIMIT.DAYSgroup by limit.relationship_id, limit.product_id) temptableon Limit.relid = temptable.relid andLimit.prodid = temptable.prodid************************************************** ****************************As you would see here, I am trying to use the condition for Days(listed in the query in CAPS) inside the inner query which would notwork... Is there any way I can do this apart from using temp tablesand co-related subqueries?Thanks in advanceKannan

View 7 Replies View Related

Multiple Nested (TOP 1 SELECT) S

Jun 1, 2006

Hi guys,

I have been struggling over the following problem for a few days... i was wondering if anyone could shed some light...!

I have the following query:

SELECT Field1, Field2
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
WHERE Table2.Field1 = ( SELECT TOP 1 Field1 FROM Table2 WHERE Field3='X' ORDER BY Date1)
AND Table2.Field2 = ( SELECT TOP 1 Field2 FROM Table2 WHERE Field3='X' ORDER BY Date1)

Is there a better way to do this. I was thinking of something very similar to the below query (Which doesnt work):

SELECT *
FROM Table1 A
INNER JOIN Table2 B ON (A.ID=B.ID)
INNER JOIN ( SELECT TOP 1 * FROM Table2 WHERE Field3='X' ORDER BY Date1 ) C ON (A.ID=C.ID)
WHERE
B.Field1 = C.Field1
B.Field2 = C.Field1

Any ideas?

Many thanks in advance,

TNT

View 2 Replies View Related

Nested REPLACE Statements Using Multiple Columns

Jun 20, 2007

I need to pass 3 column values and one Formula string into 4 replace statements and output the result in one column.

Nesting them in the usual way doesn't seem to work as that only allows for one column.

My table consits of four columns...PF (numeric), Hours (numeric), TotalNumber INT, and Formula (nvatchar)

My function needs to search and replace the Formula column for instances of all the three number columns and output the formula as a mathmatical formula rather than a string.

Here is what I have so far which works fine if all three columns have a value, but if only one is null then it will retrun NULL and not the other two values.

FUNCTION GetFormula
(@numPF NUMERIC(10,2), @numHours NUMERIC(10,2), @intTotalNumber INT, @strFormula nvarChar(200)) RETURNS nvarchar(200)
AS
BEGIN
DECLARE @strExpression nvarchar(200)

SELECT @strExpression=REPLACE(@strFormula, 'TotalNumber',@intTotalNumber)
SELECT @strExpression=REPLACE(@strExpression, 'PF',@numPF )
SELECT @strExpression=REPLACE(@strExpression, 'Hours',@numHours )
RETURN @strExpression
END


Many Thanks

View 3 Replies View Related

SQL Server 2012 :: Structure For Multiple Nested Queries

Feb 27, 2014

I am using Server 2012 and very new to SQL. I have a request from a physician for a list of his patients that meet a criteria. This is stored in a temp table names #cohort.

Using this cohort he wants each row to be one patient with a list of labs, vitals, etc. Three items are the most recent lab value and date. I could query each lab individually and place it into a temp table and then join all temp tables at the end, but I am trying to move past that and have all labs in one temp table. All temp tables are joined with PatientSID.

I tried to do something for just 2 labs, but it is not working. There could be nulls values when joined with the #cohort table.

Individually the SELECT statements pull in the most recent lab value and date, but I cannot get them into a temp table with one row of PatientSID and then the lab value and date if they exist.

IF OBJECT_ID ('TEMPDB..#lab') IS NOT NULL DROP TABLE #lab
SELECT
cohort.PatientSID
,SubQuery1.LabChemResultNumericValueAS 'A1c%'
,SubQuery1.LabChemCompleteDateTimeAS 'A1c% Date'
,SubQuery2.LabChemResultNumericValueAS 'LDL'

[Code] .....

View 1 Replies View Related

Nested Joins - Joining One Table To Another Multiple Times

Jan 9, 2006

Hi,I'm having problems constructing a nested join. It's quite complex, sohere's a simplfied example of the problem. Any thoughts on what I'mdoig wrong - or if I've got the whole approach wrong are welcome.I've two tables :-one is a contact table contacting name, addresses etc. Three of thefields represent users - 'created by', 'last modified by' and 'owner'.They contain usernames - eg. JDOE, BSMITH etc.The other table contants usernames and new ID codes.What I want to do is create a new dataset by joining the contacts tablewith the user table on all three fields - so the new dataset containsthe ids for the creator, last modifier and owner.I've tried things similar to:select c.*, u1.id, u2,id, u3.idfrom contact cleft outer join users u1left outer join users u2left outer join users u3on (u3.username = c.owner)on (u2.username = c.modified)on (u1.username = c.creator )But it compains that"The column prefix 'c' does not match with a table name or alias nameused in the query."The problem is referencing c (contact) through the whole set of joins.I would like to do this in some similar format as the query is within acursor and post-processing would be very long-winded.Thanks

View 1 Replies View Related

Can A Calc'd Query Column Be Compared Against A Multi Value Variable Without A Nested Query?

Nov 15, 2007

do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like

SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name
FROM...
WHERE ... and calcd name in (@variable)

View 1 Replies View Related

Row Level Security Across Multiple Columns

Nov 5, 2005

I need to secure a datawarehouse table at the row level based on 1 tomany keys on that table. A user should only see the rows they haveaccess to. I need to be able to figure out which rows they have accessto using a single sql statement and it cannot be dynamic SQL or astored procedure (this is a limitation based on the reporting tool weuse).The conditions can be any combination of "and" and "or" operators. Ihave seen posts (and actually have three of his books) by Joe celkodescribing disjunctive canonical form. If not familiar please clickthe link below.http://groups.google.com/group/comp...db6abcfa6209d2fA rules table in disjunctive canonical form looks like it mightpotentially be a solution, but I can't figure out how to use it acrossmultiple keys.Here ia an example of what I am trying to do including current tablestructures and sample data. I cannot really change the generic_facttable schema, but the security cross reference table can be modified oraditional tables added.CREATE TABLE generic_fact (generic_fact_key int NOT NULL ,salesrep_key int NOT NULL ,product_key int NOT NULL ,customer_key int NOT NULL ,sales_amount decimal(18, 0) NOT NULL ,CONSTRAINT PK_generic_fact PRIMARY KEY(generic_fact_key))GOinsert into generic_fact values(1,1,10,20,45)insert into generic_fact values(2,1,10,21,90)insert into generic_fact values(3,1,11,22,17)insert into generic_fact values(4,2,10,20,32)insert into generic_fact values(5,2,13,25,6)insert into generic_fact values(6,2,12,24,56)insert into generic_fact values(7,3,11,34,75)CREATE TABLE security_cross_reference (cross_ref_key int NOT NULL ,user_key int NOT NULL ,security_type varchar (50) NOT NULL ,security_value int NOT NULL ,security_operator varchar (20) NOT NULL ,CONSTRAINT PK_security_cross_reference PRIMARY KEY(cross_ref_key))GOinsert into security_cross_reference values(1,1,'product',10,'or')insert into security_cross_reference values(2,1,'customer',21,'or')insert into security_cross_reference values(3,2,'customer',20,'and')insert into security_cross_reference values(4,3,'salesrep',3,'and')insert into security_cross_reference values(5,4,'salesrep',1,'and')insert into security_cross_reference values(6,4,'customer',22,'and')insert into security_cross_reference values(7,4,'product',11,'and')So based on the data in the security cross reference tableUser 1 can see sales for customer 21 or product 10(generic_fact table rows that have generic_fact_key values of 1,2,and4)User 2 can see sales for customer 20(generic_fact table rows that have generic_fact_key values of 1 and 4)User 3 can see sales for salesrep 3(generic_fact table rows that have a generic_fact_key value of 7)User 4 can see sales for salesrep 1 and product 11 and customer 22(generic_fact table rows that have a generic_fact_key value of 3)Does anyone have any ideas on the best way to do this?

View 3 Replies View Related

T-SQL (SS2K8) :: Nested Stored Procedure - Multiple Results Of Record Sets Coming

Oct 1, 2014

I am calling stored procedure called GetCommonItemCount within another stored procedure called CheckBoxAvailability, the first stored procedure should return a count to second stored procedure and based on that some logic will be executed.

I have 2 problems in that

1. The result is not coming from first stored so the variable called @Cnt is always 0 although it should be 18
2. At the end i need to see in the output the result from second stored procedure only while now i am seeing multiple results of record sets coming.

I have attached the scripts also, the line i described in step1 is

View 9 Replies View Related

SQL Server 2012 :: Multiple Level Join On Same Table?

Oct 10, 2015

i have below queries each select is fetching records at one level. Is there a way i can write single query to get to nth level (recursion) instead joining same table 10 times (i don't know in some cases there is may be next level) I stopped at 10th level now. In below example i gave only two levels.

SELECT Distinct
a.Col1 AS EmpID,
a.Col1 AS EmpID,
a.Col2 AS Emp_guid,
a.Col2 AS Emp_guid,
case

[code].....

View 1 Replies View Related

Schema-level Security For Multiple Users In One Database

Apr 17, 2007



My developers would like a 'sandbox' database with full ddl and dml permissions, however, they do not want others to read/change/drop their objects. With SQL 2005, can DDL permissions be granted to a user at the schema level? I'd rather not set up a database for each developer.

View 3 Replies View Related

Need Help With An SQL Nested Query

Sep 13, 2005

Hi,Please can somone help me with a nested SQL query.  I have two tables please see belowTable 1CallIDEmployeeIDCallSummaryCallStatusTable 2CallHistoryIDCallIDDataAddedCallActionI would like to return the CallID, EmployeeID, CallSummary and CallStatus from Table 1, and also display the last CallAction from Table 2.This is a helpdesk database so a Call will have many CallActions i.e. Open, Held, Assigned Internal.  How do I return the last CallAction Added against the selected CallID, I know I use the DateAdded but not sure about nested statements.The results I would like to return to the user would look like this:-Call ID: 1EmployeeID: 1Call Sumary: SQL ProblemCall Status: OpenCall Action (Last Action): Assigned Internal.

View 2 Replies View Related

NESTED QUERY

Oct 4, 1999

Hi,

I want to write one query which will select multiple distinct records from one table
For e:g
Lets say in a table i have 3 fields name,tel_no,sex
Now i want to list all the records which are distinct in each of these fields
like distinct name,distinct address

IS IT POSSIBLE and if yes HOW

Thanks

Ashish

View 1 Replies View Related

Help With Nested Query

Mar 2, 2004

I am having trouble with the following query.

Important Tables:
Product (table of products)
--ProductID
--ProductName

ProductCategories (Associates a Product with one or more categories)
--ProductID
--CategoryID

Category (table of categories that a product may fall under)
--CategoryID
--CategoryName

Information:

Basically I have a product that falls into two categories. Therefore there are two records in the ProcuctCategories Table. I am trying to create a query that will find all products that are in categories 1 & 2.

Attempted Solution:
SELECT * FROM Product
WHERE (ProductID IN (SELECT CategoryID FROM ProductCategories WHERE CategoryID =1))
AND
(ProductID IN (SELECT CategoryID FROM ProductCategories WHERE CategoryID =2))

This returned zero records though it should have returned the product that is in categories 1&2.

I would appreciate any help available.

Thank you,
-Patrick

View 2 Replies View Related

Help With Nested Query

Jul 20, 2005

HiI have 2 tables. The first has employee information and the second haspayroll information. I need to find out people who are not in thepayroll but in the employee table.Since the payroll has multiple instances i have to filter it and findout for each payroll.I don't think i have explained it very well so here is the data set.hope someone can help me with this.Thanks in advancepritTbl EmployeePlanIDSSN100111111111110012222222221001333333333TblPayrolldetailIDNumPlanID SSN11001111111111110012222222222100122222222221001333333333Required RESULT required(Missing employees from payroll)IDNumSSN13333333332111111111

View 1 Replies View Related

Nested Repeater Query

Mar 11, 2007

Hello Everyone,I am trying to create a query for the purpose of a nested repeater relation. The information needs to be pulled from one table. I have shortened the columns to the ones that are required.table - PagesIDPageNameParentPageIDSo, take the following example:ID 14, PageName - Service A, ParentPage ID = 6ID 15, PageName - Service B, ParentPage ID = 6ID 36 PageName - Client 1, ParentPage ID = 14ID 37 PageName - Client 2, ParentPage ID = 14ID 38 PageName - Client 3, ParentPage ID = 15ID 39 PageName - Client 4, ParentPage ID = 15 So, I want to create a query that will get my nested repeater to display as follows:Service A    Client 1    Client 2Service B    Client 3    Client 4What I have come up with so far is:SELECT * from tbl_Pages WHERE ParentPageID IN  (Select ID From tbl_Pages)SELECT p.ParentPageID, p.PageName, p.ID FROM tbl_Pages pThe relation would be based off ParentPageID. I keep getting errors that either there is no unique value or the relation is null. What am I am missing here? 

View 5 Replies View Related

Nested Loop In SQL Query

Nov 1, 2005

Hi,I'm probably missing something obvious (either that or doing this totally wrong).I'm trying to use a nested loop to generate the following results:Unit          Day1         Day2           Day3        Day4          Day5Name1     25             45               89             54              76Name2     48             54               81             74              98What I have so far is this:WHILE @FCount < @TotalFoodUnitsBEGINSELECT (SELECT Unit FROM tbl_acc_FoodVenues WHERE UnitID = (@FCount + 1)) AS Unit  WHILE @FDCount < @Days BEGIN SELECT  (SELECT FdRevenue_a FROM tbl_acc_aud_SportsAudits WHERE AudDate = DATEADD(day, @FDCount, @pdStartDate)) AS Rev  SET @FDCount = @FDCount + 1 END SET @FCount = @FCount + 1ENDAny suggestions please

View 3 Replies View Related

Nested Exists Query

Nov 15, 2001

I am trying to write a query that does not use inner joins to see if it returns rows faster than a query using inner joins.
My trouble is that I don't know how to write the syntax for the 3rd table that I am comparing against.
Can I nest Exists in a Where clause?
If so how is the 2nd exists statement added to the query?
see my sample query below
================================================== ===
'With 2 tables

SELECT DISTINCT s1.ID, s1.SITE
FROM SITE_TBL s1
WHERE EXISTS (SELECT *
FROM DEVICE_TBL d1
WHERE s1.ID = d1.SITE_ID AND d1.DELETEFLAG <> 'D')
ORDER BY SITE



'with 3 tables this doesn't work

SELECT DISTINCT s1.ID, s1.SITE
FROM SITE_TBL s1
WHERE EXISTS (SELECT *
FROM DEVICE_TBL d1
WHERE s1.ID = d1.SITE_ID AND d1.DELETEFLAG <> 'D')
AND
(SELECT *
FROM BIG_TBL b1
WHERE d1.fqdn = b1.xyz)
ORDER BY SITE

================================================== ===
Thanks for the help
Jim

View 1 Replies View Related

Nested Query Question

Aug 4, 2001

Hi, I have the following scenario that I am not sure how to best tackle. Any
advice or examples is appreciated.

I am creating a stored proc that requires a code to be passed to it. In
return data gathered from 3 different tables will be returned. The big catch
is that 1 of the tables resided in a differenct database.

So, here is the data layout.

Database 1, Table 1 contains the following fields: Job, CustID, ShipID, and
ShipMethod.

Database 2, Table 1 contains CustID, ShipID, Address, City, State, Zip,
etc...

Database 2, Table 2 contains CustID, CustomerName.

So the first question is how should the stored proc look with an input
parameter of "Job" and output of Job, CustID, ShipMethod, ShipID, Address,
City, State, Zip, and CustomerName?

Secondly, which database should the stored proc reside?

Again, any advise, suggestions, pointers, etc. are appreciated.

View 1 Replies View Related

Nested Query Troubles...

Jan 26, 2006

Hi All,

Can anybody please tell me if a query such as this (Valid in MS Access)
can work in SQL Server:


SELECT Description, Sum(Total) FROM (
SELECT Description, Total FROM Table_A
UNION ALL
SELECT Description, Total FROM Table_B
UNION ALL
SELECT Description, Total FROM Table_C
)
GROUP BY Description


The group of unions work by themselves, but when I try to nest an outer query to do some a Summation(), I have syntax errors.

Any insight would be greatly appreciated. Thank you.

View 2 Replies View Related

Nested Query (Urgent)

Feb 17, 2004

Folks

I have two queries

Select Account_Id , Branch_Cd from Accounts

SELECT SUM (dbo.HOLDING.Shares_Par_Value_Qty * dbo.ASSET.Current_Prc) AS MarketValue
FROM dbo.HOLDING INNER JOIN
dbo.ASSET ON dbo.HOLDING.Property_Num = dbo.ASSET.Property_Num
Group by dbo.HOLDING.Account_ID

Account_ID is the same in both the queries ie in both the tables
Holding and Account.


I need the output like this


Select Account_Id, Branch_Cd, MarketValue from -------



But MarketValue should be calculated exactly in the above method.
How do I combine these two queries. I need it asap.
Help me out.



Thanks

View 2 Replies View Related

Nested Query Or A Join?

Feb 8, 2007

I have a database that contains a PERSONNEL table, a VISIT table, and a STARSHIP table.
I am trying to generate a single column list of the personnel that are from Vulcan (PERSONNEL.PLANET) and all starships that have visited Vulcan (VISIT.PLANET). VISIT.SHIP and STARSHIP.REGISTRY columns contain the ships identifiers. How would I accomplish this? I am just beginning sql so please be nice ;)

View 1 Replies View Related

Nested Query Or Should I Use Over Partition Perhaps?

Nov 7, 2007

I have the following result set #1 from the query below. As you can see, there are four different provider roles and four different physicians listed for the same case (acctnum). Is there a way I could add a nested select (or other method) which would allow me to list this case as one line item to appear in the manner of result set #2?

RESULT SET #1 (the results of the query I have now)














MRN
ACCTNUM
PTNAME
AGE
ADMDT
DISCHDT
LOS
PROVIDER_CODE
PROVIDER_ROLE
PHYSNAME

12345
11111117777
DOE, JANE
48
Nov 29 2006
Nov 30 2006
1
10
ANE1
MILLER DR.

12345
11111117777
DOE, JANE
48
Nov 29 2006
Nov 30 2006
1
20
ADM
MAY DR.

12345
11111117777
DOE, JANE
48
Nov 29 2006
Nov 30 2006
1
30
ATT
SCHULTZ DR.

12345
11111117777
DOE, JANE
48
Nov 29 2006
Nov 30 2006
1
35
PRIN
THOMAS DR.

RESULT SET #2 (this is how I desire the results to look)














MRN
ACCTNUM
PTNAME
AGE
ADMDT
DISCHDT
LOS
ANE1PHYS
ADMPHYS
ATTPHYS
PRINPHYS

12345
11111117777
DOE, JANE
48
Dec 13 2006
Dec 14 2006
1
MILLER DR.
MAY DR.
SCHULTZ DR.
THOMAS DR.


Select

e.medrec_no,

e.account_number,

Isnull(ltrim(rtrim(pt.patient_lname)) + ', ' ,'')

+

Isnull(ltrim(rtrim(pt.patient_fname)) + ' ' ,'')

+

Isnull(ltrim(rtrim(pt.patient_mname)) + ' ','')

+

Isnull(ltrim(rtrim(pt.patient_sname)), '')

AS SRM_PatientName,

pm.PatientAge,

left(e.admission_date,11) as Admit_Date,

left(e.episode_date,11) as Disch_Date,

(CASE WHEN DATEDIFF(DAY, e.admission_date,e.episode_date) = 0 Then 1

ELSE DATEDIFF(DAY, e.admission_date,e.episode_date) END) AS LOS,

epi.PROVIDER_CODE,

epi.PROVIDER_ROLE,

pe.PERSON_NAME as physician_name,

From srm.episodes e inner join

dbo.PtMstr pm on pm.accountnumber=e.account_number inner join

srm.ITEM_HEADER ih ON ih.ITEM_KEY = e.EPISODE_KEY INNER JOIN

srm.PATIENTS pt ON pt.PATIENT_KEY = ih.LOGICAL_PARENT_KEY inner join

srm.CDMAB_PROV_EPI epi on epi.episode_key=e.episode_key inner join

srm.providers p on p.provider_key = epi.provider_key inner join

srm.person_element pe on pe.item_key = p.provider_key

Where e.episode_date is not null and pm.AnyProc like '%4495%'

View 7 Replies View Related

Nested Query Question

Mar 17, 2008

I guess I need help in understanding how to do the nested query option and see what works here and what doesn't.

Lets go with some psudo code

SELECT Col1, Col2, COUNT(*) AS Expr1 FROM Table1 INNER JOIN Table2 ON Table1.Col2 = Table2.Col1

Why doesn't the following work, is there a work around?

SELECT Col1, Col2, COUNT(*) AS Expr1, (SELECT COUNT(*) FROM Table2) As Expr2 FROM Table1 INNER JOIN Table2 ON Table1.Col2 = Table2.Col1

View 6 Replies View Related

Configuring SQLdataadapter With Nested Query.

Aug 14, 2007

 
How to configure sqldatadapter with query like
 "select name ,id from tlb1 where id in (select id from tlb2 where dept=@dept)"
   Is the nested subquery is not allowed while  configuring sqldaadapter?
  Swati
 
 

View 1 Replies View Related

How To Write A Nested GROUP BY Query, Please Help.

Jun 6, 2004

Use Pubs
SELECT pub_id, type, SUM(price) as Total_price
FROM titles
GROUP BY pub_id, type

The above query returns the following resultset:


0736business 2.9900
1389business 51.9300
0877mod_cook 22.9800
1389popular_comp42.9500
0736psychology 45.9300
0877psychology 21.5900
0877trad_cook 47.8900
0877UNDECIDED NULL


Now I want to add another "Group By" on type, so I tried:

Select type, sum(Total_Price) from
(SELECT pub_id, type, SUM(price) as Total_Price
FROM titles
GROUP BY pub_id, type)
Group By type

But I got error: Incorrect syntax near the keyword 'Group'. How can I write such a nested group by query.

Thanks in advance for any help.

View 5 Replies View Related

Nested Query With Distinct Function

Jan 12, 2015

I need to know how many widgets are located at each factory.

I have a table called "Widgets". The pertinent column(s) are:

Factory UID

By using only this table I can group the results by the FactoryUID to get the answer. However, this table does not tell me the factory name.

I have a table called "Factories". The pertinent column(s) are:

FactoryUID
FactoryName

I can join these two tables by the FactoryUID. But I don't know how to write this query so that my results will look like the following table:

FactoryName Widgets
Factory1 100
Factory2 200
Factory3 300

View 6 Replies View Related

Imitating Nested For Each Loop In SQL Query

May 25, 2007

Dear All,

I need to create a query to list all the subfolders within a folder.

I have a database table that lists the usual properties of each of the folder.

I have another database table that has two columns

1. Parent folder
2. Child folder

But this table maintains the parent child relationship only to one level.

For example if i have a folder X that has a subfolder Y and Z.
And Y has subfolders A and B.
and B has subfolder C and D
and C has subfolder E and F

The database table will look like

parentfolder child folder
X Y
X Z
Y A
Y B
B C
B D
C E
C F

I want to write a query which will take a folder name as the input and will provide me a list of all the folders and subfolders under it. The query should be based on the table (parent - child) and there should not be any restriction on the subfolder levels to search and report for.

I have been banging my head to do this but i have failed so far. Any help on this will be highly appreciated.

View 3 Replies View Related







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