|
I need to pull a FIELD value from multiple records from the same table based off of like fields from the records and I dont know how to construct such a query. Here is some sample data:
<br />
71N7R61 GX280 APL VER 1 8.0.0<br />
71N7R61 GX280 APL VDF 1 4417<br />
71N7R61 GX280 APL DRL 1 10/13/2005<br />
71N7R61 GX280 APL ACT 1 VirusScan Running<br />
71N7R61 GX280 APL DIN 1 05/05/2005<br />
71N7R61 GX280 APL NMM 1 Network Associates<br />
71N7R61 GX280 APL NMP 1 McAfee VirusScan Enterprise<br />
71N7R61 GX280 APL NMP 2 MarimbaEndPoint<br />
71N7R61 GX280 APL NMM 2 Marimba Inc.<br />
71N7R61 GX280 APL DIN 2 04/01/2004<br />
71N7R61 GX280 APL ACT 2 Marimba Running<br />
71N7R61 GX280 APL VER 2 6.0<br />
the first 2 fields doesn't matter... primary keys. Third is a component code field, fourth field is a code tells me what the data in the 6th field means, 5th field is the is a sequence number... so in the sample VirusScan is '1' in the sequence of 'apl' applications and marimba is '2'...
now... what i would really like... is a query that would spit out this:
McAfee VirusScan Enterprise VER 1 8.0.0<br />
McAfee VirusScan Enterprise VDF 1 4417<br />
McAfee VirusScan Enterprise DRL 1 10/13/2005<br />
McAfee VirusScan Enterprise ACT 1 VirusScan Running<br />
McAfee VirusScan Enterprise DIN 1 05/05/2005<br />
McAfee VirusScan Enterprise NMM 1 Network Associates<br />
MarimbaEndPoint NMM 2 Marimba Inc.<br />
MarimbaEndPoint DIN 2 04/01/2004<br />
MarimbaEndPoint ACT 2 Marimba Running<br />
MarimbaEndPoint VER 2 6.0
any clues?
-- modified at 18:12 Friday 14th October, 2005
using c#, asp.net, and sql2000 BTW
|
|
|
|
|
Joshua Lunsford wrote: the first 2 fields doesn't matter... primary keys. Third is a component code field, fourth field is a code tells me what the data in the 6th field means, 5th field is the is a sequence number... so in the sample VirusScan is '1' in the sequence of 'apl' applications and marimba is '2'...
You could join the table onto itself like this:
SELECT a.data, b.dataType, b.sequenceNumber, b.data
FROM #MyTable AS a
INNER JOIN #MyTable AS b ON a.pk1 = b.pk1 AND a.pk2 = b.pk2 AND a.sequenceNumber = b.sequenceNumber
WHERE a.dataType = 'NMP'
AND b.dataType <> 'NMP'
As you can see, I've aliased the table so that I can refer to it as "a" and "b" as if they were different tables.
I'm not sure of the importance of the 'APL' in the source data so I've ignored it. However, it may be important and would likely need referencing the join or where clause. The sample data you've given doesn't give any clues.
Also, the first two columns cannot make the primary key because they are all the same through the sample data - I suspect that the first 5 columns make the primary key.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
thats almost perfect... let me play with that.. ty
|
|
|
|
|
Here you go
CREATE TABLE dbo.ctm
(
Type varchar(32) NULL,
Indx int NULL,
Val varchar(32) NULL
)
go
Insert ctm Values('VER', 1, '8.0.0')
Insert ctm Values('VDF', 1, '4417')
Insert ctm Values('DRL', 1, '10/13/2005')
Insert ctm Values('ACT', 1, 'VirusScan Running')
Insert ctm Values('DIN', 1, '05/05/2005')
Insert ctm Values('NMM', 1, 'Network Associates')
Insert ctm Values('NMP', 1, 'McAfee VirusScan Enterprise')
Insert ctm Values('NMP', 2, 'MarimbaEndPoint')
Insert ctm Values('NMM', 2, 'Marimba Inc.')
Insert ctm Values('DIN', 2, '04/01/2004')
Insert ctm Values('ACT', 2, 'Marimba Running')
Insert ctm Values('VER', 2, '6.0')
select t1.Val1, ctm.Type, t1.Indx, ctm.Val from ctm
inner join
(select distinct Indx, case when Type = 'NMP' then Val end as Val1 from ctm) as t1
on t1.Indx = ctm.Indx
where Type <> 'NMP' and t1.Val1 <> null
|
|
|
|
|
I'm working on a program that needs to be able to update/delete rows in tables which do not (for whatever reason) have primary keys. I've done extensive searching on the subject and so far all I can turn up are suggestions to write my own custom update/delete SQL statements and attach them to the DataAdapter. The problem with this is that this program doesn't connect to one or two tables exclusively; rather, it allows the user to bring up -any- table.
The only thoughts my coworkers and I have had are to use Oracle's (we're using and Oracle database btw) rowid property, or to maintain a server-side cursor and somehow update/delete using that. The problem with these two solutions is:
1) Oracle's rowid is based off the row's location on the hard drive and is unreliable for uniquely referring to a row
2) I've been reading that ADO.NET has no server-side cursor
Does anyone have -any- ideas at all on how to solve this problem?
|
|
|
|
|
Well, it seems like the Table requires some designing. Assuming you cannot change the schema (like me, I had a similar situation), put all the columns with RowVersion.Original into the where clause. Sounds crazy but let me know if there is a better way.
|
|
|
|
|
Unfortunately, no, I can't change the table design - I am just a lowly developer  I thought about generating a where clause like you said, but that doesn't help us if there are two duplicate rows. Anyone have any other suggestions?
I know this has got to be a common problem, how are people solving it?
|
|
|
|
|
I think first of all if there are duplicate records, you need to delete them. Having duplicate records in a database table does not make sense to me at all.
You can get the duplicate records using a query similar to the following.
select au_lname, au_fname, city, state, count(*)
from dup_authors
group by au_lname, au_fname, city, state
having count(*) > 1
order by count(*) desc, au_lname, au_fname
The following statements will resolve the duplicates.
select distinct *
into #holding
from dup_authors
truncate table dup_authors
insert dup_authors
select *
from #holding
drop table #holding
|
|
|
|
|
I thought the same thing, but 2 things:
1) I'm not in charge of what data is in the database
2) It does make sense if you're keeping track of transactions (ie: $1 was added on 10/20, and then later another $1 was added on 10/20). Suffice to say, the company I work for needs these duplicate rows for some reason.
We are now pursuing using Oracle's pseudo primary key: ROWID. I'll let you know how this works out, but according to someone from Microsoft, this is how the original ADO did it.
|
|
|
|
|
yah i was going to say that they might not actually be duplicates IF a unique key was in the table
"there is no spoon"
{biz stuff} {about me}
|
|
|
|
|
Jasonc221 wrote: I'm working on a program that needs to be able to update/delete rows in tables which do not (for whatever reason) have primary keys.
I don’t know how to do this in ADO.NET or if it’s even possible, but cant you append a client side index column. Delete the row, and update it?
Just an idea.
ADOX 2.8 API Reference[^]
Append Method (Columns)[^]
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
without a primary key how do u identify the rows to delete?
i think ur actually asking the impossible unless u somehow add a primary key in some way either permanently or temporarily ... whoever designed the database table should be shot quite frankly
"there is no spoon"
{biz stuff} {about me}
|
|
|
|
|
Well we're migrating from an ADO application and I believe it knew which to update because of a server-side cursor of some sort. Unfortunately, ADO.NET does not have such a cursor. I don't know who designed the tables, but keep in mind we're not just talking about a few tables here - this program can query literally any table in this (quite large) company. So there's a fair chance that some of these tables weren't designed by DBA's, but by people who's specialty lies in other fields (like accounting) and just needed a place to dump some data.
I wish they were all designed by DBA's, but you really can't blame the people who created these tables.
|
|
|
|
|
Hi,
I looked into a few forums but while I am sure someone else had the problem before, I just can't seem to find an answer.
Here it is:
I am simply following the MS Press lab for "Dev. XML web services" for the exam.
When implementing the Airline example (ch.5), I keep getting an SQL exception that "the SQL server does not exist or access is denied".
I have tried connect to the server and it is running.
I have tried implementing it with the service (in the app) and it works.
But when I try to access it thru the ServicedComponent derived class, it always get the error.
Any pointers?
regards,
Eric.
|
|
|
|
|
Nevermind!
Apparently I had an old version of my COM+ component left.
I had to Delete it using the Component Services smc.
now it works 
Hope it can help someone else later.
Eric.
|
|
|
|
|
From what I read, you need to encode the unicode text in UTF8 format and then save it into a regular CHAR or VARCHAR field, and you need to decode the UTF8 text when you want to use the data later (like, convert it to UTF16 format).
Is that true? Is there a NCHAR or NVARCHAR type in Oracle that corresponds to the same type in SQL Server?
Thanks.
My articles and software tools
|
|
|
|
|
There are unicode types in Oracle, nchar and nvarchar2 are there. You should create database with a unicode - characterset (i.e. AL32UTF8) for better performance and sorting.
see also
http://www.oracle.com/technology/tech/globalization/index.html
|
|
|
|
|
|
I want to encrypt a connection string so the password won't be sent in plain text. How would I attempt this? Thanks
|
|
|
|
|
I'm presuming you're talking about what's sent over the wire.
If you're talking about SQL Server 2000, the password isn't sent in plain text. However it looks like the encryption is pretty trivial - I can see that every character always encodes to the same byte every time, and that the 0 bytes (the password is sent as UTF-16, so for a password that uses only ISO-8859-1 [Latin-1] characters every other byte is a 0) are always 0xa5. So it's really not secure.
Instead of sending a username and password, you should consider using a trusted connection (set Integrated Security=SSPI) which will use a better protocol for authentication - NTLM or I think even Kerberos. However, you can't specify the username to be used - the security identity of the current thread will be used.
[EDIT] You can also enable SSL. See 'Net-Library Encryption' in Books Online. [/EDIT]
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thanks, I'll give that a shot.
|
|
|
|
|
Hey all, I have a problem with openxml. In my ASP.NET application I use XmlSerializer to turn a object into XML, things work fine. I then send the xml to a stored procedure on the SQL server (2000). The weird thing is the same XML can be sent to two different SPs and one does not work when declaring one of the XML fields as ntext.
ie. in one I do an insert,
insert into table1 (cust_id, job_requested, job_status)
select @useCustId,
xmlDetails.Description,
xmlDetails.StatusId,
from OPENXML(@idoc, 'JobList/Job', 2)
WITH (CustomerId int, Description ntext, StatusId int) xmlDetails
Now this is trimed down, but it works fine. now when the same XML gets sent to another SP that updates like:
update job_main set
job_requested = xmlDetails.Description,
job_status = xmlDetails.StatusId,
from OPENXML(@idoc, 'JobList/Job', 2)
with (Id int, Description ntext, StatusId int) xmlDetails
where job_id = xmlDetails.Id
Now this one fails with 'Internal SQL Server error.' And I have no idea why, as it is the same XML as in the insert statement. If i change the ntext to nvarchar or varchar it works, but text and ntext do not.
Anyone seen anything like this, or know if i'm doing something stupid? I have no idea.
Thanks all
Luke
|
|
|
|
|
Is it possible to have 2 fields to be a datamember of a listbox? I want to have last and first name display. Thanks
|
|
|
|
|
Concat them together in your query.
<br />
select firstname + ' ' + lastname as fullname<br />
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
I am a new teacher and the program we use with the students won't uplaod and keeps giving me these error messages everytime we try to log on! I know that my server isn't reading my database but what do I do? The computer lab is closed until I can fix this! Please Help!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
