a where case question

I need to change the criteria of the select so that if @.thstype is 1 then
where a =b else a<> b. This is in a stored proc...
given declare @.thsType as INT
set @.thsType = 1
select *
from t1,t2,t2,...
where u.unvid case @.thsType When 1 then = else <> end @.thsUnvID
and u.unvtype = @.thsunvType
and i1.idxhstdate = @.thsDate1
and i2.idxhstdate = @.thsDate2
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kesYou were so close :-)
Use Northwind
DECLARE @.Test INT
SET @.test = 1
Select *
from customers where customerID =
(CASE WHEN @.Test = 1 THEN 'ALFKI' ELSE '###' END)
HTH, Jens Suessmeyer
http://www.sqlserver2005.de
--
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:7178EEFF-4304-4871-8138-7F2DF1A2D5BC@.microsoft.com...
>I need to change the criteria of the select so that if @.thstype is 1 then
> where a =b else a<> b. This is in a stored proc...
> given declare @.thsType as INT
> set @.thsType = 1
> select *
> from t1,t2,t2,...
> where u.unvid case @.thsType When 1 then = else <> end @.thsUnvID
> and u.unvtype = @.thsunvType
> and i1.idxhstdate = @.thsDate1
> and i2.idxhstdate = @.thsDate2
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes|||i'm not sure that will work. I really need something like this
Select *
from customers where
CASE WHEN @.Test = 1 THEN
customerID = 'ALFKI'
ELSE
customerID <> 'ALFKI'
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Jens Sü?meyer" wrote:

