|
Many ways to do validation in database
(1) Constraints
(2) SP
(3) Triggers
|
|
|
|
|
|
Hi,
I have a table where the results are sorted using an "priority" column, eg:
Doc_Value priority
------------------------
aaa 0
xxx 1
bbb 3
ccc 0
aaa 2
I need the SQL results to be in a specific order based on the "priority", but not in ascending or descending order. The order
that I want them in is 1,2,3,0,0,0,0....
Highest priority start with 1 to infinite (9999) number but the lowest priority is 0...
Any suggestion guys? 
ma tju
Software Application Engineer
Petaling Jaya,Selangor, Malaysia
Ring Master SB MVP 2008 
Petaling Jaya MOP (Otai)
|
|
|
|
|
Use a case statement in your order by clause
order by case when priority = 0 then 9999 else sequence end
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks a lot its working. +5 
ma tju
Software Application Engineer
Petaling Jaya,Selangor, Malaysia
Ring Master SB MVP 2008
Petaling Jaya MOP (Otai)
|
|
|
|
|
Introduce a new table called PriorityOrder with something like:
Priority PriorityOrder
-------------------------
0 5
1 1
2 2
3 3
4 4
And then join your tables with this table on the Priority column and then Order by the PriorityOrder column.
This design gives you the flexibility to change the ordering any time by changing the contents of this table instead of changing code.
|
|
|
|
|
Wow what a nasty solution, he simply wants to move the 0 value records to the end of the sort and you want to create and support another table. So every time a new record is created he potentially needs to update the priority table.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Priorities are usually limited and may be used in more than one table. This is a generic solution which works best if Priority is stored in its own reference table (a.k.a. Master table), in which case the PriorityOrder column can appear in the same table.
|
|
|
|
|
Hi Shameel,
Thanks for your reply to my problem.
Like Mycroft Holmes said, I just want a simply to move the 0 value records to the end of the sort.
Regards.
ma tju
Software Application Engineer
Petaling Jaya,Selangor, Malaysia
Ring Master SB MVP 2008
Petaling Jaya MOP (Otai)
|
|
|
|
|
Hi,
Mycroft's solution is elegant and I like the way he presents the solution.
However, there are a few more ways which will accomplish the task, though again I like Mycroft's solution
declare @t table(docvalue varchar(50),priority int)
insert into @t select 'aaa',0 union all select 'xxx', 1 union all select 'bbb', 3 union all
select 'ccc',0 union all select 'aaa',2
Query1:
select docvalue,priority from @t where priority <> 0 group by priority,docvalue
union all
select * from @t where priority = 0
Query 2:
select distinct * from @t where priority <> 0 group by priority,docvalue
union
select * from @t where priority = 0
Output:
docvalue priority
xxx 1
aaa 2
bbb 3
aaa 0
ccc 0
Niladri Biswas
|
|
|
|
|
Hi guys.. Actually i have an web application running on vs2003 it fetches the records from a list of xml file now i want to import all my data that is present in the xml file to sql db(sybase).. can any one guide me as to how can i import the data using c# coding... i googled alot dint get the apt one..I tried using Sqlbulkcopy but that doesnt work with vs2003... and also there are duplicate rows in my xml files...
Thanks in advance...
|
|
|
|
|
Read in your xml file
convert it into a datatable
clean out the duplicates
save each record to the database via a database connection and a sqlcommand
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanx for ur response... since im a fresher do u hav any sample codes or can u mention any tutorials that wil provide me a helping hand... anyways thanks for ur reply...
regards,
Thashif
|
|
|
|
|
Sorry, that's maybe 4 hours work and if I did that for you then how do you expect to become anything but a "fresher"
As a general comment, learn to use the keyboard, typos I can live with, but that crap you used is just horrible.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I neva asked u 2 work or complete the task for me.. i only asked u 2 mention any tutorials or sample codes if u know.. wel learn 2 use keyboard ???? what does that mean??? again, crap??? i dint get u....
|
|
|
|
|
<<thashif>> wrote: I neva asked u 2 work or complete the task for me
You are right so take the ideas I gave you one line at a time and feed them into Google, look at the results and you will find lots of articles and tutorial on each of the subjects. Then put them together in a solution.
As for your keyboard use, are you serious, there are 101 keys on most keyboards and you want to use your like a bloody phone. Wake up to yourself and communicate like a professional or at least competent person.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have uninstalled sql server express 2005 and installed sql server express 2008 but now I found that almost all )reporting services) windows services are still exists and that's why the reporting services installation for 2008 was failed because it was using the same instance name.
what can I do?
|
|
|
|
|
I am using SQL server 2008 express on WIndows server 2008 and everything is fine so far but today is the first time for me to use the reprting services..
when I go to the reporting services web page I just get a blank white page with the following text:
server/ReportServer_SSGSQL - /
Microsoft SQL Server Reporting Services Version 10.0.1600.22
what can I do?
Jassim Rahma
|
|
|
|
|
Hi All ,
If have table contains of seven columns or more, and i need to return the douplictate records across only three column.how i can writ this using SQL query ?
for example i have this table :
Clmn1 | Clmn2 | Clmn3 | Clmn4 .......|Clmn7
AJ 35 25 0 19
BQ 10 12 1 8
KS 35 25 3 19
i need to return all records that have duplicat value in this three columns only "Clmn2" , "Clmn3" and "Colmn7" . like this:
Clmn1 | Clmn2 | colmn3 | clmn4 .......|colmn7
AJ 35 25 0 19
Thanks,
KS 35 25 3 19
|
|
|
|
|
Hint: Use GROUP BY clause with HAVING COUNT(*) > 0
|
|
|
|
|
you know , Your Are Great !!
Many thanks,
could you please allow me to ask you a question :
i wrote this:
<code>SELECT</code> MyTable.Column1 , S.Column2,S.Column3, S.Column4
<code>FROM</code> MyTable , (
<code>Select</code> MyTable.T2 , MyTable.T3, MyTable.T4
<code>From</code> MyTable
<code>Group By</code> MyTable.T2 , MyTable.T3, MyTable.T4
<code>Having</code> ( Count(Sheet1.T2)>1 and Count(Sheet1.T3)>1 and Count(Sheet1.T4)>1 )
) as S
<code>Where</code> MyTable.T2=S.T2 and MyTable.T3=S.T3 and MyTable.T4=S.T4 and it is work , but are there better than this way ? becuse when i put Column1 column in the nested select clause with out to be in group by cluase , i got error that show " the coulmn1 is not in the agregation function " so why this is happen? . instade of that i use join to get Column1 , was what i did right ?
|
|
|
|
|
Hi,I hope this will help you
SELECT
(SELECT top 1 m2.Clmn1 FROM mytable m2 WHERE m2.Clmn2= m.Clmn2) Clmn1 ,--by this subquery you get first value of clmn1 column
clmn2,clmn3,clmn7
FROM mytable m
GROUP BY m.clmn2,m.clmn3,m.clm7
HAVING
COUNT (col2) > 0 AND
COUNT (col3) > 0 AND
COUNT (col7) > 0
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
yeeees !! Blue_Boy , realy realy i appreciate your reply. you are great and helpfull.
yor answer is very celever .
Many Thanks
|
|
|
|
|
You are always welcome bro
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Hello,
Following the faq, I am attempting to query the a DB to obtain the list of table names with the following query:
SELECT * FROM sqlite_master WHERE type='table' ORDER BY name
I am using an ODBC driver and have written code in c#:
DbConnection.Open();
OdbcCommand DbCommand = DbConnection.CreateCommand();
DbCommand.CommandText = ("SELECT * FROM sqlite_master WHERE type='table' ORDER BY name");
DbReader = DbCommand.ExecuteReader();
List<string> TableNames = new List<string>();
for (int i = 0; i < DbReader.FieldCount; i++)
{
TableNames.Add(DbReader.GetName(i));
Trace.WriteLine(DbReader.GetName(i));
}
DbReader.Close();
DbConnection.Close();
I have a feeling that dbreader.getname(i) is returning the wrong thing, versus the query being incorrect.
Can anyone help me figure out a way to identify the names of tables in an SQLite3 DB?
Thanks,
Matt
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
