|
|
OK, I think this must be possible, but my head is melting and running out my ears, any help appreciated. I have table structure like this:
Table: Statement
Columns: ID (PK)
Name
TypeCode
PeriodId
Table: Heading
Columns: ID (PK)
StatementID
Name
Table: Line
Columns: ID (PK)
HeaderId
Name
These three tables make up a hierarchal 'tree' of data for a statment (structure simplified for this example). Each statement has a period represented by an id, and there will be a number of statement types for each period. What i need to do is copy each statement for a period and all of it's children to a new period.
After creating the new statements in STATEMENT table (simple), I've been able to do this:
INSERT INTO Heading (STATEMENTID, NAME)
SELECT SNEW.ID,H.NAME
FROM HEADING H
INNER JOIN STATEMENT S
ON H.StatementID = S.ID
LEFT OUTER JOIN STATEMENT SNEW
ON S.TYPECODE = SNEW.TYPECODE
AND SNEW.PeriodID = @NewID
WHERE S.PeriodID = @PrevId
Which inserts all the heading children appropriately. I can't seem to get the join syntax correct though to go down and do the same for the lines, closest i've got is this:
SELECT HNew.ID,L.NAME,
FROM LINE L
INNER JOIN HEADING H
ON L.HEADINGID = H.ID
INNER JOIN STATEMENT S
ON H.StatementID = s.ID
LEFT OUTER JOIN STATEMENT SNEW
ON S.TYPECODE = SNEW.TYPECODE AND SNEW.PeriodID = @NewId
LEFT OUTER JOIN HEADING HNew
ON HNew.StatementID = SNEW.ID
WHERE F.PeriodId = @OldId
This returns too many records. I know my join has gone wrong somewhere, but i can't see it... 
any help would be appreciated.
|
|
|
|
|
What are some of the rows in the STATEMENT table?
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
I spent some time on this today, and i don't think the kind of query i want is possible with the current table structure, but thanks for looking...
|
|
|
|
|
Hi all,
I need a sql statement that returns all my Items and Categories from a database. I was thinking of using an inner join statement
SELECT tblCategories.CategoryID, tblCategories.CategoryName, tblCategoryItems.ItemID, tblCategoryItems.Item
FROM tblCategories INNER JOIN
tblNews ON tblCategories.CategoryID = tblCategoryItems.CategoryID
But this returned the category more then once.
This is how my current database looks like.
SQL table tblCategories have fiels (CategoryID, CategoryName)
SQL table tblCategoryItems have fields (ItemID, CategoryID, Item, Price, SellDate, Supplier Approved)
tblCategoryItems.CategoryID = tblCategories.CategoryID
I want to bind these to a datalist, with repeatcolumns=2.
Also each Item has to go under the Category. I must not display the same category more then once. For say there is 5 items for the Frozen Food category, I must display the Frozen Food category, and under that the 5 items.
Need something like this:
Row 1, Column 1 "Frozen Food" with Items "Mince, Chicken"
Row 1, Column 2 "Vegatables" with Items "Carrots, Brocolli"
Row 2, Column 1 "Fruit" with Items "Apples, Peaches, Bananas"
Row 2, Column 2 "Dairy" with Items "Milk, Cheese"
|
|
|
|
|
Relational databases don't act like that. You will need to process the returned data using a language like C# or VB, or a stored procedure.
|
|
|
|
|
Hi!!
I'm trying to connect to a remote server using SQL Server Management Server. I'm from South Africa and the website is hosted in the States. We have 2 databases hosted there, apparently on the same server. I can connect to the one, but the other server keeps on telling me "Microsoft SQL Server, Error 53". It just fdoes not want to connect. I can connect from the website http://sql.glodns.net.
I need to export a list of users from the database to my boss, but I'm struggling to log in. I tried exporting the data from glodns but it won't work, it times out the whole time.
I desperately need help.
Regards,
ma se
|
|
|
|
|
Hi!
Make sure that you are using TCP/IP as your network protocol. You can configure that using SQL Server's Client Configuration Tool (cliconfg) or by specifying the protocol in your connection string. In SQL 2005 you can specify the protocol when connecting using management studio.
As the server's admins for the correct IP port on which their server is listening. Default is 1433 - maybe they have changed it. It could also be possible that they block external access to their sql servers.
Last but not least make sure that the support sql server authentication. If they restrict access to windows authentication you will not succeed.
Regards,
Rainer.
Rainer Stropek
cubido business solutions gmbh
Email r.stropek@cubido.at
Visit my blog at http://www.cubido.at/Blog/tabid/176/BlogID/4/Default.aspx
|
|
|
|
|
can u tell maximum length of
varchar()
nvarchar()
char()
nchar()
text
-
|
|
|
|
|
Assuming SQL Server 2000
varchar(8000)
nvarchar(4000)
char(8000)
nchar(4000)
text = 2Gb
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
hi, i have a program that loads an MS Access in the program. i have a country combobox and a list of countries are there by me from the database. now .. i want that the user can enter a new country if he want to from a combobox. can this happen or can i do something else?? i have a country in another table apart from the contacts table.
help me please
Adrian De Battista: .Net Programmer, Java Programmer and Web Designer.
|
|
|
|
|
i have 2 tables. TABLEA and TABLEB. the structures of both the tables are same. the columns are "branch_name" , "book_ISBN" and "book_name".
branch_name is the primary key in both the tables
the data in the tables are
TABLEA has data as
branch_name book_ISBN book_name
book1 123 vishu
book2 456 vishu
TABLEB has data as
branch_name book_ISBN book_name
book1 123 vishu
book2 567 vishu
as you can see, the 2nd row in both the tables has mismatch in "Book_ISBN" column.
i used a query to find the mismatch in data
SELECT A.branch_name,
CASE WHEN A.branch_name=B.branch_name THEN 'Y' ELSE 'N' END as "Comp_Branch",
A.book_ISBN,
CASE WHEN A.book_ISBN=B.book_ISBN THEN 'Y' ELSE 'N' END as "Comp_book_ISBN",
A.book_name,
CASE WHEN A.book_name=B.book_name THEN 'Y' ELSE 'N' END as "Comp_book_name"
FROM Tablea AS A
LEFT OUTER JOIN Tableb AS B ON A.branch_name = B.branch_name
the result came as
book1 Y 123 Y vishu Y
book2 Y 456 N vishu Y
which shows that "456" of TABLEA is not matching with "567" of TABLEB
now if i want that only the rows having "N" (means mismatch) should be displayed, then how to do this in the above comparison query ?
Please reply ASAP
Vishal
|
|
|
|
|
You've got a number of options.
First of all, you could use the comparisons that are in your case statements as a where clause ...
WHERE<br />
A.branch_name<>B.branch_name<br />
OR A.book_ISBN<>B.book_ISBN<br />
OR A.book_name<>B.book_name
Or you could make your original query into an in-line table and select from it:
Select * FROM<br />
(<br />
SELECT A.branch_name,<br />
CASE WHEN A.branch_name=B.branch_name THEN 'Y' ELSE 'N' END as "Comp_Branch",<br />
A.book_ISBN, <br />
CASE WHEN A.book_ISBN=B.book_ISBN THEN 'Y' ELSE 'N' END as "Comp_book_ISBN",<br />
A.book_name,<br />
CASE WHEN A.book_name=B.book_name THEN 'Y' ELSE 'N' END as "Comp_book_name"<br />
FROM Tablea AS A<br />
LEFT OUTER JOIN Tableb AS B ON A.branch_name = B.branch_name<br />
) Comparison<br />
WHERE Comp_Branch = 'N' or Comp_book_ISBN = 'N' or Comp_book_name = 'N'
If you have multiple uses for this information, (e.g. sometimes you just want to see the comparisons where the ISBNs are different) then you may want to create a view from your original query and then select from the view:
Select * from COMPARISON_VIEW<br />
WHERE Comp_Branch = 'N' or Comp_book_ISBN = 'N' or Comp_book_name = 'N'
Jim Conigliaro
jconigliaro@ieee.org
-- modified at 8:11 Friday 19th May, 2006
|
|
|
|
|
If we use SQL Exception in DA Class
sqltransacrion trans=new ...
try
{
call SP
trans.commit
}
catch(sqlException ex)
{
ex.msg
trans.rollback
}
or
//SqlConnection con = new SqlConnection();
//SqlCommand com = new SqlCommand("procname", con);
//com.CommandType = CommandType.StoredProcedure;
//com.Parameters.Add(@Arg, SqlDbType.Text).Value = @Arg;
//com.ExecuteNonQuery();
create proc procname @Arg
as
insert into values..
if(@@Error<>0)
raiserror(...)
what is the difference in the errors raising in the above two calls
what is the difference between the error throwing
-
|
|
|
|
|
Hello,
In SSMSE I mistakenly ran a create table query on my database in SQL Server 7.0. I then launched SQL Server Enterprise Manager, and noticed that one of my databases are greyed out with (Suspect) labeled beside it.
I checked C:\MSSQL7\Data to see if my database was present, with the right file size.
How can I enable that database?
Thanks in advance.
|
|
|
|
|
(Is it interveal or interval or something else?)
Anyway, I have a database, and I was wandering if it is possible through SQL to substract 2 dates from the cells in the tables.
eg. I have a start time and end time, and I want to know what's the time that between... how much time did it take etc...
My teacher has showed me something with Sum and CDate SQL Methods, but I want a second opinion (and even if some better ideas).
Thanks alot...
NaNg.
|
|
|
|
|
If your database is in Microsoft's SQL Server, then look up DATEDIFF().
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
I use access in the meanwhile
|
|
|
|
|
It's DateDiff() there too:
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
The DateDiff function syntax has thesenamed arguments:
Part Description
interval Required.String expression that is the interval of time you use to calculate the difference between date1 and date2.
date1, date2 Required; Variant (Date). Two dates you want to use in the calculation.
firstdayofweek Optional. Aconstant that specifies the first day of the week. If not specified, Sunday is assumed.
firstweekofyear Optional. A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.
Settings
The intervalargument has these settings:
Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second
The firstdayofweek argument has these settings:
Constant Value Description
vbUseSystem 0 Use the NLS API setting.
vbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday
Constant Value Description
vbUseSystem 0 Use the NLS API setting.
vbFirstJan1 1 Start with week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days in the new year.
vbFirstFullWeek 3 Start with first full week of the year.
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
|
I am working on a project and to test I would like to make a copy of the SQL database just so I don't mess up the original data. How can I do this?
|
|
|
|
|
Do a complete backup of the database then restore it with a different name.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Create a database with new name and export all data of existing database to new one.
Saqib
|
|
|
|
|
But if you do that you don't get the stored procedures, UDFs, users, etc. In other words, it is not a copy of the database, it is just a copy of the data.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
get backup of existing database, create new database and restore that backup to new created database, this would now contain all things in it.
Saqib
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
