conditions, expressions

I have a table

CREATE TABLE [dbo].[CmnLanguage]
(
[Id] [char](2) NOT NULL CONSTRAINT PkCmnLanguage_Id PRIMARY KEY,
[EnglishName] [varchar](26) NOT NULL,
[NativeName] [nvarchar](26) NOT NULL,
[DirectionType] [smallint] NOT NULL,
[IsVisible] [bit] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(),
[ModifiedDateTime] [datetime] NULL
)

We will use these 3 queries

select * from CmnLanguage where IsVisible = 0
select * from CmnLanguage where IsVisible = 1
select * from CmnLanguage

I want to make a method which handles these queries.

But at the back end on Stored Procedures

We have to write 3 queries

Which I don't want to do.

I want to minimize the queries and conditions

and want to just write one for these 3

Can any one do it?

How about this:

SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOCREATE PROCEDURE dbo.sp_MyProcedure(@.IsVisibleAS BIT =NULL)ASBEGINSELECT*FROM[dbo].[CmnLanguage]WHERE[IsVisible] =CASEWHEN @.IsVisibleISNULLTHEN [IsVisible]ELSE @.IsVisibleENDENDGO

|||

Nice.

Very Useful.

Thanks.

Conditional SQL Insert Query

I have a simple ms access table with no primary key. I want to check if the value exists before it exists. I know there is way to do that directly using a insert clause without having a select statement but cannot seem to get it right.

Any help would be greatly appreciated.

Regards,

Vibhu Bansal.not sure if this is what you need but you can give it a try...

INSERT INTO <table> (field1, field2...) VALUES (value1, value2...)
WHERE (SELECT COUNT(*) FROM <table> WHERE <ColumnToCheck> = <ValueToCompare>) > 0;|||

I am looking for something similar but this gives an error in MsAccess saying semicolon expected. The semicolon is expected before "WHERE" clause!

Vibhu Bansal

|||can you post your code?|||

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

|||

Hi there,

From experience, you cannot insert WHERE clause into an INSERT statement. Period.

I discovered this in the early days when I was just learning SQL and tried using an INSERT statement instead of an UPDATE statement and got an error kicked back at me.

I think there is an IF statement for SQL but am not sure - anyone else know?

Thanks,

medicineworker

|||

Vibhu Bansal wrote:

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

Try this, use select instead of values()

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) select '05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO' where (select count(*) from tblTexas where ID='05320052')=0

|||

This does not work either...

select will require a table name or something...:)

|||Try doing a select command first i.e. select * from tblTexas where ID='05320052
then check @.@.ROWCOUNT for rows returned then the insert statement. Also Try selecting by table value rather than count. Like this:

select * from tblTexas where ID='05320052
if @.@.ROWCOUNT = 0
insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO')

Conditional SQL Insert Query

I have a simple ms access table with no primary key. I want to check if the value exists before it exists. I know there is way to do that directly using a insert clause without having a select statement but cannot seem to get it right.

Any help would be greatly appreciated.

Regards,

Vibhu Bansal.not sure if this is what you need but you can give it a try...

INSERT INTO <table> (field1, field2...) VALUES (value1, value2...)
WHERE (SELECT COUNT(*) FROM <table> WHERE <ColumnToCheck> = <ValueToCompare>) > 0;|||

I am looking for something similar but this gives an error in MsAccess saying semicolon expected. The semicolon is expected before "WHERE" clause!

Vibhu Bansal

|||can you post your code?|||

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

|||

Hi there,

From experience, you cannot insert WHERE clause into an INSERT statement. Period.

I discovered this in the early days when I was just learning SQL and tried using an INSERT statement instead of an UPDATE statement and got an error kicked back at me.

I think there is an IF statement for SQL but am not sure - anyone else know?

Thanks,

medicineworker

|||

Vibhu Bansal wrote:

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

Try this, use select instead of values()

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) select '05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO' where (select count(*) from tblTexas where ID='05320052')=0

|||

This does not work either...

select will require a table name or something...:)

|||Try doing a select command first i.e. select * from tblTexas where ID='05320052
then check @.@.ROWCOUNT for rows returned then the insert statement. Also Try selecting by table value rather than count. Like this:

select * from tblTexas where ID='05320052
if @.@.ROWCOUNT = 0
insert

into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair)

values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO')

Conditional SQL Insert Query

I have a simple ms access table with no primary key. I want to check if the value exists before it exists. I know there is way to do that directly using a insert clause without having a select statement but cannot seem to get it right.

Any help would be greatly appreciated.

Regards,

Vibhu Bansal.not sure if this is what you need but you can give it a try...

