Database

Re: what's the problem with this?!

6-Apr-13 23:09

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

Richard MacCutchan7-Apr-13 1:16

Richard MacCutchan

7-Apr-13 1:16

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

compare performance between two query

mhd.sbt4-Apr-13 23:30

mhd.sbt

4-Apr-13 23:30

Which one of two below query is excellence of another?
and why?
thanks for your idea

query 1:

SQL

if @aa = 1 
select @b = left(@a,10)
else
select @b = left(@c,10)

query 2:

SQL

SELECT @b = CASE WHEN @aa = 1 THEN LEFT(@a,10)
WHEN @aa = 2 THEN LEFT(@c,10)
END

Tim Carmichael5-Apr-13 3:11

Tim Carmichael

5-Apr-13 3:11

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.

PIEBALDconsult5-Apr-13 3:21

PIEBALDconsult

5-Apr-13 3:21

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.

Eddy Vluggen5-Apr-13 7:21

Eddy Vluggen

5-Apr-13 7:21

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 Suspicious | :suss:

If you can't read my code, try converting it here[^]

mhd.sbt5-Apr-13 8:44

mhd.sbt

5-Apr-13 8:44

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

Eddy Vluggen5-Apr-13 8:54

Eddy Vluggen

5-Apr-13 8:54

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 Suspicious | :suss:

If you can't read my code, try converting it here[^]

mhd.sbt5-Apr-13 9:01

mhd.sbt

5-Apr-13 9:01

ok
thanks for your good advice.

Eddy Vluggen5-Apr-13 9:04

Eddy Vluggen

5-Apr-13 9:04

You're welcome Smile | :)

JOIN 3 Tables

Richard.Berry1004-Apr-13 19:01

Richard.Berry100

4-Apr-13 19:01

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

SQL

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

SQL

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)...

SQL

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

Mycroft Holmes4-Apr-13 20:04

4-Apr-13 20:04

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

Richard.Berry1005-Apr-13 2:49

Richard.Berry100

5-Apr-13 2:49

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.

Mycroft Holmes5-Apr-13 13:12

5-Apr-13 13:12

Oh sh*t - sorry to hear that. You might try creating views with calculated unique fields, I know it comes down to the same thing but it may be easier to work with the key fields rather than the concats.

I would seriously look at a rewrite, you will spend more time pissing about trying to get a badly designed DB to work than you will on a rewrite. This I know, having done it a number of times and walked away from a contract where they refused to do the rewrite.

SQL Server will allow you to add an identity field to an existing table, getting the foreign key in place is a bitch. If you can add the keys you can progressively rewrite the DB using those instead of a complete break.

Never underestimate the power of human stupidity
RAH

Richard.Berry1005-Apr-13 23:41

Richard.Berry100

5-Apr-13 23:41

Thanks Mycroft - I think I am going to go with your suggestion - re-write. Do you have any pointers you could offer to handle the same stock code in various locations? Should I use an Identity field, or use Location+StockCode as the PK? My starting point should be the stock_master table, which at this stage only has unique stock items. So I could write some code to duplicate all these items across all the warehouses, and let SSE assign an ID value? I could then do away with the stock_qty table, since all this has is a location, stock_code and qty, so I could just add qty to the stock master table. Then for BatchHistory, StockHistory, SalesOrders, PurchaseOrders, WarehouseTransfer tables, I guess I could also write code to lookup the ID in the stock master and populate an ID field

At the moment the app is using an MS Access DB which I am busy migrating to SSE because there are all sorts of problems with data not getting written to the database which I would assume could be due to
a) Poor coding
b) Concurrency problems with MS Access
c) Possibly a bad network

The company sells some pharmaceutical products which require batch traceability so I cant lose their data...

So yeah, sure as hell paying my school fees on this one!!! And as for MS Access, lets just say we are not very good friends at the moment!

modified 6-Apr-13 7:17am.

Mycroft Holmes6-Apr-13 14:14

6-Apr-13 14:14

Your relationship with MS Access is going to get worse - sorry it really is a crap tool for database work.

