Conditionally required field

How can I make a field required based on the status of other fields? I have
a Users table for my app that I also reference in forms that are filled out
by everyone. Most users don't need to use this table for login, so they
don't require a password. Each user has a UserName, Password, and a bit for
each privelege that I offer. If all priveleges are 0, I want to make the
password an optional field so I don't have to some up with a bunch of
passwords or use a random character generator. However, if they do have
priveleges, they are required to have a password so that if someone finds ou
t
their UserName (not hard at all), they still can't log in under a priveleged
account.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsRules.
Most people thing of rules in an IF.. THEN format which simply won't work.
Think of a rule as a boolean function where YES/TRUE accepts the row and
NO/FALSE rejects the row. Your requirements would lead to a rule like this:
Priv1 <> 0 OR Priv2<> 0 OR PRiv3 <> 0 OR Password <> ''
Look up CREATE RULE and sp_bindrule in BOL for syntax details.
Geoff N. Hiten
Microsoft SQL Server MVP
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:848BFC64-0105-42CC-8F1D-E1C4BAF25D1E@.microsoft.com...
> How can I make a field required based on the status of other fields? I
> have
> a Users table for my app that I also reference in forms that are filled
> out
> by everyone. Most users don't need to use this table for login, so they
> don't require a password. Each user has a UserName, Password, and a bit
> for
> each privelege that I offer. If all priveleges are 0, I want to make the
> password an optional field so I don't have to some up with a bunch of
> passwords or use a random character generator. However, if they do have
> priveleges, they are required to have a password so that if someone finds
> out
> their UserName (not hard at all), they still can't log in under a
> priveleged
> account.
> Thanks in advance
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Look up CHECK constraints in SQL Server Books Online. You can easily write
one up based on the column values in a single row.
Anith|||Try:
create table t
(
PK int primary key
, UserID char (5) not null
, Password varchar (15) null
, priv1 bit not null
, priv2 bit not null
, priv3 bit not null
, constraint CK_t check (
case
when cast (priv1 as int) + priv2 + priv3 = 0 then 1
when Password is not null then 1
else 0
end = 1)
)
go
insert t values (1, 'Me', null, 0, 0, 0)
insert t values (2, 'You', null, 1, 0, 0) -- fails
insert t values (3, 'Him', 'pwd', 1, 0, 0)
go
drop table t
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:848BFC64-0105-42CC-8F1D-E1C4BAF25D1E@.microsoft.com...
How can I make a field required based on the status of other fields? I have
a Users table for my app that I also reference in forms that are filled out
by everyone. Most users don't need to use this table for login, so they
don't require a password. Each user has a UserName, Password, and a bit for
each privelege that I offer. If all priveleges are 0, I want to make the
password an optional field so I don't have to some up with a bunch of
passwords or use a random character generator. However, if they do have
priveleges, they are required to have a password so that if someone finds
out
their UserName (not hard at all), they still can't log in under a priveleged
account.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/Apps

Conditionally referring to fields

Hi,
I am using RS 2000. In a report, I have database field whose name keeps changing everytime based on some condition. Say, a stored proc returns a field Aug2005. The name of this field becomes Oct2005 on some other condition. How can I use this field in the layout (to drag n drop). By what name/alias could I refer to this field. I read that in RS 2005 there is an option like Fields.Items(index).Value to access the field conditionally but I tried it in RS 2000 to no avail. Please suggest a solution.
Thanks,
Biju.

When you use the Fields.Items syntax, what you are varying is the field name, not the underlying database query column name (called DataField in RDL). All columns returned by the query must be known and mapped in the RDL.

If you have a query that returns different columns, you need to add them both to the query and then conditionally switch between them.

Conditionally referring to fields

Hi,
I am using RS 2000. In a report, I have database field whose name keeps changing everytime based on some condition. Say, a stored proc returns a field Aug2005. The name of this field becomes Oct2005 on some other condition. How can I use this field in the layout (to drag n drop). By what name/alias could I refer to this field. I read that in RS 2005 there is an option like Fields.Items(index).Value to access the field conditionally but I tried it in RS 2000 to no avail. Please suggest a solution.
Thanks,
Biju.

When you use the Fields.Items syntax, what you are varying is the field name, not the underlying database query column name (called DataField in RDL). All columns returned by the query must be known and mapped in the RDL.

If you have a query that returns different columns, you need to add them both to the query and then conditionally switch between them.

sqlsql

Conditionally load Drop downs in Parameter toolbar

