|
This is a very bad SQL programming technique. Read about the poor performance and dangers of SQL Injection. Since T-SQL can handle a long parameter list, use that instead.
CREATE PROCEDURE X (p01 INTEGER, p02 INTEGER, ..p99 INTEGER)
AS
BEGIN ..
SELECT ..
FROM ..
WHERE p IN (COALESCE (p01, 0), COALESCE (p02, 0), COALESCE (p99, 0)zzz0;
.. END;
There are better ways to write this in SQL-2008 and Standard, but the idea is to build a one-coolumn table from parameter and remove the NULLs. Also, by definitio, there is no such thing as a "status_id"; nan attribute can be an identifier or a status, but not both at once.
|
|
|
|
|
You may have better luck just using a parameterized command rather than a stored procedure.
|
|
|
|
|
Hi
I am using MS Access 2003 as database and also as front end. I want to open crystal reprot on MS Access form using coding in access. I want to open report when a buton on form is clicked.
I m searching for this but could not find the solution
|
|
|
|
|
You're more than likely going to have to use VBA to accomplish this task.
I believe that you would be better served creating your reports by using Crystal Reports to hit the MS Access db than the way you seem to be approaching this problem. YMMV.
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
The mans a masochist, Access AND Crystal Reports, what a nightmare.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a product table with 100000 products and also I have a user table with 5000 records. and suppose a user can give us feedback about any combination of this products. So suppose he chooses to compare products 1,3,100,200,400,500 and so on. (he can send us another feedback about some other products)
So here is my question, I just want to know what an expert database designer would think about this situation where the number of products and users are big. One way to store this ratings are in a single string like: 1#5 3#4 100#5 .... x#y means that he gave y stars to product with id of x. I can load this vote_string in my php script for example and extract the details of it.
So the rating table would likely have this structure: id, user_id, vote_string
Another way is to store this feedback in this structure:
vote Table: id, user_id, date
vote_details Table: vote_id, product_id, vote ==> (vote_id,products_id) as primary key and vote_id is foreign key.
(or may these 2 tables can be compressed in a single table like id,user_id,product_id,vote [id,user_id,product_id] as primary key)
So i guess it's very easier to query in the second design but it consumes more space and time for each query, also in case of product deletion it's quite better to use the second design. What would you do?
Any kind of idea is appreciated.
|
|
|
|
|
All you need is a single votes Table: user_id, date, product_id, vote
And never store something that isn't a string in a string!
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
|
I'd go with Luc's suggestion, it's spot on for what you need.
Would the string be able to handle the pathological case where a user rates every single product in the db?
Not likely but there are some strange people out there. 
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
|
|
|
|
|
The first option is definitely NOT viable, you will never be able to query the product/vote value.
Your 2nd option is technically correct, if you need to manage the instance of a user/vote event then the 2 table structure saves you storing the userid and date multiple times and is therefore technically correct.
If you do not need to manage the vote event (when did user x vote) then you can use Luc's suggestion. Personally I would use your 2nd structure but them I'm a pedant where data is concerned having been bitten by lousy design in the past.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I don't mind a little pedant discussion, so here it goes.
For a simultaneous vote on N products the one-table approach takes N records of 4 fields each; the two-table approach takes N records of 3 fields each plus one record of 3 fields.
So the storage break-even would be where 4N=3N+3, hence N=3 (that is assuming IDs and dates have same cost). Therefore the 2-table approach would save space only for N>=3. How likely that is depends much on the correlation between products, and the kind of GUI used for voting.
Also selecting/searching/ordering the votes would take an extra join in the 2-table approach.
Having heard no real arguments in favor of 2-table (e.g. "we expect users to vote on an average of 5 products), I would call it over-normalized; to carry it somewhat further, one could come up with 3 tables, by listing all (product_id, vote) combinations in a third table. That would favor situations where some products always get high votes, and others always get low votes. However there are no facts to justify this approach.
Hence I'm with Einstein, who said something along the line "the simplest approach that covers it, is the right approach" or was it "you should simplify as much as possible, but no more". So I'll stick with the 1-table design.
Cheers.
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
modified on Sunday, July 19, 2009 12:23 AM
|
|
|
|
|
Luc Pattyn wrote: So the storage break-even would be where 4N=3N+3
I don't think the storage size/fields is relevant these days, I was focused more on the normalisation issue of storing redundant data.
I don't agree with the over normalised point although the additional join has it's issue. Trying to get the number of times a user has voted from the single table structure would be more difficult than with the 2 table approach. I know it was not requested but I can hear his manager (or marketing) asking for the information about 3 weeks after the thing goes live and while the solution is trivial it does highlight the issue.
Nope I'd go for the 2 table approach but acknowledge that neither structure is perfect, thats where the art comes in and while my design may not suit all it would suit me.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Interesting. I'll keep your marketing remark in mind next time I'm faced with such decisions.
Cheers.
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
|
All,
Just looking for peoples advice based on experience with sql high availability.
Basically in the past we have used SQL Clustering without any issues for high availability, I have used this in many companies how ever we are currently moving environements which I will not have the option to use clustering.
So basic setup, two sql servers (active/passive roles) running SQL 2008 standard, database files are stored on a high speed SAN.
So need some options, I know I can use log shipping, however failure time is >1min. Also for patching how do I restart a server and ensure the other server which is being logged shipped to has the latest data ?
Mirroring is a option, however according to MS mirroring should not be used on more than 10 databases. We have lots more!
The last option which seems bad practice but could work is simply kill all users, detach db's and then reattach to other server. Any issues with this ?
Ignore any issue with client connectivity based on server names, this can be resolved in different ways.
Any real advice appreciated, I have read most of the SQL documentation so no need to just forward me to there
Thanks
Shane
|
|
|
|
|
Hello friends,
I need a optimized query for below condition
CaseName ScriptName ID Status Version
---------------------------
TC1 TS1 1 PASS R1
TC1 TS1 2 FAIL R1
TC1 TS1 3 PASS R1
TC2 TS1 1 PASS R1
TC2 TS1 1 PASS R1
TC3 TS1 1 PASS R1
TC1 TS2 4 PASS R2
TC1 TS3 5 PASS R2
Want to get distinct casename, ScriptName,status,Max(ID),version
Result I need is
TC1 TS1 3 PASS R1
TC2 TS1 1 PASS R1
TC3 TS1 1 PASS R1
TC1 TS2 4 PASS R2
TC1 TS3 5 PASS R2
When i tried using the below queries, both query taking hell lot of time(seems it may stuck). Table contains atleast 7 lakhs(700 thousands) of records.
SELECT DISTINCT CaseName,ScriptName,ID,Status,Version FROM Metrics AS t1 WHERE ID = (SELECT MAX(ID) FROM Metrics WHERE CaseName = t1.CaseName AND ScriptName = t1.ScriptName AND Version = t1.Version)"
SELECT CaseName,ScriptName,ID,Status,Version FROM Metrics WHERE ID IN (SELECT MAX(ID) from Metrics GROUP BY CaseName,ScriptName,Version)"
Thanks in Advance
Jishith
|
|
|
|
|
SELECT CaseName,ScriptName,ID,Status,Version FROM Metrics a
WHERE EXISTS(SELECT CaseName FROM Metrics
WHERE a.CaseName=CaseName AND a.ScriptName=ScriptName AND a.Version = Version
GROUP BY CaseName,ScriptName,Version
HAVING MAX(ID)=a.ID)
I hope it can reduce your time
|
|
|
|
|
Is it helpful?
Select distinct CaseName , ScriptName , max(ID),Status ,Version
from Metrics Where Status = 'PASS'
Group By CaseName , ScriptName ,Status ,Version
|
|
|
|
|
Hi vica dianto,
This Query also takng much time.. but much faster than what i posted. I want to insert these result in an access table also. Is the insertion creating the touble or the query itself?
Hi abcurl,
"Status = 'PASS'".. Status can be fail or pass, but if two similar Cases having different status I want the lastest status, ie Case having Laresgt ID. One more thing i want the result to be inserted in an access table also
Thanks alot for the replies
Jishith
|
|
|
|
|
I have a typed dataset. It uses a connection string from the web.config file. I have changed the database name in the connection string, but it is still taking the old database name. How do I get that changed?
-----Have A Nice Day-----
|
|
|
|
|
If that's the case, then it's not really using the connection string from your Web.config file Use the Dataset Designer to make sure you're really referencing your external connection string, and not using a copy saved in the dataset's code.
|
|
|
|
|
Can you please guide me as to what needs to be done and how do I change it.
-----Have A Nice Day-----
|
|
|
|
|
I want to pass an array of Id's to a stored proc to be processed, but i keep getting an error saying : Error converting data type varchar to numeric.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_getTranfer] @TRANSFER_ID int = null, @COMPANY VARCHAR(5),@FORMTYPE VARCHAR(30), @TRANSFER_IDS VARCHAR(500) = NULL, @SEAL_DATE datetime = NULL AS
-- Declare variables
DECLARE @rc Int, @ThisID varchar (10), @Pos int
IF @SEAL_DATE IS NULL
BEGIN
SELECT
COMPANY,
FORMTYPE,
COMPANY_NAME,
COMPANY_ADDRESS1,
COMPANY_ADDRESS2,
IMID,[DESCRIPTION],AMOUNT,AMOUNT_TRANSFER,
CONSIDERATION,
TRANSFER_ID,
[DATE],
CURR,
CMF_NAME_1,
CMF_NAME_2,
CMF_ADDR_1,
CMF_ADDR_2,
CMF_ADDR_3,
CMF_ADDR_4,
CMF_POSTAL_CODE,
Cons_Curr = CASE
WHEN LTRIM(RTRIM(Cons_Curr)) <> '' THEN Cons_Curr
ELSE CURR
END,
-- Cons_Curr,
TRANSFERYEAR,
TRANSFERMONTH,
TRANSFERDAY,
dbo.CurrencyToWords(CURR) AS CURR_WORDS,
---dbo.CurrencyToWords(Cons_Curr) AS CONNS_CURR_WORDS
CONS_CURR_WORDS = CASE
WHEN LTRIM(RTRIM(CONS_CURR)) <> '' THEN dbo.CurrencyToWords(CONS_CURR)
ELSE dbo.CurrencyToWords(CURR)
END
FROM
VW_TRANSFERS
WHERE
TRANSFER_ID= @TRANSFER_ID
AND
FORMTYPE= @FORMTYPE
AND
COMPANY = @COMPANY
OR
TRANSFER_ID IN (@TRANSFER_IDS)
END
-------------------
ELSE
SELECT
COMPANY,
FORMTYPE,
COMPANY_NAME,
COMPANY_ADDRESS1,
COMPANY_ADDRESS2,
IMID,[DESCRIPTION],AMOUNT,AMOUNT_TRANSFER,
CONSIDERATION,
TRANSFER_ID,
[DATE],
CURR,
CMF_NAME_1,
CMF_NAME_2,
CMF_ADDR_1,
CMF_ADDR_2,
CMF_ADDR_3,
CMF_ADDR_4,
CMF_POSTAL_CODE,
--Cons_Curr,
Cons_Curr = CASE
WHEN LTRIM(RTRIM(Cons_Curr)) <> '' THEN Cons_Curr
ELSE CURR
END,
TRANSFERYEAR,
TRANSFERMONTH,
TRANSFERDAY,
dbo.CurrencyToWords(CURR) AS CURR_WORDS,
---dbo.CurrencyToWords(Cons_Curr) AS CONNS_CURR_WORDS
CONS_CURR_WORDS = CASE
WHEN LTRIM(RTRIM(CONS_CURR)) <> '' THEN dbo.CurrencyToWords(CONS_CURR)
ELSE dbo.CurrencyToWords(CURR)
END
FROM
VW_TRANSFERS
WHERE
FORMTYPE= @FORMTYPE
AND
COMPANY = @COMPANY
AND
DATE = @SEAL_DATE
OR
TRANSFER_ID IN (@TRANSFER_IDS)
Ferron
|
|
|
|
|
Your problem is that SQL Server does not allow an in clause to be a variable:
TRANSFER_ID IN (@TRANSFER_IDS)
for example will not work. What you need to do is split the variable into a table variable and join to that. Google has loads of examples
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
folks,
I have a table with column upddate with datatype as datetime.
I updated the table
update table set upddate=getdate()
when i select the values all the upddates are same.
I expected difference in milli seconds to find the last updated record.
can somebody help me out.
|
|
|
|
|
Actually, your expectation is not wrong. But the effect can only be seen if there are millions of records. For lesser number of rows, it will reflect the same time.
-----Have A Nice Day-----
|
|
|
|
|
Simple really, the update happened so quickly they are all the same time. Also be warned, SQL Server is only accurate (from memory) to a third of a millisecond.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
