|
I have a join on two tables, for document header and line data:
select * from DocumentHeader dx
inner join DocumentLine ix on ix.DocID = dx.DocID
I need to get sequential numbering for all DocumentLine records, as below, where all <H> lines are from table DocumentHeader and all <L> lines from DocumentLine. Note how the numbering does not restart for each new document header.
"X","@<H>","","R","2342",2008.02.29,2008.05.18,"",5016,"ZAR"
"X","@<L>",1,290002,2508
"X","@<L>",2,290002,2508,
"X","@<H>","","R","2342",2008.02.29,2008.05.18,"",2508,"ZAR"
"X","@<L>",3,290002,2508
Any ideas of how I could achieve that in a query? BTW, I'm not asking for my work to be done, I am also hard at work trying to find a solution to this.
Semicolons: The number one seller of ostomy bags world wide. - dan neely
|
|
|
|
|
Solved! I create a temp table called #LineNumber with an identity column, LineNumber for each DocItemID in DocumentLine and just join to the temp table in my main query.
Semicolons: The number one seller of ostomy bags world wide. - dan neely
|
|
|
|
|
If you are using SS2005 then look at the row_number (also rank and partition) function. It will allow you to do this without the temp table.
Also look at a table variable, you can have the same identity field without having to create the table in the temp DB.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks! row_number() looks perfect. ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
Semicolons: The number one seller of ostomy bags world wide. - dan neely
|
|
|
|
|
hi
i have a job that scheduled to perform a specific transaction
so i want to change the command of this job by programming
but when i set the value of a cmd(SqlCommand object) and call cmd.ExecuteNoneQuery(); nothing happens but when i run that query by query analyzer that do corrctly.
how can i do this action programmatically???
nobody help you...
you have to help you yourself
and this is success way.
|
|
|
|
|
Hi all,
I'm using OleDbCommandBuilder to build commands for an OleDbDataAdapter automatically
The problem is when using OleDbDataAdapter to retrieve data from a "View" in a SQL Express database
Ex: OleDbDataAdapter("select * from ViewName", ConnectionString);
The generated CommandText for generated commands is invalid, it looks like this:
InsertCommand ex:
INSERT INTO D:\\FOLDER_NAME\\DATABASE_FILE_NAME.MDF.dbo.TableName
DeleteCommand ex:
DELETE FROM D:\\FOLDER_NAME\\DATABASE_FILE_NAME.MDF.dbo.TableName
UpdateCommand ex:
UPDATE D:\\FOLDER_NAME\\DATABASE_FILE_NAME.MDF.dbo.TableName
Where TableName is the source table of the "View" !!!
Ofcource executing command causes an error of "Incorrect syntax near 'D:'"
Notice that, this problem doesn't occur with tables, only with views !!
Also problem doesn't occur with SqlCommandBuilder, but I need to use OleDbCommandBuilder because I'm dealing with multiple database providers
Please help
modified on Sunday, May 18, 2008 6:45 AM
|
|
|
|
|
|
Flight_ID Passenger_ID Seat_Number Seat_Cost Seat_Class
1 3 j15 $112.00 Business
2 5 K16 $38.00 Economy
3 4 A12 $85.00 Economy
4 2 B05 $40.00 Economy
5 1 C08 $85.00 Economy
6 5 B12 $85.00 Economy
8 7 K17 $42.00 Economy
9 6 L25 $125.00 Business
I need to be able to allow duplicated in the Flight_ID which is the primary key so i can allow more passengers to be able to "fly" on that flight
 iD
