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


ADVERTISEMENT

Does The Varray Or/and Nested Table Mechanism Exists In Sql Server

Jun 6, 2007

Hi.Like in subject. I know the varray and nested tables from oracle, and I'm trying to implement them in sql server. I've been googling for any information but with no result. Can somebody direct me ??Sorry for any english mistakesThanks for help 

View 2 Replies View Related

IF NOT EXISTS (... - EXISTS TABLE : Nested Iteration. Table Scan.Forward Scan.

Sep 20, 2006

Hi,

This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).

I'm looking at these new databases and I'm seeing code similar to this all over the place:

if not exists (select 1 from dbo.t1 where f1 = @p1)
begin
select @errno = @errno | 1
end

There's a unique clustered in dex on t1.f1.

The execution plan shows this for this statement:

FROM TABLE
dbo.t1
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.

It's not using my index!!!!!

It seems to be the case with EXISTS statements. Can anybody confirm?

I also hinted to use the index but it still didn't use it.

If the existence check really doesn't use the index, what's a good code alternative to this check?

I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.

SET ROWCOUNT 1
SELECT @cnt = (SELECT 1 FROM dbo.t1 (index ix01)
WHERE f1 = @p1
)
SET ROWCOUNT 0

Appreciate your help.

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

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

Help With NOT EXISTS Query

Jul 20, 2005

I am having trouble with what will surely be a simple query for you experts.I have 2 tables with inventory data.IMITMIDX contains the master item infoIMINVLOC contains location specific data such as quantity on hand at thatlocation.These tables have 2 commons fields, ITEM_NO and LOCI need to search the IMINVLOC table for any records where ITEM_NO and LOC donot match that in the IMITMIDX table.The following query give me zero records even though I can manually findsome records:SELECT *FROM IMINVLOC_SQL INNER JOINIMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_nowhere not exists (select loc from iminvloc_sql where IMITMIDX_SQL.loc =IMINVLOC_SQL.loc)Any ideas?Thanks.

View 5 Replies View Related

NOT EXISTS Query

Dec 6, 2007

I have a NOT EXISTS Query that works, but I have been giving a new requirement from MGMT. They initially wanted to know if a record existed in the system. Now they want to know if one exists over certain time periods (i.e. last 24 hours, 48 hours, etc...)



The System:



This is an ASP.NET application that uses table1 and table2 as list items in a drop down list. Once a record is created in the system it puts a time date stamp on the record along with other fields. For reporting I only care about reporting on two fields. This will be used to show activity in the system. MGMT expects at bare minimum 3 entries per 24 hour period.





SELECT table1.Department, Table2.field2
FROM table1 INNER JOIN
Table2 ON Table1.DeptID = Table2.DeptID
WHERE (NOT EXISTS
(SELECT Table3.Department, Table3.Field2
FROM Table3
WHERE Table1.Department = Table3.Department AND Table2.Field2 = Table3.Field2))



The above returns records where there are no entries in Table3 for table1.Department AND Table2.field2. I now have the requirement to do the same thing, but also to include records where table3.insert_date was in the last 24 hours, 48 hours, etc...



I have tried adding:

Table3.insert_date >= GetDate() - 1)



but it still only returns records with no entry at all.



SELECT table1.Department, Table2.field2
FROM table1 INNER JOIN
Table2 ON Table1.DeptID = Table2.DeptID
WHERE (NOT EXISTS
(SELECT Table3.Department, Table3.Field2
FROM Table3
WHERE Table1.Department = Table3.Department AND Table2.Field2 = Table3.Field2 AND Table3.insert_date >= GetDate() - 1))



I have moved it all over the place in this query changed AND to OR and cannot get the desired result.

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

MS Sql Query Problem Mit Not Exists , Help

May 9, 2007