> You were so close :-)
> Use Northwind
> DECLARE @.Test INT
> SET @.test = 1
> Select *
> from customers where customerID =
> (CASE WHEN @.Test = 1 THEN 'ALFKI' ELSE '###' END)
> HTH, Jens Suessmeyer
> --
> http://www.sqlserver2005.de
> --
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:7178EEFF-4304-4871-8138-7F2DF1A2D5BC@.microsoft.com...
>
>|||Try this:
DECLARE @.Test INT
SET @.test = 1
Select *
from customers
where (@.Test = 1 and customerID = 'ALFKI') or
(@.Test <> 1 and customerID <> 'ALFKI' )
Perayu
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:8CBDCEFA-740D-4DA6-BA4D-18452E54FE1C@.microsoft.com...
> i'm not sure that will work. I really need something like this
> Select *
> from customers where
> CASE WHEN @.Test = 1 THEN
> customerID = 'ALFKI'
> ELSE
> customerID <> 'ALFKI'
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes
>
> "Jens Smeyer" wrote:
>|||well.........., yes you have an answer and thank you!
However, it turned my mega proc of .5 sec into a 6 second proc.
I can write an if else and have two queries in the proc, but i'd like to
avoide that if i could.
Query below, for what is does it's very fast the condition needs to be in
the joined query at the bottom: (i added the or)
select
s.csistkcsisym,
s.csistksym1,
s.csistkcompany,
case s.csistkExchange when 'OTC' THEN 'Nasdaq' Else s.csistkExchange END as
Exchange,
u.unvName,
isnull(r.unvname, '********') as Sector,
case s.csistkActive when 0 then 'INACTIVE' else 'ACTIVE' END as status,
case h2.stkhstBuySell WHEN '' THEN 'N/A' WHEN 'B' THEN 'Buy' WHEN 'S' then
'Sell' ELSE h2.stkhstBuySell END as PFBuySell,
CASE
WHEN h2.stkhstBuySell = 'B' and h1.stkhstBuySell = 'S' THEN 'gnBK'
WHEN h2.stkhstBuySell = 'S' and h1.stkhstBuySell = 'B' THEN 'rdBK'
ELSE 'wtBK'
END as NEWPFBuySell,
h2.stkhstXO,
CASE
WHEN h2.stkhstXO = 'X' and h1.stkhstxo = 'O' THEN 'gnBK'
WHEN h2.stkhstXO = 'O' and h1.stkhstxo = 'X' THEN 'rdBK'
ELSE 'wtBK'
END as NEWPFXO,
CASE h2.stkhstLine WHEN 'A' THEN 'Above' WHEN 'B' THEN 'Below' ELSE 'N/A'
END as Trend,
CASE
WHEN h2.stkhstLine = 'A' AND h1.stkhstLine = 'B' THEN 'gnBK'
WHEN h2.stkhstLine = 'B' AND h1.stkhstLine = 'A' THEN 'rdBK'
ELSE 'wtBK'
END as NEWPFtrend,
case h2.stkhstRSBS WHEN '' THEN 'N/A' WHEN 'B' THEN 'Buy' WHEN 'S' then
'Sell' ELSE h2.stkhstRSBS END as RSBuySell,
CASE
WHEN h2.stkhstRSBS = 'B' AND h1.stkhstRSBS = 'S' THEN 'gnBK'
WHEN h2.stkhstRSBS = 'S' AND h1.stkhstRSBS = 'B' THEN 'rdBK'
ELSE 'wtBK'
END as NEWRSBuySell,
h2.stkhstRSXO,
CASE
WHEN h2.stkhstRSXO = 'X' AND h1.stkhstRSXO = 'O' THEN 'gnBK'
WHEN h2.stkhstRSXO = 'O' AND h1.stkhstRSXO = 'X' THEN 'rdBK'
ELSE 'wtBK'
END as NEWRSXO,
case When (h2.stkhst10wk - h2.stkhstClose) >= 0 then 'Below' else 'Above'
end as tenBeat,
CASE
WHEN ((h2.stkhst10wk - h2.stkhstClose) >= 0) AND ((h1.stkhst10wk -
h1.stkhstClose) < 0) then 'rdBK'
WHEN ((h1.stkhst10wk - h1.stkhstClose) >= 0) AND ((h2.stkhst10wk -
h2.stkhstClose) < 0) then 'gnBK'
ELSE 'wtBK'
END AS NEWtenBeat,
h2.stkhst10Wk,
h2.stkhstClose,
case
WHEN r.i2idxhstStatus is null
then (dbo.fn_rtnRSStatus(h2.stkhstRSBS,
h2.stkhstRSXO)+dbo.fn_rtnPFStatus(h2.stkhstBuySell, h2.stkhstLine))*2
else ((dbo.fn_rtnBPStatus(r.i2idxhstStatus, r.i2idxhstPosChartPos)*50) +
(dbo.fn_rtnRsRStatus(r.i2idxhstRSBSXO)*25) +
(dbo.fn_rtnBPStatus(r.i2idxhstRSXOStatus, r.i2idxhstRSXOPos)*25) +
(dbo.fn_rtn10Status(r.i2idxhst10Status, r.i2idxhst10ChartPos)*50) +
(dbo.fn_rtnBPStatus(r.i2idxhstStatus, r.i2idxhstPosChartPos)*25) +
(dbo.fn_rtnBPStatus(r.i2idxhstRSXOStatus, r.i2idxhstRSXOPos)*25) )/4+
dbo.fn_rtnRSStatus(h2.stkhstRSBS, h2.stkhstRSXO)
+dbo.fn_rtnPFStatus(h2.stkhstBuySell, h2.stkhstLine)
END as StockRate,
case
WHEN r.i2idxhstStatus is null
then (dbo.fn_rtnRSStatus(h2.stkhstRSBS,
h2.stkhstRSXO)+dbo.fn_rtnPFStatus(h2.stkhstBuySell, h2.stkhstLine))*2
else ((dbo.fn_rtn10Status(r.i2idxhst10Status, r.i2idxhst10ChartPos)*50) +
(dbo.fn_rtnBPStatus(r.i2idxhstStatus, r.i2idxhstPosChartPos)*25) +
(dbo.fn_rtnBPStatus(r.i2idxhstRSXOStatus, r.i2idxhstRSXOPos)*25) )/2+
dbo.fn_rtnRSStatus(h2.stkhstRSBS, h2.stkhstRSXO)
+dbo.fn_rtnPFStatus(h2.stkhstBuySell, h2.stkhstLine)
END as ShortTermRate,
case s.csistkActive when 0 then 'INACTIVE' else 'ACTIVE' END as status
from
csistk s
join unvmem m on m.unvmemCsiId = s.csistkcsisym
join unv u on u.unvID = m.unvmemUnvID
join stkhst h2 on h2.stkhstcsisym = s.csistkcsisym
join stkhst h1 on h1.stkhstcsisym = s.csistkcsisym
left join
(select u.unvname,
u.unvid,
m.unvmemcsiid,
i1.idxhstStatus as i1idxhstStatus,
i2.idxhstStatus as i2idxhstStatus,
i2.idxhstPosChartPos as i2idxhstPosChartPos,
i2.idxhstRSBSXO as i2idxhstRSBSXO,
i2.idxhstRSXOStatus as i2idxhstRSXOStatus,
i2.idxhstRSXOPos as i2idxhstRSXOPos,
i2.idxhst10Status as i2idxhst10Status,
i2.idxhst10ChartPos as i2idxhst10ChartPos
from unv u
join unvmem m on m.unvmemunvid = u.unvid
join idxhst i1 on i1.idxhstidxid = u.unvID
join idxhst i2 on i2.idxhstidxid = u.unvID
where @.thsOne = 1
and u.unvid <> @.thsUnvID
and u.unvtype = @.thsunvType
and i1.idxhstdate = @.thsDate1
and i2.idxhstdate = @.thsDate2
or
@.thsOne<> 1
and u.unvid = @.thsUnvID
and u.unvtype = @.thsunvType
and i1.idxhstdate = @.thsDate1
and i2.idxhstdate = @.thsDate2) r on r.unvmemcsiid = s.csistkcsisym
where u.unvid = @.thsUnvID
and h2.stkhstdate = @.thsDate2
and h1.stkhstdate = @.thsDate1
order by s.csistksym1
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Perayu" wrote:

