| Author |
Topic |
|
samtoffa
Yak Posting Veteran
60 Posts |
 Posted - 2004-06-23 : 12:16:17
|
| I need to insert record counts from two or more tables into a single summary table with the following 4-column structure:timestamp | Val_1 | Val_2 | Val_2/Val_1*100timestamp is just to know what time the insert was doneVal_1 is e.g. SELECT COUNT(Current_Users) FROM Table_1Val_2 is e.g. SELECT COUNT(Old_Users) FROM Table_2Val_2/Val_1*100 is the percentageIs there a way to do this in PURE SQL because I may need to make this work with other databases (MS Access and Oracle)? I cannot merge tables 1 and 2 in any way because they may have different structures. |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-06-23 : 13:03:01
|
| What is the relationship between the two tables?JimUsers <> Logic |
 |
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-06-23 : 13:28:02
|
| Unless I am missing something here, would this work?insert...select GETDATE(), (select count(current_users) from Table_1), (select count(old_users) from Table_2), ((select count(old_users) from Table_2)*1.00)/((select count(current_users) from Table_1)*1.00)*100 |
|
|
|
samtoffa
Yak Posting Veteran
60 Posts |
Posted - 2004-06-23 : 17:47:06
|
| Hi Jim. There is no real-life relationship between the tables. One table has user details for a legacy application the other has similar (but not identical) data for a new application. The Summary table shows how many people who used the legacy application are using the new software. This is a European healthcare application with potentially 2 Million users listed in a table. Hence the problem with the cartesian products that this code seems to generate. Any ideas?Hi drymchaser. You aren't missing anything. It looks like it should work but it doesn't. This was the first method that I tried and it returns a cartesian product. Also in MS Access (yes I know this is an SQL Server forum) this gives an error message that is reserved. I'd really like to find a way to do this in a platform independant way. ie pure SQL.thanks,Sam |
|
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2004-06-24 : 09:06:55
|
| Did you copy and paste my query in QA? Should not be a cartesian product per se. Each portion of the "outer" select returns a single value and therefore should be one row. The problem with my query I think is GETDATE() for portability. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-24 : 09:47:44
|
quote:
Originally posted by samtoffa
Also in MS Access (yes I know this is an SQL Server forum) this gives an error message that is reserved. I'd really like to find a way to do this in a platform independant way. ie pure SQL.
You can'tAll RDBMS's have their own extension...and I don't think (damn that happens so often) that you could call Access an RDBMS...well maybe you can, but it ain't client/serverTry here:http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3Brett8-) |
|
|
|
samtoffa
Yak Posting Veteran
60 Posts |
Posted - 2004-06-24 : 16:05:02
|
| Drymchaser was right. My problems were caused by my bad use of parentheses...I should have copied and pasted your code rather than modifying mine...lesson learned! As you have all hinted I will have to tailor the query for each RDBMS that I use. Getdate() in SQL Server Date() in Access???? Surely Microsoft have enough money to have a team implementing ANSI standards throughout their product range?! Maybe I need to go back to Oracle. Thanks all for your help.Sam :-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-24 : 16:09:49
|
| GETDATE() is not an ANSI thing. And SQL Server does use ANSI. You just need to use it.Tara |
|
|
|
samtoffa
Yak Posting Veteran
60 Posts |
Posted - 2004-06-24 : 20:55:00
|
| ...please consider my wrists appropriately slapped!!And of course appologies to Microsoft for daring to suggest that their software may be anything less then perfect. But I am slightly confused. Are you saying that if a query like this ( excluding GETDATE() ) is written using ANSI-SQL it will run in MS Access and SQL Server? If so, please show me the code, and then 'I just need to use it' ;-)Sam |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-24 : 21:21:01
|
| Microsoft knows it's an issue Sam. That's why they're keep trying to push Access users to not use the Jet database engine and pushing people away from FoxPro. The fact is that ANSI does not support functions, which all major database systems need. If you use SQL Server, and utilize the front-end and reporting capabilities that Access has to offer you, you will be further ahead in the long run on all counts.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
Another INSERT problem