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.

Conditions on latest record

I have a table that has records layed out as so:

Table:
fd_Id INT IDENTITY (1, 1)
fd_User VARCHAR(30)
fd_Effective DATETIME

Data could be as follows:
1 | "user1" | 6/20/2001
2 | "user2" | 6/1/2002
3 | "user2" | 6/5/2002
4 | "user2" | 6/5/2002
5 | "user2" | 2/1/2002
6 | "user3" | 9/1/2003
7 | "user3" | 10/2/2002
8 | "user4" | 1/1/2005

What I need to retrieve from that table is the SINGLE LATEST item of
each fd_User.

Results:
1 | "user1" | 6/20/2001
3 | "user2" | 6/5/2002 (or 4 | "user2" | 6/5/2002) since the dates are
the same but only 1 of them
6 | "user3" | 9/1/2003
8 | "user4" | 1/1/2005Untested

SELECT
MAX(FD_ID) AS 'FD_ID',
FD_USER,
MAX(FD_EFFECTIVE) AS 'FD_EFFECTIVE'
FROM F_TABLE
GROUP FD_USER|||select min(a.fd_Id) as fd_Id,
a.fd_User,
a.fd_Effective
from mytable a
inner join (select fd_User,max(fd_Effective) as fd_Effective
from mytable
group by fd_User) b on a.fd_User=b.fd_User and
a.fd_Effective=b.fd_Effective
group by a.fd_User,a.fd_Effective|||Verticon:: wrote:
> I have a table that has records layed out as so:
> Table:
> fd_Id INT IDENTITY (1, 1)
> fd_User VARCHAR(30)
> fd_Effective DATETIME
> Data could be as follows:
> 1 | "user1" | 6/20/2001
> 2 | "user2" | 6/1/2002
> 3 | "user2" | 6/5/2002
> 4 | "user2" | 6/5/2002
> 5 | "user2" | 2/1/2002
> 6 | "user3" | 9/1/2003
> 7 | "user3" | 10/2/2002
> 8 | "user4" | 1/1/2005
> What I need to retrieve from that table is the SINGLE LATEST item of
> each fd_User.
> Results:
> 1 | "user1" | 6/20/2001
> 3 | "user2" | 6/5/2002 (or 4 | "user2" | 6/5/2002) since the dates are
> the same but only 1 of them
> 6 | "user3" | 9/1/2003
> 8 | "user4" | 1/1/2005

First add the constraint that you're apparently missing:

ALTER TABLE tbl
ADD CONSTRAINT ak1_tbl
UNIQUE (fd_User, fd_Effective);

Then:

SELECT fd_Id, fd_User, fd_Effective
FROM tbl
WHERE fd_Effective =
(SELECT MAX(fd_Effective)
FROM tbl AS t
WHERE t.fd_User = tbl.fd_User);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--sqlsql

conditions in where clause

Hi I am upsizing the access database to SQL 2005.
I currently converting Access Query's to SQL stored procs and functions but i have got stuck on one issue.

In access there is a update statement that has an IIF in the where clause, i have tried replicating this in T-SQL buy using case statement but it does not work.

in access the where looks some thing like this Where IIF(table1.Column1 = 0,Table2.column1,Table3.Column1)

Any one know how i can best replicate this behavior?

Dagz

In place of IIF, use CASE. Using your example:

Where IIF(table1.Column1 = 0,Table2.column1,Table3.Column1)

Code Snippet

WHERE CASE Table1.Column1
WHEN 0 THEN Table2.Column1
ELSE Table3.Column1
END

|||Here you go

Code Snippet

Case WHEN table1.Column1 = 0 THEN Table2.Column1
else Table3.Column1
END|||

when i try this i get the error message, where is of none boolean type?

Conditional Split Transformation

Hi

Can any one please tell me how do I give multiple conditions in Conditional Split Transformation.

Exp:

I have few columns as

ReturnSUK

TimeSUK

EntitySUK

PeriodSUK

Now the condition should be :

! ISNULL (ReturnSUK) & ! ISNULL (TimeSUK) & ! ISNULL (EntitySUK) &! ISNULL (PeriodSUK)

Please provide me the proper condition for the above mentioned requirement.