There are a number of schools of though for the distributed database. Some like to us GUIDs, others like the location/ID concatenation. There are some where you have a master server for such things as products, only HO can add a code etc. I don't even think there are good guidelines on which to use where.

Personally I prefer the location/id solution, I know of at least 1 respected member here who would recommend the GUID path (PITA to read the ID) so it will be your choice.

You should look into replication before you start, designing so the data can be consolidated from the start is always a GREAT idea.

Depending on your business requirements I would opt for a parallel application and write a script that can migrate your data to the new structure. This allows you to completely redesign your structure getting rid of the crap design you have now.

Don't finalise your DB design until the bulk of yor app development is done, then make sure your migration script works. It should be possible to migrate your data at any time and repeat until the dev is complete.

Consider using a web based solution if the local speed is acceptable - eliminates the distributed problems but introduces others (uptime issues) and criticallity. I recommend not using the cloud if your data is both critical and confidential.

Good Luck you have interesting time ahead of you.

Never underestimate the power of human stupidity
RAH

SQL QUERY

girijeshkumar3-Apr-13 20:08

3-Apr-13 20:08

plz help me am begginner to ms sql server

my question mulitple rowwise data should be display to one columnwise

suppose from table1 i created column like empname,reference

table2 i created column like reference and salary details

join both table retrieve and display empname, reference,and all salary details for particular employee in one column

table1 value
-----------------------------|
empname | referenceno
-----------------------------|
girijesh | 111
-----------------------------|
ajay | 121
-----------------------------|

table2 value
-----------------------------------------------------|
referenceno | salary
-----------------------------------------------------|
111 | jan-10000
-----------------------------------------------------|
111 | feb-20000
-----------------------------------------------------|
111 | mar-30000
-----------------------------------------------------|
121 | jan-10000
-----------------------------------------------------|
121 | feb-20000
-----------------------------------------------------|

so now result like below
-----------------------------------------------------|
empname | reference | salary
-----------------------------------------------------|
girijesh | 111 | jan-10000,feb-20000,mar-30000
-----------------------------------------------------|
ajay | 121 | jan-10000,feb-20000
-----------------------------------------------------|
i want query to display like above result

https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/[^]

i.j.russell3-Apr-13 21:36

i.j.russell

3-Apr-13 21:36

girijeshkumar3-Apr-13 23:05

3-Apr-13 23:05

in query u put case statement 1,2,3 like that but problem is reference number should chage every time wat to do ... then how to retrieve the data..

i.j.russell4-Apr-13 2:07

i.j.russell

4-Apr-13 2:07

Read the section on 'Concatenating values when the number of items is not known'.

Bernhard Hiller3-Apr-13 21:38

Bernhard Hiller

3-Apr-13 21:38

The function you need is called PIVOT. See e.g. http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx[^]

girijeshkumar3-Apr-13 23:07

3-Apr-13 23:07

plz gothrough it and plz understand my concept and solve it.. bcoz u not properly understand my output

ritesh.gupta55553-Apr-13 22:28

ritesh.gupta5555

3-Apr-13 22:28

select t1.empname,t1.referenceno,t2.salary
from table1 t1 join table2 t2
on t1.referenceno=t2.refereceno

girijeshkumar3-Apr-13 22:58

3-Apr-13 22:58

its show answer like below
-----------------------------------|
empname | reference | salary
-----------------------------------|
girijesh | 111 | jan-10000
-----------------------------------|
girijesh | 111 | feb-20000
-----------------------------------|
girijesh | 111 | mar-30000
-----------------------------------|
ajay | 121 | jan-10000
-----------------------------------|
ajay | 121 feb-20000
-----------------------------------|

this wat if i executed ur query i got but i want reslut like below see ur ouput and wat am accepted output
so now result like below
-----------------------------------------------------|
empname | reference | salary
-----------------------------------------------------|
girijesh | 111 | jan-10000,feb-20000,mar-30000
-----------------------------------------------------|
ajay | 121 | jan-10000,feb-20000
-----------------------------------------------------|
i want query to display like above result