|
sorry..
i mentioned 'AS' instead of 'IS'..
the complete cursor is
CURSOR cur IS select a from abc.
this is oracle pl/sql..
|
|
|
|
|
|
hello my Friends
I do not convert sql server 2008 r2 database to sql server 2005
Please help me 
|
|
|
|
|
You can't simply back up and restore as you would going the other way, so you are going to have to script the schema across and then write a script to transfer the data.
Red-Gate have tool that we use to do this job (usually between UAT and production, never 2008 => 2005)
[edit] Red-Gate are not free or cheap and it may not be worth it for a one off job [/edit]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
I think you posted this reply to the wrong person, although from the titles I did not know you could use backup/restore, interesting.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
OK, thank you very much for your reply.
I moved my answer...
|
|
|
|
|
|
hi to all
i want to know that whats difference between below two query?
and which of those is better of another in performance respective?
SELECT ID INTO #TempLoan FROM bml.Loan WHERE id IN(1,2,100,700)
SELECT * FROM #TempLoan
and this query:
WITH TempLoan(id)
AS
(SELECT ID FROM BML.Loan WHERE id IN(1,2,100,700))
SELECT * FROM TempLoan
thank you for any idea
|
|
|
|
|
Here have some google foo[^]
The first thread seems to have a comprehensive discussion, try researching before dropping the question into a forum.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
I have the following stored procedure on MySQL. It's running perfectly the MySQL on my laptop but when I try to create it on my host server I am getting:
You have an error in your SQL syntax. check the manual that corresponds to yout mySQL server version for the right syntax to use near 'param_limit; END;
ELSE
BEGIN
SELECT event_log.event_log_date_time, even' at line 5
this is the stored procedure:
IF (param_limit IS NOT NULL) THEN
BEGIN
SELECT event_log.event_log_date_time, event_log.event_log_title FROM event_log WHERE event_log.event_log_member_id = param_member_id ORDER BY event_log.event_log_date_time DESC LIMIT param_limit;
END;
ELSE
BEGIN
SELECT event_log.event_log_date_time, event_log.event_log_title FROM event_log WHERE event_log.event_log_member_id = param_member_id ORDER BY event_log.event_log_date_time DESC;
END;
END IF;
Technology News @ www.JassimRahma.com
|
|
|
|
|
Caveat: I have absolutely no knowledge of MySQL, but the error message tells you precisely what you should do.
Jassim Rahma wrote: check the manual that corresponds to yout mySQL server version for the right syntax to use near 'param_limit; END;
I suspect they changed the syntax and you have an additional ; in your query.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
At a guess it does not like the semi-colons at the end of the statement(s), and/or the extra END; clause. However, in order to be sure you should check the manual.
Use the best guess
|
|
|
|
|
Which one of two below query is excellence of another?
and why?
thanks for your idea
query 1:
if @aa = 1
select @b = left(@a,10)
else
select @b = left(@c,10)
query 2:
SELECT @b = CASE WHEN @aa = 1 THEN LEFT(@a,10)
WHEN @aa = 2 THEN LEFT(@c,10)
END
|
|
|
|
|
The first query only checks for @aa =1. If @aa is not equal to 1, the second select is executed.
The second query checks for @aa = 1 or @aa = 2; if @aa is neither, then no select is executed.
|
|
|
|
|
The first thing isn't a query. And they are unequal, as Tim mentioned.
And have you considered: SELECT @b = LEFT(CASE @aa WHEN 1 THEN @a ELSE @c END ,10)
I expect they are all very similar in performance, but I would stick with pure SQL.
|
|
|
|
|
Hunting for micro-optimizations usually indicates that one is in need of a profiler, to discover where the real bottleneck is.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
ok thank you for reply.
but how can i do this ,i mean use of sql profiler for compare performance between two query?
thank you
|
|
|
|
|
mehdi.sabet wrote: but how can i do this ,i mean use of sql profiler for compare performance between two query?
It's usually not very helpful to try and find "small" differences like those; there's also some intelligence in the server, and something called an optimizer. If your queries are slow, find out which ones are the mayor culprits. You can simply time them, by whichever means you want. Then, optimize those.
Also periodically check your server for the usual stuff that a DBA would check; are your indexes still good? Does the Wizard from the management studio agree with that? (I believe there's an index-wizard in Sql2008+) Do the procedures fetch unnecessary data (think "select *" when "select cola, bolb" would do), do any of the tables need partitioning? How about caching stuff?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
ok
thanks for your good advice.
|
|
|
|
|
You're welcome 
|
|
|
|
|
Hi
I wonder if you could assist with a JOIN on three tables. I just cant see where I am going wrong.
StkQty Join Stk History - WORKS
SELECT tStk.WH + ' - ' + tStk.StockCode as [WP], tHst.HstTot
FROM stock_qty AS tStk
LEFT JOIN (SELECT stock_history.Location + ' - ' + stock_history.StockCode AS [WP], sum(stock_history.Qty) as [HstTot]
FROM stock_history
GROUP BY stock_history.Location+ ' - ' + stock_history.StockCode) AS [tHst] ON tHst.WP =tStk.WH + ' - ' + tStk.StockCode
GROUP BY tStk.WH + ' - ' + tStk.StockCode,tHst.HstTot
StkQty Join SalesOrders - WORKS
SELECT tStk.WH + ' - ' + tStk.StockCode as [WP], tSO.SoTot
FROM stock_qty AS tStk
LEFT JOIN (SELECT sales_order_header.Wh + ' - ' + sales_order_detail.StockCode as [WP], sum(sales_order_detail.QtyDespatched) as [SoTot]
FROM sales_order_detail, sales_order_header WHERE sales_order_detail.SalesOrder = sales_order_header.SalesOrder
GROUP BY sales_order_header.Wh + ' - ' + sales_order_detail.StockCode) as tSO ON tSO.WP = tStk.WH + ' - ' + tStk.StockCode
GROUP BY tStk.WH + ' - ' + tStk.StockCode,tSO.SoTot
StQty JOIN StkHistory JOIN SalesOrders - FAIL Syntax Error (missing operator)...
SELECT tStk.WH + ' - ' + tStk.StockCode as [WP], tSO.SoTot, tHst.HstTot
FROM stock_qty AS tStk
LEFT JOIN (SELECT sales_order_header.Wh + ' - ' + sales_order_detail.StockCode as [WP], sum(sales_order_detail.QtyDespatched) as [SoTot]
FROM sales_order_detail, sales_order_header WHERE sales_order_detail.SalesOrder = sales_order_header.SalesOrder
GROUP BY sales_order_header.Wh + ' - ' + sales_order_detail.StockCode) as tSO ON tSO.WP = tStk.WH + ' - ' + tStk.StockCode
LEFT JOIN (SELECT stock_history.Location + ' - ' + stock_history.StockCode AS [WP], sum(stock_history.Qty) as [HstTot]
FROM stock_history
GROUP BY stock_history.Location+ ' - ' + stock_history.StockCode) AS [tHst] ON tHst.WP =tStk.WH + ' - ' + tStk.StockCode
GROUP BY tStk.WH + ' - ' + tStk.StockCode, tSO.SoTot, tHst.HstTot
|
|
|
|
|
Richard I would like to make a suggestion - used IDs in all your sub queries, I presume your product records all have a ProductID.
Then after you have the result you want join the result to your product table to get the descriptors. Joining on descriptors is not a good idea, joining on concatenated descriptors is even worse!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi Mycroft
Thanks for the Advice - I agree but would have to re-write just about all the code in the app.
The app was initially never intended to have multiple locations/warehouses - this requirement came later.... FRUSTRATION!!! So I have a stock_master table with all the product codes, and then a stock_qty table, which has all the stock codes for all the locations. So the only unique identifier I have is the stock_qty.WH + stock_qty.Product. Not sure if it would help if I added some sort of ID to this table, because the history table and sales order tables cant really share the same ID. Not sure if there is a way to add table columns with the concatenation of the location + product and use this, and if this could be done by some sort of trigger or something in the database as opposed to re-writing all the code in the app.
I think I'm still stuck with getting these ugly queries to work.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
