Sql Server Nested Set, How?
Nov 8, 2006
Hi,
I'm not sure if this is a good place to ask sql questions, so please bear with me here...
I have a table like so
id,
parentid
What I'm trying to do is to write a self join where given a random ID, it'll give me the whole tree of its decendents. (I don't need its parent)
so say I have data liek so
id parentid
1 null
2 1
3 2
when I specificy 1, it'll give me 2 and 3, even though 3 is indirectly related to 1. when I say 2, it'll just give me 3.
Thanks a lot. GREATLY appreicate it.
View 1 Replies
ADVERTISEMENT
Mar 30, 2004
Hi There,
We have problems getting the right records from a joined table.
First we have a table with all our Customers (eg. [Algemeen-B])
We join them with a table representing all Contacts per Customer (eg. [Algemeen-C])
An SP joins the tables because we have a few dynamic filters and parameters.
What we need is to link a maximum of @amount contacts per Customer.
Where each contact has a RANK (the TOP @amount ) joining the Customer Table.
We come thisfar:
-- The SP results this SQL select:
select *
from [Algemeen-B] left outer join
(
select * from [Algemeen-C] AS C
where exists
(
select * from [Algemeen-C]
group by bedrnummer
having bedrnummer=C.bedrnummer And min(rank) <= C.Rank and count(*) <= 2
) ) S
on [Algemeen-B].bedrnummer = S.bedrnummer
where KWP>=8
---
In this case I should return a maximum of 2 contacts per each Distinct Customer.
But I think the: 'And min(rank) <= C.Rank and count(*) <= 2' is wrong.
How can I select the TOP 2 Ranked Contacts for each Customer?
Because when there are no Contacts for the Customer it should return only the Columns for [Algemeen-B]
and NULL values for the Colums for [Algemeen-C].
If you need more info Tell me..
Hope someone can help me out..
Colin
View 6 Replies
View Related
May 8, 2004
Hi all,
What is the equivalent for Oracle's nested table concept in SQL Server ?
Is there anything like TABLE( ) function to select from nested table as in Oracle ?
Eg in Oracle :
SELECT t.* FROM TABLE(nested_table_datatype) t;
( like the above query used in Oracle PL/SQL and 'nested_table_datatype' is a table datatype created in Oracle using 'create type ...' syntax )
Please give the equivalent for above...
Thanks,
Sam
View 2 Replies
View Related
Oct 16, 2014
I'm trying to reverse engineer an XML output based on a client's need of a very specific format. Besides the issue of getting the XML declaration written in and removing NULLs, I'm having an issue with a three times nested PATH query.
So far the document almost has the correct format, except for the first nested root being returned. Is there any way to prevent this?
*some data redacted
Query:
DECLARE @a XML
SET @a =
(SELECT FileCreationDate,
(
SELECT PCE_TPD.PJN,
(
SELECT top (2) TaskCode, TaskName, RSI, TAFFD, AFD, Notes, PID, CUSID, NeedToBeNA
[Code] ....
Current Output (first few lines):
<TDBUIData xmlns="http://www.xxxxx">
<TDBUIData12 xmlns=""> --NEED TO ELIMINATE THIS
<FileCreationDate>2014-10-15T23:23:00</FileCreationDate>
<TDBUIDL>
<PJN>MRWSH010824</PJN>
[Code] ...
Desired Output (first few lines):
<TDBUIData xmlns="http://www.xxxxx">
<FileCreationDate>2014-10-15T23:23:00</FileCreationDate>
<TDBUIDL>
<PJN>MRWSH010824</PJN>
<TaskData>
[Code] ...
View 4 Replies
View Related
Apr 15, 2008
I am creating an XML file from 'n' number of tables using Dataset.Writexml()
Now, I join tables using INNER JOIN and fill the resultset into a dataset (There is a foreign key called ID in each of the tables).
The resultant XML doesn't shows the nested nodes, It shows the nodes sequentially.
The Result I got was,
<Root>
<Name>
<FName>Fxxx</FName>
</Name>
<Name1>
<LName>Lxxx</LName>
</Name1>
</Root>
The FName and LName resides in different tables.
<Root>
<Name>
<FName>Fxxx</FName>
<Name1>
<LName>Lxxx</LName>
</Name1>
</Name>
</Root>
I have tried the DataTable Relations and Dataset merge.
Any work-around / Straight approaches ?
View 7 Replies
View Related
Dec 15, 2014
I need to perform some operations that seem to require multiple nested tables.
Is the following code the way I should be attempting this or is there a better way?
Here is the sample code:
SELECT tt.tdl_ID, tt.tx_ID, adj.Prof_Chgs, rc.Rvu_Comp
FROM Table1 as tt
LEFT JOIN (
SELECT tt1.Tdl_Id,
CASE
WHEN tt1.tdl_ID = '1000'
[code]....
View 8 Replies
View Related
May 29, 2008
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
View 5 Replies
View Related
Sep 26, 2006
I'm working on an MSI-based install that installs a suite of applications. One of the applications requires SQL Server 2005 Express to be installed or already on the machine, but if the user doesn't want that app installed, I don't want to have to bother with the SQL Server issue; however, if that app is to be installed and SQL Server is not already installed, I want to be able to have it installed automatically during our install in order to make it easier for our end users. The obvious answer to this is to launch the SQL Server Express install as a nested MSI install at the appropriate point in our install. Since the SQL Server install is so complex, I was wondering if this would work if I were to launch the SQL Server install's setup.exe; or do I have to launch one of its dozen MSI files? If it's the latter, which one should my install launch? (Or is the SQL Server Express install simply too complex to be included in my install in this way? My plan is to have the SQL Server install sitting somewhere on our install CD - we've already obtained a redistribution licence - and have our install access it from there.) And will SQL Server Express appear as a separate entry in the Add/Remove Programs applet if it's installed this way? (I'd like to be able to leave SQL Server in place when uninstalling our application suite - or even just the one app that needs it - and allow SQL Server to be uninstalled separately if the user wishes.)
View 6 Replies
View Related
Oct 10, 2007
I have a problem in running this query on linked servers, which has distributed partitioned view.
SQLTransaction transaction;... public void DeleteAllData() { try { connection.Open(); transaction = connection.BeginTransaction(); { Console.WriteLine("Deleting All Data..."); string cmdText = ""; cmd = new SqlCommand(cmdText, connection, transaction); cmd.CommandText = "delete from NEW_ORDER where NO_O_ID > 0"; cmd.ExecuteNonQuery(); cmd.CommandText = "delete from ORDER_LINE where OL_NUMBER > 0"; cmd.ExecuteNonQuery();
transaction.Commit();
Console.WriteLine("Sucessful"); } } catch (Exception e) { Console.WriteLine("Failed in Method DeleteAllData"); throw e; } finally { connection.Close(); } }
The error returned was:
Failed in Method DeleteAllDataSQL Exception caught: Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "SERVER_B". A nested transaction was required because the XACT_ABORT option was set to OFF.OLE DB provider "SQLNCLI" for linked server "SERVER_B" returned message "Cannotstart more transactions on this session.".
I realized that this problem may be eliminated if I'm using stored procedures on the database, and SET XACT_ABORT ON before any transaction.
Please could anyone help me.
View 1 Replies
View Related
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
Jul 27, 2007
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)
View 8 Replies
View Related
Feb 27, 2014
I am using Server 2012 and very new to SQL. I have a request from a physician for a list of his patients that meet a criteria. This is stored in a temp table names #cohort.
Using this cohort he wants each row to be one patient with a list of labs, vitals, etc. Three items are the most recent lab value and date. I could query each lab individually and place it into a temp table and then join all temp tables at the end, but I am trying to move past that and have all labs in one temp table. All temp tables are joined with PatientSID.
I tried to do something for just 2 labs, but it is not working. There could be nulls values when joined with the #cohort table.
Individually the SELECT statements pull in the most recent lab value and date, but I cannot get them into a temp table with one row of PatientSID and then the lab value and date if they exist.
IF OBJECT_ID ('TEMPDB..#lab') IS NOT NULL DROP TABLE #lab
SELECT
cohort.PatientSID
,SubQuery1.LabChemResultNumericValueAS 'A1c%'
,SubQuery1.LabChemCompleteDateTimeAS 'A1c% Date'
,SubQuery2.LabChemResultNumericValueAS 'LDL'
[Code] .....
View 1 Replies
View Related
May 12, 2015
I have a tree and I need to copy a nested sub-tree (an element with its children, which in turn may have their owns) from one place to another.
The system should allow to handle up to 8 levels. I do know how to move, but cannot figure out how to copy.
Below is a working example With Create Table, Select and Cut / Paste (implemented via Update).
I would like to know how to copy a nested tree with reference id 4451 from Parent_Id 1 to Parent_Id = 2
--***** Table Definition With Insert Into to provide some basic data ****
IF (OBJECT_ID ('myRefTable', 'U') IS NOT NULL)
DROP TABLE myRefTable;
GO
CREATE TABLE myRefTable
(
Reference_Id INT DEFAULT 0 NOT NULL CONSTRAINT myRefTable_PK PRIMARY KEY,
[Code] ....
How to Copy nested sub-tree 4451 with all its children to Parent_Id 2, without deleting from Parent_Id = 1 ?
View 7 Replies
View Related
Jul 16, 2015
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.
View 6 Replies
View Related
Aug 6, 2015
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]....
View 2 Replies
View Related
Jan 7, 2008
Hi, I have a server-side stored procedure that I created in C#. The SP issues one DMX statement (within the implicit connection) which has a prediction join with SHAPE/APPEND. It then tries to map the results of the AdomdDataReaders to System.Data.DataTable objects, returning one System.Data.DataTable object. Because of the SHAPE/APPEND, I end up with two nested AdomdDataReaders.
Code Block
using Microsoft.AnalysisServices.AdomdServer;
using System.Data;
...
public DataTable VivaOMengoSP()
{
AdomdCommand c = new AdomdCommand(CreatePredictedDMX());
// select ... prediction join ... shape ... append
DataTable dt = new DataTable();
dt.Columns.Add("att1", typeof(string));
dt.Columns.Add("att2", typeof(int));
dt.Columns.Add("predictednestedmonster", typeof(DataTable));
object[] row = new object[3];
try
{
AdomdDataReader dr = c.ExecuteReader();
while (dr.Read())
{
DataTable innerdt = new DataTable();
innerdt.Columns.Add("inneratt1", typeof(int));
innerdt.Columns.Add("inneratt2", typeof(string));
row[0] = dr[0];
row[1] = dr[1];
AdomdDataReader innerdr = (AdomdDataReader)dr[2];
object[] innerrow = new object[2];
while (innerdr.Read())
{
innerrow[0] = innerdr[0];
innerrow[1] = innerdr[1];
innerdt.Rows.Add(innerrow);
}
row[2] = innerdt;
dt.Rows.Add(row);
}
dr.Close();
return dt;
}
catch
{
return null;
}
}
(The code is a modification to listing 20-3, page 807, of the book "Programming Microsoft SQL Server 2005" by Brust & Forte)
I call it directly in SSMS and I get this result:
Executing the query ...
Execution of the managed stored procedure VivaOMengoSP failed with the following error: Microsoft::AnalysisServices::AdomdServer::AdomdException.
Errors in the high-level relational engine. The System.Data.DataTable type is not supported by Analysis Services code name "Katmai".
Execution complete
Is it possible to have nested table as a SP result? Is DataTable the appropriate class?
Thanks,
Gustavo Frederico
View 4 Replies
View Related
Jul 9, 2006
Hi,
I would like to use a stroed procedure within another stored procedure ( nested sp )
in a SQL project in VS.NET 2005. Since I have to use "context connection = true" as
connection string, I wont be able to use this connection for another sqlconnection
object. Because its already open. and If i try to use a regular connection string
("server=localhost;...") it will raise a security permission error. Having this
problem, Im not able to use nested stored procedures. Would anyone please give me a
hint how to resolve this issue?
View 4 Replies
View Related
Oct 7, 2015
Any easy way to find if there are any nested views existing in the database?...using SQL server 2014...
View 1 Replies
View Related
Nov 30, 2007
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 .
View 1 Replies
View Related
May 26, 2007
Hello,
Quick question, I hope, I am trying to create a table that has a column that is a nested table in SQL Server 2005 Express Edition. Any ideas how I could go about doing this?
Sincerely,
James Simpson
Straightway Technologies Inc.
View 4 Replies
View Related
May 20, 2008
I've only been doing sql 2005 for a couple of months with minimal training so there's a lot I don't know.
What I'm trying to do is load a nested table (industry & customer totals) based on a value from the table it's nested in.
This is the relationship of the data.
I have at the highest group, an industry code, then a customer, then the part and then the fiscal year.
What I'm trying to accomplish is to get a group total (footer) for the (1) industry code and (2) the customer code. This footer would contain the fiscal years (ascending) and their monthly totals. I would like to take the industry code from table one and pass it to the select statement in the dataset that feeds the nested table. I've read this is not possible to load a dataset field into a parm but I've seen where some people know how to work around this. If you reply, please explain in simple terms. Thanks!
industry Customer Year OCT NOV DEC
001 - Signposts
M12345
Part 1
2006 5 6 2
2007 0 3 1
Part 2
2006 4 3 0
2007 1 0 7
Customer M12345 totals
2006 9 9 2
2007 1 3 8
M45678
Part 3
2007 8 4 7
2008 3 4 8
Part 4
2006 3 8 7
2007 5 6 6
Customer M45678 totals
2006 3 8 7
2007 13 10 13
2008 3 4 8
Industry 001 totals
2006 12 17 9
2007 14 13 21
2008 3 4 8
View 1 Replies
View Related
Feb 27, 2007
Is it that I have a syntax error in the nested OPENQUERY or is there another issue? Do I need to specify a different provider in the Server Link such as OLEDB? Non-nested OPENQUERYs work fine.
I'm generally following theTips and Tricks article.
"Executing predictions from the SQL Server relational engine". One problem is the sample doesn't actually complete the example query after the second nested OPENQUERY call.
e.g.
SELECT * FROM OPENQUERY(DMServer,
'select €¦ FROM Modell PREDICTION JOIN OPENQUERY€¦')
The SQL Server server link's provider is configured to allow adhoc access. I appears that the inner OPENQUERY cannot be prepared by Analysis Server or the Server link provider? but I need to return a key value t.[CardTransactionID] for joining to SQL Server data elements.
OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Errors in the back-end database access module. The data provider does not support preparing queries.".
Msg 7321, Level 16, State 2, Line 2 An error occurred while preparing the query
SELECT * FROM OPENQUERY(DMServer,
'SELECT
t.[CardTransactionID],
t.[PostingDate],
[Misuse Abuse Profile].[Even Dollar Purchase],
PredictProbability([Misuse Abuse Profile].[Even Dollar Purchase]) AS Score,
PredictSupport([Misuse Abuse Profile].[Even Dollar Purchase]) AS Suppt,
t.[BillingAmount]
FROM
[Misuse Abuse Profile]
PREDICTION JOIN
OPENQUERY([Athena Dev],
''SELECT
[CardTransactionID],
[PostingDate],
[BillingAmount],
[AccountNumber],
[SupplierStateProvinceCode],
[MerchantCategoryCode],
[PurchaseIDFormat],
[TransactionTime],
[TaxAmountIncludedCode],
[Tax2AmountIncludedCode],
[OrderTypeCode],
[MemoPostFlag],
[EvenDollarPurchase]
FROM
[dbo].[vMisuseAbuseProfile]
'') AS t
ON
[Misuse Abuse Profile].[Account Number] = t.[AccountNumber] AND
[Misuse Abuse Profile].[Supplier State Province Code] = t.[SupplierStateProvinceCode] AND
[Misuse Abuse Profile].[Merchant Category Code] = t.[MerchantCategoryCode] AND
[Misuse Abuse Profile].[Purchase ID Format] = t.[PurchaseIDFormat] AND
[Misuse Abuse Profile].[Transaction Time] = t.[TransactionTime] AND
[Misuse Abuse Profile].[Tax Amount Included Code] = t.[TaxAmountIncludedCode] AND
[Misuse Abuse Profile].[Tax2 Amount Included Code] = t.[Tax2AmountIncludedCode] AND
[Misuse Abuse Profile].[Order Type Code] = t.[OrderTypeCode] AND
[Misuse Abuse Profile].[Memo Post Flag] = t.[MemoPostFlag] AND
[Misuse Abuse Profile].[Even Dollar Purchase] = t.[EvenDollarPurchase]
')
In desparation I tried returning the case key (CardTransactionID) and the predictive column elements but I get an error when I try that. I assume this is a no-no?
OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Error (Data mining): Only a predictable column (or a column that is related to a predictable column) can be referenced from the mining model in the context at line 2, column 15.".
View 4 Replies
View Related
Jan 3, 2007
Can you give a whole SQL statement an alias so you can use it later?
Eg.
SELECT * FROM Employees WHERE age < 19
-- Could I call the above statement something like 'statement1' to use below as shown
SELECT * FROM Employees WHERE age < 25 AND NOT IN (statement1)
Soin effect I get a nested statement.
The reason I am asking about aliases is because this would need to be repeated for, E.g. age < 30 Then age < 35 and so on and so forth.
So basically, I just want to alias a qhole SQL statement
Any help would be greatly appreciated - George
View 14 Replies
View Related
Jul 20, 2005
Hi all,I have a query that looks like so:SELECT GLDCT AS [Doc Type], GLDOC AS DocNumber, GLALID ASPerson_NameFROM F0911WHERE (GLAID = '00181913')However by stipulating that GLAID = GLAID I cannot get the person_nameas not all the GLALID fields are filled in. from my reading of thehelpdesk I have a felling that a nested query might be the way to goor a self-join but beyond this I am lost!?Many thanks for any pointers in advance.Sam
View 3 Replies
View Related
Oct 15, 2007
I am trying to do some nested IF ELSE conditions. I get an error saying 'Error near work Begin'. Below is teh query and the variables comes in thru cursor.
Can somebody advise me on this and also let me know the best practices and alternative to this if any.
IF (@CCTable = 'Claiminassoc')
BEGIN
IF ( @ClaimCenterField = 'ClaimID' AND @VALUE ='Claim')
BEGIN
UPDATE dbo.Table SET ColName = 'Y'
WHERE ID = @ID AND CCTable = 'Claiminassoc' AND CCField = 'ClaimID'
AND DWField = 'CatastropheDesignationFlag'
END
END
ELSE IF (@CCTable = 'EmploymentData')
BEGIN
IF (@VALUE ='TRUE')
BEGIN
UPDATE dbo.Table SET ColName = 'Y'
WHERE ID = @ID AND CCTable = 'Claim' AND CCField = 'WagePaymentCont'
END
ELSE IF (@VALUE ='FALSE')
BEGIN
UPDATE dbo.Table SET ColName = 'N'
WHERE ID = @ID AND CCTable = 'Claim' AND CCField = 'WagePaymentCont'
END
END
View 4 Replies
View Related
Apr 2, 2008
Hi gang,
I have a challenge, which seems like it is probably trivial, but SQL chops are not up to the task, and I am hoping one of you hot-shot DBAs can throw me a bone!
I have a query that populates an OLAP Time dimension table (basically one row per day of the year over several years). What I want to do is expand that table to include each hour of each day over the time span.
The CTE I am using for the day population is:
Code Snippet
WITH dates(date)
-- A recursive CTE that produce all dates between 2006 and 2057-12-31
AS
(
SELECT cast('2006' AS dateTime) date -- SQL Server supports the ISO 8601 format so this is an unambigious shortcut for 1999-01-01
UNION ALL -- http://msdn2.microsoft.com/en-us/library/ms190977.aspx
SELECT (date + 1) AS date
FROM dates
WHERE
date < cast('2058' AS dateTime) -1
)
What I wanted to do was something like:
Code Snippet
WITH hours(hr)
AS
(
SELECT (DATEPART(hh,date) hr
UNION ALL
SELECT (hr + 1) AS hr
FROM hours
WHERE hr < 24
)
inserted just after
Code Snippet
FROM dates
in the initial CTE. But from what I have read, it seems as though nested CTEs are not allowed.
Can anyone show me how to do this?
Thanks in advance for ANY insight/input!
Cheers,
Chris
View 5 Replies
View Related
Feb 14, 2008
Greetings -
Can someone pls advise the maximum number of nested IIF statements allowed in a VS 2005 report builder layout textbox expression? I seem to be hitting a wall at about 10, but cannot find verification. In case the limitation is by characters, the full expression would run about 3,500. Any other limitations which may have a bearing?
Tks & B/R
View 1 Replies
View Related
Feb 2, 2008
Hi all! I have a problem with my stored procedure, What I'm trying to do here is whenever a user tries to register, the stored procedure will check if the username already exists, and if not it'll now check if the email has already been entered into the database then if not the stored procedure will go ahead and insert the values into the database. *If the username already exists it'll return -1, and if the email already exists then return -2.
SELECT Username FROM UserAccount WHERE Username = @UsernameIF @@ROWCOUNT = 0 SELECT Email FROM UserAccount WHERE Email = @Email
IF @@ROWCOUNT = 0 BEGIN
INSERT INTO UserAccount (Username, Password, Email, FirstName, LastName, Gender, BirthDate, Country, State, Zip, AdditionalInfo)
VALUES (@Username, @Password, @Email, @FirstName, @LastName, @Gender, @BirthDate, @Country, @State, @Zip, @AdditionalInfo)
END
ELSE BEGIN
RETURN -2
END
ELSE BEGIN
RETURN -1
END
END
Thanks!
View 3 Replies
View Related
Oct 16, 2007
When running reports from data, is it faster using nested views approx 4 levels deep, or writing data to a temp tables then running the report?
View 3 Replies
View Related
Apr 7, 2008
What's worng, please help? SELECT TTarea,personel,Date FROM person_table WHERE TTarea = (SELECT TTarea FROM TTarea_table WHERE Center='CENTER_office') I have many TTarea and I want to send back from inner SELECT statement but give an error that inner select statement don't return many result.I want to return many result and I display many TTarea in the CENTER_office
View 2 Replies
View Related
Nov 12, 2003
Hi All,
Im inserting some data into a table and grabbing the new UserID with this statement
SELECT @UserID = @@IDENTITY
I would like to use the @UserID to Execute another SP within the same proc..
..something like this
Exec AnotherSP(@UserId)
But this doesnt seem to be working....Its seems to me that this is a much better approach performance wise rather than returning the UserID to the Business Logic Layer and calling another SP....Im I correct in that assumption....any assistance would be greatly appreciated.
Thanks in advance
AMRITZ
View 2 Replies
View Related
Jun 12, 2004
I think I am getting an endless loop here... anyone know how to fix it?
***********************
CREATE PROCEDURE TrigSendPreNewIMAlertP2
@REID int
AS
Declare @RRID int
Declare @ITID int
Declare @FS2 int
Declare @FS1 int
Declare crReqRec cursor for
select RRID from RequestRecords where REID = @REID and RRSTatus = 'IA' and APID is not null
open crReqRec
fetch next from crReqRec
into
@RRID
Declare crImpGrp cursor for
select ITID from RequestRecords where RRID = @RRID
open crImpGrp
fetch next from crImgGrp
into
@ITID
while @@fetch_status = 0
select @FS1 = @@Fetch_Status
EXEC TrigSendNewIMAlertP2 @ITID
FETCH NEXT FROM crImpGrp
into
@ITID
close crImpGrp
deallocate crImpGrp
while @@Fetch_Status = 0
select @FS2 = @@Fetch_Status
FETCH NEXT FROM crReqRec
into
@RRID
close crReqRec
deallocate crReqRec
GO
View 1 Replies
View Related
Apr 30, 2005
Hi,
Although I am quite familiar with MS Access-grade SQL, I am struggling a bit with proper grown up SQL Server. My usual approach to counting things in Access is to first create a query with the conditions on the data, then use this as the basis of a second query that does the actual counting of the presorted data. I believe the way to do this in SQL server is to use a nested query. I want to generate the top 10 highest counts for each pesticide detected (detection is level>0) for a client between two dates.
Currently I am using
<code>
SELECT top 10 Count(Pesticide) AS CountOfPesticide, Pesticide FROM (SELECT tblData.Pesticide, tblData.Level, tblData.Clast, tblData.Client FROM tblData WHERE (((tblData.Day>@sdate) AND (tblData.Day<@edate))) and (tbldata.level>0) and (tbldata.clast=@clast) and (tbldata.client=@client)) as monkeyboy GROUP BY Pesticide ORDER BY Count(Pesticide) ASC"
</code>
The results that the above SQL turns out though are not reliable. For example, if I set the dates to now and 14 days ago, it produces higher counts for some pesticides then if I set the dates to now and 30 days ago. Any pointers or general advice about nested sql is gratefully accepted!
thanks
Mike
View 3 Replies
View Related