Hello,
i hope that somebody can help me. I have a problem with ms sql query.
i have two tabels(wtmenSends and T1) in a database. i need pick up all email addresses from table wtmenSends but without those email addresses which are in table T1.
I have written this sql query to get these Emails, but i get nothing by this query. Can somebody tell me, where is the problem? Thanks. 
select wtmenSends.Email
from wtmenSends
where not exists(
select T1.*
from(
SELECT wtmenSends.Id, wtmenSends.Email, wtmenSends.IDMailing, wtmenSends.Title, wtmenSends.Firstname, wtmenSends.Lastname, wtmenSends.IDUser,
wtmenSends.IdStatus, wtmenSends.IsSent, wtmenSends.DateSent, wtmenSends.wtobjIDClass, wtmenSends.wtobjDateCreated,
wtmenSends.wtobjDateChanged, wtmenSends.wtobjUserCreated, wtmenSends.wtobjUserChanged
FROM wtmenRobinsons INNER JOIN
wtmenSends ON wtmenSends.Email NOT LIKE '%' + wtmenRobinsons.Filter AND wtmenRobinsons.IsDomain = 1) as T1 inner join
wtmenRobinsons on wtmenRobinsons.Filter = T1.Email)

View 2 Replies View Related

Help Replacing 'where Not Exists' In A Query

Jul 20, 2007

I am trying to clean up an ugly query that's based on trying to find items that exist in one table but not the other.
My tables are like this:
ITEMSitemID,itemName,itemDescriptionetc...
ORDERITEMSOrderLineID,OrderID,itemID
ORDERSOrderID,OrderCompleted
Currently my query looks something like this:
Select Items.* from ITEMS where not exists (select 1 from ORDERITEMS inner join ORDERS on ORDERITEMS.OrderID = ORDERS.OrderID where ORDERITEMS.itemID=ITEMS.itemID and (ORDERS.OrderCompleted=1))
So this query is looking for ITEMS what don't have a corresponding entry in the ORDERITEMS table.  As I understand it this is pretty inefficient as it is going to be executing the sub query in the Not Exists statement for each entry in the ITEMS table.  Is the preferred method to do something along the lines of somehow making the sub query into a derived table and doing a left or right join?
Thanks for reading!
Ryan

View 7 Replies View Related

Exists Query With Priorities

Jul 23, 2005

I've been presented with a task to do a query similar to the followingand I was curious as to what the quickest query would look like.Anyone have any ideas??Some_Id Value1 A1 B1 C2 C2 A2 B3 B3 C4 C5 Q5 C5 R6 T7 P7 BThe problem is that I want to select one record for each ID. If arecord with the value of 'A' exists, then I want to select that recordfor that ID. If not, I want to select the record with the value 'B'for that ID if it exists. Otherwise, just give me the first record forthat ID that exists. The result set would look like this:Some_ID Value1 A2 A3 B4 C5 Q6 T7 BThanks for your input!

View 12 Replies View Related

Using EXISTS With Dynamic Query

Sep 21, 2007

In a SP I need to know if certain records already exist
but the query is parameter dependent so I can't code
IF EXISTS (SELECT ...)
because the proper select must be calculated.
Using
EXEC (@CalculatedQuery)
IF @@ROWCOUNT = 0
Puts the results of @CalculatedQuery into my SP result set.
This is highly undesireable.

View 5 Replies View Related

Transact SQL :: If Exists In Query

Nov 18, 2015

I am using SQL Server 2008 - and what I want to do is set my variable @dh.  If the @startDate and @endDate falls into the criteria for my if exists statement, I want to set @dh equal to datediff(h, logontime, logofftime) BUT if that criteria is not true, I want to set @dh = 24.  How can I do that?

Declare @startDate date, @endDate date, @employeeID varchar(100), @userid varchar(100), @dh int

Set @startDate = '11/09/2015'
Set @endDate = '11/14/2015'
Set @employeeID = 'ab12345'
Set @userid = '162489'

[Code] .....

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

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

Nested Scalar Query Problem

Jan 31, 2008



I'm having a trouble with a nested scalar query

I have a table that refers to itself, and I need to loop through it to get the total number of entries in the structure with an object_type of 40 this is the function I came up with to do this. The problem is that the nested function doesn't seem to get called.




Code Snippet
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections.Generic;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
private struct PerformanceCriteriaCounter
{
public PerformanceCriteriaCounter(int id, string objecttype)
{
Id = id;
Object_Type = objecttype;
}
public int Id;
public string Object_Type;
}

