|
Hi everyone,
I have not been in touch with SQL for a long time. I had to start using it again since a few weeks and it has been quite a struggle.
I have a question and I would be really grateful if someone can help me with it. Consider the following table structure:
ID TESTNUMBER STATE VALUE
1 100 1 0
2 100 1 1
3 100 2 1
4 100 2 0
5 101 1 1
6 101 1 0
7 101 2 1
Now, what I want to do is get the DISTINCZ count of TESTNUMBER, say where (State = 1, Value = 0) and (State = 2, Value = 1). So basically this should give me a count of 2 because TestNumber 100 has an entry for (State = 1, Value = 0) and (State = 2, Value = 1). Also, entry 101 has (State = 1, Value = 0) and (State = 2, Value = 1) entries. However, I am unable to write this using a query. Please help!
Thanks,
Sincerely,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
Try this:
SELECT COUNT(*), state, value
FROM table
GROUP BY state, value
The statement above will group your result based on state and value, e.g.
Count State Value
2 1 0
2 1 1
2 2 1
1 2 0
If you need to add a condition, add HAVING state = 1 AND value = 0 (for example).
|
|
|
|
|
Hi,
I think I did not explain my question well. Let me explain the scenario. I have a code that has to generate all possible combinations of chosen States and Values.. Say for our example the combinations are (State = 1, 0) (State = 2, 1).
Now, I have to go in the database and find the TestNumber which has an entry for (State = 1, 0) AND (State = 2, 0) (which would only be TestNumber 100. If I had used an OR in my Select query it would return both 100 and 101. Also, if I use AND for the same field it would return me nothing)... So all combinations exist for this state. What SQL construct exists for performing such queries?
Thanks,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
You can join your table to itself to do this. (I think this is what you are asking for...
select t1.testnum, count(t1.testnum)
from test t1
join test t2 on (t1.testnum = t2.testnum)
where t1.state = 2 and t1.value = 1
and t2.state = 2 and t2.value = 0
group by t1.testnum
this selects the testnum and a count from the table, joins itself based on the testnum and the where clause specifies where your first criteria matches on the "first" table and the second criteria matches on the "second" (your self join) table. Group it by testnum to see the count for each testnum that matches. Here are the results of that query...
Testnum count
100 1
If you change the t2.state = 2 to t2.state = 1, you get back
100 1
101 1
If this isn't what you are after, enlighten me some more on what you want.
|
|
|
|
|
Hi,
It had takem me a while but I figured that out. Thanks a lot for replying. I have a more complex one for you. I am really no good with SQL and unfortunately I have to work with it on this one
Consider the following table again:
ID TESTNUMBER STATE VALUE
1 100 1 0
2 100 1 1
3 100 2 1
4 100 2 0
5 101 1 1
6 101 1 0
7 101 2 1
8 101 2 0
9 103 1 1
10 103 3 1
Now, I have to get all TESTNUMBER which have different sets of State and Values. So, this table will return 2 sets...
Set 1:
1 0
1 1
2 0
2 1
(Since, 100 and 101 share a common set)
Set 2:
1 1
3 1
(102 does have an identical set as 100 and 101. Common items are allowed but the sets must be identical (same elements))
Any suggestions on how to achieve this?
Thanks a lot,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
Honestly, something that convoluted, I would just query the table and figure out the sets in my app side code. You're more than likely going to have to loop through the records, and I'd rather do that in code than with SQL Cursors.
|
|
|
|
|
Yeah, that is what I ended up doing. I am not a big fan of SQL
Thanks,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
ListObjectPermissions
what does the above mention statement do??
can i use this to get the access permissions of a user?
ranjani
|
|
|
|
|
ListUserPermissions
what does this method do..
does this tell me the user access perm for a particular database?
ranjani
|
|
|
|
|
Ranjani,
You post messages pretty often. Why don't you sign up as a user? Just wondering.
Basically, the way SQL Server permissions go, you have permissions as the result of being a member of a Windows group or role, right? (When I speak of permissions, I also mean that you can be denied access to something.) Then on top of these group-related permissions, you can be explicitly granted or denied extra permissions. These are usually granted/denied/revoked using the GRANT , REVOKE , and DENY statements in Transact-SQL, since most admin types use Transact-SQL to script this stuff up.
The ListUserPermissions method lists the permissions of the second type: special added-on permissions granted specially for a specific user. It won't include permissions that a user has through inclusion in a role, for instance, so be careful.
Also, you should realize that the Permission object can represent the fact that a user is denied access to something-- check out the Granted property.
Hope this helps.
Regards,
Jeff Varszegi
|
|
|
|
|
HI ,
I have made a procedure for sql server with visual c++ and i have test it on my computer and it is well but when i have move it to the server it could not work and it give me the following error
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL sim.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).
can any body help me
|
|
|
|
|
You've probably used some run-time library that you have on your computer but isn't on the server.
To check dependencies for a DLL, get hold of Dependency Walker[^].
If you have built a Debug build of your DLL, I recommend building a Release build and testing that on your computer before deploying that to the server.
|
|
|
|
|
I have build the release one and tested it on my local computer and it is working , and for information i did not use any external library just the standered one , and i still have the problem
|
|
|
|
|
If your DLL was built using Visual C++ .NET 2002 or 2003, and you opted to use the multithreaded DLL version of the C runtime library, there's a new version that may not be on the server.
For 2002, you need msvcr70.dll; for 2003 it's msvcr71.dll.
If you've used any C++ standard library features (for example, iostreams) you might also need msvcp7x.dll. If you used 'old' iostreams (declared in <iostream.h> rather than <iostream> ) you'll also need msvci7x.dll.
Even if you only used Visual C++ 6.0 to compile, the server might not have all the required C runtime DLLs, although this is less likely. Windows NT 4.0 did not originally ship with msvcrt.dll version 6.0, but it's included with SQL Server 2000.
I still recommend using Dependency Walker to view the actual DLLs required by your DLL. Using it on the server will show what's missing.
The other possibilities I can think of are that you've specified the wrong DLL name - although I would expect that you'd noticed this - or that the DLL is in the wrong directory. You may be able to specify a full path - I haven't tried this.
|
|
|
|
|
Thaks for your help i have copy the msvcrt71.dll file to the server and it works .
Mhmoud Rawas
------------
Software Eng.
|
|
|
|
|
Is it possible to script a dts package the way one can script an sp? I was thinking that running the script through query analyser might help me discover exacly where the package is failing in copying the larger of our 3 db's to our disaster recovery server, but I haven't managed to find an option (other than saving as a VB file).
Thanks again
Look at the world about you and trust to your own convictions. - Ansel Adams
Meg's World - Blog
Photography - The product of my passion
|
|
|
|
|
Hi,
I've the following requirements:
1) Allow users to edit columns 2 & 3 of a DataGrid.
2) Prevent users from adding rows.
I can solve (1) by checking the event of moving cells, and then doing the following:
---------------------------------------------------------------
if (this.DataGrid1.CurrentCell.Columnnumber==0)
this.DataGrid1.ReadOnly=true;
else
this.DataGrid1.ReadOnly=false;
---------------------------------------------------------------
This works really neatly. I can't figure out how to solve problem (2) though.
Any ideas?
Pauwl
|
|
|
|
|
IIRC, it's a property on the underlying DataView: AllowNew.
|
|
|
|
|
Mike Dimmick wrote:
IIRC, it's a property on the underlying DataView: AllowNew.
Thanks Mike, this does the trick.
I somehow need to set this each time the control becomes active, but still, it works nicely.
thanks,
Pauwl
Pauwl
|
|
|
|
|
Could you please give me some website that can help me while migrating SQL to Oracle?
|
|
|
|
|
Oracle has a tool to migrate from SQL Server, I read about it in otn.oracle.com[^], but can't find the link.
Do some search in there.
Free your mind...
|
|
|
|
|
|
Do in-memory database systems such as Prevayler or commercial products have a user-specifiable number N, such that if the system uses more than N bytes of RAM, the system will use virtual memory? That way the database system would use the operating system's virtual memory smarts for managing what goes on disk and what goes in memory, while providing the ability to have a larger database than just what will fit in available RAM.
|
|
|
|
|
I'd expect that such products (at least Bamboo Prevalence) will use virtual memory when necessary, as Windows may allocate more memory than the RAM available when an application asks for it.
As for setting the limit for when virtual memory will be used, Bamboo Prevalence does not expose such a setting. Maybe the OS will let you set such limits...!?!?
Have a look at my latest article about Object Prevalence with Bamboo Prevalence.
|
|
|
|
|
I'm wondering if it's possible to stop a SQL database from running, from within a DTS package? It's essential that the SQL Agent keeps running on the machine however.
Thanks for any info
Look at the world about you and trust to your own convictions. - Ansel Adams
Meg's World - Blog
Photography - The product of my passion
|
|
|
|