|
I have a table that looks something like this
ID Site_Code Item_Code Some_Other_Stuff....
=======================================================
1 AA 123 ...
2 DD 234 ...
3 GG 567 ...
4 QQ 111 ...
4 FF 123 ...
My problem is this, I need to retrieve all the rows within a group of Site_Codes (basically all the Site_Codes that relate to say, North America), but for each site the Item_Code may reoccur. So, Item "123" appears both in "AA" and "FF" but I only want to retrieve one copy of "123" for the group. To make things a little more complicated though, "123" in "AA" and "FF" may (although they are the same item) have different values in the "Some_Other_Stuff" fields, so I have to decide whether I want the "AA" copy or the "FF" copy of "123".
So, ultimately what I want to do is set a priority order the the site codes. So I'd retrieve all rows with Site_Code = "AA" and then all rows with Site_Code in ("DD","GG",...) that have Item_Codes not in "AA". I'd image I could do this with some creative table joining, but what would be the most efficient way to do this? The database is on Oracle and unfortunately I don't have the option to change the design.
Thanks
|
|
|
|
|
wouldn't something like this work?
Select A.Site_Code, A.Item_Code
FROM MyTable A
WHERE A.Item_Code NOT IN (Select Item_Code FROM MyTable WHERE Site_Code='AA')
|
|
|
|
|
Something like that would work. In particular I can do this:
Select A.*
FROM MyTable A
WHERE A.Item_Code NOT IN (Select Item_Code FROM MyTable WHERE Site_Code='AA') AND Site_Code in ('BB','CC','DD')
UNION
SELECT * FROM MyTable WHERE Site_Code='AA'
And that mostly works. I get all the records with Site_Code='AA' plus all the records in 'BB','CC' and 'DD' with Item_Codes not in 'AA'. The problem, of course, is that there may be a few Item_Codes in 'BB' and 'CC' but not in 'AA', which means I get duplicate records for that Item_Code. There aren't very many of them, but they do exist.
I assume I could fix the problem with something like this (not yet tested):
Select * FROM MyTable WHERE Site_Code='AA'
UNION
Select A.* FROM MyTable A WHERE A.Item_Code NOT IN
(Select Item_Code FROM MyTable WHERE Site_Code = 'AA') AND Site_Code='BB'
UNION
Select A.* FROM MyTable A WHERE A.Item_Code NOT IN
(Select Item_Code FROM MyTable WHERE Site_Code IN ('AA','BB')) AND Site_Code='CC'
UNION
Select A.* FROM MyTable A WHERE A.Item_Code NOT IN
(Select Item_Code FROM MyTable WHERE Site_Code IN ('AA','BB','CC')) AND Site_Code='DD'
...
But that just seems absolutely horrible. Is there an easier way?
|
|
|
|
|
Even though it's another horrible option, You could create a temporary table that contains all site codes that are less than the current one.
I really think the problem is due to poor database design than anything else.
|
|
|
|
|
ednrgc wrote: I really think the problem is due to poor database design than anything else.
And I absolutely wouldn't disagree. But I only have limited leverage to try and use against the infrastructure people to get anything done. I'm not even sure if I have permission to create a temporary table. 
I'll probably have to end up doing a hybrid of table joins to try and catch most of the duplicates and then maybe prune out the remaining ones in code by iterating through the whole dataset. There are only a few thousand records to deal with (22K in all sites, about 8+K for all US sites).
|
|
|
|
|
Dear All,
i wanted to know is it possible to debug stored procedure as we debug in dotnet. As i have created following stored procedure to save the records.When
i Exec it it does not show any error but it does not insert the record to corresponding table. Please guide me
Stored procedure is
CREATE PROCEDURE [dbo].[servchargsav]
@branch varchar(20),
@serv varchar(20),
@scharge varchar(20),
@cname varchar(50),
@ip varchar(20)
AS
if @branch is not null
begin
if @serv is not null
begin
declare serv_cursor cursor for
select servicecharge from branchwisecharge where branchid = @branch and serviceid =@serv
open serv_cursor
fetch next from serv_cursor
if @@fetch_status = 0
begin
insert into branchwiseservice(branchid,serviceid,servicecharge,bcreatedby , bcreateddt,ipadd) values(@branch,@serv,@scharge,@cname,getdate(),@ip )
end
else
begin
update branchwisecharge set servicecharge = @scharge, bmodifiedby = @cname, bmodifieddt = getdate() where branchid = @branch and serviceid =@serv
end
close serv_cursor
deallocate serv_cursor
end
end
GO
regards
imran khan
|
|
|
|
|
You can debug a stored procedure with Query Analyzer
Wout Louwers
|
|
|
|
|
Try this version instead, which doesn't use cursors:
CREATE PROCEDURE [dbo].[servchargsav]
@branch varchar(20),
@serv varchar(20),
@scharge varchar(20),
@cname varchar(50),
@ip varchar(20)
AS
if @branch is not null
begin
if @serv is not null
begin
SELECT @Count = COUNT(1) FROM branchwisecharge
WHERE branchid = @branch and serviceid =@serv
-- A count of zero means we can't find this record.
IF @Count = 0
BEGIN
INSERT INTO BranchWiseService(branchid, serviceid, servicecharge,
bcreatedby, bcreateddt,ipadd)
VALUES (@branch, @serv, @scharge, @cname, getdate(), @IP)
ELSE
UPDATE branchwisecharge set servicecharge = @scharge, bmodifiedby = @cname,
bmodifieddt = getdate() where branchid = @branch and serviceid =@serv
END
END
END
GO
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks Pete, Thanks for your idea. I was unnessacesary using cursur
THanks once again.
regards
imran khan
|
|
|
|
|
That's a bit arcane. I'd use
IF EXISTS
( SELECT * FROM branchwisecharge
WHERE branchid = @branch AND serviceid = @serv )
BEGIN
INSERT ...
END
ELSE
BEGIN
UPDATE ...
ENDNote that IF/ELSE in T-SQL is like if/else in C: the IF only governs the immediately following statement, not everything up to the ELSE. If you want to put multiple statements after an IF, you must use a BEGIN/END block (analogous to {} in C). Stylistically, I always use a block after an IF for the same reason as in C - it can be easy to get confused about whether a statement is controlled by the IF or not.
|
|
|
|
|
I have a table with a bitmask value column and would like to select from this table where records have any match with another bitmask value.
For instance 3 database records:
ID, bitmask_value
1, 1
2, 3
3, 16
To check if a single bit is selected, I can:
SELECT * FROM table WHERE (bitmask_value & 1) = 1
(checks if bit 1 is selected - returns ID 1 and ID 2)
But I would like to get all records where a criteria bitmask value I define is selected, such as, user selects 1 and 2 = 3.
I could do this:
SELECT * FROM table
WHERE ((bitmask_value & 1) = 1 OR (bitmask_value & 2) = 2)
BUT if my criteria bitmask value includes several bits, can I query a different way to compare bitmask_value and criteria_bitmask, and get the proper results without checking each bit individually?
So given a criteria_bitmask value of 3... can I query using the value 3 somehow and get the following:
-any rows with 1 selected
-any rows with 2 selected
-any rows with 1 and 2 selected
-any rows with 1 and any others selected
-any rows with 2 and any others selected
-any rows with 1 and 2 and any others selected
I'm only filtering rows that DO NOT have a 1 or a 2 or both a 1 and a 2.
|
|
|
|
|
Sorry, some of your terminology is confusing me.
Leftyfarrell wrote: 1 and 2 = 3.
Actually 1 AND 2 = 0. Did you mean 1 OR 2?
Leftyfarrell wrote: SELECT * FROM table
WHERE ((bitmask_value & 1) = 1 OR (bitmask_value & 2) = 2)
What about:
WHERE (bitmask_value & 3) <> 0
bitmask_value result
0 0
1 1
2 2
3 3
4 0
5 1
6 2
7 3
8 0
etc.
|
|
|
|
|
I did mean 1 OR 2 selected. By saying 1 and 2 = 3, I meant that the search criteria is defined for me by a user selecting options, and if they select options 1 and 2, I need to pull anyone that matches 1 OR 2. Their interface assumes an OR condition on the options they select.
I believe what you have provided is what I need. I just need to test a bit more.
Thank-you.
|
|
|
|
|
I currently store wav binary data in my database, and I am wondering how to play the files on my website. The binary files are placed into the database through another VB.NET application. What I want to do is have an ASP grab those files from the database and play them for the user. I have tried using ADODB.Stream, but it doesn't seem to be working. I also can spit out all the binary data as plain text, but that doesn't play either.
|
|
|
|
|
In the .aspx file to play the sound in:
<object classid="clsid:22D6F312-B0F6-11D0-94AB-0080C74C7E95">
<param name="MyFile" value="LoadMyFile.aspx?FileID=123" />
</object>
In the LoadMyFile.aspx.cs file (sorry, I don't have an example in vb but the syntax should be similar).
int FileID = Convert.ToInt32(Request.QueryString["FileID"]);
byte[] SoundData = LoadSoundDataFromDatabaseAsByteArray(FileID);
Response.Clear();
Response.ContentType = "audio/wav";
Response.OutputStream.Write(SoundData,0,SoundData.Length);
Response.End();
The html content of the LoadMyFile.aspx is replaced by the content provided in Response.OutputStream.Write... (which in this case is your sound data). Changing the Response.ContentType to "audio/wav" lets the browser to expect wav data and not html.
Hope that gets you in the right direction
|
|
|
|
|
Chris Buckett wrote: the syntax should be similar
I think you mean semantics. Syntax refers to the grammar, while semantics refers to the meaning.
|
|
|
|
|
I bow to your obvious greater linguistic skills then 
|
|
|
|
|
How can I build this query for date input also:
str1 = string.Format("INSERT INTO Table1(ERROR, STATION, TIME) VALUES ({0},{1}, CONVERT(DATETIME, '2009-09-09 00:00:00', 102))",x,y) ;
I want that the date will be variable
thankss
|
|
|
|
|
You are injecting values into the SQL statement. This will compromise security of your application. See SQL Injection Attacks and tips on how to prevent them[^]. If I recall this was mentioned to you before[^].
By solving the security problem, you also solve the problem of formatting dates. Part of the solution to the security problem is to use parameters. This then removes the need for you to worry about how to format dates in SQL because you can pass a DateTime object as a parameter.
string sql = "INSERT INTO Table1(Error, Station, Time) VALUES (@error, @station, @time)"
SqlCommand cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.Parameters.Add("@error", x);
cmd.Parameters.Add("@station", y);
cmd.Patarmters.Add("@time", someDateTimeObject);
|
|
|
|
|
thanks,
I'm doing my first step in SQL so I'm trying
to do the simple things first...
|
|
|
|
|
Hi,
I didn't know where I should post this, but because it is ADO.NET, it would be the best place (but I have posted in the C# forum also).
Anyway, I have a question, I have created a datasource to the northwind database and created a simple windows form. With the first name, last name textboxes etc. and to select the employees I use the listbox with the employees last names in it. I have added an the TextBox_Validate Event to each textbox with the code:
<br />
private void TextBox_Validated(object sender, EventArgs e)<br />
{<br />
employeesTableAdapter.Update(northwndDataSet1.Employees);<br />
<br />
employeesTableAdapter.Fill(northwndDataSet1.Employees);<br />
}
Yet, when I change a name, and then tab or click somewhere else, it doesn't update the database with the new name.
What am I doing wrong?
Thanks,
Any reply is appreciated.
|
|
|
|
|
Hi - I added a new table. Tested on two different databases. One of the database had db_owner role checked for this user and inserting into this table worked. The other database didn't have this option checked. By default this option is not checked for our database.
Is there a way I could set permission on table level, just for this table?
thanks
|
|
|
|
|
In my stored proc, changed a DML "truncate" to use "delete" - it is now working.
|
|
|
|
|
Hi,
I want to know,How to know Store Procedure Input and Output parameters.
What I want.
When I pass any Store Procedure Name.it's return that Store Procedure Input and Output parameters.
Help me out
it's very arrgent.
Thanks
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