> Try this:
> DECLARE @.Test INT
> SET @.test = 1
> Select *
> from customers
> where (@.Test = 1 and customerID = 'ALFKI') or
> (@.Test <> 1 and customerID <> 'ALFKI' )
> Perayu
>
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:8CBDCEFA-740D-4DA6-BA4D-18452E54FE1C@.microsoft.com...
>
>|||On Thu, 8 Sep 2005 13:41:02 -0700, WebBuilder451 wrote:

>well.........., yes you have an answer and thank you!
>However, it turned my mega proc of .5 sec into a 6 second proc.
>I can write an if else and have two queries in the proc, but i'd like to
>avoide that if i could.
>Query below, for what is does it's very fast the condition needs to be in
>the joined query at the bottom: (i added the or)
(snip)
> where @.thsOne = 1
> and u.unvid <> @.thsUnvID
> and u.unvtype = @.thsunvType
> and i1.idxhstdate = @.thsDate1
> and i2.idxhstdate = @.thsDate2
> or
> @.thsOne<> 1
> and u.unvid = @.thsUnvID
> and u.unvtype = @.thsunvType
> and i1.idxhstdate = @.thsDate1
> and i2.idxhstdate = @.thsDate2) r on r.unvmemcsiid = s.csistkcsisym
Hi WebBuilder451,
While this and/or condition will work, it is not very maintainable and
not very efficient either. Please remember that many people do not know
the precedence of evaluation for and and or by head. Just adding
brackets would make this code easier to understand!
But the code below, while equivalent, also has a better chance of being
able to use indexes:
where u.unvtype = @.thsunvType
and i1.idxhstdate = @.thsDate1
and i2.idxhstdate = @.thsDate2
and ((@.thsOne = 1 and u.unvid <> @.thsUnvID)
or (@.thsOne <> 1 and u.unvid = @.thsUnvID))
) r on r.unvmemcsiid = s.csistkcsisym
If that doesn't solve your speed problem and speed is important for you,
than you'll have to duplicate your stored procedure to make two
versions: one for @.thsOne = 1 and one for @.thsOne <> 1. That will allow
SQL Server to create optimized execution plans for both situations.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

a small SQL help regrading a criteria

i want the ordline.qty * product.prodprice AS ordercost

where ordercost = > 150
but it does not work. Every time i type: where ordercost => 150, it would give me an error.
this is my full query:

select customer.custno, customer.custfirstname + " " + customer.custlastname as custfullname, ordertbl.ordno, ordertbl.orddate, employee.empno, employee.empfirstname + " " + employee.emplastname as empfullname, product.prodno, product.prodname, ordline.qty * product.prodprice AS ordercost from (((ordertbl inner join customer on ordertbl.custno = customer.custno) inner join employee on employee.empno = ordertbl.empno) inner join ordline on ordline.ordno=ordertbl.ordno) inner join product on product.prodno=ordline.prodno where (ordertbl.orddate = datevalue('01/23/2007')) and (making ordercost = > 150) ..............

Thanks for help in advancethis has to be either microsoft access or sql server, it doesn't look very much like ANSI SQL

in any case...

one solution is to wrap the query in another SELECT

