|
Hi,
How can I find all leaves (nodes that don't have any child), in a hierarchy structure in my table in sql?
Best wishes
|
|
|
|
|
Peronally, when I represent Heirachies in a relational table I always add a boolean "IsLeaf" field so I can query just this situaltion easily (SELECT * FROM MyHeirachy WHERE IsLeaf=1)
However, it should be easy enough without it, just look for any rows where there is no row referencing this row as its parent.
SELECT *
FROM MyHeirachy H
WHERE NOT EXISTS (SELECT * FROM MyHeirachy WHERE ParentID=H.ID)
|
|
|
|
|
The following code uses the Employees table from Northwind.
WITH EmployeeStructure
AS
(
SELECT employeeid, reportsto, 0 as level
FROM Employees
WHERE reportsto is null
UNION ALL
SELECT e.employeeid, e.reportsto, level + 1
FROM Employees e
INNER JOIN EmployeeStructure es
ON es.employeeid = e.reportsto
)
SELECT *
FROM EmployeeStructure
WHERE EmployeeID NOT IN (SELECT reportsto FROM EmployeeStructure GROUP BY reportsto HAVING ReportsTo IS NOT NULL)
ORDER BY level ASC
|
|
|
|
|
Hi, I have a XML column (name = 'xml') in a table 'contact' in my DB which has rows as the following
<Contact xmlns="http://www.focus-solutions.co.uk/focus360/1.0" id="00496bb7-0046-4459-8cf8-002cc5921f4c" documentId="494f4671-efe8-4577-aecc-4424b699a89c">
<Type code="FirstMeeting">First meeting</Type>
<Location code="ClientHome">Client Home</Location>
<PresentationInd>true</PresentationInd>
<OtherAddress>
<HouseNameNumber>1</HouseNameNumber>
<AddressLine1>Royal Terrace Gardens</AddressLine1>
<City>Edinburgh</City>
<Postcode>EH7 5DX</Postcode>
</OtherAddress>
<Date>2009-09-08</Date>
<Time isoTime="15:00:00">
<Hours>03</Hours>
<Minutes>00</Minutes>
<Period>PM</Period>
</Time>
<Duration>
<Hours>01</Hours>
<Minutes>00</Minutes>
</Duration>
<Notes />
<LetterSentInd>true</LetterSentInd>
<Documents>
<Document id="a7b36eec-ed51-4f22-8465-267a893fd8a0" externalId="494f4671-efe8-4577-aecc-4424b699a89c" userAttached="false">
<Type code="ConfirmationLetter">Confirmation Letter</Type>
<Reference>Joe Marton First meeting</Reference>
<CreatedDate>2009-09-18</CreatedDate>
</Document>
</Documents>
<CustomQuestions>
<Question type="HasMeetingTakenPlace">
<Answer>true</Answer>
</Question>
<Question type="ClientContacted">
<Answer>Joe Marton</Answer>
</Question>
</CustomQuestions>
<Valid>true</Valid>
</Contact>
So this is just one row in the table.I am complete beginner when it comes to querying XML datatypes in a table.
I need to be able to list all records in the table CONTACT for which the the value <PresentationInd>true</PresentationInd> is true.
Help appriciated
|
|
|
|
|
SSRS - Web Service vs URL access. Pro's and Con's?
Thanks
dev
|
|
|
|
|
|
um... I've written apps which accesses SSRS reports via URL access, and I've written apps which accesses SSRS reports via Web Services.
This article is for someone who hasn't done it...
dev
|
|
|
|
|
Turns out I was specifying the length of the XML string from the application. Goes to show how dumb one can be sometimes!
This error has been driving me mad. Testing the whole run from within SSMS using the same values that are provided by the application yields a perfectly working result; The application itself returns "XML parsing: line 1, character 4, unexpected end of input"
The actual XML being sent is:
<Data>
<AssignedDiet>
<PatientID>127686419</PatientID>
<AdmittanceID>3</AdmittanceID>
<DietID>1</DietID>
<Notes></Notes>
</AssignedDiet>
</Data>
The stored procedure called is:
<pre>
------------------------------------------------------------------------------------------------------------------------
-- Author: Mustafa Ismail Mustafa
-- Procedure Name: [EMR].[NV_VW_Orders_Diet_Insert]
-- Date Generated: Sunday, August 09, 2009
-- Company: Netvareas Solutions
-- Project Name: RCH Automation
------------------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE [EMR].[NV_VW_Orders_Diet_Insert]
@PatientID char(10),
@AdmittanceID int,
@IssuingEmployeeID int,
@DateTimeGiven datetime,
@OrderDetails nvarchar(max),
@IsDoctor bit,
@IsCanceled bit,
@IsCompleted bit,
@ClosingEmployeeID int,
@ClosingDateTime datetime,
@IsRepeated bit,
@IsDefaultTime bit,
@DefaultTimeID int,
@PeriodID int,
@PeriodValue int,
@Notes nvarchar(max),
@DietID int,
@OrderType nvarchar(50),
@Tags XML,
@SubOrderID int OUTPUT,
@OrderID int OUTPUT
AS
SET NOCOUNT ON
BEGIN TRANSACTION
/*
1. Retrieve the SubOrderID (Max current DietOrderID + 1)
2. Insert the PatientOrder and retrieve the OrderID
3. Insert the DietOrder
4. Extract from the XML all the rows to be inserted into mmPatientDiets
5. Insert rows into the persistant table mmPatientDiets
*/
DECLARE @ERR int = 0;
--1.
SET @SubOrderID = ISNULL((SELECT MAX(DietOrderID) FROM EMR.PatientAssignedDiets WHERE PatientID = @PatientID AND AdmittanceID = @AdmittanceID),0) + 1;
--2.
EXEC [EMR].[NV_PatientOrders_Insert]
@PatientID, @AdmittanceID, @DateTimeGiven, @IssuingEmployeeID, @OrderDetails, @IsDoctor, @IsCanceled,
@IsCompleted, @IsRepeated, @IsDefaultTime, @DefaultTimeID, @PeriodID, @PeriodValue, @OrderType,
@SubOrderID, @OrderID OUTPUT
SET @ERR = (SELECT @@ERROR)
if @ERR &lt;&gt; 0
GOTO ErrorHandler
--3.
EXEC [EMR].[NV_PatientAssignedDiets_Insert]
@PatientID, @AdmittanceID, @OrderID, @SubOrderID
SET @ERR = (SELECT @@ERROR)
if @ERR &lt;&gt; 0
GOTO ErrorHandler
--4.
EXEC sp_xml_preparedocument @Handle OUTPUT, @Tags
SET @ERR = (SELECT @@ERROR)
if @ERR &lt;&gt; 0
GOTO ErrorHandler
--5.
INSERT INTO EMR.mmPatientDiets
SELECT PatientID, AdmittanceID,@OrderID,@SubOrderID, DietID, Notes, GETDATE(), null
FROM OPENXML (@Handle, '/Data/AssignedDiet', 2) WITH
(PatientID char(10), AdmittanceID int, DietID int, Notes nvarchar(max) );
SET @ERR = (SELECT @@ERROR)
if @ERR &lt;&gt; 0
GOTO ErrorHandler
ErrorHandler:
IF @ERR &lt;&gt; 0
BEGIN
Rollback
RETURN @Err
END
COMMIT
</pre>
I don't know what else to post, but I'd be more than happy to oblige. What makes it absolutely annoying is that it works fine from SSMS!
TIA
If the post was helpful, please vote, eh!
Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ
Now and forever, defiant to the end.
What is Multiple Sclerosis[ ^]?
modified on Saturday, September 26, 2009 4:45 PM
|
|
|
|
|
hi
how i can run this:
select * from men where Tdate between 01/01/01 and 02/02/02
but, Tdate is nvarchar type
i work with sqlCE
thank's
|
|
|
|
|
Hi,
I would convert the string to a real datetime using convert(datetime, Tdate), then use that in a WHERE clause.
it could be something like ... where convert(datetime, Tdate, 1)>='01/01/01' and convert(datetime, Tdate, 1)<='02/02/02'
The '1' code specifies the format, see here[^].
I would also suggest to STORE dates as such, not as strings.
Luc Pattyn
Local announcement (Antwerp region): Lange Wapper? Neen!
|
|
|
|
|
Luc has given you the solution but he was not forceful enough on storing the data as a datetime data type. If you do not change your data format to datetime then you will continiously run into this problem and all the associated datetime issues we see here regularly.
STORE YOUR DATA IN THE RIGHT FORMAT.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: he was not forceful enough
Sorry, I must be having an off-day then.
Luc Pattyn
Local announcement (Antwerp region): Lange Wapper? Neen!
|
|
|
|
|
As we are no longer encouraged to beat them with the sarcasm stick  , I see no reason not to scream it in their face.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: As we are no longer encouraged to beat them with the sarcasm stick Sigh , I see no reason not to scream it in their face.
Even though neither method has the desired effect in too many cases 
|
|
|
|
|
hi everyone,
I have a tab control with 3 tabs, A,B,C.
I have one one database "student" in which i have 4 columns.
There are two datagridviewB and datagridviewC in the two tabs B and C.
I am updating the entries of a datagridviewB by entering details in a form present in Tab A.
Now when I am working in tab C to update the datagridviewC, say for example filtering "student" database and displaying filtered results in datagridviewC; while updating datagridviewC, I noticed that datagridviewB in tab B also getting updated.
My code for filter "student" database in tab C is like this:
cmd = New SqlCommand("SELECT * FROM student WHERE [Name] <= '" + name + "'", con)
myDA = New SqlDataAdapter(cmd)
myDataSet = New DataSet()
myDA.Fill(myDataSet, "student")
datagridviewC.DataSource = myDataSet.Tables("student").DefaultView
Can someone tell me, how to update the datagridviewC in tab C without altering the entries present in datagridviewB in tab B?
FYI : my datagridviewB contains all the student data and in tab C i am filtering the database based on "Name" and displaying in datagridviewC.
|
|
|
|
|
If your datasource for the datagridviews is the same then naturally the data is the same. You can separate the datasources into bindingsources or copy the dataview to a new table for each datagridview.
BTW this should be in the Windows Forms forum.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi guys.
I'm using LINQ to modify a database.
I want to take a back up from the database.
Could you please guide me , how I can do it with LINQ ?
|
|
|
|
|
You don't need Linq to backup a database. What is it that you want to accomplish?
only two letters away from being an asset
|
|
|
|
|
I've created a SQL database beside my exe file, I'm trying to take backup from it through my application.
At first , I've used File.Copy, but I receive this Exception cannot access because it is being used by another process
Could you guide me, how I can do it ?
|
|
|
|
|
Mohammad Dayyan wrote: cannot access because it is being used by another process
Perhaps your application? If the app uses a mdf file as a resource then it is possibly loading the file at start up. That should give you a start of where to look.
only two letters away from being an asset
|
|
|
|
|
Run normal backup code from your application, rather than to use File.Copy. File.Copy needs the mdf files to be detached from your server instance which is not good technique.
BACKUP DATABASE [DbName] TO DISK = N'C:\dbName.bak' WITH NOFORMAT, NOINIT, NAME = N'DbName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
Just change the dbName, disk location for your backup and Name of the Backup file and run this using command.ExecuteNonQuery().
And believe me... you dont require LINQ for doing this . 
|
|
|
|
|
Thanks Abhishek Sur.
I'm using LINQ, so I've used the following code :
public static void GetBackUp()
{
if (!Directory.Exists(StaticVariables.BackUPFolder))
Directory.CreateDirectory(StaticVariables.BackUPFolder);
using (RezaRestaurant.SQL.DataClasses1DataContext dbc = new RezaRestaurant.SQL.DataClasses1DataContext())
{
dbc.ExecuteCommand(@"BACKUP DATABASE " + dbc.Mapping.DatabaseName + " TO DISK='c:\aaa.bak'");
}
}
and this Exception has occurred :
http://www.codeguru.com/forum/attachment.php?attachmentid=25454&d=1253973435[^]
|
|
|
|
|
I have a project to convert a database from Postgresql to SQL Server. I know of tools to convert the database from one to the other but all I have are scripts right now. Does anyone know of a utility that can take scripts created for Postgresql and create a SQL Server database from them?
only two letters away from being an asset
|
|
|
|
|
Depending on the size of the database it would almost be worth installing postgre just to use the conversion tools. I'm assuming they have an express type product.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
There is no express version but I already have Postgresql installed. The problem in the scripts I've been provided with won't run. I wanted to try converting them to TSQL and running them against SQL Server directly since the ultimate goal is to port it to SQL Server anyway.
only two letters away from being an asset
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