In trying to incorporate business rules into my SSRS report, I need to be
able to conditionally load drop downs based upon what the selects for other
drop downs.
Can anyone tell me how? Example:
DropDown1 = Country
DropDown2 = State/Region
How Can i leave DropDown2 empty until they select from DropDown1?
Thanks.Hi JrMcG,
Thank you for your posting!
Based on my experience, you could do the following step to get the
Parameters related.
1. Create a dataset and add a Report Parameter named Country.
2. Create another dataset named States and use the parameter in the query
text. For example:
select State from tbl_Region where Country = @.Country
3. Create a new Report Patameter named State and in the Available values,
you need to use From query, and choose the dataset States, Value filed and
Label filed use State.
Then, in the preview, you could see the Parameter State could not get the
value untill you specify the value of Country.
Please try the above steps and let me know the result. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi JrMcG,
Have you got any chance to check this issue? Please let me know if you need
any help, thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||You are looking for a "Cascading Parameter" Report; there is a good
example in the sample set for SSRS 2005.
Dennis Graham
JrMcG wrote:
> In trying to incorporate business rules into my SSRS report, I need to be
> able to conditionally load drop downs based upon what the selects for other
> drop downs.
> Can anyone tell me how? Example:
> DropDown1 = Country
> DropDown2 = State/Region
> How Can i leave DropDown2 empty until they select from DropDown1?
> Thanks.|||My subject is very closeley tied to this one so i hope it's OK if I post
here...
I did the same thing but also added an 'all' option in my dataset. Selecting
'all' and a single option works but when selecting multi values the report
breaks. What can i do in my WHERE claus to get this working. Without it the
Bussiness Rules are useless.
"Wei Lu [MSFT]" wrote:
> Hi JrMcG,
> Have you got any chance to check this issue? Please let me know if you need
> any help, thank you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>

Conditionally hidden groups

Hi,

I have conditionially visible groups that are show/hide based on a report parameter. The problem is that I also want to have a document label on this group. When the group is hidden a blank entry appears in the doument map rather that no entry at all. Is this a bug or is there some work around. Thanks.

This worked for me.

I have two report parameters, Group1 and Group2, and the user selects what field they want to group on. One of the options for Group2 is "None", meaning that they do not want to have a second grouping for the report. In this case, I hide the group header and footer rows, and set the document map label to Nothing.

Edit the group, and set the document map label to something like:

=IIF (Parameters!Group2.Value = "None", Nothing, "My Document Map Label for Group2")

cheers,

Helen

|||Thanks for the reply, I'll give it a whirl.

Conditionally hidden groups

Hi,

I have conditionially visible groups that are show/hide based on a report parameter. The problem is that I also want to have a document label on this group. When the group is hidden a blank entry appears in the doument map rather that no entry at all. Is this a bug or is there some work around. Thanks.

This worked for me.

I have two report parameters, Group1 and Group2, and the user selects what field they want to group on. One of the options for Group2 is "None", meaning that they do not want to have a second grouping for the report. In this case, I hide the group header and footer rows, and set the document map label to Nothing.

Edit the group, and set the document map label to something like:

=IIF (Parameters!Group2.Value = "None", Nothing, "My Document Map Label for Group2")

cheers,

Helen

|||Thanks for the reply, I'll give it a whirl.

Conditionally hidden groups

Hi,

I have conditionially visible groups that are show/hide based on a report parameter. The problem is that I also want to have a document label on this group. When the group is hidden a blank entry appears in the doument map rather that no entry at all. Is this a bug or is there some work around. Thanks.

This worked for me.

I have two report parameters, Group1 and Group2, and the user selects what field they want to group on. One of the options for Group2 is "None", meaning that they do not want to have a second grouping for the report. In this case, I hide the group header and footer rows, and set the document map label to Nothing.

Edit the group, and set the document map label to something like:

=IIF (Parameters!Group2.Value = "None", Nothing, "My Document Map Label for Group2")

cheers,

Helen

|||Thanks for the reply, I'll give it a whirl.

Conditionally end a report

I am new to CR so this might be a really stupid question. CR version 10.

I want to end the report after X number of records based on a parameter field. Specifically I want to list customers in order of total sales but specify how many customers to print out. Like, Top 100 Customers by Sales.

I know I could print all records to the screen then choose to only print X number of pages but that's not possible if the report goes right to the printer.

Thank you.Type TOP into Crystal's online help (index) and see what you get.l

Conditionalize field values based on other field values

Here's a portion of the current statement.

UPDATE EngagementAuditAreas

SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,

numInherentRiskID = parent.numInherentRiskID,

numControlRiskID = parent.numControlRiskID,

numCombinedRiskID = parent.numCombinedRiskID,

numApproachTypeId = parent.numApproachTypeId,

bInherentRiskIsAffirmed = 0,

bControlRiskIsAffirmed = 0,

bCombinedRiskIsAffirmed = 0,

bApproachTypeIsAffirmed = 0,

bCommentsIsAffirmed = 0

