|
My answer to your question is that there is no really elegant schema as by creating database fields one generalizes and there will be specific instances that do not fit into that generalization.
Here is a schema I use and has worked well for years:
Name,
Address1,
Address2,
Address3,
Region,
ZIP,
Country
Because the schema is a generalization the work has to be done at the reporting level i.e. moving data up one line if a column contains a null value.
Also the data is only as good as the people typing it in - so if someone types a country into the region column there is not much you can do.
You always pass failure on the way to success.
|
|
|
|
|
Hello all,
I am a SQL newbie, so it's not obvious to me what I am doing wrong. Any help is very much appreciated. I am working in a stored procedure, and I want to run a T-SQL UPDATE statement if a certain field comes back as being NULL after doing a SELECT, or a different UPDATE otherwise.
What I have so far is:
ALTER PROCEDURE myProc
-- ...
32 IF @add_new_field = '1' --@add_new_field is a BIT parameter
33 BEGIN
34 IF (SELECT man.[notes] FROM [manhours] man WHERE man.[log_number] LIKE @log_number) IS NULL
35 UPDATE [manhours] SET [notes] = @notes WHERE [log_number] LIKE @log_number
36 ELSE
37 UPDATE [manhours] SET [notes] = [notes] || ' ' || @notes WHERE [log_number] LIKE @log_number
38 END
where the numbers on the left are line numbers. When I hit 'Execute' the following error occurs:
Msg 170, Level 15, State 1, Procedure uspSaveNotes, Line 34
Line 34: Incorrect syntax near '|'
I am a SQL newbie, so it's not obvious to me what I am doing wrong. Any help is very much appreciated.
Sincerely Yours,
Brian Hart
Department of Physics and Astronomy
University of California, Irvine
|
|
|
|
|
Brian Hart wrote: UPDATE [manhours] SET [notes] = [notes] || ' ' || @notes WHERE [log_number] LIKE @log_number
This syntax isn't valid in T-SQL. The OR operator in T-SQL is OR. Also, T-SQL doesn't have short-circuit logical operators like C-based languages.
I'm not sure what you are trying to do with this line of code. What are you trying to set [notes] to in your UPDATE statement?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
If you are trying to concatenate strings, you can use the + operator.
|
|
|
|
|
Hi! everyone.
I have created on stored procedure "sp_SaveListingImages".
This procedure is use for inserting data into the database table based on value passed by user from .asp page.
From my asp page I pass all value for this procedure.
But when the procedure is execute don't know why but it get null value for some field!!
and when I try to print that value in my form it shows me value, but null in procedure.
How should I solve this problem?
Please Help me!
Thank You.
|
|
|
|
|
Please post the code that causes the problem, how can we tell the problem without looking into the code?
|
|
|
|
|
Please read the post on top of this forum about how to ask intelligent questions.
yunussheikh2007 wrote: as soon as possible!!!
When you're paying, you can set a deadline.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Hi
Are you concatenating values of multiple fields in your procedure? In that case, if one of the fields is null, concatenating with any other field (even if the second does contain some value) will result in a null.
HTH
Chandra
|
|
|
|
|
Hi All,
Could you please let me know how to set up a trigger for the following senario?
How could i setup a trigger to populate a back_up table every time i do an Insert on the master Table.
//--------------------------------------------------------------
INSERT INTO [repor32].[dbo].[Master_table] SELECT * FROM [repor32].[dbo].[Back_up] WHERE [ADETDATE] NOT IN (SELECT [ADETDATE] FROM [repor32].[dbo].[Master_table])
Thank you
|
|
|
|
|
I'm not sure that I understand the scenario here. Is this the query that you will use in the trigger? or is the query that makes the insert in the master?
amyway, check the CREATE TRIGGER[^] syntax from msdn
|
|
|
|
|
I have a SQL question if any of you have time:
I want to do an update using two tables. I used to know how to do this but I've forgotten.
Put simply I havw two tables, Cars and UnMatched
Cars:
ModelCode number (Primary Key)
FuelType text
UnMatched:
ID number (Primary Key)
FuelType text
Cars has lots of rows, Unmatched has less.
I want to update Cars setting the FuelType to the value of FuelType in Unmatched, where the ModelCode in Cars = the ID in UnMatched.
So for example if Cars had the following Rows:
10 ""
11 ""
12 ""
13 ""
And UnMatched had these rows
11 "PETROL"
13 "DIESEL"
14 "HYBRID"
You'd end up with Cars being
10 ""
11 "PETROL"
12 ""
13 "DIESEL"
I know it's some kind of correlated subquery but I can't remember what it is.
This is on an Access database but I'd imagine it would be the same on others.
Thanks - Ben
|
|
|
|
|
In SQL Server you would do
UPDATE Cars
SET FuelType = UnMatched.FuelType
FROM UnMatched
WHERE ModelCode = UnMatched.ID It acts rather like a join but you don't qualify the names of columns for the table you're updating with the name of the table.
However, I don't think this is supported by Jet.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Hi all
i want to convert char data to datetime but it dosent give me reqd output
select convert(char(8),fieldname,108) as myTime from TableName
fieldname datatype is char(8) it contain values like 1004,1200,1354
required outpot as 10:04,12:00,13:54
i used above expression but it doesn`t convert data in hh:mm:ss
plz help
The Stifler
--
Bugs can neither be created nor be removed from software by a developer. They can only be converted from one form to another. The total number of bugs in the software always remain constant.
|
|
|
|
|
This will show the result in the required format, but it does not convert it to datetime:
Select substring(fieldname,1,2) +':'+ substring(fieldname,3,2)
|
|
|
|
|
Hi Friends,
How are you,i have one doubt,i made a wrong update to my table and now i want to rollback the transcation,i have used sql query analyser.
Kindly please tell me the solution to rollback.
Thanks & Regards
Senthil Kumar
|
|
|
|
|
Do you have any backups before the change?
If no, I'm afraid this won't be possible.
|
|
|
|
|
You're misunderstanding what a transaction is.
You can only rollback the transaction if the transaction hasn't been commited yet. If you used the Query Analyer to make this change using an UPDATE query, it's done. The transaction was started, the change was made, and the transation automatically committed when you clicked on the Execute button.
AFAIK, there is no way to roll this back without restoring the database from backup.
|
|
|
|
|
I have to tables MASTER_ACCOUNTS and DEPOT_ACCOUNTS. I need to UPDATE the MASTER_ACCOUNTS table with the values in DEPOT_ACCOUNTS where the ACCOUNTID columns match. Both table structures are the same.
Not sure how I should achieve this, so any help would be appreicated.
Thanks
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
The long way
update master_accounts
set master_accounts.a = de.a,master_accounts.b = de.b,etc...
from master_accounts
join depot_accounts de
on master_accounts.acountid = de.accountid
Or if you are feeling like throwing caution to the wind:
Another thing you can do is delete all the rows in master_accounts that have a matching accountid in depot_accounts.
Keep a record of these accountids in a temp table first.
Then insert all the rows in depot_accounts that correspond, to the accountids in the temp table, into master_accounts.
Since the table structures are the same this will mean that you do not need to type all those = statements.
You always pass failure on the way to success.
|
|
|
|
|
I was hoping there would be a better, more eligent way, like the INSERT INTO aTable SELECT * FROM bTable.
Ho well! Thanks for educating me.
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
You would have the issue of duplicated records if you ran the query this way.
Instead of insert you would have to use update and this requires column names to be specified.
This is because insert does not replace records it appends records.
Although SQL is a declarative language you still need to do a lot of work in writing the queries 
Microsoft are apparently developing new declarative languages so in the future things may be easier...
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
|
Hi I need to write a function in SQL that returns the date of the previous Monday, for example:
if today is Tuesday thru Sunday then get previous Monday
else if today is Monday get todays date.
Any help would be appreciated.
Thanks.
|
|
|
|
|
I actually got this to work so I thot I'd share...
ALTER FUNCTION [dbo].[fn_GetMonday]
( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
DECLARE @Result DATETIME
SET @pInputDate = CONVERT(VARCHAR(10), @pInputDate, 111)
IF DATENAME(dw, @pInputDate) = 'Sunday'
BEGIN
SET @Result = DATEADD(DD, -6, @pInputDate)
END
ELSE
SET @Result = DATEADD(DD, -DATEPART(DW, @pInputDate) + 2, @pInputDate)
RETURN @Result
END
|
|
|
|
|
I am no expert and there may be a better way of doing it, but have a look at DATEPART and DATEADD functions along with a WHILE loop.
DECLARE @MyDate DATETIME<br />
SET @MyDate = GETDATE()<br />
WHILE (DATEPART(dw, @MyDate)) <> 'Monday'<br />
BEGIN<br />
SET @MyDate = DATEADD(day, -1, @MyDate)<br />
END
MyDate should now contain the date of the previous monday or today's date if today is a Monday.
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
I have an application in which I fill a listbox with values from a dataset. But the listbox is filled with 'system.data.datarowview' here the values are displayed correctly.But when the page is load for the 1st time tha list box showing 'system.data.datarowview'after click on dat dat coming..plz help me
i ve writen the code like
con.Open();
SqlDataAdapter adp= new SqlDataAdapter(lstrsql,con);
DataSet objds=new DataSet();
adp.Fill(objds);
lst_emp.DataTextField=objds.Tables[0].Columns["txtval"].ToString();
lst_emp.DataValueField=objds.Tables[0].Columns["emp_id"].ToString();
lst_emp.DataSource = objds;
lst_emp.DataBind();
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