select * from ( select customer.custno, customer.custfirstname + " " + customer.custlastname as custfullname, ordertbl.ordno, ordertbl.orddate, employee.empno, employee.empfirstname + " " + employee.emplastname as empfullname, product.prodno, product.prodname, ordline.qty * product.prodprice AS ordercost from (((ordertbl inner join customer on ordertbl.custno = customer.custno) inner join employee on employee.empno = ordertbl.empno) inner join ordline on ordline.ordno=ordertbl.ordno) inner join product on product.prodno=ordline.prodno ) as d where orddate = datevalue('01/23/2007') and ordercost = > 150|||sorry
im not sure where to ask
im using this in sql server and access|||doesn't the star calls out all the rows of the tables?
i'll try|||doesn't the star calls out all the rows of the tables?
i'll tryno, the star "calls out" all the columns

in this case it's all the columns of the derived table, show here in blue --

select * from ( select customer.custno, customer.custfirstname + " " + customer.custlastname as custfullname, ordertbl.ordno, ordertbl.orddate, employee.empno, employee.empfirstname + " " + employee.emplastname as empfullname, product.prodno, product.prodname, ordline.qty * product.prodprice AS ordercost from (((ordertbl inner join customer on ordertbl.custno = customer.custno) inner join employee on employee.empno = ordertbl.empno) inner join ordline on ordline.ordno=ordertbl.ordno) inner join product on product.prodno=ordline.prodno ) as d where orddate = datevalue('01/23/2007') and ordercost = > 150|||hey it works!
why does the * makes it work?|||nm
you have explained.

select * from ( select customer.custno, customer.custfirstname + " " + customer.custlastname as custfullname, ordertbl.ordno, ordertbl.orddate, employee.empno, employee.empfirstname + " " + employee.emplastname as empfullname, product.prodno, product.prodname, ordline.qty * product.prodprice AS ordercost from (((ordertbl inner join customer on ordertbl.custno = customer.custno) inner join employee on employee.empno = ordertbl.empno) inner join ordline on ordline.ordno=ordertbl.ordno) inner join product on product.prodno=ordline.prodno ) where orddate = datevalue('01/23/2007') and ordercost = > 150

u had "as d" as typos =p
__________________|||it works because of the derived table, which uses column names as defined in its SELECT

the expression with the column alias ordercost becomes an actual column in the derived table|||as d was not a typo|||i took the as d out and it works too
and what's as d
sorry for being a noob|||i took the as d out and it works too
and what's as d
This sets "d" as the alias name (actually: table name) for the temporary "table" inside the parentheses.

According to standard SQL, every table (or view or nested table expression) must have a name. By having the "AS d" after the definition, it's as if you created a view, viz:
CREATE VIEW d (custno, custfullname, ordno, orddate, empno,
empfullname, prodno, prodname, ordercost)
AS
SELECT customer.custno,
customer.custfirstname || ' ' || customer.custlastname,
ordertbl.ordno,
ordertbl.orddate,
employee.empno,
employee.empfirstname + " " + employee.emplastname,
product.prodno,
product.prodname,
ordline.qty * product.prodprice
FROM ordertbl inner join customer on ordertbl.custno = customer.custno
inner join employee on employee.empno = ordertbl.empno
inner join ordline on ordline.ordno=ordertbl.ordno
inner join product on product.prodno=ordline.prodno
Now this view can be interrogated:SELECT *
FROM d
WHERE orddate = datevalue('01/23/2007')
and ordercost => 150
Rudy's query is exactly this, except for the fact that no view with the name "d" is ever created, it's just temporarily available for the scope of the current query. Such a "view" is often called a "nested table expression". It's to be written asSELECT d.whatever
FROM (SELECT whatever, ... -- the NTE
) AS d
WHERE ...Note the "d.whatever", where the table name "d" is used. That's why the "AS d" is needed: it's really the name of the NTE !|||thanks peter, i was away from the computer all day, but your answer was much better than mine would've been :)

and i call it a derived table instead of nested table expression|||i call it a derived table instead of nested table expressionNTE is the DB2 terminology.
I'm probably a bit biased...

A query that is over my head...

Question:
How do I return a list of items that matches one or more criteria that I pass in?

Background:
A user enters a sales lead (a company is looking for a place to have their event). That lead has a number of
criteria elements (start date, end date, city, region, maximum room rate, one or more amenities, etc. - more
details below) that should be used when trying to find Hotels that match that criteria. Obviously, some
criteria is more important than others (city, start and end date are more important than the maximum room
rate) - and it's unlikely that many (if any) of the Hotels will match *all* of the criteria entered by the
user. So, I'm looking to return a list of Hotels that match at least one of the criteria - if possible,
ordered by how many criteria elements match.

