|
Can someone please help show me how to combine 2 unrelated tables? for example
I have:
TABLE A with values
DESC
DM
ED
and TABLE B with values
DATE
01/07/2008
01/14/2008
02/18/2008
I want TABLE C to look like this
DESC -- DATE
DM -- 01/07/2008
DM -- 01/14/2008
DM -- 02/18/2008
ED -- 01/07/2008
ED -- 01/14/2008
ED -- 02/18/2008
Thanks..
|
|
|
|
|
I tried this and it worked..
Select desc, date
from TableA, TableB
|
|
|
|
|
SELECT * FROM tableA
CROSS JOIN tableb
|
|
|
|
|
I used the crystal report wizard to connect a Ms SQL Server. During the procedure the wizard requested for the username and password to the database.
However, when i run the application and want to pull up a report the page request for the database password.
What am i doing wrong?
JESA
|
|
|
|
|
Does anyone know of, or can point me in the direction of a database address schema? Ideally I would like a schema that can handle addresses throughout the world.
My current design uses the lowest element (excluding street address) in the address as one location id (eg in Australia it would be the suburb and I can then recurse to find postcode, state and country.
In (say UK), this doesn't work too well as the postcode is the lowest element in an address followed by city, county and country (and no doubt other variations). I'm guessing some other countries have more elements in the address as well, so it would be nice to be able to handle them as well.
|
|
|
|
|
What do you mean by 'lowest element' ? I'd look at a few sites like Amazon, those guys have solved the issue of a generic address, I am willing to bet.
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 )
|
|
|
|
|
I had a look at Amazon and its very basic. They must rely on server power rather than elegent design. There is no difference between data in different countries. Ebay is much better, groups by country and then county/state/province etc. Doesn't appear to then divide into city and locality though, so although its part way to what I want, its seems to be less than my current design.
When I say lowest element, I think the official term is granularity, eg in Australia, my address is 'my place', THORNTON, NSW, 2322, Australia. Thornton is the most granular element (apart from the actual street address) which is a member of postcode(2322) which itself is a member of state (NSW) et seq.
In contrast the UK addresses most granular element is the postcode followed by region/city, county et seq.
Perhaps I'm just getting too picky!
|
|
|
|
|
Not quite mate 
Postcodes are seperate to locales (thornton). If you grab the data from auspost you'll find that while similar, postcodes can span multiple locales and locales can span multiple postcodes.
|
|
|
|
|
Thanks - appreciate the info.
I had a look at the data and there are indeed many instances where there is the same locality name spanning different postcodes, however, when you look at the reasons for their existance, then each one is actually a different physical location. In most cases this seems to be semantic although technically a different location. Examples are mainly for a large organisation to sort mail eg to differentiate say general mail, competition entry mail and perhaps parcels which are delivered to (most likely) the same building.
I couldn't see any instance of a locale (if you include the delivery office and category + state) spanning multiple postcodes, although I have to admit I only scanned the data. I'll run some queries on it later and post the results, although I guess you have already done this at some point 
|
|
|
|
|
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.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
