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
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)
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 RelatedI 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
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 RelatedI have a question about what is possible with a prediction query
against a nested table. Say I have a basic customer-product case and nested table mining model like so:
Mining Model DT_CustProd
(
[Id] ,
[Gender] ,
[Age]
[Products] Predict
(
[ProductName] ,
[Quantity]
)
)
Using Microsoft_Decision_Trees
I can write a query to find the probability of product (and quantity) A like so:
SELECT (select * from Predict(Products,INCLUDE_STATISTICS)
where ProductName = 'A' )
FROM DT_CustProd
NATURAL PREDICTION JOIN
(SELECT 'M' AS [Gender],
27 AS [AGE] ) AS t
What if I know that the query customer (M,27) in question has purchased product B, how can I use that in the prediction join to predict product A? The fact that product B was purchased might influence the prediction, right?
I am using SQL Server 2000. I have a somewhat large query and hope someone could help me with it.
(Man, there needs to be a way to use colors...) The bolded parts of the query need to be replaced by the underlined part. I can't say 'b.HW' because of the scope of the inline query and that they are all on the same level. I've been told I need to change to a nested subquery, but can't for the life of me figure out how to do that. Can someone please show me?
The current query:
SELECT x.SSN,x.RealName,x.BudgetCode,x.TH,b.HW,x.HP,z.HL,x.NHW,x.FSLAOT,y.HHW AS AH,y.NHH,CASE WHEN x.TH < x.HP THEN 'XX' WHEN x.TH > x.HP THEN x.NHW - x.HP - y.NHH WHEN x.TH <= x.HP THEN 0 END AS SOT,CASE WHEN x.TH > x.HP THEN x.NHW - x.HP - y.NHH + x.FSLAOT WHEN x.TH <= x.HP THEN 0 END AS AO
FROM(SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS TH,CASE WHEN SUM(a.hours) > 40 THEN (SUM(a.hours) - 40) * 1.5 WHEN SUM(a.hours) <= 40 THEN 0 END AS FSLAOT,CASE WHEN SUM(a.hours) >= 40 THEN 40 WHEN SUM(a.hours) < 40 THEN SUM(a.hours) END AS NHW,32 AS HP
FROM dbo.ActivitiesInCurrentFiscalYear aWHERE a.ItemDate BETWEEN startdate and enddate AND a.ScheduleType = 1 AND a.EmployeeType = 2GROUP BY a.SSN, a.RealName, a.BudgetCode) x LEFT OUTER JOIN
(SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS HHW,CASE WHEN SUM(a.Hours) >= 8 THEN 8 WHEN SUM(a.Hours) < 8 THEN SUM(a.Hours) END AS NHH
FROM dbo.ActivitiesInCurrentFiscalYear a, dbo.Holidays hWHERE a.ItemDate = h.HolidayDate AND a.ItemDate BETWEEN startdate and enddate AND a.EmployeeType = 2 AND a.ScheduleType = 1GROUP BY a.SSN, a.RealName, a.BudgetCode) y ON x.SSN = y.SSN AND x.RealName = y.RealName AND x.BudgetCode = y.BudgetCode LEFT OUTER JOIN
(SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS HLFROM dbo.ActivitiesInCurrentFiscalYear aWHERE a.ItemDate BETWEEN startdate and enddate AND a.EmployeeType = 2 AND a.ScheduleType = 1 AND (a.program_code = '0080' OR a.program_code = '0081')GROUP BY a.SSN, a.RealName, a.BudgetCode) z ON x.SSN = z.SSN AND x.RealName = z.RealName AND x.BudgetCode = z.BudgetCode LEFT OUTER JOIN
(SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS HWFROM dbo.ActivitiesInCurrentFiscalYear aWHERE a.ItemDate BETWEEN startdate and enddate AND a.EmployeeType = 2 AND a.ScheduleType = 1 AND NOT (a.program_code = '0080' OR a.program_code = '0081')GROUP BY a.SSN, a.RealName, a.BudgetCode) b ON x.SSN = b.SSN AND x.RealName = b.RealName AND x.BudgetCode = b.BudgetCode)
I have a nested query that retrieves a min value. I would like to know how do i retrieve the corresponding time with that.
my data looks like this
valuevaluetime
1212/31/14 14:51
12.42/19/15 23:30
[highlight=#ffff11]10.92/21/15 6:40[/highlight]
13.21/25/15 20:47
My min value is 10.9 and i need the date 02/21/15
my nested query is as follows
( select min(cast(f.valuestr as float))
from bvfindings f
where f.ObjectName = 'Hematocrit'
and f.sessionid = s.sessionid
and f.ValueTime > s.open_time)
the above returns me the 10.9
i modified the query
select min(cast(f.valuestr as float)),
f.valuetime
from bvfindings f
where f.ObjectName = 'Hb'
but i get all values then.
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]....
Hi,
on executing the below query i am getting the following error
ERROR: Errors in the back-end database access module. Nested table keys in a SHAPE query must be sorted in the same order as the parent table. The nested query may be missing an ORDER BY clause.
even though the order by clause is presenet in the nested query
SELECT t.[ProductId], Predict ([Association].[Product Basket],3)
From
[Association]
PREDICTION JOIN
SHAPE {
OPENQUERY([Adventure Works Cycle MSCRM],
'SELECT DISTINCT [ProductId] FROM (SELECT ProductId FROM ProductBase) as [Product] ORDER BY [ProductId]')}
APPEND
({OPENQUERY([Adventure Works Cycle MSCRM],
'SELECT [ProductId] FROM (SELECT ProductId FROM ProductBase) as [Product] ORDER BY [ProductId]')}
RELATE [ProductId] To [ProductId]
)
AS
[Product] AS t
ON
[Association].[Product Id] = t.[ProductId] AND
[Association].[Product Basket].[Product Id] = t.[Product].[ProductId]
I hope I'm posting this in the correct forum. If not I apologize. I have a nested select query that I imported from Oracle:
Oracle Version:
Code Snippetselect avg(days) as days from (
select dm_number, max(dm_closedate) - max(comment_closed_date) as days from dm_data
where
dm_type = 'prime' and
dm_closedate <= '31-dec-2007' and
dm_closedate >= '1-dec-2007' and
program = 'aads'
group by dm_number)
SQL Version:
select round(abs(avg(days)), 0) as days from
(select dm.dm_number, abs(datediff(DAY,max(dm.dm_closedate), max(dm.comment_closed_date))) as days
from dm_data dm, ProgramXref px
where
px.Program_Name = 'aads'
and dm.Program_Id = px.Program_Id
and dm.dm_type = 'prime'
and dm.dm_closedate <= '31-dec-2007'
and dm.dm_closedate >= '1-dec-2007'
group by dm.dm_number)
In Oracle the query runs fine. In SQL I am getting a "Line 10: Incorrect syntax near ')'." error. If I run just the nested portion of the query, there are no errors. It only happens when the first query tries to query the nested query. Can anyone help me get the syntax correct?
Thanks,
Lee
OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.
Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.
CatID | Name | Description | No. Products
0001 | Cars | Blah blah blah | 5
etc etc
At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!
However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.
Many thanks!
Ok here is a run down of the situation:
I'm running Server 'A' and Server 'B' which are both on SQL 2005 SP2. Server B connects to Server A using the linked server functionality via the SQLNCLI provider. I am issuing an update statement from a web api in a nested transaction that uses a distrubted transaction.
However, I am receiving the following error :
Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "A". A nested transaction was required because the XACT_ABORT option was set to OFF.
OLE DB provider "SQLNCLI" for linked server "A" returned message "Cannot start more transactions on this session.".
I've researched the issue and understand how XACT_ABORT works.
Also, I looked at the Linked Server provider options for SQLNCLI and came accross the Nested Queries option being unchecked. I checked the option and applied it to the Linked Server.
Okay, so after my long post my questions are: Do i need to restart SQL in order for this to take effect? If not, what do i need to do? I 've restarted IIS to no avail .