|
|
|
|
|
You can't have duplicate primary keys, it kind of defeats the entire purpose of having it as a primary key. A primary key needs to be unique.
What you can do instead is make a combined primary key consisting of multiple columns or revisit whether you need/want that column as a primary key to begin with.
Considering the snippet of your table you have posted, I would think that a combined key of Flight_ID and Passenger_ID (A specific passenger on a specific flight should all things equal be unique.) would be the best way to go.
|
|
|
|
|
To allow adding multiple passagers on one flight then make combination of primary key with Flight_id and Passager_ID
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Your table design sucks - big time.
You should have the following tables to get this data
Flight - FlightNo
Passenger - PassengerID
Seat - SeatNo
Cost - CostID
Transaction
TranID
FlightNo
PassengerID
SeatNo
CostID
Your primary key Flight_ID is NOT the primary key for the transaction table as you are attempting to make it!
Recommendation you REALLY need to get a book on data structuring before adventuring into this area, you will save yourself months of heartache. Even if you get this working for flight 1 (it is possible for 1 flight) as soon as you add another flight or another plane design or want to change the cost of a class of seats you are in trouble. GET A DESIGN BOOK - READ IT.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
Composite key on Fight_ID and Passenger_ID that might cause same problem in following scenario.
If the same passenger let say 101 is booking fight 1001 for two different dates.
101 1001 $112.00 Business 5/3/2008
101 1001 $38.00 Business 7/3/2008
So best sol is to add identity column ( i. e TransactionID) in this case.
|
|
|
|
|
Ok, I have this very bizarre problem. I have a table with these columns: id, created_date, status. For some weird and stupid reason, the DBA decided that both id and created_date are keys. So, say that we want to change the status for id=1. You'd think that an update would be a nice idea. Wrong. It must insert another record, same id, different created_date, and the new status. Ok, if boss wants it that way, so be it.
The thing is that now, I'm requested to create a query (returned from a stored procedure) that will return me this fields, BUT if an id has two records (say, one with 'Active' status and the other one with 'Inactive' status, I should keep only the 'Inactive' record.
As an example, let's say that in the table we have the following data:
ID Created Status
================================================
1 2008-05-10 Active
2 2008-05-11 Active
2 2008-05-12 Inactive
3 2008-05-13 Inactive
then, the query should show the first line, the third line (because there are two rows with the same ID, being the second line the discarded because the status is 'Active'), and the fourth line.
Any ideas will be really appreciated, as all I've thought is useless so far.
Stupidity is an International Association - Enrique Jardiel Poncela
Die deutsche Sprache sollte sanft und ehrfurchtsvoll zu den toten Sprachen abgelegt werden, denn nur die Toten haben die Zeit, diese Sprache zu lernen. - Mark Twain
|
|
|
|
|
here it is:
select distinct id ,<br />
(select top 1 t1.status from myTable as t1 where t1.id = myTable.id order by t1.status desc) as Status,<br />
(select top 1 t2.created from table3 as t2 where t2.id = myTable.id order by t2.status desc) as Created<br />
from myTable
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
That was nice! Didn't think of using that top and those selects. It worked perfectly. Thanks a lot.
Stupidity is an International Association - Enrique Jardiel Poncela
Die deutsche Sprache sollte sanft und ehrfurchtsvoll zu den toten Sprachen abgelegt werden, denn nur die Toten haben die Zeit, diese Sprache zu lernen. - Mark Twain
|
|
|
|
|
T-SQL is very cool, You are welcome...
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
I created a search stored procedure that accepts a parameter to perform the search with. I need to use this parameter with the LIKE and use also the '%" if it is possble . This is the code that i am using
WHERE (tbl.User LIKE '%'+@parameter+'%')
is it good?
thanks
|
|
|
|
|
Yes it seems good, but test it and you will be sure if it works properly.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
I would be more inclined to do the following:
WHERE (tbl.User LIKE LTRIM(RTRIM(@parameter))+'%')
Particularly if the parameter is from user input as:
-There may be white space at the beginning or end of the parameter.
-As it is a User you are looking for it may be better not to have the % at the beginning as you may get User(s) returned that contain the parameter within them rather than at their start.
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
|
|
|
|
|
hi,
how i use .asmx web service with sqlServer user defined function?
best regards.
|
|
|
|
|
You do a select, just like a columns
select dbo.udf_function(.....)
Bob
Ashfield Consultants Ltd
|
|
|
|
|
dbo.udf_function(how connect asmx web service. please can you write how to connecting asmx web service code here )
|
|
|
|
|
i want to use the database which is in the variable. I try the following codings but it is not work. Please help me this is argent.
declare @s as type
set @s='Nothwind'
Use @s
ssss
|
|
|
|
|
DECLARE @QueryValue varchar(1000) <br />
<br />
DECLARE @databasename sysname <br />
SELECT @databasename = 'mydatabase' <br />
SELECT @QueryValue = 'Select * from ' + @databasename + '.dbo.TableName'<br />
<br />
EXEC (@QueryValue )
hope it will helps
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
I am using MYSQL for generating some reports using LogiXML.The problem is that date format in mysql is yyyy-mm-dd and i need this to be converted to UK Format(dd/mm/yyyy).In report i am inputting date in dd/mm/yyyy and should return value matching corresponding dates.If i'm directly querying a value with date like
select DATE_FORMAT(dateIn, '%d/%m/%Y') as 'Date Arrived' from Employee where DATE_FORMAT(dateIn, '%m/%d/%Y') BETWEEN
DATE_FORMAT('2008-04-23', '%m/%d/%Y') AND DATE_FORMAT('2008-05-13', '%m/%d/%Y') is returning records which are not between these dates.
This is very urgent
Thanks
Anup
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
