|
i have an query
There are four tables::
table_1 :: date,acct_ref,prod_ref,short_qty,long_qty
table_2:: acct_num,date,cus,Qty
table_3:: acct_ref,acct_num,acct_name
table_4:: prod_ref,cus
use table_3 to join between acct_num and acct_ref & table_4 to join cusip and prod_hub
table_1 has (-ve)qty in short_qty & (+ve)qty in long_qty whereas qty column in table2 has both (-ve) & (+ve) qty.
There can be either a (-ve)qty or (+)qty
Requirement:: To identify where the qty is different from table1 & table2 & for which all accounts
please suggest the sql query to do so
Reghards
Varun
varun handa
|
|
|
|
|
This reads like a homework question - and a badly written one at that.
So, I hope you'll understand that I'm not going to give you a solution, but I will do my best to nudge you along in the right direction.
varun80 wrote:
use table_3 to join between acct_num and acct_ref
So, table_3 is linked to table_1 through the acct_ref column
table_3 is also linked to table_2 through the acct_num column
varun80 wrote:
table_4 to join cusip and prod_hub
cusip and prod_hub don't exist so I'm guessing this is copied wrong.
I'm assuming that cusip == cus and prod_hub == prod_ref
table_4 is linked to table_1 through the prod_ref column
table_4 is also linked to table 2 through the cus column
varun80 wrote:
To identify where the qty is different from table1 & table2 & for which all accounts
table_1 and table_2 are related via table_3 - the joins you made earlier should show you how match the rows in table_1 with the corresponding row in table_2
Because table_1 has two qty columns (long_qty and short_qty) they will need to be added together to produce a value that is comparable to the Qty column in table_2
varun80 wrote:
please suggest the sql query to do so
As this looks suspciously like homework I'll decline that request - you won't learn anything if I do all the work for you. However, as I have said, I am quite happy to nudge you along in the right direction.
If you get stuck, post your workings to show where you got.
Good luck.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hi All,
I have developed an Module using SQLDMO in ASP.NET that takes the backup of the database and puts the .bak file on the Database server itself.
Now when I install the application on a fresh machine wiht Windows 2000 Server Operating System, which does not contain the SQL Server Client installed on it, the application does not give the result.
But if I install the SQL Server Client on the machine then the application would run properly.
Can any one help me out of this situation, and tell me what needs to be done......
Its Urgent..........
Thanks in Advance 
Prashant N.Rana
|
|
|
|
|
SQLDMO is a pig to re-distribute. You should find some info on the original SQL server CD or from the MS website. There are 3 ways to get an SQLDMO application to work on a client.
1: install SQL server on the PC
2: install SQL server Client tools on the PC
3: install all the SQLDMO dlls to the correct locations on the client PC.(see below)
These are the files that need to be installed and where they need to be installed to:
sqlunirl.dll to (WinSysPath)
sqldmo.rll to C:\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033
sqlsvc.rll to C:\Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033
sqlresld.dll to C:\Program Files\Microsoft SQL Server\80\Tools\Binn
w95scm.dll to C:\Program Files\Microsoft SQL Server\80\Tools\Binn
sqlsvc.dll to C:\Program Files\Microsoft SQL Server\80\Tools\Binn
sqldmo.dll to C:\Program Files\Microsoft SQL Server\80\Tools\Binn
This is probably why most people will use ado / ado.net and use the system stored proceedures which can do pretty much everything SQLDMO cna do anyway.
I think thats it.
Jon
|
|
|
|
|
Hi Jon,
Thanks a lot for the quick response.
Sorry for the late response from my side, but I was in the process of implementing the suggessions given by you.
Well I was able to execute the application even with the third option.
I know that this was a very bad option to select and I should have used stored procedures. Well I will keep this in mind the next time I develop such kind of an applicaiton.
Thanks a lot once again for the quick response.
Thanks & Regards,
Prashant N.Rana
|
|
|
|
|
I am kind of stuck on stupid, and need some help. The following code works only once and then I get and error because of multi '@Param' use. I coded it this way because of the need to do some conversions as you can see. Can someone tell me how to code this in the correct way? I am new at this kind of code.
Norm
try
{
sqlInsertCommand1.CommandText = "INSERT INTO services([service-code], [service-description] ,[large-animal-cost], [medium-animal-cost], [small-animal-cost])" +
" values(@Param1,@Param2,@Param3, @Param4,@Param5) SELECT @@IDENTITY";
SqlParameter param = sqlInsertCommand1.Parameters.Add("@Param1",SqlDbType.Int);
param.Value = txtServiceCode.Text;
param = sqlInsertCommand1.Parameters.Add("@Param2",SqlDbType.VarChar);
param.Value = txtServiceDesc.Text;
param = sqlInsertCommand1.Parameters.Add("@Param3",SqlDbType.Money);
param.Value = Convert.ToDecimal(cbLargeAnimalCost.Text);
param = sqlInsertCommand1.Parameters.Add("@Param4",SqlDbType.Money);
param.Value = Convert.ToDecimal(cbMediumAnimalCost.Text);
param = sqlInsertCommand1.Parameters.Add("@Param5",SqlDbType.Money);
param.Value = Convert.ToDecimal(cbSmallAnimalCost.Text);
sqlInsertCommand1.Connection = sqlConnection1;
if (sqlConnection1.State != ConnectionState.Open)
sqlConnection1.Open();
sqlInsertCommand1.ExecuteNonQuery();
nbohana
|
|
|
|
|
First, it looks like you have used the designer to write your query, in which case the query object is stored as a member variable in the class. This means that each time you run the command it keeps on appending the parameters to what was there before.
I would write this so that each time it creates a new instance of the Command object it starts afresh and you don't need to worry about what state it was left in the last time you used it.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I have a database in which there are companies, sites, and accounts servicing the sites. Most of the time there is one account per site but it may be the case that one account covers many sites. There is a sites table and an accounts table. Is there a way to join these guys without the use of an intermediate table holding id's from each table?
Sorry if this is obvious but I am used to financial modeling not database development.
Mark
|
|
|
|
|
mjackson11 wrote:
Is there a way to join these guys without the use of an intermediate table holding id's from each table?
No, you must use an intermediate table if you want to keep the data model normalised. Even in database systems that permit Many-to-Many joins directly it is implemented with an intermediate table under the covers.
If you are willing to denormalise the datamodel you could implement it without the intermediate table if there were a limited number of possibles joins, say an Account can cover a maximum of 3 sites. You have three site foreign key references on Account that are nullable (just in case there are not exactly 3 sites) and vice versa. This will, however, become much more difficult to maintain than the intermediate table solution.
Is there any particular reason why you don't want to use an intermediate table?
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
>Is there any particular reason why you don't want to use an intermediate table?
I'm lazy  . No reason really, it will just take a couple days to add the table and make sure the data is converted, then audited.
We have sites/account combinations that would require 15 links so I will put in a sites-account table.
Thx for the info. I found your blog article very helpful.
|
|
|
|
|
We are developing a VB.NET database application, using windows forms and Microsoft Access.
The application requires dynamic reports, where the users change data on the forms, if a report is being shown it will automatically update.
To do this we are using the .NET crystal report viewer, which loads a crystal report that we set it's data source at runtime using a dataset. If the user changes data on the data collection form, we update the dataset and refresh the reports data source.
The problem we are getting it's with the memory overhead. When we first show the form containing the crystal viewer control, an additional 20Mb of memory is allocated. Every time the report is then refreshed with the updated dataset, 1-2Mb is added to the memory each time. If we close the report viewer form, none of the memory is ever recovered. If the report is displayed again, the memory keeps increasing with every refresh.
Is there a way to release the memory used by the crystal viewer, or is this a know issue. If it is an issue does anyone have any better alternative to crystal reports?
Any advise would be appreciated.
Jason
|
|
|
|
|
I am getting the same problem, any suggestions would be very much appreciated.
|
|
|
|
|
We had a similar problem a while ago with crystal 8.5. From the research we did we found the following (to the best of my understanding). It seems when you open the report it loads all the data into an array in memory. You initially get a very large increase in the resources used (about a meg for the viewer object and other objects if you use charts etc and the rest for the data for the first report, This in your case is about 18 meg). When you close the form the viewer object is destroyed but the array is not(you should see a small drop in allocated memory). The next time you run a report it again creates the viewer object adding a few more meg. The data is loaded into the already existing array. If the data is more than before the array will grow. If it is less it all fits into the existing array and NO changes are made to the size. The array is destroyed when you close your application.
As our application was running on a terminal server we found out about the problem very quickly (clients server died if several people ran reports). With crystal the problem is made all the worst by the fact it will load ALL the data into the array then work on any critera you have set, so it may load 3 linked tables in their entirety yet only display 2% of the records if that all that fits the selection criterea.
Rather than upgrade and risk running into the same issues we just switched to Active Reports by DataDynamics which are XML layout based and have a tiny memory footprint.
So, sorry to say but i think if you want to use crystal you are stuck with this.
Jon
|
|
|
|
|
Thanks for your reply. I am not tied into sticking with crystal reports, but after looking on DataDynamics website, it would cost us $10k for a site license of active reports.
DO you know of any cheaper alternatives?
|
|
|
|
|
Sorry but no. There are only 2 of us developing here and one of the things we really wanted to do was give the end users the ability to alter the reports. The crystal report designer component licence is more restrictive and would result in us having to substantially increase to cost of our software. The DataDynamics one is royalty free so for us it was actually a far cheaper option than crystal anyway.
Jon
|
|
|
|
|
 hello i am a beginer. i want to program on a PDA to identify chinese medicine. i need to setup a database to store all the info about chinese medicine. how to store both text and pictures inside and can i use chinese characters?
|
|
|
|
|
tudou wrote:
can i use chinese characters
I would have thought so.
tudou wrote:
how to store both text and pictures inside
If you don't know how to store text, then you need to buy a basic book on databases, or read the help that came with your database.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
Hello,
I'm programming an application for a multi user environment, so multiple users will work with different applications on the same database tables.
Now, here's my problem.
Lets say user A en user B start the application, and there local dataset is filled. Let's say user A changes row 1, automatically the application will save this in the application and in the database. But now user B doesn't have this latest info yet.
How do i solve this?
Nowadays it's working like this, each minute the application is getting the latest info from the database, and the datagrid is redrawn with the new latest info.
but i would like to compare the current dataset for changes with the new dataset from the database. Is there an easy way to do this?
thx
|
|
|
|
|
There's nothing you can do about what is shown on one clients screen while another is changing it, unless you lock records so only one person can have them at a time. I'd lock only when you get to a point of editing, and then users will find out someone beat them to it if they try to edit something that someone else has grabbed. The 1 minute refresh is basically a race condition, and a drain on your database server resources.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
I have created one site and want to upload it .but i do not now how to restore the database to the server.if i can do this only by using enteprize manager .if yes i do not now how to enter the server!for example i can copy the web pages of the site in the www.root ,i can copy the database to the wwwroot too.but can not restore it.The problem is i do not now how to access to the programs(such as enteprize manager)in the server?
|
|
|
|
|
Anonymous wrote:
i can copy the database to the wwwroot too.but can not restore it
I answered a very similar question a few hours ago. You can find it on this forum here[^]
Anonymous wrote:
The problem is i do not now how to access to the programs(such as enteprize manager)in the server?
If you need to access programs in general on the server then you can use Remote Desktop Connection (assuming the administrator of the server permits in-bound connections and your account has permission to be accessed remotely in this way), it is in your Start --> All Programs --> Accessories --> Communications menu.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
I am trying to create a deployment project for my windows app using the MSDE deployment toolkit so my database and SQL will be automatically configured on client machines. The only information i have to work with is an article at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmsde/html/msdedepl.asp[^]
I was wondering if anyone could help me out with the code and or sql statements for actually creating the database, tables, login info, etc. I dont know that much about SQL or SQL statements. I have written a complete app using a SQL database and it works great i just need help with my deployment using the MSDE toolkit because that looks like the best way to go for ease of installation. If anyone has any input, code, just any kind of help with this, i would greatly appreciate it. Thank you.
|
|
|
|
|
Can I create a table for a *.mdb file with a sql statement (crate table{}) in asp.Thanks for your help from now
enigma
|
|
|
|
|
i've developed an application that uses ADO.NET.
If want to distribuite it do i need to save in the user installation diretory some dll or other files?
Thanks
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
