|
|
Hi Gurus:
i m new to database programing , so i m in problem even at first step ,
i have two tables (using MS Access as DBMS)
Purchase_Detail
OrderId | ProductID | QtyPurchased
1====== | == 1 ======== | == 20
1====== | == 2 ======== | == 20
And
Purchase_Return
OrderId == | == ProductID == | == QtyReturn
1 ======= | ====== 1 ====== | ==== 5
now i want to get the result from Product_Detail table as
using query
OrderId == | == ProductID == | == QtyPurchased
1 ======== | ===== 1 ======= | == 15
1 ======== | ===== 2 ======= | == 20
i m using the following query
SELECT Purchase_Detail.*
FROM Purchase_Detail LEFT JOIN Purchase_Return ON [Purchase_Detail].[pdOrderID] = [Purchase_Return].[prOrderID] AND
(Purchase_Detail.pdProductID = Purchase_Return.prProductID) AND (Purchase_Detail.pdOrderID = Purchase_Return.prOrderID)
WHERE (((Purchase_Detail.pdOrderID = '4')AND(Purchase_Return.prOrderID) Is Null));
the result of which is
OrderID == | == ProductID == | == QtyPurchased
1 ======== | ==== 2 ======== | ==== 20
missing the product ID 1 which has remaing 15 items in the stock....
can any one help me..
Thanks in advance.....
Seefou
|
|
|
|
|
Try this:
SELECT Purchase_Detail.OrderId, Purchase_Detail.ProductId, Purchase_Detail.QtyPurchased - ISNULL(Purchase_Return.QtyReturn, 0)<br />
FROM Purchase_Detail <br />
LEFT JOIN Purchase_Return ON Purchase_Detail.OrderId = Purchase_Return.OrderId <br />
AND Purchase_Detail.ProductID = Purchase_Return.ProductID
Basically this is returning everything from Purchase_Detail as it is a left join, but the QtyPurchased has been deducted from Purchase_Return.QtyReturn (if there is any, otherwise it is deducted by 0).
I hope it works 
Edbert P.
Sydney, Australia.
|
|
|
|
|
Hi:
The problem remains same and i m getting the message that wrong number of arguments in the Function IsNull
can any one help me ,,,.........
Thanks
"Winner's don't do different things , they do things differently "
|
|
|
|
|
Sorry..I forgot that you're using MSAccess.
You have to change the IsNull function into IIF(IsNull(Purchase_Return.QtyReturn), 0, Purchase_Return.QtyReturn)
So from
- ISNULL(Purchase_Return.QtyReturn, 0)
to
- IIF(IsNull(Purchase_Return.QtyReturn), 0, Purchase_Return.QtyReturn)
Edbert P.
Sydney, Australia.
|
|
|
|
|
I am writing a program in C# to access a SQL Server database using the ODBC ADO.NET classes. I am having a problem with it timeing out before the dataset is returned. I have tried to increase the connect timeout (60) and the command timeout (300 to as much as 48000) but I am still getting the timeouts. In fact, it seems that it is ignoring the timeout I am setting. Is there a way to disable the command timeout and just have it process until finished.
NOTE: The command consistently returns in less than 45 seconds and I am getting partial data, depending on the network traffic, in the dataset upon return from the call.
NOTE: I don't know if this helps any but I am also converting reports, originally written in MS XL to Crystal Reports. The XL version of the reports has the same timeout problem but the Crystal Reports version does not, and I think this is due to the fact that the Crystal Reports version runs considerably faster.
Additional info: yesterday, the database had just over 1 million records in it. It returned the requested number of records, but was accompanied by a timeout. today, there are over 2 million records and it times out after the same amount of time, but no records are returned, when it should have returned the same records as yesterday.
|
|
|
|
|
I've personally seen problems like this crop up. There are actually many layers of timeout not just between the client and the DB.
The general problem is that between one of the layers a timeout is being reached. You've found the most obvious one in the client connection. But there is one from the DB too. Depending if you are using a transport like HTTP that has a timeout too. And so on and so on.
By your description it seems that your query succeeded but ran out of time trying to return data to the client. The time taken to run the query and complete dumping the data to your client is passing some timeout. A time out which you might not be able to change.
What I would do is "by hand" run the query. Plug it into Query Analyzer and see how long it takes for it to complete on the server side. If the query is too long or returns way to much data you might want to consider a different approach. Instead of batching the entire query waiting minutes for it to return the result you may want to think of a small selection set or using cursors.
ps. Are you catching the exception? What does it report?
|
|
|
|
|
Thanks. I finally found the solution. It turns out that it was something simple, but I wouldn't have known it without digging through the endless dbConnect values in the debugger. In essence, I was doing the following:
dbCommand = new OdbcCommand(Query, dbConnect);
dbCommand.CommandTimeout = 300;
dbAdapter = new OdbcDataAdapter();
dbData = new DataSet();
dbAdapter.SelectCommand = dbCommand;
dbAdapter.Fill(dbData,"Play");
What I found, after going through the 'dbCommand' parameters in debug, is that the
'dbAdapter.SelectCommand = dbCommand'
RESETS THE TIMEOUT VALUE BACK TO THE DEFAULT!!! This is why it would ignore any timeout values I set. AAAARRRRGGGGHHHH!!!! So, you MUST set the timeout AFTER calling that line, preferrably just before the "Fill()" call. When I did this...
dbCommand = new OdbcCommand(Query, dbConnect);
dbAdapter = new OdbcDataAdapter();
dbData = new DataSet();
dbAdapter.SelectCommand = dbCommand;
dbCommand.CommandTimeout = 300;
dbAdapter.Fill(dbData,"Play");
it worked fine. No timeout whatsoever. GEEEEZZZZEEE!! It even worked great setting a timeout of 90, but 60 timed out. Microsoft should not reset values like this. Putting initial values should be left up to constructors and explicitly called initializers ONLY!!!
I have noticed other people on this board complaining of the same problem. They were probably doing the very same thing as I was.
|
|
|
|
|
Hi,
table containing two fields one is code and another is name. i need to fetch repeated names only.(more than one occurance of name and code is not same). any one help to me. thanks in advance.
Have A Nice Day!
Murali.M
|
|
|
|
|
SELECT name
FROM table
GROUP BY name
HAVING COUNT(name)>1
Cheers,
_____________
stefan bornuz
|
|
|
|
|
hi stefan,
thanks for ur help.
Have A Nice Day!
Murali.M
|
|
|
|
|
in C# /w asp.net using SQL... (which forum, lol)
i have a dataset that i bind to a html datagrid, is there anyway i can manipulate the data in between the grab from the database, and the posting into the table?
i just want to do some simple replaces... to replace some strings with images, etc... things i can't do with simple formatting... this is NOT for posting back to the db...
|
|
|
|
|
I feel your question is not clear enough to tell us your requirement. Can you explain it bit more detail?
If I understand your requirement correctly, I feel you can use template columns in which you can have the images.
|
|
|
|
|
the data in the database in the columns i want to "interject" is signed integers.... i want negetive integers to have a upwards arrow (html img tag) replace the negative sign, and positive integers to have a downwards facing arrow in front of the number
|
|
|
|
|
Hi,
Sure can! Your easiest solution is probably to add a column to your DataTable and set an Expression on that column. Here's code snippet:
<code><pre>
DataColumn DC=new DataColumn("strDisplay");
DS.Tables[0].Columns.Add (DC);
DS.Tables[0].Columns["strDisplay"].Expression = "IIF(iIndex<0,'your downarrow html' + -1*iIndex,'your up arrow html'+iIndex)";
</pre></code>
Check out the VS.NET help for DataColumn.Expression
I am assuming 1) that you only have one DataTable in your DataSet 2) that the column of intergers on which you wish to operate is named "iIndex".
Then you just display the content of the new column, "strDisplay", in your DataGrid.
Hope this helps,
Bill
|
|
|
|
|
|
how can i pass a null value to PutElement()?
|
|
|
|
|
I am creating a 3 level tree menu using dataset. I have an sql statement that gets the parent of the tree.. but how do i loop the dataset and re-query the database to associate each parent to it's child? I am coding in c#.
String strConn="...";
string strSQL = "SELECT TabName FROM rb_Tabs WHERE ParentTabID IS NOT NULL AND TabID='" + tabid + "' OR ParentTabID='"+ tabid + "'";
SqlConnection objConn = new SqlConnection(strConn);
objConn.Open();
DataSet objDS = new DataSet();
SqlDataAdapter daSuppliers = new SqlDataAdapter(strSQL,objConn);
daSuppliers.Fill(objDS, "rb_Tabs");
objConn.Close();
TreeNode nodeSupp, nodeProd;
foreach(DataRow rowSupp in objDS.Tables["rb_Tabs"].Rows)
{
nodeSupp = new TreeNode();
nodeSupp.Text = rowSupp["TabName"].ToString();
Treeview1.Nodes.Add(nodeSupp);
}
objDS.Dispose();
daSuppliers.Dispose();
objConn.Close();
objConn.Dispose();
Laine
|
|
|
|
|
When I try to execute an SqlCommand (even the shortest and very simple), the application always throws an exception: "VNSAPI32.dll is missing. Reinstalling the product may solve the problem." I don't know what Windows means by "product", 'cos I've re-installed the IDE, but nothing changed. The same after creating a new instance of SQL server. I don't want to suffer with reconfiguring XP; so is there a solution for this?
|
|
|
|
|
It's trying to find the Banyan VINES network library - it appears that some SQL Server and MDAC updates enable this when it shouldn't be.
Run cliconfg.exe and disable Banyan VINES by selecting it in the right-hand box ('Enabled Protocols') and clicking Disable.
|
|
|
|
|
Hi there I habve a problem with a query on two relations
I have two relations here:
RESERVED_LEGS
PNR|FLNO|FLDATE|RSRVDATE
And
Tickets_Purchased
TKTNO|CLASS|DATEPURCHASE|PRICE|LEGID|PNR|WLR
this is a simple flight reservation scenario.
-When a passenger buys a ticket an entry is made in the tickets
purchased relation
- and if he makes a booking on that relation the ticket number is
associated with an autogenerated PNR value (other wise null if no reservation is made on ticket) and updated in the
tickets_PURCHASED relation
- AND LIKEWISE an entry with the correposnding PNR is made in the
RESERVED_LEGS TABLE where details of the flight number and date are
added.
There are 2 classes of tickets here namely
-first
-Third
The problem is that I have to make a query which shows the number of
resercvations made (in this case entries in the Reserved_Legs table) on
the basis of class of tickets upon which reservations were made as well
as broke up into months i.e.
------------------------------------------------
|Class |Jan|Feb|March|April|May|June|July|Aug.....
------------------------------------------------
------------------------------------------------
|First |130|500|320 |112 |120|100 |145 |145|
------------------------------------------------
|Third |402|180|110 |510 |410|110 |128 |162|
------------------------------------------------
The thing is that I'm not sure how this can be accomplished by an SQL
query, I've been hacking at it for a while and still have no luck. I
tried some queries but the thing is that at anytime there is no
guarantee that there will be a ticket of all teh classes booked in all
the months.
What would be the best way to do this. Any SQL that can create the
above relation or is any PL required. I'm doing this for a project and
I'm using SQL server 2000  and would like any suggestions.
|
|
|
|
|
Ah, the old cross-tab problem. Quite tricky in SQL Server 2000.
One technique that I've had success with is to define a temporary table containing all the months of interest:
DECLARE @months TABLE
(
MonthNum int NOT NULL
)
INSERT INTO @months
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12 You also need a table for categories:
DECLARE @classes TABLE
(
ClassName varchar(5) NOT NULL
)
INSERT INTO @classes
SELECT 'First' UNION ALL
SELECT 'Third' You can then join these tables onto your data to get an output that looks something like this:
Class | Month | Reservations
-----------------------------
First | Jan | 130
First | Feb | 500
First | Mar | 320
First | Apr | 112
...
First | Dec | count
Third | Jan | count
Third | Feb | count
...
Third | Dec | count You'll then need to juggle the results into the appropriate output format in the client application.
To give you a bit of a start, you might count the number of tickets issued in each month like so:
SELECT
ClassName AS Class,
DATENAME(month, DATEADD(m, MonthNum - 1, '20040101')) AS Month,
COUNT(TKTNO) AS [Count]
FROM
Tickets_Purchased TP
RIGHT JOIN
(
SELECT ClassName, MonthNum
FROM @classes CROSS JOIN @months
) Crit
ON TP.CLASS = Crit.ClassName AND MONTH(TP.DATEPURCHASE) = Crit.MonthNum
GROUP BY
ClassName, MonthNum
ORDER BY
ClassName, MonthNumI'm sure you can work out the rest from here.
|
|
|
|
|
This is quite complex, but I'm gonna give it a try
You can probably do this in two ways (without using a temporary table, that is).
1. If you're fine with the result being shown vertically:
SELECT CLASS, DATENAME([month], RSRVDATE) AS Month, COUNT(*) AS Total<br />
FROM RESERVED_LEGS <br />
INNER JOIN Tickets_Purchased ON RESERVED_LEGS.PNR = Tickets_Purchased.TKTNO<br />
GROUP BY CLASS, MONTH(RSRVDATE)<br />
ORDER BY CLASS, MONTH(RSRVDATE)
This should return something like:
CLASS Month Total
First January 100
First February 150
...
2. If you want the result to be shown horizontally (this is more difficult):
SELECT CLASS, <br />
(SELECT COUNT(*) FROM RESERVED_LEGS INNER JOIN Tickets_Purchased AS TP ON RESERVED_LEGS.PNR = TP.TKTNO WHERE MONTH(RSRVDATE) = 1) AND TP.CLASS = TicketsPurchased.CLASS) AS Jan, <br />
(SELECT COUNT(*) FROM RESERVED_LEGS INNER JOIN Tickets_Purchased AS TP ON RESERVED_LEGS.PNR = TP.TKTNO WHERE MONTH(RSRVDATE) = 2) AND TP.CLASS = TicketsPurchased.CLASS) AS Feb,<br />
(SELECT COUNT(*) FROM RESERVED_LEGS INNER JOIN Tickets_Purchased AS TP ON RESERVED_LEGS.PNR = TP.TKTNO WHERE MONTH(RSRVDATE) = 3) AND TP.CLASS = TicketsPurchased.CLASS) AS Mar,<br />
...<br />
...<br />
...<br />
FROM Tickets_Purchased<br />
GROUP BY CLASS<br />
ORDER BY CLASS
You can always use the query from #1 and format it to present it the way you want it to be, as I think it will be the most efficient query from both.
I hope it helps 
PS: You might want to tweak the SQL queries a bit to make it work
PPS: Maybe there is a more efficient way to do #2. Anyone?
Edbert P.
Sydney, Australia.
|
|
|
|
|
i am very new to SQL, and my first project has been a pretty big undertaking... i am grabbing a set of textfiles from a webserver, parsing them, and doing a number of calculations, etc on them, and storing the result in SQL, this is the main load on SQL... other loads are nearly irrelevant and are always simple selects with few joins.
i am having the problem that the CPU is getting toasted, and i know there is plenty of room for optimization in my (lengthy) procedure, which is being supplied to sql pretty much with no overhead (the parsing program uses very little cpu power and during an update always has a queue ready)
so if you see anything at a glance that can speed it up please help!
(i have guessed that having no extra indexes for this database is probably for the best?)
Method Caller (C#) from web downloader
Method (C#) i have a class that manages all the sql operations for the program
SQL Stored Procedure (SQL) huge thing that is CPU bound...
any hints are more than appreciated... this is for a project i intend to be release as code for anyone to use... but first i need to get it running at a decent speed...
if i did all the processing in .net and dumped it into a txt, and BCP'd it in, would it be faster?
i would still need many of the database operations there.
-Luke
|
|
|
|
|
One thing you may want to try is using a dataset and dataadapter. Read your files and fill in the dataset, then call the DataAdapter.Update rather doing the ExecuteNonQuery for every line read.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