[Microsoft.SqlServer.Server.SqlProcedure(Name = "usp_PerformanceCriteriaCounter")]
public static int CountPerformanceCriteria(int ParentID, int SourcePosition)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
int Counter = 0;
int Counter2 = 0;
SqlContext.Pipe.Send("Called with ParentID=" + ParentID + " SourcePosition=" + SourcePosition);
conn.Open();
using (SqlCommand myCommand = conn.CreateCommand())
{
myCommand.CommandText = @"SELECT CompetenceLearningObject.Object_Type, CompetenceLearningObject.LearningObject_ID
FROM CompetenceLearningObjectParent INNER JOIN
CompetenceLearningObject ON CompetenceLearningObjectParent.LearningObject_ID = CompetenceLearningObject.LearningObject_ID
WHERE (CompetenceLearningObjectParent.Approved = 1) AND (CompetenceLearningObject.Archived = 0 OR
CompetenceLearningObject.Archived IS NULL) AND (CompetenceLearningObject.Approved_Status = 1) AND
(CompetenceLearningObjectParent.Parent_ID = @ParentID) AND (CompetenceLearningObjectParent.Position_ID = @SourcePosition)";
myCommand.Parameters.AddWithValue("@ParentID", ParentID);
myCommand.Parameters.AddWithValue("@SourcePosition", SourcePosition);
List<PerformanceCriteriaCounter> loList = new List<PerformanceCriteriaCounter>();
SqlDataReader reader = myCommand.ExecuteReader();
while (reader.Read())
{
SqlContext.Pipe.Send("Adding item");
loList.Add(new PerformanceCriteriaCounter(int.Parse(reader["LearningObject_ID"].ToString()), reader["Object_Type"].ToString()));
}
reader.Close();

foreach (PerformanceCriteriaCounter lo in loList)
{
SqlContext.Pipe.Send("Testing item, ObjectType =" + lo.Object_Type);
if (lo.Object_Type == "40")
{
SqlContext.Pipe.Send("Incrementing counter");
Counter++;
}
else
{
SqlContext.Pipe.Send("Calling procedure with ParentID=" + lo.Id + " and Position=" + SourcePosition);
using (SqlCommand myCommand3 = conn.CreateCommand())
{
myCommand3.CommandType = CommandType.StoredProcedure;
myCommand3.CommandText = "usp_PerformanceCriteriaCounter";
myCommand3.Parameters.AddWithValue("@ParentID", lo.Id);
myCommand3.Parameters.AddWithValue("@SourcePosition", SourcePosition);
object objValue = myCommand3.ExecuteScalar();
if (objValue != DBNull.Value)
{
Counter2 = Convert.ToInt32(objValue);
SqlContext.Pipe.Send("It's not null value=" + Counter2.ToString());
Counter = Counter + Counter2;
}
}
}
}
}
return Counter;
}
}
}






This is the output from SQL Server when I call the function.



Code Snippet
Called with ParentID=1 SourcePosition=89
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Testing item, ObjectType =30
Calling procedure with ParentID=2 and Position=89
It's not null value=0
Testing item, ObjectType =30
Calling procedure with ParentID=3 and Position=89
It's not null value=0
Testing item, ObjectType =30
Calling procedure with ParentID=4 and Position=89
It's not null value=0
Testing item, ObjectType =30
Calling procedure with ParentID=5 and Position=89
It's not null value=0
Testing item, ObjectType =30
Calling procedure with ParentID=6 and Position=89
It's not null value=0
Testing item, ObjectType =30
Calling procedure with ParentID=7 and Position=89
It's not null value=0
Testing item, ObjectType =30
Calling procedure with ParentID=8 and Position=89
It's not null value=0
Testing item, ObjectType =20
Calling procedure with ParentID=9 and Position=89
It's not null value=0
Testing item, ObjectType =20
Calling procedure with ParentID=10 and Position=89
It's not null value=0
Testing item, ObjectType =20
Calling procedure with ParentID=11 and Position=89
It's not null value=0
Testing item, ObjectType =20
Calling procedure with ParentID=12 and Position=89
It's not null value=0
Testing item, ObjectType =20
Calling procedure with ParentID=13 and Position=89
It's not null value=0
Testing item, ObjectType =20
Calling procedure with ParentID=14 and Position=89
It's not null value=0