What makes this query particularly difficult, is that some of the criteria to match are stored in multiple
tables. For example, each Hotel has "Amenities" (Golf, Spa, etc.) - that are stored in a seperate table.
When a user enters a lead, they select which amenities they want to match. Also, a lead specifies a number
of rooms to block for each day between the Arrival and Departure date - these numbers can change from day
to day - but for this query - I think it's acceptible to get the largest number of rooms needed from any of
the days and compare that one number against the "MaxDailyRoomBlock" field of a Hotel (represented by the
"Property" table). Also, since a Hotel has different rates defined for each season, the query will have to
match the "MaxRate" against the rate of the correct season based on the Arrival and Departure dates. Also,
the rate can be within 20% of the stated "MaxRate".

Here are the following variables that will be passed into the query as criteria items:
RequestCity, RegionINDEID, ArrivalDate, DepartureDate, MaxRate, MaxTheaterSeating, MaxBanquetSeating,
MaxSchoolSeating, MaxBreakoutRooms, MaxRoomBlock

I know this is a huge post - and I sincerly appreciate any help you can provide.

DDL for Tables:
*In the DDL.txt attachment

Sample Data:
* In the data.txt attachment

Previous Attempts:
Unfortunately, I don't even know where to begin, so I haven't tried anything yet.

Expected Results:

PropertyID Name NumOfMatches
------ ------ ----
1 Marriot San Diego 5
2 Hilton San Diego 3
3 Hilton San Diego Downtown 2

Thanks in advance, again...Sounds like you need weighted values, not just a count of how many matches.
You are going to need to do this in a stored procedure, and it will likely require serveral steps depending upon the complexity of the schema and business requirements.
I strongly suggest you find a DBA proficient in SQL programming to help you with this, as it could end up being a big job for you.|||I can give some enlightment to ur problem.place the mandatory certeria in ur where clause,(eg:regionID,startdate,enddate) and put the other certeria in ur case statement as follows.
I have given weightage 1 for every certeria,u can change as per ur requirement(u can see at 'case statment')

NB:I didnt understand ur 20% of Maxrate.give me a example


-- details--
select l.LeadID,p.PropertyID,p.Name,
case when l.MaxDailyRoomBlock<=p.MaxDailyRoomBlock then 1
else 0 end as block,
case when l.MaxBreakoutRooms<=p.MaxBreakoutRooms then 1
else 0 end as breaks,
case when l.MaxTheaterSeating<=p.MaxTheaterStyleSeats then 1
else 0 end as Theater,
case when l.MaxBanquetSeating<=p.MaxBanquetStyleSeats then 1
else 0 end as Banquet,
case when l.MaxSchoolSeating<=p.MaxSchoolStyleSeats then 1
else 0 end as School,

(select count(*) from LeadAmenity la,
PropertyAmenity pa where l.LeadID=la.LeadID
and la.AmenityID=pa.AmenityID and p.PropertyID=pa.PropertyID) as AmenityCount
from
Property p,
Lead l





-- summary---
select LeadID,PropertyID,Name,(block+breaks+Theater+Banqu et+School+AmenityCount) as NumOfMatches
from
(
select l.LeadID,p.PropertyID,p.Name,
case when l.MaxDailyRoomBlock<=p.MaxDailyRoomBlock then 1
else 0 end as block,
case when l.MaxBreakoutRooms<=p.MaxBreakoutRooms then 1
else 0 end as breaks,
case when l.MaxTheaterSeating<=p.MaxTheaterStyleSeats then 1
else 0 end as Theater,
case when l.MaxBanquetSeating<=p.MaxBanquetStyleSeats then 1
else 0 end as Banquet,
case when l.MaxSchoolSeating<=p.MaxSchoolStyleSeats then 1
else 0 end as School,

(select count(*) from LeadAmenity la,
PropertyAmenity pa where l.LeadID=la.LeadID
and la.AmenityID=pa.AmenityID and p.PropertyID=pa.PropertyID) as AmenityCount
from
Property p,
Lead l
) as tm order by LeadID,PropertyID


come back if u have any doubts|||Thanks a ton for your reply, and sorry for being so slow to say so - I've been overwhelmed by the same project that prompted this question. It's helped me out a lot... thanks!

Chad