|
i have 2 column in sqlserver that their type is datetime. i want formula column b and thats formula be this: column a - 48hours = column b
i dont know how should i do this?
|
|
|
|
|
Ok I assume you have a history of Access development. SQL Server does not support these columns.
There is not requirements to store the column, you have a number of choices, create a view on the table and add the cacled col or calc the data when you store the information if you require to store it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Add a computed column like:
ALTER TABLE WhatEverTableName ADD ColumnB AS (ColumnA-2);
If you want to store the result in the database (not mandatory) add PERSISTED keyword.
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I hav a table emp in which 3 records contain same data...means im not using any primary constraint soooo in all the 3 rows the data is same.
My question is how to delete the other 2 rows from the table and will it be applicable if the case is like......
i hav duplicate rows for 2 different different records
|
|
|
|
|
You need to do it in steps.
select * into allrecs from table1 where 1 = 2 -- create any empty table
insert in to allrecs select * from table1 -- copy all your current data
truncate table table1 -- clear your table
insert into table1 select dustinct * from allrecs -- now you just have unqiue records in your table
drop table allrecs
You may wnt to keep the allrecs table until you are satisfied you have not lost anything that you wanted.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
can't we do without creating a new table.....
beacuse this question was asked in an interview soo i guess it is having some one command line answer....
aneways thanks for the answer
|
|
|
|
|
harsha_mec345 wrote: can't we do without creating a new table
Not that I know of - unless you export the data to a file and read it back in.
harsha_mec345 wrote: soo i guess it is having some one command line answer....
Why? I wouldn't expect 1 line answers.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I'm guessing that you're using Oracle (based on the table name).
If that's true and all the rows are identical, you can use rowid in delete statement. For example, if you want to leave the 'first' row, the statement could be:
DELETE FROM Emp
WHERE ROWID > (SELECT MIN(ROWID)
FROM Emp);
Correction: As Ashfield pointed out, I misunderstood the question. The statement above will delete all the records except one (for some reason I understood that all are duplicates).
If the table has both duplicate and non-duplicate rows, you can use correlated condition. The condition would be something like the following (all the fields from Emp must be included in correlation):
DELETE FROM Emp a
WHERE a.ROWID > (SELECT MIN(b.ROWID)
FROM Emp b
WHERE a.Field1 = b.Field1
AND a.Field2 = b.Field2...);
Sorry for the confusion,
Mika
The need to optimize rises from a bad design.
My articles[ ^]
modified on Tuesday, November 11, 2008 11:42 AM
|
|
|
|
|
Mika Wendelius wrote: DELETE FROM EmpWHERE ROWID > (SELECT MIN(ROWID) FROM Emp);
Mika, its not often I question your expertise, but won't this delete everything except the very first record in the table? Or am I missing a trick with Oracle (it is along time since I used it)
Bob
Ashfield Consultants Ltd
|
|
|
|
|
My 2 pesos.
Generally, you wouldn't want to do this, because of what your stating. There would typically be a mix match of good and duplicated records. (hence your post would be best suited in this scenario).
The original question stated that the table in question has 3 rows which all contain the exact same value without a primary key and to delete the duplicated rows; meaning only to keep 1 row - so Mika's answer is a little more concise and gets the job done.
Splitting hairs though...
Any suggestions, ideas, or 'constructive criticism' are always welcome.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
You're absolutely correct. Everything is deleted except 1 row. It may be that I've misunderstood the question!!!
Thanks very much for the correction, I really appreciate it! I'll modify the original answer (and perhaps learn to read  ).
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
It's not often I doubt your answer, it just didn't seem right - the new one is what I expected. It happens to us all from time to time - hopefully just after a backup
Bob
Ashfield Consultants Ltd
|
|
|
|
|
 Just did a full restore and created a FLASHBACK TABLE[^]... just in case. To fully correct the situation, I think that a personal reboot is required. 
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Friends
this is my first time in terms of designing a Distributed Database system software (A distributed POS). There are certain types of things I have to consider like Security most important, reliability, light in terms of resource etc..
Now It would be very help full if some one tell me how should I ensure top most security (Hack proof) designing this thing.
|
|
|
|
|
Thanks for all your help. have another question.
I have a database name star. My star database have 4 tables contact, units invoices and payments
in my contact table I have columns contact id Fname, LName, Address, City, State, zip, email.
in the invoice table I have columns invoice id, contact id, startdate, enddate, subtotal, total,
I wrote a little app from the help from here to migrate my data because SSIS 2005 was having problems with null value data in the contact table import perfectly and invoice imports as well but in but my contact id in the Contact table is not matching the contact id in the invoice table.
if I can get the a sample of some code in sql I can do it in c#
Learning to Code
|
|
|
|
|
It is called referential integrity and is a fundamental of database design, you need to understand it before you will be able to progress with this. You need to understand the use of foreign keys and identity fields.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Is there way for me to do this in c#.
Learning to Code
|
|
|
|
|
Hi to all,
In My SP i created this table
DECLARE @inc_table TABLE(ID INT identity(1,1),ModelName VARCHAR(20),Region VARCHAR(20),Tot VARCHAR)
BEGIN
INSERT INTO @inc_table (ModelName)
SELECT ModelName FROM SRO_ModelMaster
END
NOw my table is fill with all modelname say,
1 PPM
2 CCP
3 MMM
NOw in same table i want to add
RegionCode using following statment
INSERT INTO @inc_table (Region)
(SELECT RegionCode FROM cmp_RegionMaster WHERE regionCode<>'TR' )
but it is inserting at the 4,5,6, id levelI want to append it to
on same id that is 1,2,3
so that it gives me
1 PPM ER
2 CCP NR
3 MMM SR
How i can achive this in my sp
Thanks for all for yours kind suggestions.
|
|
|
|
|
If you want to update existing records, use UPDATE[^] statement, not INSERT.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi everyone,
It seems there is next to no help online anywhere for using the Odbc classes in the .Net framework with Oracle Lite. There is plenty of online info for full blown Oracle but that appears to be no use to me. Has anybody had any experience with this? My connection string as it stands is:
Driver={Oracle Lite 40 ODBC Driver};dsn=faultdb9i;UID=system;PWD=XXXXXXXXX;
but I get "POL-3203 Error: Database does not exist". It does exist, and I can connect to it fine using Oracles MSQL application. I have tried many different variations of connection string with no success - I've stuck with this one as the .net framework is at least catching an Oracle error so I must be getting close.
Is there some magic connection string parameter for use with Oracle Lite?
Many thanks,
Chris Chambers.
|
|
|
|
|
Are you sure about the error? Based on documentation POL-3203 means "Bad BLOB block address".
Also you could could try to use connection string like:
dsn=faultdb9i;UID=system;PWD=XXXXXXXXX (without driver info) or
Driver={Oracle Lite 40 ODBC Driver};server=faultdb9i;UID=system;PWD=XXXXXXXXXMika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi Mika,
Thanks for looking into that for me. The error was my mistake - a typo. I meant POL-3023 which really is "The database does not exist".
The Microsoft ODBCConnection class won't accept a connection string without a driver and I tried using the server item too but that didn't work either - still got POL-3023. After spending the best part of 6 hours researching and trying dozens of different variations of connection string I decided to give up on the System.Data.ODBC namespace and opt for the Oracle.DataAccess.Lite DLL direct from Oracle instead. Unfortunately you can only get your hands on it by downloading the 400MB+ Oracle 10g Lite installation package but, once I'd done that, it worked first time with:
dsn=faultdb9i;UID=system;PWD=XXXXXXXXX
Oh well, you win some, you lose some.
Cheers,
Chris.
|
|
|
|
|
That's odd. Normally it's enough that you specify a DSN if it's correctly defined in Control Panel. Driver is typically needed only if you create a DSN on-the-fly.
However, I believe that native client will be much better for you since it's more efficient and allows you to better use Oracle specific features.
About the client. I typically use full Oracle client when connecting to Oracle and I'm under the impression that it works against any Oracle database edition (starting from version 9i). Have you tried using Oracle Data Access Components (ODAC). If you have time, I think it's worth checking out. The download size is less than 50 megabytes and it supports xcopy deployment.
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Mika Wendelius wrote: That's odd. Normally it's enough that you specify a DSN if it's correctly defined in Control Panel. Driver is typically needed only if you create a DSN on-the-fly.
That's exactly what I thought, but I always got an Invocation exception when I didn't specify a driver. I know that the datasource is set up correctly because I have an older C++ application that uses good old-fashioned ADO that connects fine with just the DSN, the UID and the PWD.
Mika Wendelius wrote: Have you tried using Oracle Data Access Components (ODAC). If you have time, I think it's worth checking out.
I haven't, but I will certainly investigate it.
Thanks for your help Mika.
Regards,
Chris Chambers.
|
|
|
|
|
You're welcome
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