FROM EngagementAuditAreas WITH(NOLOCK) ...

And what I need is to conditionalize the values of the "IsAffirmed" fields by looking at their corresponding "num" fields. Something like this (which doesn't work).

UPDATE EngagementAuditAreas

SET numDeterminationLevelTypeId = parent.numDeterminationLevelTypeId,

numInherentRiskID = parent.numInherentRiskID,

numControlRiskID = parent.numControlRiskID,

numCombinedRiskID = parent.numCombinedRiskID,

numApproachTypeId = parent.numApproachTypeId,

bInherentRiskIsAffirmed = (numInherentRiskID IS NULL),

bControlRiskIsAffirmed = (numControlRiskID IS NULL),

bCombinedRiskIsAffirmed = (numCombinedRiskID IS NULL),

bApproachTypeIsAffirmed = (numApproachTypeID IS NULL),

bCommentsIsAffirmed = (parent.txtComments IS NULL)

FROM EngagementAuditAreas WITH(NOLOCK)

Thanks.

Here is a small example of how you might accomplish your task.

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
Affirmed char(4),
Num int
)

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( NULL, 1 )
INSERT INTO @.MyTable VALUES ( NULL, 0 )
INSERT INTO @.MyTable VALUES ( NULL, NULL )

UPDATE @.MyTable
SET Affirmed = CASE Num
WHEN 0 THEN 'Yes'
WHEN 1 THEN 'No'
ELSE 'n/a'
END

SELECT *
FROM @.MyTable

RowID Affirmed Num
-- -- --
1 No 1
2 Yes 0
3 n/a NULL

However, it is usually NOT a good idea to have two columns that contain the same information (even if in two forms). You can easily 'transform' the values in the select queries using the same CASE structure as above.

Conditional XQuery: How to select a desirable node when it occurs multiple times

I would very much appreicate if someone could help me with the following

Return CountryCodes node based on the following rules:

(1) Ignore <AlternativeState> completely

(2) When <CurrentEvent>MarketSize</CurrentEvent> get CountryCodes from <MarketSize> node only

(3) When <CurrentEvent>MarketShare</CurrentEvent> get CountryCodes from <OtherEvents> node only

(4) When <CurrentEvent> doesn't exist then xml would have only one CountryCodes; get that node

I have come up with the following so far which is far from what is desirable

