|
I’ve tried writing queries but they just don’t work. I tried joining tables, selecting the tables more than once and renaming it using AS.
I’d like to know if my desired output is really possible with mere using access sql ?? I need the query to set in my datatable for the gridview control. I am thinking of creating multiple datatable and assign them to one gridview instead of writing this complex query. But if there is a sql solution for this, I will stick to sql. Please share your ideas. Thank you.
I’ve been stuck with this problem, it’s like I’m trying to turn my rows into column. Here are my tables and the desired output.
Database tables:
TableDATA
ID GB FREE% FS_DATE
1 0.58 43% 8/19/13 1:00 AM
2 2.42 52% 8/19/13 1:00 AM
3 4.93 2% 8/19/13 1:00 AM
4 5.12 75% 8/19/13 1:00 AM
5 0.50 1% 8/19/13 1:00 AM
6 0.582 430% 8/19/13 2:00 AM
7 2.422 520% 8/19/13 2:00 AM
8 4.932 200% 8/19/13 2:00 AM
9 5.122 750% 8/19/13 2:00 AM
10 0.522 100% 8/19/13 2:00 AM
TableFS
ID FS MountPT FS_DATE SERVER_ID
1 /dev/1 a 8/19/13 1:00 AM HP-UX
2 /dev/2 b 8/19/13 1:00 AM HP-UX
3 /dev/3 c 8/19/13 1:00 AM HP-UX
4 /dev/4 d 8/19/13 1:00 AM HP-UX
5 /dev/5 e 8/19/13 1:00 AM HP-UX
6 /dev/1 a 8/19/13 2:00 AM HP-UX
7 /dev/2 b 8/19/13 2:00 AM HP-UX
8 /dev/3 c 8/19/13 2:00 AM HP-UX
9 /dev/4 d 8/19/13 2:00 AM HP-UX
10 /dev/5 e 8/19/13 2:00 AM HP-UX
Required Output:
(8/19/13 1:00) (8/19/13 2:00) (8/19/13 3:00 and so on.....)
FS MountPT GB FREE% GB FREE%
/dev/1 a 0.58 43% 0.582 430%
/dev/2 b 2.42 52% 2.422 520%
/dev/3 c 4.93 2% 4.932 200%
/dev/4 d 5.12 75% 5.122 750%
/dev/5 e 0.50 1% 0.522 100%
modified 18-Aug-13 16:27pm.
|
|
|
|
|
This article [^]may help, it is designed to work with SQL Server, I don't know if it will work with Access.
It requires 2 nested queries, fist prepare your data so you have exactly what is required (join the table) and get the column names to pivot on (Date/Times). This pivot the data on the date/time column.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi...
What the advantages are of object oriented programming over Procedural coding? Thank you... 
|
|
|
|
|
You could easily discover papers on the subject through a Google search. Also, this forum is for questions about using Database software.
Use the best guess
|
|
|
|
|
Wrong forum. And it's it's a situational thing; use the right tool for the right job.
|
|
|
|
|
candz_kritz wrote: What the advantages are of object oriented programming over Procedural coding?
The most obvious one to me is readability; in the procedural days I was often passing a structure to each procedure as a param (to avoid using a global var, which is even uglier) - now it's nicely embedded in the object as a private field.
There's more advantages which improve life, like inheritance. If it's homework, I'd recommend the Wikipedia; it'll be more complete in it's definitions.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Procedural coding is about procedures/functions. We write procedures which just take an input and produce an output. They don't have any kind of relation with another procedure. Sometimes they are just able to use some global or shared data when required.
OOP is all about objects. We do write procedures/functions in OOP, but the main thing is the class. A class has a relation; it can be a parent or base class, it can be a child class. And then we do things on the objects created by those classes. As classes have relation so we can say that the the objects of those classes do have the relations also.
For deeper understanding, you should check for articles online. Just google 
|
|
|
|
|
Much of my current job involves combining data from various sources into one SQL Server database. One of the sources is a MySQL database and I have noticed something odd.
One of the first steps I take when I get access to a new data source is to investigate the schema. As a fan of ADO.net and database-agnosticism, my main tools for doing this are:
System.Data.Common.DbConnection.GetSchema() , System.Data.Common.DbConnection.GetSchema(string) , and System.Data.IDataReader.GetSchemaTable()
which every compliant ADO.net provider should implement.
The way I use GetSchemaTable is to cobble up a SELECT * FROM name WHERE 0=1 query for each table and view, call ExecuteReader, call GetSchemaTable, and display the results.
The WHERE 0=1 clause is intended to keep the server from doing a bunch of needless work, but just return an empty dataset. This works, but I have since found that it hides problems -- mostly related to views that are out of sync with their sources. So I removed the WHERE clause and was surprised that one column in one of the MySQL views was reported as Int64 rather than Int32 as it is when I use the WHERE clause.
I got the latest version of the MySQL Connector/net and the situation persists.
What I then found, by using GetSchema("VIEWS"), is that the column is defined like IF((id IS NOT NULL),id,NULL) AS 'id' (id is involved in a JOIN among several tables).
I don't have access the tables so I don't know what type the actual field is. And I see comments on the Web that IF can return unexpected types. I have been unable to reproduce the oddity in my own test database.
But my question is, "why use the IF function in this case?" How is this better than just returning id whether it's NULL or not? Is there some historical reason?
|
|
|
|
|
Is this id existing in more than one table and is therefore used as a join condition in the view?
About your question, why the use of the IF function, I can only speculate that the original developer had a brain fart.
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
|
|
|
|
|
Jörgen Andersson wrote: used as a join condition in the view?
Yes.
Jörgen Andersson wrote: had a brain fart
That's what I'm thinking, but I'm no MySQL expert.
|
|
|
|
|
PIEBALDconsult wrote: but I'm no MySQL expert
Neither am I, I have never worked with MySQL.
But I have an idea that might explain the weird behavior.
In Oracle 10g you could define a query as:
SELECT ID
FROM A,B
WHERE A.ID = B.ID
Note that you didn't need to specify which table the column should come from if it existed in the join condition.
What happens if the ID Columns have different types, will there be an implicit cast? And which table will the selected column come from?
In Oracle 11g you always have to specify the table.column
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
|
|
|
|
|
Jörgen Andersson wrote: you always have to specify the table.column
Yes, but that's not the issue, so I left it out for simplicity.
|
|
|
|
|
I have the following code, which i generated using Access DB, i need to translate it to T-SQL, am mainly getting error on the IIF statement
SELECT dbo.casyAgeGroups.Sequence, dbo.casyAgeGroups.Description, Sum(IIf([gender]='Male' And [age]>=[startage] And [age]<=[endage],1,0)) AS Male, Sum(IIf([gender]='Female' And [age]>=[startage] And [age]<=[endage],1,0)) AS Female FROM dbo.casyAgeGroups, [qryStats-AgeGenderProfileDetails] GROUP BY dbo.casyAgeGroups.Sequence, dbo.casyAgeGroups.Description, [qryStats-AgeGenderProfileDetails].EntityType HAVING ((([qryStats-AgeGenderProfileDetails].EntityType)=0));
|
|
|
|
|
The IIF function was added in SQL 2012[^]. If you're using an earlier version, you'll need to replace the IIF function with a CASE block[^]:
Sum(CASE
WHEN gender = 'Male' And age >= startage And age <= endage THEN 1
ELSE 0
END)
You could also simplify the age condition by using the BETWEEN operator[^]:
Sum(CASE
WHEN gender = 'Male' And age Between startage And endage THEN 1
ELSE 0
END)
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
If anybody can offer some advice on a problem with Integration Services I have I'll be eternally grateful. I'm all out of ideas and getting frustrated to say the least! 
1. I have a SQL job that runs an Integration Services package
2. The job is set up to run using a proxy account.
3. When then job is run it fails with the following error:
Could not load package because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired). The SQL statement that was issued has failed.
4. The package name is correct.
5. I’ve double-checked that the password used for the proxy account is correct.
6. I am able to connect to Integration Services using the same account as the proxy and run the job directly from there.
|
|
|
|
|
lmaycock wrote: (Login timeout expired)
I think that is your smoking gun.
You can set the timeout on connections to infinite(usually 0 represents infinite as a value) to solve this sort of issue.
Something like this: commandObj.CommandTimeout = 0
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Thanks... I'll hopefully take a look again tomorrow but the same setup (as far as I can tell) works perfectly in my test environment. Typical...
|
|
|
|
|
Hi Folks,
I have a quick question in relation to how i select data from a table, as seen in the table below i want to get rows where Person1 & Person2 are both on the same row, so in the table below i would just want Rows 1 & 2.
Table:
ID | Person1 | Person2 | . . .
1 | ABC | DEF | . . .
2 | DEF | ABC | . . .
3 | ABC | GHI | . . .
.
.
.
I have tried the following:
Select * From MyTable Where Person1 = 'ABC' OR Person1 = 'DEF AND Person2 = 'ABC' OR Person2 = 'DEF'
This does not have the desired effect however.
Where am i going wrong here?
|
|
|
|
|
Nevermind figured it out, was missing brackets in the SQL statement
KeithF wrote: Select * From MyTable Where (Person1 = 'ABC' OR Person1 = 'DEF) AND (Person2 = 'ABC' OR Person2 = 'DEF')
|
|
|
|
|
This is another way of getting the same result:
Select * From MyTable Where Person1 in('ABC','DEF') AND Person2 in('ABC','DEF')
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Thanks GuyThiebaut, that is cleaner code.
|
|
|
|
|
KeithF wrote: Where (Person1 = 'ABC' OR Person1 = 'DEF) AND (Person2 = 'ABC' OR Person2 = 'DEF')
Your query will also return rows where Person1 and Person2 are the same value. It's not obvious from your description whether that would be a problem, or even if it's possible in your database.
If you only want rows which have both people on the row, try:
WHERE 'ABC' IN (Person1, Person2) And 'DEF' In (Person1, Person2)
or the slightly longer version:
WHERE (Person1 = 'ABC' And Person2 = 'DEF') Or (Person1 = 'DEF' And Person2 = 'ABC')
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks Richard,
It would not be possible for ABC to exist in Person1 and Person2 in the same row, but its no harm using the syntax you describe to circumvent it in case it were to happen.
|
|
|
|
|
Hi,
I had long running queery last May, early June. I got all sorts of errors trying to connect.
Then the VS2012Express Compiler gave up!
Eddy Vluggen made great effords to help me, but, in the face of a non compiling compiler, I had to call it a day.
That Compiler has now been fixed with the traditional indecent haste on behalf of Microsoft, (the Last Auto Ugrade apparently fixed it,got no notice of the fix, I hope it will last) However, the DB Connection String Problem has NOT Gone away. I now do wonder, Is this another Microsoft Issue, or am I doing something wrong!
I have the following (More or Less)from Eddy Vluggen, and in line with the documentation.:-
public static String GetConnectionString()
{
string Result="server=b-pc\\Softguard;"+
"database=SgTextiles; " +
"Name=sa;Password=12345;"+
"connection timeout=5;";
return Result;
}
The Code that uses this is:
String ConnectionString = GetConnectionString();
SqlConnection myConnection = new SqlConnection(ConnectionString);
I get the Exception:"System.ArgumentException: Keyword not supported: 'name'."
Irrespective of DB Configuration issues, the parser of the Connect String seems to choke on the Keyword 'name' (incidentely confirming the Non Case Sensitivity of the particular keyword parser).
Kind Regards 
Bram van Kampen
|
|
|
|
|
Shouldn't Name be User ID?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
