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

x
?
Solved

where clause

Posted on 2009-04-27
6
Medium Priority
?
262 Views
Last Modified: 2012-05-06
Hi,

How can I say if @projectID has no value then select * otherwise select what ever the variable passing?

here's what I have.


insert into #TempData (acct,ProjectID,Sub,Perpost,CrAmt,DrAmt)
SELECT  dbo.GLTran.acct,dbo.GLTran.ProjectID,dbo.GLTran.Sub,dbo.GLTran.PerPost,
      CASE WHEN (SUM(CrAmt) - SUM(DrAmt)) >= 0 THEN (SUM(CrAmt) - SUM(DrAmt)) ELSE 0 END AS CrAmt,
    CASE WHEN (SUM(CrAmt) - SUM(DrAmt)) < 0 THEN (SUM(DrAmt) - SUM(CrAmt)) ELSE 0 END AS DrAmt
FROM         dbo.Account INNER JOIN
                      dbo.GLTran ON dbo.Account.Acct = dbo.GLTran.Acct
WHERE     (dbo.Account.Acct like '4%' and (dbo.Account.Acct not in (4410,4600))) AND (dbo.GLTran.LedgerID = 'Actual')
            AND (dbo.GLTran.PerPost BETWEEN @BgDate AND @EndDate) AND
         (dbo.GLTran.JrnlType <> 'AR')
      and  dbo.GLTran.ProjectID = Rtrim(@ProjectID)


so in the where clause.  it says
and  dbo.GLTran.ProjectID = Rtrim(@ProjectID)
that line is not working I want to say if the projectID has no passed value then select *
otherwise select what's been passing.

Thanks,


0
Comment
Question by:lulu50
  • 4
6 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 24241877
You can try something like this:
and  (@ProjectID <> 0 and (dbo.GLTran.ProjectID = Rtrim(@ProjectID))
or
and  (@ProjectID is not null and (dbo.GLTran.ProjectID = Rtrim(@ProjectID))
depending on the data type of @projectID
0
 
LVL 43

Accepted Solution

by:
TimCottee earned 2000 total points
ID: 24241884
Hello lulu50,

And (dbo.GLTran.ProjectID = RTrim(@ProjectID) Or @ProjectID Is Null)

Should do it

Regards,

TimCottee
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24241899
Sorry missed brackets there:

and  (@ProjectID <> 0 and (dbo.GLTran.ProjectID = Rtrim(@ProjectID))) -- if @projectID is int

or

and  (@ProjectID is not null and (dbo.GLTran.ProjectID = Rtrim(@ProjectID)))  -- if @projectID is varchar
0
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!

 

Author Closing Comment

by:lulu50
ID: 31574942
Thank you
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24242258
I'm sorry but TIM cotte is the same solution as mine. you should split points in this case
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24242449
Would you mind reopen the question for further discussion?
0

Featured Post

Independent Software Vendors: 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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

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