Custom SQL

Hi,

I have a sql statement:

SELECT [ItemName], [Startprice], [Percentreduction], [Quantityavailable], [PhotoURL], [proID] FROM [items] WHERE ([featured] = @.featured)

but I would like to add in 2 more where clauses. One is AND (aswell as) the current one, so

WHERE ([featured] = @.featured) AND ([Quantityavailable > @.Quantityavailable) ?(@.Quantity available value set to 0)(is that right?)

and also I want another AND which is taken from another column. i.e:

WHERE ([featured] = @.featured) AND ([Quantityavailable > @.Quantityavailable) AND ([numberclickedin< *the number from the numtaken column*])

So I guess my 2 questions are:

1. is the format right for my custom sql statements.

2. how do I get the number from the numtaken column to dynamically enter into the third statement?

Thanks,

Jon

SELECT [ItemName], [Startprice], [Percentreduction], [Quantityavailable], [PhotoURL], [proID] FROM [items] WHERE ([featured] = @.featured) AND ([Quantityavailable] > @.Quantityavailable) AND ([numberclickedin] < [numtakenin])

You can use a field just like you use the @.QuantityAvailable parameter value. So one field can be compared against another just fine. I don't see any problem with that query...

|||

Hi

SELECT [ItemName], [Startprice], [Percentreduction],[Quantityavailable], [PhotoURL], [proID]

FROM [items]

WHERE (

([featured]= @.featured) AND

([Quantityavailable > 0) AND -- Comment: You can also say ([Quantityavailable > @.Quantityavailable) or both using AND --

Just not sure what you mean by the last part numclickedin...? I think you probably need Count( A Field Name ) as totalNum. Can you explain a bit more please.

|||

Hi, thank you both for you help!

To bmains: So it is as simple as that, to compare columns against each other you just put them in? You dont have to use extra code or anything to retrieve their data?

To anyone:

How about using data from 2 columns to fill in a third columns value?e.g. numberproducts = ([numberclickedin] *divided by* [numtaken]) ? or adding, subtracting and multiplying?

Thanks,

Jon!

|||

Hey,

Yes, it is that simple, and you can use multiplication and such, just beware of division if the value is zero (to avoid division by zero, you could even add to the where statement "and numtaken <> 0). Actually, you would be surprised how dynamic you can get with queries, when it comes to T-SQL in SQL Server, and PL-SQL in Oracle.

|||

Hi,

Im glad something is simple!

What symbols do I use for multiplication etc? * / + - ?

Thanks,

Jon

|||

Yep, the standard arithmetic operators are the ones you use, plus ( ) in the standard math way.

The [ ] brackets you were using in your first statement were gummed up.

They are used around database names, schema names, table/view names and column names.

They are not used around expressions. Take a look back at your statement and you will see partial [ ] pairs. You have to be careful about that!

Also, they are only needed if you have spaces or weird characters in the name in question, or if you have used a keyword.

My basic rule of thumb is "Don't do that." and everyone's life is simpler.

|||

Thanks!

Jon