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)
I have the following query and where I need to use the t_PrevSession.DischargeTime which is in the nested query that is bolded below. How do i bring it up to the main select statement?
SELECT
s.facilityid,
s.sessionid,
s.MRN,
[code]....
I am working with a table that has a column which stores multiple data/values that are comma separated.
I need to be able to query that table and get those rows where the values in that column match a pre-defined search list.
I was thinking of somehow trying to take the search list and convert it to a table(temp or a cte) and then JOIN to the table.
however, since the column may contain multiple values, i would need to parse/separate that first. I am not sure how to parse and then join to a list (if that is even the best way to solve this) to only get the rows where the search column contains one or more of the items we're looking for.
Below is some sample data:
Declare @BaseTable table (PKCol int, Column2Search varchar(2000))
Insert into @BaseTable (PKCol, Column2Search)
Select 1001, 'apple,orange,grapefruit'
UNION ALL
Select 1002, 'grapefruit,coconut'
UNION ALL
[Code] ....
Is it possible to use a column name variable in a Select Statement for a column name?For example I have a dropdown with FName,LName,Phone and a text box. The user can select the field to search and the criteria will go into the text box. The problem is the select doesn't like a variable for the field name. I have tried both a standard variable and a Case statement (see below). This is being used in a Stored Procedure with MSSQL. The actual select is much more complicated than this but it gets the point across. Thanks for your help in advance@Field as varchar( 50),@Value as varchar (50)SELECT *FROM customersWHERE @Field = @ValueORSELECT *FROM customersWHERE CASE WHEN @Field = 'Fname' THEN Fname = @Value END, CASE WHEN @Field = 'Lname' THEN Lname = @Value END, CASE WHEN @Field = 'Phone' THEN Phone = @Value END;
View 1 Replies View RelatedHi,
I have a report in SQL Reporting Services 2005 which calls a stored proc and the report takes a very long time to run and sometimes returns zero records. But when i run the stored proc in query analyzer it takes about 4 seconds!!
I have checked the execution log on the RS using the below sql:
Code Snippet
use ReportServer
Select * from ExecutionLog with (nolock) order by TimeStart DESC
It shows that i have a large amount of time for the dataretrieval (601309ms, about 10mins) and does not return any records most likely because of a query timeout:
TimeDataRetrieval TimeProcessing TimeRendering Source Status ByteCount RowCount
601309 2227 3 1 rsSuccess 4916 0
The weird thing is that when i run it in query analyzer, i get about 400 records in 4 seconds !!
I dont understand what RS is doing to take up so much time like this to retrieve data.
The report is very simple - it basically returns the records straight out into a table.
The only thing I somewhat suspected was a parameter data type conflict between RS and SQL, specifically dates. I have a start and end date parameter in the report - i tried specifying this as date and string to see if it made any difference but it didn't.
Any help would be greatly appreciated.
Hello All
I have the following problem running an sp with a table variable (sql server 2000) - the error which occurs at the end of the query is: "must declare the variable @THeader" . @THeader is the name of the variable table and the error occurs with such references as @THeader.ApplyAmt, @THeader.TransactionHeaderID, etc.
declare @THeader TABLE (
TransactionHeaderID [int] NOT NULL ,
PatientID [int] NOT NULL ,
TransactionAllocationAmount [money] NOT NULL ,
ApplyAmt [money] NULL ) - create table variable
insert into @THeader select TransactionHeaderID,PatientID,TransactionAllocationAmount,ApplyAmt from mtblTransactionHeader where PatientID = 9 - fill the table variable
UPDATE @THeader
set TransactionAllocationAmount =
(SELECT isnull(Sum(mtblTransactionAllocation.Amount),0)
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID or
mtblTransactionAllocation.CRID = TransactionHeaderID) from @THeader, mtblTransactionAllocation - do the updates on the table variable
Update @THeader
set ApplyAmt = (SELECT mtblTransactionAllocation.Amount
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID and
mtblTransactionAllocation.CRID = 187 and PatientID = 9) from @THeader, mtblTransactionAllocation - do the updates on the table variable
- below is where the problems occur. It occurs with statements referencing columns in the table variable, i.e. @THeader.ApplyAmt
UPDATE mtblTransactionHeader
SET mtblTransactionHeader.TransactionAllocationAmount = @THeader.TransactionAllocationAmount,
mtblTransactionHeader.ApplyAmt = @THeader.ApplyAmt
FROM @THeader, mtblTransactionHeader
WHERE @THeader.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID - put the values back into original table
Thanks in advance
smHaig
Hi,
I'm try to get the query second column value when it is assinged to a varchar variable.
Ex:In SP below is statement I wrote
SET @Values = (SELECT COL1,COL2 FROM TABLE)
Question 1: How to access the COL2 value from @Value2?
Question 2: How to access the other row values if above returns more than a row?
Please send me the solution as soon as possible.
Thanks-Vikash/Bala
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)
[code]...
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 RelatedHi,
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
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
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 RelatedHello 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 RelatedHi,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 RelatedI 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
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.
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.
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
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 ;)
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%'
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
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:
DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;
I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:
DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;
I have almost 50 references to these parameters in the query so a substitution would be helpful.
Dan
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
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.
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
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.
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?
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
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...
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)
Hi,
I have a following query:
select cu.Currencies_ShortName
from dbo.Currencies cu
inner join
(
select p1.Pairs_Shortname as f1, right(p1.Pairs_Shortname,3) as Currency1,
/*v1*/ -- max(SpotBid) as SpotBid, max(SpotAsk) as SpotAsk
/*v2*/ SpotBid, SpotAsk
from dbo.Pairs p1 inner join dbo.PairsQuotes pq1
on p1.Pairs_Id=pq1.Pairs_Id
and pq1.PriceDate=(select max(PriceDate) from dbo.PairsQuotes)
where p1.Pairs_Shortname like 'EUR%'
/*v1*/ -- group by p1.Pairs_Shortname, right(p1.Pairs_Shortname,3)
) as sr
on cu.Currencies_ShortName=sr.Currency1
which would work like /*v2*/, but not like /*v1*/ - there would be a message:
Server: Msg 8624, Level 16, State 3, Line 1
Internal SQL Server error.
Is Group By not manageable in a nested query, or is it some other problem?
thx
Katarina
My multiple level nested corelated query is not fetching correctresult. It work fine on small set of data, but fails on larger set ofdata. Any clue?Explaining data storing and discussing design would be tough for mehere, still to show you how complex I have created my life, here is thequery:select(SELECT Top 1 RowNSBranchID FROM AssoExtBranchToNSBranchMstM AM-- MMMWHERE AM.RowExtSysID IN(SELECT RowID FROM ExternalSystemMstM WHERE ExtSysID =(SELECT ExtSysID FROM ExternalSystemMstM WHERE SF = 'Active' ANDRowID =(SELECT MAX(RowID) FROM ExternalSystemMstM WHERE MCStatus = 2 ANDExtSysCode = UM.SystemCode)))AND RowExtBranchID IN(SELECT RowID FROM ExternalBranchMstMWHERE ExtBranchID =(SELECT ExtBranchID FROM ExternalBranchMstMWHERE ROWID =(SELECT RowID FROM ExternalBranchMstMWHERE ROWID =(SELECT MAX(ROWID) FROM ExternalBranchMstM WHERE MCStatus = 2 ANDExtBranchCode = UM.UpBranchCodeAND RowExtSysID IN(SELECT RowID FROM ExternalSystemMstM WHERE ExtSysID =(SELECT ExtSysID FROM ExternalSystemMstM WHERE SF = 'Active' ANDRowID =(SELECT MAX(RowID) FROM ExternalSystemMstM WHERE MCStatus = 2AND ExtSysCode = UM.SystemCode))))AND (SF = 'Active'))))AND AM.SF = 'Active'order by AssoID desc,TrackID desc) nsbranchid, UM.*fromTmpInProcessData062005MstM UM
View 5 Replies View Relatedhey there, i'm trying to move one record from one table to the next,so i'm doing this by doing an insert into table, then delete from theprevious table. Only thin g is on the insert i want to include aparameter. Can't work how to do this. Do you think i need to doanother update query and insert the parameter that way, after theinsert is done?ALTER PROCEDURE dbo.ReturnLoan(@strBarcode varchar(100),@strLibrary varchar(100),@dateReturned datetime)ASINSERT INTO Loan_History(Barcode,UserID,Date_Borrowed,Library)Select Barcode, UserID, Date_Borrowed, Library FROM Loans WHEREBarcode = @strBarcodeAND Library = @strLibrary;DELETE FROM LoansWHERE Barcode = @strBarcodeAND Library = @strLibrary ;RETURN
View 1 Replies View Related