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..
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 )
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>
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.)
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.
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
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 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'
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 ?
( 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'
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?
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());
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?
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 .
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?
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
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.".
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
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
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
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 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?
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
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
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.
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
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
