SQL code to display text 1

[jpeters01]

This was a Crytal suppression formula that we are trying to use in SQL but it is not working. We need to display the text "See MAR for complete order detail" when the following conditions are not met. Can you help us with this please?

=IIF (IsNothing(Fields!PATIENT_ID.Value) OR IsNothing(Fields!DOSESTRENGTH.Value)
OR Fields!DOSESTRENGTH.Value = " " OR TRIM(Fields!DOSESTRENGTH.Value) = ""
OR (NOT(Fields!DOSESTRENGTH.Value LIKE "%PER%ORDER%")
AND NOT(Fields!DOSESTRENGTH.Value LIKE "%SLIDING%SCAL%")
AND NOT(Fields!DOSESTRENGTH.Value LIKE "%1%PER PROTOCOL%")
AND NOT(Fields!DOSESTRENGTH.Value LIKE "%1%EA%")
AND NOT(Fields!DOSESTRENGTH.Value LIKE "%1%UNIT/0.01%ML%-%%")
AND NOT(Fields!DOSESTRENGTH.Value LIKE "%TO BE GIVEN BY%")),
"",
"See MAR for complete Order Detail on (" & Fields!DESCRIPTION.Value & ") listed above.")

 

[djj55]

Is this to be run by SQL or Crystal?

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[RiverGuy]

Well, replace your IIF with CASE, replace your IsNothings with IS NULL, replace your double quotes with single quotes. For example:

YourDerivedColumnName =
CASE 
  WHEN PATIENT_ID IS NULL OR ISNULL(LTRIM(RTRIM(DOSESTRENGTH)), '') = '' OR DOSESTRENGTH NOT LIKE '%SLIDING%SCAL%' THEN  'See MAR for complete Order Detail on ' + [DESCRIPTION] +  ') listed above.'
ELSE ''
END

I only added about five of your conditions. You can add the rest. I noticed that you were looking to see if DOSESTRENGTH was NULL, was a single space or was an empty string. I consolidated those three into a single condition with the ISNULL and LTRIM/RTRIM combination above.

 

[SQLBill]

And it's being assumed that you mean you want it to run in Microsoft SQL Server. There are several different versions of SQL (Access - JetSQL, Oracle - SQLPlus and other versions of SQL). So if you don't mean SQL Server, you will need to put your post in the appropriate forum.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[jpeters01]

Sorry...this is the first time I have posted here as I am a Crystal Report writer but am trying to help get my report into Reporting Services. Our DBA says there is no CASE and it requires double quotes but she will try to put Riverguy's code in the custom code section...

 

[SQLBill]

No CASE??? What version of Microsoft SQL Server are you using? SS2000, SS2005, and SS2008 all accept CASE.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Emtucifor]

P.S. djj55, it's spelled Shepherd not Shepard

 

[jpeters01]

We are using SQL Server 2005 Reporting Services, going against Oracle databases. There are Case Statements at the SSRS Dataset (sql) level, but not at the SSRS Formula Expressions level.

The issue was the use of "%" versus "*" (all MS SQL Servers T-SQL requires single quote and percent for wild cards)
but for some reason, SSRS requires "*".......

Here is the code that works:

=IIF (IsNothing(Fields!PATIENT_ID.Value)
OR (IsNothing(RTRIM(LTRIM(Fields!DOSESTRENGTH.Value))))
OR ((NOT(Fields!DOSESTRENGTH.Value LIKE "*PER*ORDER*")
AND NOT(Fields!DOSESTRENGTH.Value LIKE "*SLIDING*SCAL*")
AND NOT(Fields!DOSESTRENGTH.Value LIKE "*1*PER PROTOCOL*")
AND NOT(Fields!DOSESTRENGTH.Value LIKE "*1*EA*")
AND NOT(Fields!DOSESTRENGTH.Value LIKE "*1*UNIT/0.01*ML*-*"))
AND NOT(Fields!COMPUTED_FREQUENCYSIG.Value LIKE "*TO BE GIVEN BY*")),
"",
"See MAR for complete Order Detail on (" & Fields!DESCRIPTION.Value & ") listed above.")


Thank you for your assistance!