[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Re define a where clause.

Posted on 2005-05-16
2
Medium Priority
?
193 Views
Last Modified: 2010-03-19
declare @ra_status varchar(1)
--set @ra_status='A'
select status = case when cd.status is null then 'Available' else cd.status end,
       book_do_no= CASE when cd.status = 'Available' then '' else cd.book_do_no end,
       cancel_status=cd.cancel_status


from etlcoil_return m
left join
(select   contract_expiry_date = cm.delivery_date,
              book_do_no = CASE  when gd.state = 'R' then ''  else  gd.do_no end,
              status =  CASE WHEN gd.state = 'R' or gm.status = 'C'  THEN 'Available' ELSE 'BOOKED' END,
              coil_no = gd.coil_id,
              cancel_status = gm.status
                   from do_det gd
                   join do_mst gm on (gm.do_no = gd.do_no)
               join contract_mst cm on (cm.contract_no = gm.contract_no)
              where gm.do_date = (select max(do_mst.do_date) from do_mst join do_det on
                                          do_mst.do_no = do_det.do_no
                                          where gd.coil_id = do_det.coil_id
                              group by do_det.coil_id) )cd on (cd.coil_no = m.etl_coil_id)

where  (left(isnull(CD.status,''),1)= @ra_status or @ra_status is null or @ra_status = '')

the result is 33 rows as follows.

status      booked_do_no  cancelle_status
Available            C
BOOKED       04051753      R
BOOKED 04051753      R
BOOKED       04051761      R
Available  NULL                 NULL
BOOKED       04051723      R
Available NULL               NULL
BOOKED       04051753      R
Available NULL               NULL
BOOKED       04051753      R
Available NULL                NULL
Available NULL               NULL
BOOKED       04051753      R
BOOKED       04051723      R
Available NULL               NULL
BOOKED       04051753      R
BOOKED       04051761      R
Available                          R            
Available NULL                NULL
BOOKED       04051723      R
BOOKED       04051786      R
BOOKED       04051786      R
Available NULL                NULL
BOOKED       04051722      R
Available NULL               NULL
Available NULL               NULL
BOOKED       04050089      R
Available NULL               NULL
Available NULL               NULL
Available NULL               NULL
Available NULL               NULL
BOOKED       04051786      R
Available NULL               NULL

If I change the lines
declare @ra_status varchar(1)
--set @ra_status='A'
with
declare @ra_status varchar(1)
set @ra_status='A'
then it shows

Available            C
Available            R
2 rows. It should have shown 17 rows.
Where r 15 rows.

If I change the lines
declare @ra_status varchar(1)
set @ra_status='B'
then it shows

BOOKED      04051753      R
BOOKED      04051753      R
BOOKED      04051761      R
BOOKED      04051723      R
BOOKED      04051753      R
BOOKED      04051753      R
BOOKED      04051753      R
BOOKED      04051723      R
BOOKED      04051753      R
BOOKED      04051761      R
BOOKED      04051723      R
BOOKED      04051786      R
BOOKED      04051786      R
BOOKED      04051722      R
BOOKED      04050089      R
BOOKED      04051786      R

16 ROWS. Correct.

Can Someone change the where statement

where  (left(isnull(CD.status,''),1)= @ra_status or @ra_status is null or @ra_status = '')

so as
When @ra_status is null it should show 33 rows.
When @ra_status = 'A' it should show 17 rows.
when @ra_status = 'B' it should show 16 rows.
0
Comment
Question by:Mateen
2 Comments
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 14010210
Try this:

where  (left(isnull(CD.status,'A'),1)= @ra_status or @ra_status is null or @ra_status = '')
0
 

Author Comment

by:Mateen
ID: 14016085
Yes!!

Before yours , I found.

where  ( left(isnull(cd.status,'A'),1) = case when @ra_status = 'A' then 'A' end )
or     ( left(isnull(cd.status,'A'),1) <>  case when @ra_status = 'B' then 'A' end )
or     ( @ra_status is null  or @ra_status = '')

Uglier and lenghty.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question