INSERT INTO <table> (field1, field2...) VALUES (value1, value2...)
WHERE (SELECT COUNT(*) FROM <table> WHERE <ColumnToCheck> = <ValueToCompare>) > 0;|||

I am looking for something similar but this gives an error in MsAccess saying semicolon expected. The semicolon is expected before "WHERE" clause!

Vibhu Bansal

|||can you post your code?|||

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

|||

Hi there,

From experience, you cannot insert WHERE clause into an INSERT statement. Period.

I discovered this in the early days when I was just learning SQL and tried using an INSERT statement instead of an UPDATE statement and got an error kicked back at me.

I think there is an IF statement for SQL but am not sure - anyone else know?

Thanks,

medicineworker

|||

Vibhu Bansal wrote:

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO') where (select count(*) from tblTexas where ID='05320052')=0

The erro says "Semicolon expected" before where clause

Sorry guys was away on vacation so could post code earlier.

Any help would be beneficial.

Vibhu

Try this, use select instead of values()

insert into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair) select '05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO' where (select count(*) from tblTexas where ID='05320052')=0

|||

This does not work either...

select will require a table name or something...:)

|||Try doing a select command first i.e. select * from tblTexas where ID='05320052
then check @.@.ROWCOUNT for rows returned then the insert statement. Also Try selecting by table value rather than count. Like this:

select * from tblTexas where ID='05320052
if @.@.ROWCOUNT = 0
insert

into tblTexas(ID, DateBirth, Race, Gender, Height, Weight, Eyes, Hair)

values ('05320052', '11/08/1976', 'W', 'M', '509', '210', 'BRO', 'BRO')

Conditional formatting based on second dataset

I am trying to create a report which has conditional formatting.

The primary dataset is a view of objects with several values

eg

object1,0,4,0,1

object2,0,3,1,1

The secondary dataset is the comparison table and just contains the values

eg

0,3,1,1

I'd like to conditionally format the values based on the comparison table but when I create an expression comparing to the second dateset

eg

=iif(Fields!object1.Value <> Fields!comp_object1.Value , "Red", "SkyBlue")

i get

Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Not sure if there is a way to tell the expression to look for comp_object1 in dataset2, even tho it is uniquely named?

Ideas gratefully received !

Yes, I'm pretty sure you can do what you want.

I'll assume that the fields in dataset2 are named comp_object1, comp_object2, etc. I will also assume that dataset2 has one row -- because if it has multiple rows to match dataset1's rows, you should put these columns in the *same* dataset.

In the table based on the first dataset, you can use expressions like this for column #1 -- and I'm pretty sure the scope is case-sensitive, so you may have to play with what I'm suggesting here

Code Snippet

=iif(Fields!object1.Value <> First(Fields!comp_object1.Value, "dataset2") , "Red", "SkyBlue")

To use the expression builder to do this, notice that on the left side there is a "Datasets" item in the list. If you select it, then the middle list has your datasets listed. Click on dataset2 in the middle list and you will see the appropriate items in your right-side list of possible fields...

>L<

|||Thanks, that worked!|||

hi,

this is works perfactly........ but i have another issue that i want to display 2 different dataset's fields in a single table. i can put 2nd database's field in table but it can put only with aggregate functions like (first, last...etc.) but i want to put it directly.........bcz if i put it with first function i can't get other records............. can u help me?

|||

Basically, except for aggregates that tell it what to display, the processing engine has no way to figure out what record from the other dataset to display in each row <s>.

You can either write a query that combines the data into one dataset, so that you provide the relationship yourself, or you can use a subreport, passing a parameter from the parent dataset that indicates what data to display in the subreport.

Alternatively you can probably do something in code to pull the data from the other dataset (I haven't tried this because I don't really see the point, but it would probably work) .

You can also nest a data region (another table or list or matrix) inside a group row for a table -- and this other data region has its own dataset -- but I gather this isn't what you have in mind.

>L<

|||

hi lisa,

Thx a lot for suggestion..............i have tried to put another data region in table with aggregate function but still its not works and gives error........ do u have any otherway?

|||

Well, I'm not sure I understand exactly what you tried so I can't explain or help <s>.

Tell us about your two datasets.

Tell us what data region you put in, and where exactly you put it, etc.

Tell us what the error was...

>L<

Conditional formatting based on second dataset

I am trying to create a report which has conditional formatting.

The primary dataset is a view of objects with several values

eg

object1,0,4,0,1

object2,0,3,1,1

The secondary dataset is the comparison table and just contains the values

eg

0,3,1,1

I'd like to conditionally format the values based on the comparison table but when I create an expression comparing to the second dateset

eg

=iif(Fields!object1.Value <> Fields!comp_object1.Value , "Red", "SkyBlue")

i get

Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Not sure if there is a way to tell the expression to look for comp_object1 in dataset2, even tho it is uniquely named?