Thank you

Use two & symbols:

!ISNULL(ReturnSUK) && !ISNULL(TimeSUK) && ....|||

Thank you Its Working

If i need to give the same condition for OR (^) rather then AND (&) so the condition would be

this :

ISNULL(ReturnSUK) ^ ISNULL(TimeSUK) ^ ISNULL(BankSUK) ^ ISNULL(EntitySUK) ^ ISNULL(PeriodSUK)

or ,can you please help me in this too.

Thank you

|||Or is written by using two || symbols:

TEST1 || TEST2 || TEST3 ....

Conditional Send Mail Task

Hi,

I want send email if certain conditions are met (by send mail task)... if compnay records does not exists in some table (returns null)... not task failure.... how do I achieve this without using Script task?

does any one have an idea about it,

regards

You need conditional workflow: http://www.sqlis.com/default.aspx?306

You can base your expression on a boolean variable that can be set from various places, including a script task.

-Jamie

|||

In contiuation i would like to ask another question i.e. I want to send the results of my query (complete result sets) from email task? How will I able to achieve the task.?

ur help will be appreciated

|||

Zadoras wrote:

In contiuation i would like to ask another question i.e. I want to send the results of my query (complete result sets) from email task? How will I able to achieve the task.?

ur help will be appreciated

Hmmm interesting one. probably the easiest way is to push that data into a flat file destination and then email that file as an attachment.

-Jamie

|||

that's what i was thinking... but i m searching for other way (may be the easiest way)

BTW... thanx for your support

if ne one come across to ne better idea than that please let me know

Conditional number formatting

How can I use conditional number formatting if one of the formats contain a
comma?
The iif statement conditions are separated by commas. If one of the
conditionals should have the number format:
#,##0,;(#,##0,);0
While the other condition should format to percentage:
#.0%;(#.0%);0.0%
how would I write the iif statement? Or is there another way to format this?I just answered my own question. I needed to put the number format syntax in
double qoutes.
"RJB" wrote:
> How can I use conditional number formatting if one of the formats contain a
> comma?
> The iif statement conditions are separated by commas. If one of the
> conditionals should have the number format:
> #,##0,;(#,##0,);0
> While the other condition should format to percentage:
> #.0%;(#.0%);0.0%
> how would I write the iif statement? Or is there another way to format this?

conditional execution of the next job in DTS

I have a job which exports and emails the data from a table (subject to some conditions) . The data is exported to a test file. I donot want to send the email if there are no rows exported. or the filesieze is 0. Otherwsie I want to send the email with this text file as attachement.

Any ideas?

Thanks
RaguYou can turn steps on or off. See the topic titled "Using ActiveX Scripts in a DTS Workflow" in BOL.

Also the topic "Using ActiveX Scripts in DTS" may be helpful.

Phil|||activex in dts is single threaded in dts and slows all kinds of stuff down.
try using any built in task first if you can.
if you want to try something trick, try the mesage queue task.
or
you can provide if logic for rows returned in an execute sql task and if successfull have the on success run an xpcmdshell from the sql task/
or try a data driven query task ++++ place if logic in the query
if rows returned then transfer to text
if no rows returned then raiserror

Books Online {Data Driven Query Task}
Books Online {Execute SQL Task}
Books Online {Message Queue Task}
Books Online {DTS tasks, overview}|||Phil/Ruprect: Thanks for your replies. I was using logic in sql (Execute SQL Task) to initiate the next step but the table has grown to 15 M rows. I plan to use activex script to just check the size of the output file (Thanks to sqldts.com site for the script) and accordingly initiate the next job.

Thanks again for the replies.

Ragu

conditional dynamic SQL in stored procedure, not returning any result

Created a stored procedure which returns Selected table from database.

I pass variables,according to conditions

For some reason it is not returning any result for any condition

Stored Procedure

ALTER PROCEDUREdbo.StoredProcedure

(

@.conditionvarchar(20),

@.IDbigint,

@.date1as datetime,

@.date2as datetime

)

AS

/* SET NOCOUNT ON */

IF@.conditionLIKE'all'

SELECT CllientEventDetails.*

FROM CllientEventDetails

