Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help With Case Statements

Status
Not open for further replies.
Cheryl3D

Cheryl3D

Programmer
Mar 26, 2002
116
US
Hi:

How can I write a CASE statement(s) in SQL Server 2000 with several 'OR' operators in the logic? I have a SQL statement that I'm converting from Access 2000 over to SQL Server 2000, that contains a WHERE clause with several immediate IF (IIF) statements that contains several 'OR' operators.

Here is the SQL statement that I need to convert to a stored procedure:

Original QryAgreement:
CREATE PROCEDURE “procQryAgreement”
@SUP bit = NULL,
@REC bit = NULL,
@MOAMOUS bit = NULL,
@MOAMOUR bit = NULL,
@BOL bit = NULL,
@HQ bit = NULL,
@SID bit = NULL,
@a bit = NULL,
@b bit = NULL,
@c bit = NULL,
@d bit = NULL,
@e bit = NULL,
@f bit = NULL,
@g bit = NULL,
@h bit = NULL,
@i bit = NULL,
@j bit = NULL,
@k bit = NULL,
@l bit = NULL,
@m bit = NULL,
@n bit = NULL,
@o bit = NULL,
@p bit = NULL,
@q bit = NULL,
@s bit = NULL
AS
SELECT QryUnionBase.[Host Base], QryUnionBase.[Office Symbol], QryUnionBase.CertStat, QryUnionBase.CertPOC, QryUnionBase.Comptroller, QryUnionBase.[ID #], QryUnionBase.SRAN, QryUnionBase.JULIAN, QryUnionBase.Receiver, QryUnionBase.Supplier, QryUnionBase.OBANOAC, QryUnionBase.Receiver_Name, QryUnionBase.Receiver_Phone, QryUnionBase.Receive_Email, QryUnionBase.Location, QryUnionBase.ExeComptroller, QryUnionBase.Agreement, QryUnionBase.Department, QryUnionBase.SR, QryUnionBase.Officers, QryUnionBase.Enlisted, QryUnionBase.Civilians, QryUnionBase.Contractors, QryUnionBase.[Analyst POC], QryUnionBase.Reimbursable, QryUnionBase.Reimburse, QryUnionBase.NONrbs, QryUnionBase.Reimb_Positions, QryUnionBase.Reimb_Notes, QryUnionBase.[SAM _POC], QryUnionBase.Status, QryUnionBase.[Rec'd_Coord], QryUnionBase.Sent_to, QryUnionBase.Date_Sent_for_Coord, QryUnionBase.Suspense_for_Coord, QryUnionBase.Coord_Complete, QryUnionBase.[Rec'd_for_Annex], QryUnionBase.Suspense_for_Annex, QryUnionBase.Annex_Complete, QryUnionBase.[Rec'd_for_Sign], QryUnionBase.Sig_Complete, QryUnionBase.[Pending Status], QryUnionBase.[Original_ISA_ Start_ Date], QryUnionBase.TriAnnual_Complete, QryUnionBase.Annual_Review_Complete, QryUnionBase.Terminated_Date, QryUnionBase.[BH], qryAnnex.TabSupplier.[ID #], qryAnnex.TabAnnex.[ID #], qryAnnex.Receiver, qryAnnex.WorkyearCost, TabOrg.Org, qryAnnex.Categories, qryAnnex.SubCategory, qryAnnex.Support, qryAnnex.RDescription, qryAnnex.RUnits, qryAnnex.RUnitCost, qryAnnex.NRManpower, qryAnnex.NRDescription, qryAnnex.NRUnits, qryAnnex.NRUnitCost, qryAnnex.NRWorkyearCost, qryAnnex.RCost, qryAnnex.NRCost, qryAnnex.Document, qryAnnex.Billed, qryAnnex.Coll
FROM ((QryUnionBase LEFT JOIN qryAnnex ON (QryUnionBase.Receiver = qryAnnex.Receiver) AND (QryUnionBase.[ID #] = qryAnnex.TabSupplier.[ID #])) LEFT JOIN QryCategories ON qryAnnex.Categories = QryCategories.Categories) LEFT JOIN TabOrg ON QryCategories.Org = TabOrg.Org
WHERE (((QryUnionBase.SR)=IIf([Forms]![FrmAgreement]!=-1,"1") Or ((QryUnionBase.SR)=IIf([Forms]![FrmAgreement]![REC]=-1,"2") Or (((QryUnionBase.SR)=IIf([Forms]![FrmAgreement]![MOAMOUS]=-1,"3") Or (QryUnionBase.SR)=(IIf([Forms]![FrmAgreement]![MOAMOUR]=-1,"4")))))) And
(((QryUnionBase.BH)=IIf([Forms]![FrmAgreement]![BOL]=-1, "1") Or (QryUnionBase.BH)=(IIf([Forms]![FrmAgreement]![HQ]=-1, "2")))))
And (IIF([Forms]![FrmAgreement]![SID] is Null, [QryUnionBase]![ID #] Not Like "Luc*" Or [QryUnionBase]![ID #] Is Null,[QryUnionBase]![ID #]=[Forms]![FrmAgreement]![SID])) And (IIf([Forms]![FrmAgreement]![a]=-1, [qryAnnex]![RCost]>0,[qryAnnex]![RCost]=0 or [qryAnnex]![RCost]>0)) And (((IIf([Forms]![FrmAgreement]![e]=-1,[QryUnionBase]![Department]="10")) Or (IIf([Forms]![FrmAgreement]![f]=-1,[QryUnionBase]![Department]="20")) Or (IIf([Forms]![FrmAgreement]![g]=-1,[QryUnionBase]![Department]="30")) Or (IIf([Forms]![FrmAgreement]![h]=-1,[QryUnionBase]![Department]="40")) Or (IIf([Forms]![FrmAgreement]!=-1,[QryUnionBase]![Department]="50")) Or (IIf([Forms]![FrmAgreement]![j]=-1,[QryUnionBase]![Department]="60"))) Or (IIf([Forms]![FrmAgreement]![e]=0 And [Forms]![FrmAgreement]![f]=0 And [Forms]![FrmAgreement]![g]=0 And [Forms]![FrmAgreement]![h]=0 And [Forms]![FrmAgreement]!=0 And [Forms]![FrmAgreement]![j]=0,[QryUnionBase]![Department] Not Like "Luc*" Or [QryUnionBase]![Department] Is Null))) And ((IIf([Forms]![FrmAgreement]![k]=-1,[QryUnionBase]![Status] Like "Ter*")) Or (IIf([Forms]![FrmAgreement]![l]=-1,[QryUnionBase]![Status] Like "Sup*")) Or (IIf([Forms]![FrmAgreement]![m]=-1,[QryUnionBase]![Status] Like "Coor*")) Or (IIf([Forms]![FrmAgreement]![n]=-1,[QryUnionBase]![Status] Like "Anne*")) Or (IIf([Forms]![FrmAgreement]![o]=-1,[QryUnionBase]![Status] Like "Sig*")) Or (IIf([Forms]![FrmAgreement]![p]=-1,[QryUnionBase]![Status] Like "Annu*")) Or (IIf([Forms]![FrmAgreement]![q]=-1,[QryUnionBase]![Status] Like "Com*")) Or (IIf([Forms]![FrmAgreement]![k]=0 And [Forms]![FrmAgreement]![l]=0 And [Forms]![FrmAgreement]![m]=0 And [Forms]![FrmAgreement]![n]=0 And [Forms]![FrmAgreement]![o]=0 And [Forms]![FrmAgreement]![p]=0 And [Forms]![FrmAgreement]![q]=0, [QryUnionBase]![Status] Not Like "Luc*" Or [QryUnionBase]![Status] Is Null))) And (IIf([Forms]![FrmAgreement]![r]=0,[QryUnionBase]![Status] Not Like "Luc*" Or [QryUnionBase]![Status] Is Null,[QryUnionBase]![Status] Like "An*" Or [QryUnionBase]![Status] Like "Si*" Or [QryUnionBase]![Status] Like "Co*")) And (IIf([Forms]![FrmAgreement]!=-1,[QryUnionBase]![Pending Status] =-1,[QryUnionBase]![Pending Status] Not Like "Luc*" Or [QryUnionBase]![Pending Status] Is Null)) And (IIf([Forms]![FrmAgreement]!=-1,[QryUnionBase]![ExeComptroller] =-1,[QryUnionBase]![ExeComptroller] Not Like "Luc*" Or [QryUnionBase]![Pending Status] Is Null)) And ((IIF([Forms]![FrmAgreement]![c]=-1,[QryUnionBase]![Original_ISA_ Start_ Date] is Null And [QryUnionBase]![TriAnnual_Complete] Is Null)) Or (IIF([Forms]![FrmAgreement]![d]=-1,[QryUnionBase]![Original_ISA_ Start_ Date] is Not Null OR [QryUnionBase]![TriAnnual_Complete] Is Not Null)) OR (IIF([Forms]![FrmAgreement]![c]=0 and [Forms]![FrmAgreement]![d]=0, [QryUnionBase]![TriAnnual_Complete] Not Like "Luck*" Or [QryUnionBase]![TriAnnual_Complete] Is Null)));

Any sugesstions?

Thanks
Cheryl
 
Sort by date Sort by votes
You can change an iif to a case statement by

iif(a=b,c,d)
case when a=b then c else d end

but you should probably look at what you are trying to do.



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
 
Upvote 0 Downvote
Thanks. I know that in Access 2000 you can have nested immediate if statements (iif). But in SQL Server 2000 can you have nested CASE statements?

Cheryl
 
Upvote 0 Downvote
Sure.
Code: 
CASE  WHEN A = B THEN CASE
                             WHEN X = Y THEN 1
                             WHEN X = Z THEN 2
                             ELSE 0
                      END
      WHEN A = C THEN CASE
                             WHEN X = M THEN 9
                             WHEN X = N THEN 8
                             ELSE 0
                      END
      ELSE 0
END

--Angel [rainbow]
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
Case sensitive request 1
Replies
3
Views
319
Chris Miller
Chris Miller
f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
569
f0rg3tfu1
f0rg3tfu1
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
1K
Chris Miller
Chris Miller
gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
2K
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
Update with a month name 1
Replies
9
Views
1K
gmmastros
gmmastros

Part and Inventory Search

Sponsor

Back
Top