Ideas gratefully received !

Yes, I'm pretty sure you can do what you want.

I'll assume that the fields in dataset2 are named comp_object1, comp_object2, etc. I will also assume that dataset2 has one row -- because if it has multiple rows to match dataset1's rows, you should put these columns in the *same* dataset.

In the table based on the first dataset, you can use expressions like this for column #1 -- and I'm pretty sure the scope is case-sensitive, so you may have to play with what I'm suggesting here

Code Snippet

=iif(Fields!object1.Value <> First(Fields!comp_object1.Value, "dataset2") , "Red", "SkyBlue")

To use the expression builder to do this, notice that on the left side there is a "Datasets" item in the list. If you select it, then the middle list has your datasets listed. Click on dataset2 in the middle list and you will see the appropriate items in your right-side list of possible fields...

>L<

|||Thanks, that worked!|||

hi,

this is works perfactly........ but i have another issue that i want to display 2 different dataset's fields in a single table. i can put 2nd database's field in table but it can put only with aggregate functions like (first, last...etc.) but i want to put it directly.........bcz if i put it with first function i can't get other records............. can u help me?

|||

Basically, except for aggregates that tell it what to display, the processing engine has no way to figure out what record from the other dataset to display in each row <s>.

You can either write a query that combines the data into one dataset, so that you provide the relationship yourself, or you can use a subreport, passing a parameter from the parent dataset that indicates what data to display in the subreport.

Alternatively you can probably do something in code to pull the data from the other dataset (I haven't tried this because I don't really see the point, but it would probably work) .

You can also nest a data region (another table or list or matrix) inside a group row for a table -- and this other data region has its own dataset -- but I gather this isn't what you have in mind.

>L<

|||

hi lisa,

Thx a lot for suggestion..............i have tried to put another data region in table with aggregate function but still its not works and gives error........ do u have any otherway?

|||

Well, I'm not sure I understand exactly what you tried so I can't explain or help <s>.

Tell us about your two datasets.

Tell us what data region you put in, and where exactly you put it, etc.

Tell us what the error was...

>L<

Concurrent Access - New Record, Primary Key problem....

Can someone explain what happens when two users concurrently attempt to
create a new record in a table with an autonumber primary key? For example,
user 1 creates a new record and manipulates it within a transaction making
use (perhaps) of the @.@.IDENTITY value when creating other, related records.
Before this transaction is complete, user 2 creates a new record and does
the same thing. Presumably they will both have the same @.@.IDENTITY? If
this is the case, how is it possible to manage such a situation?

Thanks."Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:csgov0$4e6$1$830fa17d@.news.demon.co.uk...
> Can someone explain what happens when two users concurrently attempt to
> create a new record in a table with an autonumber primary key? For
> example, user 1 creates a new record and manipulates it within a
> transaction making use (perhaps) of the @.@.IDENTITY value when creating
> other, related records. Before this transaction is complete, user 2
> creates a new record and does the same thing. Presumably they will both
> have the same @.@.IDENTITY? If this is the case, how is it possible to
> manage such a situation?
> Thanks.

No, each session will have a different value, so there's no problem with
concurrency - check out SCOPE_IDENTITY(), IDENT_CURRENT() and @.@.IDENTITY in
Books Online.

Note that just defining a column as an identity column is not enough to
guarantee uniqueness - you can still create duplicates manually (see SET
IDENTITY_INSERT in BOL), so if you want to use the column as a PK, make sure
it is declared as a PK when you create the table.

Simon|||Ok that simplifies things somewhat. Yes, the columns in question are both
Identity and Primary Key.

Thanks very much for your reply.

Robin

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41ebea66$1_3@.news.bluewin.ch...
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:csgov0$4e6$1$830fa17d@.news.demon.co.uk...
>>
>> Can someone explain what happens when two users concurrently attempt to
>> create a new record in a table with an autonumber primary key? For
>> example, user 1 creates a new record and manipulates it within a
>> transaction making use (perhaps) of the @.@.IDENTITY value when creating
>> other, related records. Before this transaction is complete, user 2
>> creates a new record and does the same thing. Presumably they will both
>> have the same @.@.IDENTITY? If this is the case, how is it possible to
>> manage such a situation?
>>
>> Thanks.
>>
> No, each session will have a different value, so there's no problem with
> concurrency - check out SCOPE_IDENTITY(), IDENT_CURRENT() and @.@.IDENTITY
> in Books Online.
> Note that just defining a column as an identity column is not enough to
> guarantee uniqueness - you can still create duplicates manually (see SET
> IDENTITY_INSERT in BOL), so if you want to use the column as a PK, make
> sure it is declared as a PK when you create the table.
> Simon