|
Try this.
Inputs:
declare @t table(childid int,name varchar(20),code int,parentid int)
insert into @t values (6219,'HBO',02,0)
insert into @t values(6220,'HBO1', 0201,6219)
insert into @t values(6221,'HEAO',0202,6219)
insert into @t values(6222,'HTS',0203,6219)
insert into @t values(6231,'MEAO',0403,6228)
insert into @t values(6232,'MTS',0404,6228)
insert into @t values(6234,'Midde',05,0)
insert into @t values(6235,'Overi',06,0)
insert into @t values(6228,'MBO',04,0)
insert into @t values(6236,'WO',10,0)
insert into @t values(6237,'Post',07,0)
insert into @t values(6239,'Basi',NULL,NULL)
insert into @t values(6448,'Athe',05001,6234)
select * from @t
Query:
;with cte as
(
select
cast(t1.childid as varchar(1000)) [path]
, t1.childid
,t1.name
,t1.code
,t1.parentid
,0 AS [Level] from @t t1 where parentid = 0 or parentid is null
union all
select
cast([path] + '/' + cast(t1.childid as varchar(1000)) as varchar(1000)) [path]
,t1.childid
,t1.name
,t1.code
,t1.parentid
,c.[Level]+1 AS [Level]from @t t1
join cte c
on c.childid = t1.parentid
)
select childid,name,code,parentid
from cte order by [path]
Output:
childid name code parentid
6219 HBO 2 0
6220 HBO1 201 6219
6221 HEAO 202 6219
6222 HTS 203 6219
6228 MBO 4 0
6231 MEAO 403 6228
6232 MTS 404 6228
6234 Midde 5 0
6448 Athe 5001 6234
6235 Overi 6 0
6236 WO 10 0
6237 Post 7 0
6239 Basi NULL NULL
Niladri Biswas
|
|
|
|
|
Thanks for ur timely help...
Nothing is Impossible. Keep always Smiling...
|
|
|
|
|
Hi!
Can someone please tell me how would I go about creating a Stored Proc to select records between dates?
I have 2 variables, date1 and date2 I want to create a stored proc as follows:
select * from table between date1 and date2 where the dates are variables in the stored proc.
Illegal Operation
|
|
|
|
|
select * from table
where datecolumn between date1 and date2
e.g.
declare @t table(activity varchar(10),datecol datetime)
insert into @t
select 'activity1', '2009-12-01' union all
select 'activity2','2009-12-02' union all
select 'activity3','2009-12-15' union all
select 'activity4','2010-01-02' union all
select 'activity5','2009-12-31'
Declare 2 date variables
declare @date1 datetime,@date2 datetime
set @date1 = '2009-12-01'
set @date2 = '2009-12-15'
I want to select records between 1st Dec 2009 to 15th Dec 2009.
Query:
select * from @t
where datecol between @date1 and @date2
Output:
activity datecol
activity1 2009-12-01 00:00:00.000
activity2 2009-12-02 00:00:00.000
activity3 2009-12-15 00:00:00.000
Niladri Biswas
|
|
|
|
|
CREATE PROCEDURE usp_GetDataFromDateRange(
@Start DATETIME,
@End DATETIME
)
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM MyTable
WHERE DateCol BETWEEN @Start AND @End
RETURN
END
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
i have table A with field password varchar(15), am migtrating the data to password in another table datatype varbinary with script component transformation. i set the password to byte stream
i have this code to encrypt the password so i can store it as varbinary
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
Try
Dim hashBytes As Byte()
Dim encoding As New UnicodeEncoding
hashBytes = encoding.GetBytes(Row.app)
Dim sha As New SHA1CryptoServiceProvider
Dim crpytPassword As Byte() = sha.ComputeHash(hashBytes)
Row.passout = crpytPassword
Catch ex As Exception
End Try
End Sub
but am getting this error
[AccountProfile New Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
[AccountProfile New Destination [16]] Error: There was an error with input column "pass" (88) on input "OLE DB Destination Input" (29). The column status returned was: "The value could not be converted because of a potential loss of data.".
thanks
|
|
|
|
|
Hi,
We have event-based data currently being logged in relational database structures. For various reasons this data need to be stored elsewhere, possibly on our existing PI historian database. We need to ascertain whether PI can relatively easily store this type of event based data, and ultimately produce reports using RT Web Parts, also relatively easily. If not, we would rather log it in a SQL Server database.
1. This data are obtained via a SSIS package from flat files and stored in a SQL server database.
2. Currently we have around 550 event based jobs running parallel typically on a daily basis.
3. This constitutes in approximately 100 000 records every 5 minutes being logged. That’s more or less 350 records every single second.
4. These events are currently sorted on a BigInt type incremented number from low to high. Can PI log values at a micro sub second level so that the values can be displayed in the correct order it was logged? Or are there some other way to do this?
5. Note that EvTime only saves at second level. EvSeqNo is necessary here to sort the data sequentially, as many records are logged per second.
6. Can Pi handle this huge influx of data?
7. A sequence can constitute a various number of events, from starting that sequence by starting a job by an operator, holding or diverting it. Starting, holding, diverting and so forth constitutes an event. There are various other events as well
8. A job can start another job as well.
9. EvJob, EvLevel, EvLabel, EvKey,EvText, EvSeqNo are all stored in separate database columns. Must this be concatenated into one delimited string or is there an easier way to store the data in PI?
10. EVJob should typically the tag name. However when a job is started by an operator EVJOb can be something like :Citect which means that the actual job being started must be retrieved from the EvText string.
11. Once all this data is being logged, can it easily be retrieved to display in reports using RT Web Parts?
|
|
|
|
|
(first posted in The Lounge but referred onwards)
Hi all
Whilst I have done quite a bit of ASP.Net programming in the past, most of my recent development work has been with Ruby on Rails. I'm now starting up a new project that requires ASP.Net and I'm trying to see how to do a couple of the really great features in RoR.
The main one is database migrations.
In RoR, you have a logical schema for the database tables stored as a series of migrations: each of which contains the changes to up or down the rev level of the database tables. As you develop the models, you add migrations. This makes rapid development involving databases really easy.
Does anything similar exists for VS and ASP.Net? I've been through much of the ASP.Net MVC pages but all the references I've seen are too micky mouse to see anything useful (i.e. they all deal with local MDF databases rather than a remote SQL Server database).
Regards
Gareth
===
GarethHowell
http://www.elastictime.com
|
|
|
|
|
|
SELECT DISTINCT test
FROM [abc].[dbo].[a]
inner join [def].[dbo].[b]
on [abc].[dbo].[a].[mail] = [def].[dbo].[b].mail
inner join idx
on idx.uid = mail.uid
where id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67'
and id <> '4RT467YH-RF43-45FH-78YG-54RYG6THYR43'
The above query filters the first id but it is ignoring the and bit ?
So
where id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67'
works but
and id <> '4RT467YH-RF43-45FH-78YG-54RYG6THYR43'
doesn't.
Is there anything am doing wrong. Can i not put where clause like above.
Thank You
Andyyy
modified on Monday, December 14, 2009 6:28 AM
|
|
|
|
|
AndyInUK wrote: where id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67' and id <> '4RT467YH-RF43-45FH-78YG-54RYG6THYR43'
What are you trying to achieve with this?
If id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67' is true then logically id <> '4RT467YH-RF43-45FH-78YG-54RYG6THYR43' must also be true, so it is unnecessary.
On the other hand, if id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67' is false then the second test after the AND is irrelevant and it doesn't matter whether it is true or false.
I don't understand why you have included the test for id <> '4RT467YH-RF43-45FH-78YG-54RYG6THYR43' since it doesn't seem to do anything useful in your query, unless I am missing something.
|
|
|
|
|
basically user can have many ids - So i have to get the users with id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67' only and it should not include the users who has got id = 'GHREFC5C-GT5F-43RF-GRT4-TGREW34TGY67' and also id = '4RT467YH-RF43-45FH-78YG-54RYG6THYR43'
Becuase if i only use the first where clause it gives me users with that id plus they have many other ids associated with them which i don't want.
|
|
|
|
|
SELECT car WHERE color='red' AND color<>'green'
Does that seem logical to you?
|
|
|
|
|
Say Bob is opted in 4 ids - a,b, c, d.
Now i want to find out users who are opted in a but not c.
So how can i find that ?
|
|
|
|
|
By using a subquery;
SELECT *
FROM [dbo].[A]
WHERE id = 'bla'
AND id NOT IN (
SELECT [id]
FROM [dbo].[C])Can you show us the definition of tables A and C? How many records does a single user have in each table?
I are Troll 
|
|
|
|
|
something along these lines should work:
SELECT * FROM `CPtest` WHERE optio = 1 AND NAME NOT IN (SELECT name FROM `CPtest` WHERE NOT optio = 1)
|
|
|
|
|
Take this example
declare @t table(username varchar(50),userid varchar(100))
insert into @t
select 'username1','a' union all select 'username1','b' union all
select 'username1','c' union all select 'username1','d' union all
select 'username2','a' union all select 'username3','b' union all
select 'username3','c' union all select 'username3','d' union all
select 'username4','a' union all select 'username4','b' union all
select 'username4','c' union all select 'username5','d' union all
select 'username6','a' union all select 'username7','b' union all
select 'username8','c' union all select 'username8','d'
username userid
username1 a
username1 b
username1 c
username1 d
username2 a
username3 b
username3 c
username3 d
username4 a
username4 b
username4 c
username5 d
username6 a
username7 b
username8 c
username8 d
I want to find the users who has userid only 'a'. So in this case the desired output will be username2 and username6
Query 1:
select username from @t
except
select username from @t
where userid in('b','c','d')
Query 2:
select t1.username from @t t1
left join (select username from @t where userid in('b','c','d')) x
on x.username = t1.username
where x.username is null
Query 3:
select username from (
select username,
stuff((select ',' + CAST(t2.userid as varchar(max)) from @t t2
where t2.username = t1.username for xml path('')),1,1,'') as userids
from @t t1
group by username )X
where X.userids = 'a'
In all the 3 cases the output is
username
username2
username6
Hope this helps
Niladri Biswas
|
|
|
|
|
Hi,
The following sample failed to create the DSN,
void main()
{
//As my DB path will change frequently i am using this method.
bool aDSNCreated = false;
char temp[MAX_PATH];
sprintf(temp,"DSN=SAMPLE\0DBQ=D:\\SAMPLE.mdb\0");
aDSNCreated = SQLConfigDataSource(NULL, ODBC_ADD_SYS_DSN, "Microsoft Access Driver (*.mdb)\0", temp);
printf("%d",aDSNCreated );
}
whereas when I execute the below code; it execute successfully.Can anyone help in this why its happening?
void main()
{
bool aDSNCreated = false;
aDSNCreated = SQLConfigDataSource(NULL, ODBC_ADD_SYS_DSN, "Microsoft Access Driver(*.mdb)\0","DSN=SAMPLE\0DBQ=D:\\SAMPLE.mdb\0");
printf("%d",aDSNCreated );
}
|
|
|
|
|
Hi all,
I have table Demo:
CREATE TABLE Demo
(
ID int PRIMARY KEY,
Name char(50),
BeModified int DEFAULT(0)
)
Whenever [Name] is updated, [BeModified] will be set to 1.
I think I should create an TRIGGER AFTER UPDATE
CREATE TRIGGER UpdateRow
ON Demo
AFTER UPDATE
UPDATE Demo
Set BeModified = 1 WHERE ID = "???"
My problem is I do not know how to identify the ID of row which has been modified.
Have you got any suggestion for me?
Thank you so much!
|
|
|
|
|
I don't think you really need a trigger here. You must be updating the name through a stored procedure/SQL query. Just make the query update the bool column as well.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
As d@nish recommended. There is also a keyword in the trigger that indicates the current record, not sure what it is, I NEVER use triggers, they are evil.
As another idea I use a datetime field instead of a boolean, it tells you so much more than a simple yes/no.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I concur. This is NOT the place for a trigger. I only use triggers to store 1)disposable, 2)derived and 3)complex data used for reporting or searching. Must meet all 3 criteria. But to answer the question-
UPDATE Demo
Set BeModified = 1 WHERE ID IN (SELECT ID FROM inserted)
OR
UPDATE Demo
Set BeModified = 1
FROM Demo
INNER JOIN inserted
ON Demo.ID = inserted.ID
This is assuming sql server.
The "inserted" table is a virtual table which contains all of the fields and values from the insert, update or delete that fired the trigger.
You might think that only a record at a time is updated but in fact since it is possible to update multiple records at the same time, this may NOT be what you want. But thats what you get with triggers. If you ever have to update all of the records in that table, BeModified will be set to 1 for all. Be warned.
|
|
|
|
|
Hello,
This is Vikash.
I have an application where on starting the application it connects to SQL Server 2000.
At this point of connection I also execute the query 'Select Name From Sysdatabases' to get a list of database names.
The problem is at my PC using My SQL Server 2000, the Query is getting executed properly giving me a list of databases.
But when I install the software on one of the clients PC where my database is attached to their existing MSSQL Server 2000, it display the error message, "Invalid Object Name 'sysdatabases'".
Can somebody tell me why this is happening. Has somebody faced similar problems.
Please any help would be highly appreciated.
Thanks in Advance.
|
|
|
|
|
Check the permissions of your connection credentials. Typically a developer assigns himself god rights to their database and lose sight of the fact that a DBA will never do that on a production server.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the reply Mycroft.
but can you tell me what credentials you are talking about.
My Connection string is :
Server=<Servername>;Database=<DBname>;Uid=<UserID>;Pwd=<Password>;
what else do I need to provide.
Is It That Sysdatabases will work only if I have access rights to all the databases in the instance.
bcoz, my client also has several other databases in this instance which I am connecting and I might not have rights to those databases.
Awaiting your reply.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