You can see it adding and checking the values and attempting to call itself, but it never seems to get re-called, any ideas?

Cheers

Jon

View 3 Replies View Related

Problem With WHERE On ROW_NUMBER When Using Nested Query

Sep 11, 2007

Hi,

I am trying to limit a result set by ROW_NUMBER. However, I am having problems getting it working.

The following query works fine, and I get a result set with PollID, AddedDate and RowNum columns.


SELECT *, ROW_NUMBER() OVER (ORDER BY Results.AddedDate DESC) AS RowNum FROM



( SELECT DISTINCT p.PollID, p.AddedDate

FROM vw_vs_PollsWithVoteCount p

JOIN vs_PollOptions o ON p.PollID = o.PollID

) AS Results

However, as soon as I add a WHERE condition:


SELECT *, ROW_NUMBER() OVER (ORDER BY Results.AddedDate DESC) AS RowNum FROM



( SELECT DISTINCT p.PollID, p.AddedDate

FROM vw_vs_PollsWithVoteCount p

JOIN vs_PollOptions o ON p.PollID = o.PollID

) AS Results

WHERE RowNum BETWEEN 1 AND 10


The query fails with an ' Invalid column name 'RowNum' ' error.

I have tried using 'Results.RowNum' but I get the same problem.


I don't understand what the issue is. The result set has a column headed 'RowNum' so why can't I apply a WHERE clause to this column? I can apply WHERE to the PollID column, for example, with no problem.

Any help very much appreciated.

Thanks...

View 7 Replies View Related

IF EXISTS SQL Question - Using Query Analyzer

Aug 3, 2007

I want to only go after Distinct email addressess that contain an @ symbol.
But then I want to return all fields. How do you correctly do that for Microsoft SQL Server?IF EXISTS (SELECT DISTINCT user_username FROM usr WHERE(user_username LIKE N'%@%'))

SELECT *
FROM usr Order By user_username 

View 2 Replies View Related

Checking Whether Record ID Exists In Another Query

Jan 10, 2014

I'm trying to check which price grids are in use using the price grid_id, and seeing whether this grid_id exists in another query that checks all active contracts. If the grid_id is present (active) I want to return 'Yes', if not I want it to return 'No'.

There are 385 price grids, but my query is only returning the 315 that are active, and ignoring any that are not used. My code is below, how I can see all the records whether Yes or No:

Select distinct
pg.grid_id [Price Grid],
pg.grid_name [Grid Name],
case when exists
(Select
c.grid_id from
customers c
inner join deltickhdr dh on dh.acct = c.custnum and dh.stage <5
where
c.type = 'C' and
dh.dticket is not null) then 'Yes' else 'No' end [Active]

From gridhdr pg inner join customers c on c.grid_id = pg.grid_id

Order by pg.grid_id

View 4 Replies View Related

Table Update With Count In Nested Query

Oct 8, 2012

I have added some SQL to an Access form which updates the dbo_BM_Map table when the user hits the Apply button. There is a temp table with various fields, two being "Chapter_No" and "Initial_Mapping_Complete" which the update is based on.

I want this update to only apply to chapters that only have one name in the "Initial_Mapping_Complete" column. If a chapter has more than one then the update should ignore it. The attached screengrab shows you. The update should ignore chapter 19 as there are two people (Jim and James) in the Initial_Mapping_Complete field. Here is my code.

pdate dbo_BM_Map inner Join Temp_Progression_Populate
on dbo_BM_Map.Product_ID = Temp_Progression_Populate.Product_ID
Set dbo_BM_Map.Initial_Mapping_Complete = Temp_Progression_Populate.Initial_Mapping_Complete
Where dbo_BM_Map.Chapter_No = Temp_Progression_Populate.Chapter_No
And Temp_Progression_Populate.Initial_Mapping_Complete in
(Select count(Initial_Mapping_Complete), Chapter_No
from Temp_Progression_Populate
Group by Chapter_No
Having Count(Initial_Mapping_Complete) = 1)

View 2 Replies View Related







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