Database

Christian Graus30-Mar-07 3:06

30-Mar-07 3:06

select rate where FromQty >= 0 and ToQty <= 5 ?

I'm not sure that I understand the question.

Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )

Test27030730-Mar-07 3:44

Test270307

30-Mar-07 3:44

No. i can enter any value through store procedure.it may be 2/8/13/19.

suppose i enter 2 then the row will be 100 0 5.
if i enter 8 then the row will be 200 6 10.
if i enter 13 then the row will be 300 11 15.
like this.

now if i generate this statement "select * from mratedetail where productcategoryid=49 and (toqty<=14.0 or fromqty>=14.0)" i have got all rows.
but in case of i want just "300 11 15".

thanks

Christian Graus30-Mar-07 4:00

30-Mar-07 4:00

Test270307 wrote:
(toqty<=14.0 or fromqty>=14.0

That obviously won't work. I think if you use AND instead of OR, it may well work.

Test27030730-Mar-07 4:02

Test270307

30-Mar-07 4:02

I have many fields and many rows.
so if i put or then it return so many records.
doesn't work for "or".
thanks

Christian Graus30-Mar-07 4:04

30-Mar-07 4:04

Test270307 wrote:
doesn't work for "or".

That's why I said you need to use AND

Christian Graus30-Mar-07 4:04

30-Mar-07 4:04

select rate from tbl where FromQty <= @amt and ToQty >= @amt

DQNOK30-Mar-07 4:09

DQNOK

30-Mar-07 4:09

SELECT Rate
FROM MyRateTable
WHERE 2 >= FromQty
AND 2 <= ToQty

Or, more generically,

SELECT Rate
FROM MyRateTable
WHERE @qty >= FromQty
AND @qty <= ToQty

Unless the FromQty and ToQty columns are indexed, the db will have to do a full scan to select ALL the rows that meet the @qty >= FromQty, then will have to do another full scan of all the selected rows to determine which of them meet the @qty <= ToQty requirement.

Alternately, if you're using a product that supports the "TOP" keyword, and you have the ToQty column indexed in assending order, you could use

SELECT TOP 1 Rate, ToQty
FROM MyRateTable
WHERE @qty <= ToQty
ORDER BY ToQty

and just ignore the ToQty in the return. This will guarantee good performance as the query will recognize the ORDER BY clause and use the existing index for the select.

Lastly, if your table really is as simple as the data you have used for your example, a mathematical formula would be better.

int getRate( int myQty )
{
if( myQty = 0 ) return 100;
else return (1+((myQty-1)/5))*100;
}

which could also be converted to SQL if you wanted.

Hope this helps.

Test27030730-Mar-07 18:40

Test270307

30-Mar-07 18:40

thank u.it works.

Noman Aftab31-Mar-07 1:01

Noman Aftab

31-Mar-07 1:01

SELECT rate
FROM Table1
WHERE (2 BETWEEN startQty AND endQty)

Pakistani Memon
Prime Minister
Post Meridian

server : error 18025

Navneet Hegde30-Mar-07 2:47

Navneet Hegde

30-Mar-07 2:47

please do help me to send mail from sql server via xp_sendmail

Develop2Program & Program2Develop

kubben30-Mar-07 3:45

30-Mar-07 3:45

Have you set up your mail profile?

Here is a Microsoft link:
http://support.microsoft.com/?id=263556[^]

Hope that helps.
Ben

Navneet Hegde30-Mar-07 3:47

Navneet Hegde

30-Mar-07 3:47

How can I do that, Please help a bit , for last 5 hours I am wrestling with it

Develop2Program & Program2Develop

kubben30-Mar-07 3:54

30-Mar-07 3:54

Here is a link to a decent example:
http://builder.com.com/5100-6388-6161839-2.html[^]

Ben

kubben30-Mar-07 3:56

30-Mar-07 3:56

Here's another link for I think the mail you are trying to send:
http://articles.techrepublic.com.com/5100-6345_11-5094201.html[^]

Ben

selecting primary key from grid view

Navneet Hegde30-Mar-07 3:58

Navneet Hegde

30-Mar-07 3:58

thanks for your prompt reply , I will try those link , Hope i get the result
again thanks a lot

Develop2Program & Program2Develop

govindkedia30-Mar-07 1:07

govindkedia

30-Mar-07 1:07

HI,

I am stuck in my project as.
i want to select primary key frm the data listed in grid view..so that i can use that key value to actually list the data which are related to tht key in other table...
so could any one tell how can i do tht...

gauthee30-Mar-07 1:47

gauthee

30-Mar-07 1:47

You would have a datasource to ur grid right! that is datatable/dataset for this you can set a primary key.

Gautham

Tirthadip30-Mar-07 1:54

Tirthadip

30-Mar-07 1:54

bind your Primary key field to the DataKey of gridview..

and then retreive data with....

Dim primaryField As String<br />
primaryField =myGridview.Datakeys(Seletedrow.index).value.tostring()

Tirtha

Miles to go before I sleep

govindkedia30-Mar-07 2:19

govindkedia

30-Mar-07 2:19

thanks so much...for the tip..
but tell me one more thing now..
could i use this
"primaryfield" to comapre with detailview which is binded with anotehr dataset in which this primaryfield is a forign key
so will it work

detailview.pageindex = primaryField.to string() ;

or if u could guide me a bit on this..
tht will be very helpful
i am using C#

thanks again

govind

Change a field on all tabel ,, row by row ?

Tirthadip30-Mar-07 2:39

Tirthadip

30-Mar-07 2:39

please be specific...I could't understand your problem

Tirtha

Miles to go before I sleep

Need Help In Query:

Shahzad.Aslam30-Mar-07 0:44

Shahzad.Aslam

30-Mar-07 0:44

Hi

select CAST((datediff(day,createdon,getdate())/30) as float) Avg, from customers

I want to give the result in Fraction is it possible? it is not showing the fraction result.
createdon is the datetime field

Any Help is appreciated.

Regards
Shahzad Aslam
Software Engineer

Re: Need Help In Query:

Krish - KP30-Mar-07 1:00

Krish - KP

30-Mar-07 1:00

datediff function's date part "day"
returns difference in number of days i.e 1 / 2 / 3 .....

if u r looking for even part of day, get the difference in hours/minutes/ ...
then try converting that to days so that you get the result as you are expecting

Regards
KP

kindman_nb30-Mar-07 0:33

kindman_nb

30-Mar-07 0:33

Hi ..
How can I path all rows on the table to change a field .. eg Serial ..
like that

For i=0 to datatabale.Rows.Count-1<br />
.<br />
.<br />
next i

but I want to use any method instead of "Update table set ....."

By using Datatable can I do it "path on all rows to change certain field" by usting the index of the row : DataTable.Rows(i)("Serial")
..
I'm sorry , that my english isn't good ..
but I hope if any one understand what I mean ..

and thanks for my best Forum Rose | [Rose]

jooooo

Re: Change a field on all tabel ,, row by row ?

kubben30-Mar-07 2:19