WHERE (ClientID = @.ID)

IF@.conditionLIKE'current_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventFrom <=ISNULL(@.date1, EventFrom))AND

(EventTill >=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'past_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventTill <=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'upcoming_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE(ClientID = @.ID)AND

(EventFrom >=ISNULL(@.date1, EventFrom))

IF@.conditionLIKE''

SELECT CllientEventDetails.*

FROM CllientEventDetails

RETURN

Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants

Hi,

Please check whether the @.condition parameter you have provided can hit in the IF statements. At the end, you don't need to use RETURN if you don't return anything.

I would not suggest you put the condition in your WHERE clause, because it will return an empty result set for the condition that does not meet. And multiple result sets will be returned for all the SELECT statements.

|||

Nitin Pawar:

Created a stored procedure which returns Selected table from database.

I pass variables,according to conditions

For some reason it is not returning any result for any condition

Stored Procedure

ALTER PROCEDUREdbo.StoredProcedure

(

@.conditionvarchar(20),

@.IDbigint,

@.date1as datetime,

@.date2as datetime

)

AS

/* SET NOCOUNT ON */

IF@.conditionLIKE'all'

SELECT CllientEventDetails.*

FROM CllientEventDetails

WHERE (ClientID = @.ID)

IF@.conditionLIKE'current_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventFrom <=ISNULL(@.date1, EventFrom))AND

(EventTill >=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'past_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventTill <=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'upcoming_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE(ClientID = @.ID)AND

(EventFrom >=ISNULL(@.date1, EventFrom))

IF@.conditionLIKE''

SELECT CllientEventDetails.*

FROM CllientEventDetails

RETURN

Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants

replaceLike by= and then try .. hope it will help

Conditional Delete without Logging Transactions

Hi,
I need to delete many records in a table based on some conditions in the
where clause. This delete is taking plenty of time. How do I disable the
logging of transactions?
Thanks and Regards,
Prasanth
Hi
You cannot disable logging during deletion as in your case.
Divide the deletion into small transaction and try to run it. Also check out
if there are some indexes defined on the table, try to drop them and after
deletion to recreate them.
"Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
news:98AE9AA6-EF24-4334-AA8A-65E919119B5C@.microsoft.com...
> Hi,
> I need to delete many records in a table based on some conditions in the
> where clause. This delete is taking plenty of time. How do I disable the
> logging of transactions?
> --
> Thanks and Regards,
> Prasanth
|||Thanks, I will do with that.
"Uri Dimant" wrote:

> Hi
> You cannot disable logging during deletion as in your case.
> Divide the deletion into small transaction and try to run it. Also check out
> if there are some indexes defined on the table, try to drop them and after
> deletion to recreate them.
> "Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
> news:98AE9AA6-EF24-4334-AA8A-65E919119B5C@.microsoft.com...
>
>

Conditional Delete without Logging Transactions

Hi,
I need to delete many records in a table based on some conditions in the
where clause. This delete is taking plenty of time. How do I disable the
logging of transactions?
--
Thanks and Regards,
PrasanthHi
You cannot disable logging during deletion as in your case.
Divide the deletion into small transaction and try to run it. Also check out
if there are some indexes defined on the table, try to drop them and after
deletion to recreate them.
"Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
news:98AE9AA6-EF24-4334-AA8A-65E919119B5C@.microsoft.com...
> Hi,
> I need to delete many records in a table based on some conditions in the
> where clause. This delete is taking plenty of time. How do I disable the
> logging of transactions?
> --
> Thanks and Regards,
> Prasanth|||Thanks, I will do with that.
"Uri Dimant" wrote:
> Hi
> You cannot disable logging during deletion as in your case.
> Divide the deletion into small transaction and try to run it. Also check out
> if there are some indexes defined on the table, try to drop them and after
> deletion to recreate them.
> "Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
> news:98AE9AA6-EF24-4334-AA8A-65E919119B5C@.microsoft.com...
> > Hi,
> >
> > I need to delete many records in a table based on some conditions in the
> > where clause. This delete is taking plenty of time. How do I disable the
> > logging of transactions?
> > --
> > Thanks and Regards,
> > Prasanth
>
>