SELECT UsageID, Countries.Code.query('

for $CountryCode in .

return data($CountryCode)

') AS CountryCodes

FROM UsageAnalysis

CROSS APPLY xmlState.nodes('//*[not(self::AlternativeState)]/*/CountryCodes') AS Countries(Code)

GO

Please keep in mind xml comes from a table column.

The following are three possible simplified cases

Case 1

<State>

<StatsState>

<CurrentState>

<MarketSize>

<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>

</MarketSize>

<CurrentEvent>MarketSize</CurrentEvent>

</CurrentState>

</StatsState>

</State>

Case 2

<State>

<DefinitionState>

<CountryCodes>BR</CountryCodes>

</DefinitionState>

</State>

Case 3

<State>

<StatsState>

<CurrentState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

<AlternativeState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

</OtherEvents>

<MarketSize>

<CountryCodes>FR,FP,FG</CountryCodes>

</MarketSize>

<CurrentEvent>MarketShare</CurrentEvent>

</AlternativeState>

</OtherEvents>

<CurrentEvent>MarketShare</CurrentEvent>

<MarketSize>

<CountryCodes>,FR</CountryCodes>

</MarketSize>

</CurrentState>

</StatsState>

</State>

Hope this solve your problem:

Code Snippet

declare @.x xml

set @.x =

'<State>

<StatsState>

<CurrentState>

<MarketSize>

<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>

</MarketSize>

<CurrentEvent>MarketSize</CurrentEvent>

</CurrentState>

</StatsState>

</State>

<State>

<DefinitionState>

<CountryCodes>BR</CountryCodes>

</DefinitionState>

</State>

<State>

<StatsState>

<CurrentState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

<AlternativeState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

</OtherEvents>

<MarketSize>

<CountryCodes>FR,FP,FG</CountryCodes>

</MarketSize>

<CurrentEvent>MarketShare</CurrentEvent>

</AlternativeState>

</OtherEvents>

<CurrentEvent>MarketShare</CurrentEvent>

<MarketSize>

<CountryCodes>,FR</CountryCodes>

</MarketSize>

</CurrentState>

</StatsState>

</State>'

select @.x.query('

for $s in /State

return

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketSize")

then $s/StatsState/CurrentState/MarketSize/CountryCodes

else (

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketShare")

then $s/StatsState/CurrentState/OtherEvents/CountryCodes

else $s//CountryCodes

)

')

|||

Should this also be returned?

<CountryCodes>,FR</CountryCodes>

Please excuse me because I am rather new to the XML sector. I am confused by the question and the answer. I coded this up:

declare @.x xml
set @.x =
'<State>
<StatsState>
<CurrentState>
<MarketSize>
<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>
</MarketSize>
<CurrentEvent>MarketSize</CurrentEvent>
</CurrentState>
</StatsState>
</State>
<State>
<DefinitionState>
<CountryCodes>BR</CountryCodes>
</DefinitionState>
</State>
<State>
<StatsState>
<CurrentState>
<OtherEvents>
<CountryCodes>FR</CountryCodes>
<AlternativeState>
<OtherEvents>
<CountryCodes>FR</CountryCodes>
</OtherEvents>
<MarketSize>
<CountryCodes>FR,FP,FG</CountryCodes>
</MarketSize>
<CurrentEvent>MarketShare</CurrentEvent>
</AlternativeState>
</OtherEvents>
<CurrentEvent>MarketShare</CurrentEvent>
<MarketSize>
<CountryCodes>,FR</CountryCodes>
</MarketSize>
</CurrentState>
</StatsState>
</State>'

select coalesce (
nullif(t.c.query('./StatsState/CurrentState/MarketSize/CountryCodes').value('.','varchar(20)'), ''),
nullif(t.c.query('./StatsState/CurrentState/OtherEvents/CountryCodes').value('.','varchar(20)'),''),
t.c.query('./DefinitionState/CountryCodes').value('.','varchar(20)')

)
as CountryCodes
from @.x.nodes('State') t(c)

and received this result:

/*
CountryCodes
--
KT,LC,VG,SU,TT,UY,VE
BR
,FR
*/

Do the correct results need to include the markup such that the results should look more like this:

/*
CountryCodes
--
<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes><CountryCodes>BR</CountryCodes><CountryCodes>,FR</CountryCodes>
*/

(Trying to learn what is going on -- and I'm a bit confused.)

I appreciate the help.

|||

Jinghao, thanks very much. Your provided snippet does exactly what I have been trying to achieve. The only change I decided to introduce is to use data() so that I could get the scalar values for country codes as follows:

select @.x.query('

for $s in /State

return

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketSize")

then data($s/StatsState/CurrentState/MarketSize/CountryCodes)

else (

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketShare")

then data($s/StatsState/CurrentState/OtherEvents/CountryCodes)

else data($s//CountryCodes)

)

')

/*

Result set from your query:

<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>

<CountryCodes>BR</CountryCodes>

<CountryCodes>FR</CountryCodes>

Results after introducing data()

KT,LC,VG,SU,TT,UY,VE BR FR

*/

Now I could use a function call to return a list of country codes.

Thanks again for your help.

|||

Kent,

I must say that it took me a while to fully understand the solution you suggested by clever use of COALESCE. It did exactly what I was trying to achieve. i.e get a list of selected country codes.

/*

KT,LC,VG,SU,TT,UY,VE

BR

FR

*/

I just wanted to have a list of countries, without having any markups. i.e. just the scalar values of <countryCodes>

Your response has shown me another use of COALESCE function and I very much appreciate your help

Conditional Sum?

Hi,

Is it possible to have a conditional sum based on an item type existance in a set of values?

Example if i have the following set:

A

A

A

A

B

I just wanna sum B else if B doens't exist sum A

Best Regards,

Luis Simoes

Have an invisible textbox in your report with the expression and say the name of the textbox is textbox20:

Count(IIf(Fields!ItemType.Value = "A", Fields!YourField.Value, 0))

Then use this expression for summing:

Sum(IIf(ReportItems1textbox20.Value > 0, IIf(Fields!ItemType.Value = "A", Fields!YourField.Value, 0), 0))

Shyam

|||

Yes that would do the trick!

I can also use that count in the field formula right? Or it does affect performance that much?

Thanks,

Regards,

Luis Simoes

|||

Hello,

Doing this gives me two errors in the summing field.

"The value expression for the textbox 'textbox30' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers."

"The value expression for the textbox ‘textbox30’ refers to the report item ‘textbox28’. Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."

Any idea why?

Conditional sum based on visibility

Hi,

I have a report that is conditionally showing a textbox based on the previous entry that is working correctly.

My issue is that the non visible entries are still being added to my Sum statement at the end of the report.

I need a way to exclude an entry based on its visibility.

Any help would be greatly appreciated.

Are you using the Previous aggregate to get the previous entry? If you are not, then you can add a conditional, using the same expression for determining the visibility, to the SUM aggregate. For example, =SUM(IIF(HiddenExpression, 0, Fields!FieldName.Value))|||

Thanks for the response.

I am using the Previous function to control the visibility. The report needs to show the first entry for each particular company code, but not the duplicate entries.

For example: =Previous(Fields!Company_code.Value) = Fields!Company_code.Value

I tried adding a conditional with the same expression to determine if it should be included in the Sum, but that does not work.

Any other suggestions?

Thanks!

|||

Try handling it in the code (Report -> Report Properties -> Code)

Declare a public shared variable (integer/float) in the code and write a public function to sum up the values based on current company code and previous company code. Your code will look something like this in VB.Net:

Public Shared SumTotal as Integer

SumTotal = 0

Public Function CalculateSum(isCompanyCodeSame as Boolean, FieldValue as Integer) As String

If isCompanyCodeSame = False Then

SumTotal = SumTotal + FieldValue

End If

CalculateSum = ""

End Function

and

append this expression to any of the textboxes in your detail row:

Fields!FieldName.Value & Code.CalculateSum(Fields!Company_code.Value=Previous(Fields!Company_code.Value), Fields!FieldToBeSummed.Value)

And use Code.SumTotal to get the sum.

Shyam

|||

Thanks for the code!

It is working correctly now.

|||

I used the same method to stop displaying rows in a table after the 10th row. It works fine in VS 2005 but it acts wierd when I publish it to the production server. I count up the rows that are visible...

Public Shared VisibleRowTotal as Integer=0

Public Function CountVisibleRow(isVisible as Boolean) As String
If isVisible = False Then
VisibleRowTotal = VisibleRowTotal + 1
End If
CountVisibleRow = ""
End Function

Then I added a column in my report table to call the code...

=Code.CountVisibleRow(ReportItems!textbox54.Value) & " " & Code.VisibleRowTotal

Then I based my row visibility on the code value.

=IIF(Code.VisibleRowTotal>=10, True,False)

To get it to work the first time I had to rename the original report on the production server and then upload the new report. Once several users start hitting the report then no rows are visible or it's intermittent.

I'll admit I've have never used custom code in a report before. Is there something different I need to do when uploading an rdl with custom code? Am I handling the custom code properly?

|||
You should change the VisibleRowTotal variable to not be Shared. Having it be shared or static will cause each instance of the report to share the same total value. So, removing the modifier will allow each report instance to execute independently from one another.

Ian|||

Thanks Ian.

I had the issue where my totals were correct for the first time the report loaded, but the totals just kept incrementing when I used different filters on the report.

Taking Shared off of the variable resolved the issue.

Conditional Selection

Hi,
Hi,
I'm trying to construct a query (in a stored procedure) which will have a nu
mber of
selection criteria based on input parameters. There are a number of these p
arameters
whose selection conditions they represent which all have to be true for a ro
w to be
returned in the resultset.
The basic query is:
SELECT Store, StoreNumber
FROM Stores
WHERE ...
I'm trying to come up with the WHERE clause.
For example, I want to define a parameter named @.ExcludeSpecialties which if
it has
the value 1, means to return all stores but exclude stores whose StoreNumber
is in
the list (800, 802, 804). If the parameter has the value 0, then it means "
don't
care" and all StoreNumbers should be returned.
One could certainly argue that there probably should have been an column in
the
Stores row to indicate the store is a specialty store, rather than using a h
ard-wired
list of numbers. But the current data schema cannot be easily changed. Bes
ides, the
list never changes.
Indeed, there is a Franchise bit column in the row which is selected by anot
her
parameter called @.ExcludeFranchise whose WHERE predicate could be written as
:
WHERE Franchise = CASE WHEN @.ExcludeFranchise = 1 THEN 0 ELSE Franchise END
and if all the parameters were like this, I wouldn't be posting. Sadly, for
the
Specialties test I'm stuck with a NOT IN list.
This is easy enough to do in an IF/ELSE block, but there are several such si
milar
parameters whose values may be specified in any combination. This, I think,
makes
IF/ELSE impractical as the number of IF/ELSE statements to handle all possib
le
combinations would grow very quickly.
I'm hoping there is a simple solution to this NOT IN list, and it's just tha
t I can't
see it.
Can anyone help?
Thanks,
-- JeffTry this first ( Several popular approaches are details here ):
http://www.sommarskog.se/dyn-search.html
Anith|||try this in your where clause. Let me know if this helps
((@.ExcludeSpecialties = 0) or (storenumber not in (800, 802, 804)))|||You could store the specialties flag in a seperate table, with StoreNumber
as the key, then query against it instead of using the hardcoded list. This
way, when a new specialty store opens, or one of the existing stores
changes, you will just insert a row into the table and not have to touch the
code. It would be better to have it in the original table, but if you can't
change the original, maybe adding a new table is an option...
create table SpecialtyStores
(StoreNumber integer, Specialty bit) -- add PK and FK info here
SELECT Store, StoreNumber
FROM Stores
left outer join SpecialtyStores as spec
on stores.StoreNumber = spec.StoreNumber
WHERE
Specialty = CASE WHEN @.ExcludeFranchise = 1 THEN 0 ELSE 1 END
"Jeff Mason" <je.mason@.comcast.net> wrote in message
news:vvl1525lk0m8baqqpv5vs5q1g36a9c6jpa@.
4ax.com...
> Hi,
> Hi,
> I'm trying to construct a query (in a stored procedure) which will have a
number of
> selection criteria based on input parameters. There are a number of these
parameters
> whose selection conditions they represent which all have to be true for a
row to be
> returned in the resultset.
> The basic query is:
> SELECT Store, StoreNumber
> FROM Stores
> WHERE ...
> I'm trying to come up with the WHERE clause.
> For example, I want to define a parameter named @.ExcludeSpecialties which
if it has
> the value 1, means to return all stores but exclude stores whose
StoreNumber is in
> the list (800, 802, 804). If the parameter has the value 0, then it means
"don't
> care" and all StoreNumbers should be returned.
> One could certainly argue that there probably should have been an column
in the
> Stores row to indicate the store is a specialty store, rather than using a
hard-wired
> list of numbers. But the current data schema cannot be easily changed.
Besides, the
> list never changes.
> Indeed, there is a Franchise bit column in the row which is selected by
another
> parameter called @.ExcludeFranchise whose WHERE predicate could be written
as:
> WHERE Franchise = CASE WHEN @.ExcludeFranchise = 1 THEN 0 ELSE Franchise
END
> and if all the parameters were like this, I wouldn't be posting. Sadly,
for the
> Specialties test I'm stuck with a NOT IN list.
> This is easy enough to do in an IF/ELSE block, but there are several such
similar
> parameters whose values may be specified in any combination. This, I
think, makes
> IF/ELSE impractical as the number of IF/ELSE statements to handle all
possible
> combinations would grow very quickly.
> I'm hoping there is a simple solution to this NOT IN list, and it's just
that I can't
> see it.
> Can anyone help?
> Thanks,
> -- Jeff|||On Thu, 27 Apr 2006 08:13:02 -0700, Omnibuzz <Omnibuzz@.discussions.microsoft
.com>
wrote:

>try this in your where clause. Let me know if this helps
>((@.ExcludeSpecialties = 0) or (storenumber not in (800, 802, 804)))
Duh.
That did it. I knew it was something simple. I was having a Brain Fog, I gu
ess.
Thank you.
-- Jeff

Conditional Select Statement

Hello dbForumers,

Yet another puzzling question. I remember I saw somewhere a particular syntax to select a column based on a conditional predicate w/o using a user defined function. What I want to accomplish is this : SELECT (if column colA is empty then colB else colA) as colC from SomeTable. Possible ? Not possible? Have I hallucinated ?

Thank You!possible.
select (case colA when ='' then colB else colA end) as colC

Originally posted by Rollmops
Hello dbForumers,

Yet another puzzling question. I remember I saw somewhere a particular syntax to select a column based on a conditional predicate w/o using a user defined function. What I want to accomplish is this : SELECT (if column colA is empty then colB else colA) as colC from SomeTable. Possible ? Not possible? Have I hallucinated ?

Thank You!|||Yay, right on target.

But now I have some difficulties testing the NULL state... the syntax: ...(CASE VTE1 WHEN NULL THEN ACHN ELSE VTE1 END) AS COND_ACHN... won't throw any errors but wont work as excepted since it always sends the ELSE case no matter what...|||select isnull(vte1,achn) as COND_ACHN
or

select (CASE WHEN VTE1 is NULL THEN ACHN ELSE VTE1 END) AS COND_ACHN

Originally posted by Rollmops
Yay, right on target.

But now I have some difficulties testing the NULL state... the syntax: ...(CASE VTE1 WHEN NULL THEN ACHN ELSE VTE1 END) AS COND_ACHN... won't throw any errors but wont work as excepted since it always sends the ELSE case no matter what...|||Yay, right on target.

But now I have some difficulties testing the NULL state... the syntax: ...(CASE VTE1 WHEN NULL THEN ACHN ELSE VTE1 END) AS COND_ACHN... won't throw any errors but wont work as excepted since it always sends the ELSE case no matter what...|||To determine if an expression is NULL, use IS NULL or IS NOT NULL rather than comparison operators (such as = or !=).
follow the code of my previous message.It should work for u.

Originally posted by Rollmops
Yay, right on target.

But now I have some difficulties testing the NULL state... the syntax: ...(CASE VTE1 WHEN NULL THEN ACHN ELSE VTE1 END) AS COND_ACHN... won't throw any errors but wont work as excepted since it always sends the ELSE case no matter what...|||I just had to remove the 'VTE1' in ...(CASE VTE1... for the predicate to work accordingly =) anyways thanks a lot it works just fine now =)

Conditional row count

Hi,

Is there a way to to use RowCount based on a condition?

I have AS400 logs in csv file which I want to append to a SQL table using filters. But during passing of each record, I also want to count row only if they matches to a specific criteria.

Thanks

Use a conditional split transformation to look at your data. Then on which ever output you desire, use a row counter. A row counter isn't a destination (it can be though), so it can go in-line with your flow.

Conditional Query

Hi,

I'm trying to construct a query (in a stored procedure) which will have a number of
selection criteria based on input parameters. There are a number of these parameters
whose selection conditions they represent which all have to be true for a row to be
returned in the resultset.

The basic query is:

SELECT Store, StoreNumber
FROM Stores
WHERE ...

I'm trying to come up with the WHERE clause.

For example, I want to define a parameter named @.ExcludeSpecialties which if it has
the value 1, means to return all stores but exclude stores whose StoreNumber is in
the list (800, 802, 804). If the parameter has the value 0, then it means "don't
care" and all StoreNumbers should be returned.

One could certainly argue that there probably should have been an column in the
Stores row to indicate the store is a specialty store, rather than using a hard-wired
list of numbers. But the current data schema cannot be easily changed. Besides, the
list never changes.

Indeed, there is a Franchise bit column in the row which is selected by another
parameter called @.ExcludeFranchise whose WHERE predicate could be written as:

WHERE Franchise = CASE WHEN @.ExcludeFranchise = 1 THEN 0 ELSE Franchise END

and if all the parameters were like this, I wouldn't be posting. Sadly, for the
Specialties test I'm stuck with a NOT IN list.

This is easy enough to do in an IF/ELSE block, but there are several such similar
parameters whose values may be specified in any combination. This, I think, makes
IF/ELSE impractical as the number of IF/ELSE statements to handle all possible
combinations would grow very quickly.

I'm hoping there is a simple solution to this NOT IN list, and it's just that I can't
see it.

Can anyone help?

Thanks,

-- JeffJeff Mason (je.mason@.comcast.net) writes:
> I'm trying to construct a query (in a stored procedure) which will have
> a number of selection criteria based on input parameters. There are a
> number of these parameters whose selection conditions they represent
> which all have to be true for a row to be returned in the resultset.

I have an article on by web site that discusses a couple of alternatives,
both with static and dynamic SQL:
http://www.sommarskog.se/dyn-search.html

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Thu, 27 Apr 2006 11:10:01 -0400, Jeff Mason wrote:

(snip)
>For example, I want to define a parameter named @.ExcludeSpecialties which if it has
>the value 1, means to return all stores but exclude stores whose StoreNumber is in
>the list (800, 802, 804). If the parameter has the value 0, then it means "don't
>care" and all StoreNumbers should be returned.

Hi Jeff,

WHERE ( @.ExcludeSpecialties = 0 OR StoreNumber NOT IN (800, 802, 804) )

(snip)
>Indeed, there is a Franchise bit column in the row which is selected by another
>parameter called @.ExcludeFranchise whose WHERE predicate could be written as:
>WHERE Franchise = CASE WHEN @.ExcludeFranchise = 1 THEN 0 ELSE Franchise END
>and if all the parameters were like this, I wouldn't be posting. Sadly, for the
>Specialties test I'm stuck with a NOT IN list.

That is indeed a common method to write such queries.

Do read the article Erland posted a link to - it describes a bunch of
methods to achieve what you need, with all their strengths and
weaknesses. Good stuff!

--
Hugo Kornelis, SQL Server MVP

Conditional page breaks

I want to set a page break at the change of a group conditionally based on a
parameter value. Is there any way to do this in Reporting Services?Any help here? Because I would *really* like to figure out how to do this.
Once again, I have a grouped table in a report layout. I would like to have
a page break at the end of a grouping happen conditionally based on a boolean
parameter that I pass to the report.

conditional look....

Hi;
is there a way to control the look of the printing based on the data?..
i want to change the look of a textbox in a table, based on the value in my
query, for example:
Job desc Name Income
Emply John 5000
Emply Peter 5000
Sup Hugo 5000
Emply Rich 5000
I want to change Font style to bold, when Job desc is Sup
Is there a way to do it?
TIAOn Apr 14, 10:09 am, "Willo" <willobe...@.yahoo.com.mx> wrote:
> Hi;
> is there a way to control the look of the printing based on the data?..
> i want to change the look of a textbox in a table, based on the value in my
> query, for example:
> Job desc Name Income
> Emply John 5000
> Emply Peter 5000
> Sup Hugo 5000
> Emply Rich 5000
> I want to change Font style to bold, when Job desc is Sup
> Is there a way to do it?
> TIA
Sure. While in the Layout view, select 'F4' (or the View tab and
Properties Window).Select the cell(s) in the table that you want to
change the font style for and in the Properties Window open up [+]
Font. To the right of 'Font Weight,' select the drop-down menu and
select '<Expression...>' and enter something like the following:
=iif(Fields!JobDesc.Value = "Sup", "Bold", "Normal")
Regards,
Enrique Martinez
Sr. Software Consultant

Conditional Join on Data Flow?

Hi,

Can we make conditional joins on the data flow?

Imagine i want to join 2 tables based on a value and an interval... Imagine i have a positioning number in one table and in the other i have a price, from_position, to_position and i want to join the to tables like position >= from_position and position <= to_position

Can we do this in SSIS?

Best Regards,

You can't do a join like that using a single component. To achieve this you might try doing a full join and then using a conditional split or a custom script to drop the rows that don't meet the desired join criteria.

Conditional inserts in trigger

Can I have a trigger that inserts into 1 of 3 different tables based on a
column being inserted on the driving table?
For example, I issue
INSERT INTO dbo.People (SSN, CategoryCode)
VALUES (123456789, 3)
If CategoryCode inserted is 3, 10 or 11 then I need to
INSERT INTO dbo.ClientInfo (PeopleID)
VALUES (inserted.PeopleID)
If CategoryCode inserted is 1 then I need to
INSERT INTO dbo.ApplicantInfo (PeopleID)
VALUES (inserted.PeopleID)
etc.
One point that may or may not be important is that the original PeopleID is
assigned in an existing insert trigger and is a random number.
Thanks.
Davidyes - see BOL for more on CREATE TRIGGER, but e.g.
create trigger yourtrigger on People for insert
as
begin
insert into dbo.ApplicantInfo (PeopleID)
select PeopleID
from inserted
where CategoryCode=1
insert into dob.ClientInfo (PeopleID)
select PeopleID
from inserted
where CategoryCode in (3, 10, 11)
end
David Chase wrote:
> Can I have a trigger that inserts into 1 of 3 different tables based on a
> column being inserted on the driving table?
> For example, I issue
> INSERT INTO dbo.People (SSN, CategoryCode)
> VALUES (123456789, 3)
> If CategoryCode inserted is 3, 10 or 11 then I need to
> INSERT INTO dbo.ClientInfo (PeopleID)
> VALUES (inserted.PeopleID)
> If CategoryCode inserted is 1 then I need to
> INSERT INTO dbo.ApplicantInfo (PeopleID)
> VALUES (inserted.PeopleID)
> etc.
> One point that may or may not be important is that the original PeopleID i
s
> assigned in an existing insert trigger and is a random number.
> Thanks.
> David
>|||That doesn't work. I get an error when it tries to create ClientInfo
because ClientInfo table has referrential integrity rule that requires
matching record in People table. Evidently, ref. integrity check does not
know that People table record exists yet. Below is my trigger code, if that
helps.
CREATE TRIGGER T_People_ITrig ON dbo.People FOR INSERT AS
SET NOCOUNT ON
DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'PersonID' */
SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @.newc = (SELECT PersonID FROM inserted)
UPDATE People SET PersonID = @.randc WHERE PersonID = @.newc
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:uHtw5DHHGHA.1180@.TK2MSFTNGP09.phx.gbl...
> yes - see BOL for more on CREATE TRIGGER, but e.g.
> create trigger yourtrigger on People for insert
> as
> begin
> insert into dbo.ApplicantInfo (PeopleID)
> select PeopleID
> from inserted
> where CategoryCode=1
> insert into dob.ClientInfo (PeopleID)
> select PeopleID
> from inserted
> where CategoryCode in (3, 10, 11)
> end
> David Chase wrote:|||David Chase (dlchase@.lifetimeinc.com) writes:
> That doesn't work. I get an error when it tries to create ClientInfo
> because ClientInfo table has referrential integrity rule that requires
> matching record in People table. Evidently, ref. integrity check does
> not know that People table record exists yet. Below is my trigger code,
> if that helps.
Set up the FK to have UPDATE ON CASCADE.
Or instead of an UPDATE, perform first an INSERT, update the childre,
and then delete the original.

> CREATE TRIGGER T_People_ITrig ON dbo.People FOR INSERT AS
> SET NOCOUNT ON
> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'PersonID' */
> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
> SELECT @.newc = (SELECT PersonID FROM inserted)
> UPDATE People SET PersonID = @.randc WHERE PersonID = @.newc
Keep in mind that a trigger fires once per statement, and thus inserted
can hold many rows.
A better bet for a random number is probably checksum(newid()).
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx