|
Alivemau5 wrote: My question is, what is the correct way of modeling this?
Normalization[^] would be the way to go, with most tables ending up in the third normal form.
A joking reference during design would be "The Key, the whole Key, and nothing but the Key, so help me Codd[^]" 
I are Troll 
|
|
|
|
|
I say 2 because that would be normalized. The enum would prevent hard coding but you really don't want to have to refer to app anytime you want to know what the value is...
For instance if you are just looking at the data in the table not in the app.
Also if you do it the second way rather then the enum you don't have to modify the enum every time you add a row.
Humble Programmer
|
|
|
|
|
The second way, definitely -- plus an enumeration in your code.
P.S. See my GenOmatic[^].
|
|
|
|
|
Isn't bad that the table with states would be finite (5 registries) and readonly?
|
|
|
|
|
No, and it would leave room for more in the future. And maybe you would use it as the base for other sports, like American football or baseball or who knows what.
It's a proper pattern to use anyway, even if there are only two entries.
|
|
|
|
|
hi, iam using asp.net with c# with sql server databae
the table fields are
username nvarchar
startdate datetime
enddate datetime
paytype int
reminder int
while registering the user will selects the startdate and enddate and the pay type is 6months or 1year and he will select the reminder before 10days
then if he selects for 6months then before 10days i have to send email. if he selects for 1year i have to send for every 6months before 10 days
then how to write the querry for this condition please give the example which helps me.
|
|
|
|
|
developerit wrote: then how to write the querry for this condition please give the example which helps me.
You'll need to write a SELECT[^] statement, with a filter on the EndDate.
You'd have to compare whether this EndDate equals "today" + 10 days. You can get todays date by using the GETDATE()[^]-function, and add 10 days using the DATEADD[^] function.
Good luck
I are Troll
|
|
|
|
|
Most of the time there are two/three record in the table.
How can you do it in SQL
IF Status = 1 or status = 0 THEN Desc = "In Progress"
TabID Status
T1 1
T1 0
If Status = 1 or status = 1 THEN Desc = "Ok"
TabID Status
T1 1
T1 1
If status = 0 or status = 0 THEN Desc = "failed"
TabID Status
T1 0
T1 0
Select should give me 1 row only
My example is not correct, kindly enhance.
select tabid, GroupDesc = case when groupstatus = '1' or groupstatus = '0' then 'In Progress'
else 'Ok' end
from tab1
where tabid = T1
Dabsukol
|
|
|
|
|
Try this:
SELECT tableIdentity,
case when groupstatus = 1 or groupstatus = 0 then 'In Progress' else 'OK' end GroupDesc
from tableOne
where tabid = T1;
You should also physically confirm that you have only a single row named T1.
|
|
|
|
|
Select Min(Priority) from Outbox where [Transmitted] = false and [FolderId] IN (7,8) and
[Deleted] = false and [InterfaceNo] =@InterfaceNo and [SwitchingAddressNo] NOT IN
(Select [SwitchingAddressNo] from AddressBook where [Block] = true and [Deleted] = false
The above query i wrote, and its working fine.
I want to know the performance related issues with this?
If its ugly?
then how can i improve the same.
|
|
|
|
|
You are using NOT IN in you query, it is not good for the query performance as the query is not going to use benefits of indexes defined on the table and will use a sequential scan on the entire table, try to use NOT EXISTS.
WWW, WCF, WWF, WPF, WFC .... WTF
|
|
|
|
|
Does this apply to all DBs? I have never used NOT EXISTS...
Humble Programmer
|
|
|
|
|
I am sure about SQL Server, Oracle and Sybase but no idea about other DB servers.
WWW, WCF, WWF, WPF, WFC .... WTF
|
|
|
|
|
Indexes and Index Hints - possibly.
|
|
|
|
|
I have created a database with n number of tables. i want to delete some rows from some tables matching certain criteria. This i have to do dynamically. I am a beginner to sql. If someone in here can shed me a light will be great.
|
|
|
|
|
Something like:
delete from table where mycolumn = 0
The where is where you put conditional statements that match your requirement. So, for instance, let's say I have a person table and one of the columns tells me that a person is active or not I might use:
delete from person where IsActive = 0
me, me, me
"The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!"
Larry Niven
|
|
|
|
|
Do you mean that you want to delete the same information from multiple tables?
Humble Programmer
|
|
|
|
|
For some reason, my query causes a data type mismatch error due to where clause #5 below. I am not sure how this is possible given clause #2 & 3.
Where clauses
1) (NOT ID_BB_SEC_NUM_DES IS NULL AND TYPENAME(ID_BB_SEC_NUM_DES)='String')
2) AND (LEN(ID_BB_SEC_NUM_DES) BETWEEN 9 AND 13)
3) AND ( RIGHT(ID_BB_SEC_NUM_DES, 1) IN ('W', 'M', 'Y') )
4) AND ( LEFT( RIGHT( RIGHT( ID_BB_SEC_NUM_DES, LEN( ID_BB_SEC_NUM_DES )-6 ), 3), 1) IN ('B','R','V') OR LEFT( RIGHT( RIGHT( ID_BB_SEC_NUM_DES, LEN( ID_BB_SEC_NUM_DES )-6 ), 4), 1) IN ('B','R','V') )
5) AND ( INSTR( RIGHT(ID_BB_SEC_NUM_DES, LEN(ID_BB_SEC_NUM_DES)-6), 'O')=0)
|
|
|
|
|
I'm not familiar with INSTR, and googling SQL instr function told me its semantics depend on the database one uses. I guess that is where the problem originates.
|
|
|
|
|
I am querying a csv file using ADO .NET in C++, which uses the JET provider. I can select the Instr statement, but cannot apply the where statement for some reason.
|
|
|
|
|
AFAIK Jet providers target MS Access, and this[^] shows a different INSTR syntax than what you are using.
|
|
|
|
|
I took a look at the page and it looks like there are multiple correct syntaxes. I am using the syntax like Instr ("Tech on the Net", "the"), except that I am using single quotes instead of double quotes.
Thanks, that's a helpful sight. I now know that MID can be used instead of SUBSTR, which I was replicating with left and right.
|
|
|
|
|
That is one of the ugliest schema I have ever seen, reminds me of SAS.
Anyway the error in (5) is due to comparing 'O' to 0, you are trying to compare a string to a numeric, one of them is wrong!
5) AND ( INSTR( RIGHT(ID_BB_SEC_NUM_DES, LEN(ID_BB_SEC_NUM_DES)-6), 'O')=0)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm trying to exclude ID's where the letter 'O' appears after the 6th character in the ID.
Instr returns an integer indicating the base one position where the character is found. I think it returns 0 if the character is not found.
It's a bloomberg schema btw
I realized that I can however directly tell INSTR to start at position 7 and look for the character 'O', so I will try doing that and see if I still have the same problem.
After I get the results of my query, I create a pivot table using ADO .NET. Then I will try to figure out how to do a custom sort of the columns. Easy for me in C or C++ STL, but I am not used to doing it in .NET
modified on Monday, March 29, 2010 1:44 AM
|
|
|
|
|
Ted2102 wrote: It's a bloomberg schema btw
Yah that would be right, stupid 1980's design.
What database use your using btw, I found INSTR is not a SQL function so I can't follow through on the error.
I think you are going about the process completely wrong, you are treating a csv file as a table in a database, trying to query and then bitching when the query does not work.
I would import the data into the database, even if you just query it and chuck it away you will not then be using the jet crap to do real work and you can use SQL properly. Don't try and clean up the data format, just load it all into varchar fields.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
