|
i, main and o are not different tables. They are all table aliases. If you look at the syntax carefully, you will see that this is a way to use items from the same table in sub-items. Change the references from Attendance to History, but leave the table aliases alone.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Sir,
I tried but i am getting the error message as "At most one record can be returned by this sub query" ?
This is the code thai i tried
SELECT main.UserName, <br />
(SELECT i.EventTime FROM History i <br />
WHERE i.FuncCode="0") AS InTime, <br />
(SELECT o.EventTime FROM History o <br />
WHERE o.FuncCode="10") AS OutTime <br />
FROM History main
Any chance of mistake in this code?
Praveen.K
System Engineer
Graviton Technologies Pvt.Ltd
|
|
|
|
|
That'll teach me to do it away from SQL Server. Here you go:
SELECT
h.EmpID,
h.[Date],
h.EventTime AS ShiftInTime,
h2.EventTime AS ShiftOutTime
FROM
History h
LEFT OUTER JOIN
History h2
ON
h.EmpID = h2.EmpID AND
h.[Date] = h2.[Date] AND
h2.EventCode = 'O'
WHERE
h.EventCode = 'I'
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Sir,
Some changes i made from u'r query ..any way u'r concept worked here,,,Thanks for u'r assistance sir
This is the code i used.....
SELECT h.UserName, h.EventDate, h.EventTime AS InTime, h2.EventTime AS OutTime, h.Dept<br />
FROM History AS h LEFT JOIN History AS h2 ON (h.UserName = h2.UserName) AND (h.EventDate = h2.EventDate)<br />
WHERE (((h.EventDate)=#2/24/2006#) AND ([h.FuncCode]="0") AND ([h2.FuncCode]="10") AND ((h.Dept)="Engg"));
Thanks and Regards
Praveen.K
System Engineer
Graviton Technologies Pvt.Ltd
|
|
|
|
|
i have 3 columns in my table the room,timeStart,timeEnd.
For example i have inserted this data
room: room1
timeStart: 2007-03-02 8:00 AM
timeEnd: 2007-03-02 3:00 PM
Here's the data that will not be accepted if try to insert
room: room1
timeStart: 2007-03-02 9:00 AM
timeEnd: 2007-03-02 4:00 PM
because the timestart is between the timeStart and the timeEnd. Room1 will only be occupied after 3:00 PM.
Can you help me with this?.
Thank you very Much
Bernie
|
|
|
|
|
Hmm. You and this guy Clickety[^] seem to be trying to solve the same problem. Coincidence? Homework?
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
please help me getting a portable data access layer code which independent of language
thanks in advance
Tony
|
|
|
|
|
tonyalex wrote: please help me getting a portable data access layer code which independent of language
Have you looked at NHibernate? It will help you build a language independent DAL.
|
|
|
|
|
Tony
DevExpress Persistent Objects is pretty good. I've used it quite a bit now and I have to say that I like it.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Sorry,
What i meant is the DAL Source Code that is given by the microsoft for all its .net language.........
|
|
|
|
|
tonyalex wrote: What i meant is the DAL Source Code that is given by the microsoft for all its .net language.........
What DAL source code? Microsoft does not supply DAL source code.
|
|
|
|
|
yes, microsoft is providing code for the data access layer for the our custom application,think some thing like SQLHELPER
|
|
|
|
|
tonyalex wrote: yes, microsoft is providing code for the data access layer for the our custom application,think some thing like SQLHELPER
That is part of the Enterprise Library. It is not a DAL, it is designed to help you create a DAL more easily.
|
|
|
|
|
Can anybody please tell me in SQL Server how should I get the records with their row numbers in a table. Like we use Rownum in Oracle. Thank you.
Regards,
Mohd. Abdul Aleem,
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
indian143 wrote: Can anybody please tell me in SQL Server how should I get the records with their row numbers in a table.
You need SQL Server 2005. However, you should realise that row numbers are a fiction. Oracle and SQL Server 2005 supply a number based on the order that the rows are returned and have little (only incidental) or no bearing on how the data is actually stored.
Anyway, the answer is ROW_NUMBER[^]
|
|
|
|
|
Could you please tell me that dont I have any other option to get it SQL Server 2000. Please. Thank you.
Regarsd,
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
I do not recommend this but you could create a insert/delete trigger that loops through the recordset and inserts the new value. This is a performance hit. A sample trigger is below. You must have a primary key on the table to do this. I am confused of why you need to reference the rownumber. It has no significance. If you dont mind a few holes here and there after you have deleted a record, use an Identity column.
<br />
CREATE TRIGGER dbo.trg_Count <br />
ON dbo._TEST<br />
AFTER INSERT, DELETE<br />
AS <br />
BEGIN<br />
<br />
SET NOCOUNT ON;<br />
DECLARE @PKEY UNIQUEIDENTIFIER<br />
DECLARE @I BIGINT<br />
<br />
IF ((SELECT trigger_nestlevel()) = 1)<br />
BEGIN<br />
DECLARE TRG_TEST_CUR CURSOR FOR<br />
SELECT [GUID] FROM _TEST<br />
<br />
OPEN TRG_TEST_CUR<br />
FETCH NEXT FROM TRG_TEST_CUR<br />
INTO @PKEY<br />
<br />
SET @I = 1<br />
<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
UPDATE _TEST SET ID = @I WHERE [GUID] = @PKEY<br />
SET @I = @I + 1<br />
FETCH NEXT FROM TRG_TEST_CUR<br />
INTO @PKEY<br />
END<br />
CLOSE TRG_TEST_CUR<br />
DEALLOCATE TRG_TEST_CUR<br />
END<br />
END<br />
GO<br />
|
|
|
|
|
I need to make a a recursive table.coz i have Main Category & this Main Category have sub category & these Sub Categoris Have Another sub category.my be alot it depend in the main Category.& in the last there are some items to the last sub category.so i need to design the tables.IF Anyone can help me.
J.M.M.G
|
|
|
|
|
You made me confused about the term of "recursive tables". There is no such thing actually because tables cannot be recursive. Regarding your message I suggest you to layout all items such as (Main Category, Sub Category, Child Category and etc.) and each item should have an index for instance lets say that "Fruits" is the main category and it has an index of 01. The banana blongs to Fruits and its index should be 0101. If bana would have a sub category you should index it as 010101... goes on like that...
I sugges you not to use a field as Number but text. That would make it easier to call back the main index.
What a curious mind needs to discover knowledge is noting else than a pin-hole.
|
|
|
|
|
I have a a couple of reports that are viewed using the reporting services report viewer control on a aspx page built with visual studio 2003. The report executes just fine. The problem is that the online page count will state a number like 5 but when you print it the real page count is 10. The online count is always off by about half. How can I make the online count be the correct number?
Thanks,
Patrick
|
|
|
|
|
Hi ..
the project is running ok ,, after only one pc restart ,, when I reRun the project the new message Is appear :
an error has occured while estaplishing a conneciton to the server ...
I use the next connection to the server ..
Public SqlCon As SqlConnection = New SqlConnection(" workstation id=localhost; Password=123; packet size=4096;user id=sa;data source=PcServer;Network=DBMSSOCN;persist security info=False; persist security info=False; initial catalog=DataB ")
that connection is working on the network as good ..
when I change the connection to be :
Public SqlCon As SqlConnection = New SqlConnection("workstation id=DESKTOP;Password=123; packet size=4096;user id=sa;integrated security=SSPI;data source=PcServer;persist security info=False;initial catalog=DataB;")
the project is running as good ..
i'm why .. the first connection was good , and it solved the problem of running the program on the LAN ,,
the second connection can't work on the LAN,,
now what can I do ,, If I use the seconed connectoin the program willn't work on the LAN ,,
it's sure that there're alot of programmers may have faced a poroblem like that >>
(
I founded that When I add Network=DBMSSOCN to the connection string the project can't run ..
-- modified at 5:01 Sunday 25th February, 2007
jooooo
|
|
|
|
|
And what is your question?
Wout Louwers
|
|
|
|
|
hi to all!!
i want to make database connectivity of say sqlserver or oracle with a language like java
kindly tell me the basic steps to do for a connectivity.
thanks a lot in advance.
|
|
|
|
|
|
Hi,
I have made a trigger on inserting a record in a table in oracle, but the problem is that if trigger fails, then insertion transaction is aborted and rolled back, is it possible to specify some attribute to the trigger so that the transaction is not aborted.
Warm Regards,
Mushq
|
|
|
|