Establishing Precedence In ORDERBY Condition Causing Problems.

Jun 11, 2007

Hi.

I really need some advice on fine-tuning a stored procedure
that is the meat of the search logic on my site. Customers
are allowed to save searches, which dumps the search logic
in a table called SavedSearches for later access to the search.

My problem started with the ORDERBY condition used for zipcode
searches. The condition did something like:
"order by CASE WHEN userID=67 THEN 1 WHEN userID=103 THEN 2 WHEN
userID=102 THEN 3 WHEN userID=81 THEN 4"
Of course, this fails when a customer described in the saved search
results deletes his profile.

I have since attempted to brace against this problem by adding a
UserPrecendence table with the following columns: email_address,
up_order (or, user precedence order), and userID.

Since I have made the precedence changes, I have been unsuccessful in
getting any results (data) back from the query. I think it has to do
with the change but am not quite sure what I am doing wrong.

I would appreciate it is somebody could take a look at my sproc with
particular attention to how precedence is handled in the ORDERBY
condition. Maybe you can see something I can not?

As always, much appreciated.

PS - In addition to the UserPrecedence change, I have attempted to add
paging - returning N amount of pages per request based on passed-in
paramaters. I'd appreciate it if you could take a quick glance here
also just to make sure my logic is OK.

----------------------------------------------------------------------------------------

ALTER PROCEDURE [dbo].[sp_PeopleSearch]
@pagenum INT = 1,
@perpage INT = 10
AS
BEGIN
SET NOCOUNT ON

DECLARE
@ubound INT,
@lbound INT,
@pages INT,
@rows INT

SELECT
@rows = COUNT(*),
@pages = COUNT(*) / @perpage
FROM
(select distinct emailAddress
from Customers with(nolock) union select distinct user_name
from CustomerPhotos with(nolock) union select distinct email_address
from EditProfile with(nolock) union select distinct email_address
from SavedSearches with(nolock) union select distinct email_address
from UserPrecedence with(nolock) union select distinct email_address
from RecentLogin with(nolock)) drv
Left Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)
Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
Left Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)
Left Join RecentLogin tab4 on (drv.emailAddress =
tab4.email_address)
Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
Left Join SavedSearches tab6 on (drv.emailAddress =
tab6.email_address)

IF @rows % @perpage != 0 SET @pages = @pages + 1
IF @pagenum @pages SET @pagenum = @pages
IF @pagenum < 1 SET @pagenum = 1


SET @ubound = @perpage * @pagenum
SET @lbound = @ubound - (@perpage - 1)

SELECT

CurrentPage = @pagenum,
PageSize = @perpage,
TotalPages = @pages,
TotalRows = @rows,
UpperBoundary = @ubound,
LowerBoundary = @lbound

-- this method determines the string values
-- for the first desired row, then sets the
-- rowcount to get it, plus the next n rows

DECLARE

@gender VARCHAR(50),
@country VARCHAR(50),
@orderby INTEGER,
@low VARCHAR(50),
@high VARCHAR(50),
@photo VARCHAR(50),
@sort INTEGER

SET ROWCOUNT @lbound

SELECT

@gender = saved_sex,
@country = saved_country,
@orderby = saved_orderby,
@low = saved_fage,
@high = saved_tage,
@sort = saved_sort,
@photo = saved_photo_string

FROM

(select distinct emailAddress
from Customers with(nolock) union select distinct user_name
from CustomerPhotos with(nolock) union select distinct email_address
from EditProfile with(nolock) union select distinct email_address
from SavedSearches with(nolock) union select distinct email_address
from UserPrecedence with(nolock) union select distinct email_address
from RecentLogin with(nolock)) drv
Left Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)
Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
Left Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)
Left Join RecentLogin tab4 on (drv.emailAddress =
tab4.email_address)
Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
Left Join SavedSearches tab6 on (drv.emailAddress =
tab6.email_address)

ORDER BY CASE @sort

WHEN 1 THEN tab1.registerDate
WHEN 2 THEN tab3.edit_date
WHEN 3 THEN tab4.login_date
WHEN 4 THEN tab5.up_order

END DESC

SET ROWCOUNT @perPage

SELECT COALESCE
(
tab1.emailAddress,
tab2.user_name,
tab3.email_address,
tab4.email_address,
tab5.email_address,
tab6.email_address
)
id ,
tab1.bday_day ,
tab1.bday_month ,
tab1.bday_year ,
tab1.gender ,
tab1.zipCode ,
tab1.siteId ,
tab1.userID ,
tab2.photo_location ,
tab2.photo_name ,
tab2.photo_default ,
tab2.no_photo ,
tab3.headline ,
tab3.about_me ,
tab4.login_date ,
tab4.login_isonline,
tab5.up_order,
tab6.saved_orderby,
tab6.saved_sort,
tab6.saved_fage,
tab6.saved_tage

FROM

(select distinct emailAddress
from Customers with(nolock) union select distinct user_name
from CustomerPhotos with(nolock) union select distinct email_address
from EditProfile with(nolock) union select distinct email_address
from SavedSearches with(nolock) union select distinct email_address
from UserPrecedence with(nolock) union select distinct email_address
from RecentLogin with(nolock)) drv
Left Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)
Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
Left Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)
Left Join RecentLogin tab4 on (drv.emailAddress =
tab4.email_address)
Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
Left Join SavedSearches tab6 on (drv.emailAddress =
tab6.email_address)

WHERE

tab1.gender = @gender
AND tab1.country = @country
AND tab1.bday_year BETWEEN @low AND @high
AND tab2.photo_default = 1 + @photo

--and not tab2.no_photo = 1
--firstName + '~' + lastName
-->= @fname + '~' + @lname

ORDER BY CASE @sort

WHEN 1 THEN tab1.registerDate
WHEN 2 THEN tab3.edit_date
WHEN 3 THEN tab4.login_date
WHEN 4 THEN tab5.up_order

END DESC

SET ROWCOUNT 0

END

View 7 Replies


ADVERTISEMENT

Help Me With SP OrderBy

Jan 18, 2005

I have a SP that accepts parameters, but the problem is that it is not ORDERING Data as required, It is returning ORDER BY NULL instead of order by my parameter "NAME "here is the output from SQL 2000 DB

'ELP B4 I jump out of LondonBridge

**********************************
SELECT * FROM #TempTable WHERE ID > 0 AND ID < 6 AND EmployerID = 54 AND Job_no = 40

ORDER BY NULL
****************************************



SELECT @MYSTATEMENT = ' SELECT * FROM #TempTable

WHERE
ID > '+ convert(varchar(20),@FirstRec) +'
AND
ID < '+convert(varchar(20),@LastRec) +'
AND EmployerID = '+ convert(varchar(20),@EmployerID)+'
AND Job_no = '+convert(varchar(20),@Job_no)+'

ORDER BY ' +
CASE WHEN @WhichColumn= 'name' AND @sortby= 'asc'
THEN 'name'
ELSE 'NULL'
END
+
CASE WHEN @WhichColumn='name' AND @sortby='DESC'
THEN ' desc'
ELSE ''
END

EXEC (@myStatement)

View 5 Replies View Related

OrderBy

Nov 14, 2005

Hi !
I have some table with some "name" field in it which looks like
"Blah blah blah Part 1"
"Blah blah blah Part 2"
"Blah blah blah Part 3"
when I query it
select * from mytable orderby name
it doesn't actually work in a "proper" way.
I keep on getting something like
"Blah blah blah Part 2"
"Blah blah blah Part 1"
"Blah blah blah Part 3"
Do you know what should I do to make it work?

View 5 Replies View Related

ORDERBY Name

Nov 14, 2005

Hi !
I have some table with some "name" field in it which looks like
"Blah blah blah Part 1"
"Blah blah blah Part 2"
"Blah blah blah Part 3"
when I query it
select * from mytable orderby name
it doesn't actually work in a "proper" way.
I keep on getting something like
"Blah blah blah Part 2"
"Blah blah blah Part 1"
"Blah blah blah Part 3"
Do you know what should I do to make it work?

View 3 Replies View Related

How Do You Do ASC And DESC With Dynamic OrderBy

Mar 21, 2006

In the followinf portion of my SQL Statement, I cannot figure out how to specify ASC or DESC for the OrderBy... If I Put OrderDate ASC after the THEN, I get an error and if I put @SortDir after the END I get an error... Can Anyone help with this?
WHERE OrderID IN(SELECT OrderID FROM #rsltTable WHERE ID >= @l_FirstRecord AND ID <= @l_LastRecord)ORDER BYCASE @OrderByWHEN 'OrderDate ASC' THEN OrderDateWHEN 'OrderDate DESC' THEN OrderDateEND
Thank You,Jason

View 9 Replies View Related

Merged Columns In OrderBy

Jul 20, 2005

Lets say I have a table PortDates with 3 columns like this:PORT ARRIVALDATE SAILINGDATENewYork 12-15-2004Rio 10-12-2004Hamburg 10-14-2004etc.I want to sort the ports on dates from both date columnsSELECT Port FROM PortDatesORDER BY (ArrivalDate JOIN SailingDate)Now, that doesn't work of course, but what should I write to get it to workso I can order by the dates as if they were one merged column?Join Bytes!

View 7 Replies View Related

Can We Include Orderby In View?.

May 5, 2008



when i tried to put an orderby in view...i am getting an error...

we can't have a orderby in view?.

View 7 Replies View Related

Sort Table Descending Without Orderby

Jun 24, 2008

can any one help me to sort a table on the basis of one colomn without using ORDER BY Desc.

Rahul Arora
07 Batch
NCCE Israna,


######################
IMPOSSIBLE = I+M+POSSIBLE

View 6 Replies View Related

Simple Select Statement OrderBy

Feb 4, 2008

Well my problem lies in that I am generating reports with the data I retrieve from my sql database. However my problem resides in the fact that I am generating one report at a time and if I want to grab each entry in the order in which they were produced its no problem.

IE - Using PowerBuilder 10.0 as an IDE for my application to generate reports.

select i_id into :insp_id from inspection where i_id = :index order by i_id asc using sqlca;

But now if I want to grab them in alphabetical order from another table I have problems.

this is the code I am trying to use maybe I am just thinking it through wrong.
select s_id into :insp_id from section where s_id = :index order by s_name asc using sqlca;

any help is appreciated. Is there a way to grab each row in alphabetical order?

View 3 Replies View Related

Passing Orderby Clause As Parameter In SP

Jan 24, 2008

Hi,

I have a requirement where i have to pass OrderBy clause to an SP as an parameter. I dont want to use dynamic sql query so i am running into problem.

Below is my procedure..

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
Create Proc [dbo].[USP_GetData]
@sortBy varchar(100),

as

BEGIN

SELECT *
FROM
(SELECT CC.C1, CC.C2, CC.C3, CC.C4,
ROW_NUMBER() OVER(ORDER BY @sortBy) as RowNum
FROM T1 CC
) as e
END

The thing is, if i execute the procedure as
EXEC USP_Get_Data ('C1 asc'), it runs without any error but it gives me unsorted result.

I am not able to figure out why.

Any help in this will be appriciated.

Regards,
Salim.

View 16 Replies View Related

Query With Row Number,Union And Dynamic Orderby.

Dec 19, 2007

Hi,
Can some one help me to complete this query.
My requirement is to convert the existing stored proc(given below) to one that allows dynamic paging as well as sorting(sql server 2005). I gave a try but invein. I am running out of time.

Existing procedure:


ALTER PROCEDURE [dbo].[spGetAllItems]
@condition varchar(7500),
@ModelId int =0,
@CategoryId int=0,
@SourcingType int=0,
@StartRow as int = 1,
@StopRow as int = 100000
AS
declare @erow int
declare @Cond varchar(255)

select @Cond=''

if @CategoryId<>0
select @Cond= ' and tbItemMaster.Category=' + cast(@CategoryId as varchar)
if @SourcingType<>0
select @Cond= ' and tbItemMaster.SourcingType=' + cast(@SourcingType as varchar)

if @StopRow=-1
select @StopRow=60
set @erow=@StartRow + @StopRow


exec('Declare @t_table table
(
[SpecialId] [int] IDENTITY (1, 1) Primary key NOT NULL,
[id] [int],
[category] [int] NULL ,
[model] [int] NULL ,
[mtype] [varchar] (50) NULL,
[itemclass] [varchar] (50) NULL,
[itemcode] [nvarchar] (25) NULL ,
[ItemCondition] [tinyint] NULL ,
[SourcingType] int,
[Title] [nvarchar] (50) NULL ,
[Description] [nvarchar] (4000) NULL ,
[InternalUse] [nvarchar] (4000) NULL,
[CompatibleModels] [varchar] (8000) NULL ,
[Quantity] [int] NULL ,
[MinOrder] [int] NULL ,
[Warranty] [tinyint] NULL ,
[Price1] [decimal](6, 2) NULL ,
[Price2] [decimal](6, 2) NULL ,
[Price3] [decimal](6, 2) NULL ,
[Price4] [decimal](6, 2) NULL ,
[LotSale] [bit],
[PricingRule] int ,
[Weight] [decimal](7, 2) NULL ,
[PackageSize] [int] NULL ,
[promo] [int] NULL ,
[SmallImage] [nvarchar] (255) NULL ,
[BigImage] [nvarchar] (255) NULL ,
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Info1] [nvarchar] (50) NULL ,
[Info2] [nvarchar] (50) NULL ,
[Info3] [nvarchar] (50) NULL ,
[Info4] [nvarchar] (50) NULL ,
[Info5] [nvarchar] (50) NULL ,
[Info6] [nvarchar] (50) NULL ,
[Info7] [nvarchar] (50) NULL ,
[Info8] [nvarchar] (50) NULL ,
[Info9] [nvarchar] (50) NULL ,
[Info10] [nvarchar] (50) NULL ,
[Info11] [nvarchar] (50) NULL ,
[Info12] [nvarchar] (50) NULL ,
[Info13] [nvarchar] (50) NULL ,
[Info14] [nvarchar] (50) NULL ,
[Info15] [nvarchar] (50) NULL ,
[Info16] [nvarchar] (50) NULL ,
[Info17] [nvarchar] (50) NULL ,
[Info18] [nvarchar] (50) NULL ,
[Info19] [nvarchar] (50) NULL ,
[Info20] [nvarchar] (50) NULL ,
[Info21] [nvarchar] (50) NULL ,
[Info22] [nvarchar] (50) NULL ,
[Info23] [nvarchar] (50) NULL ,
[Info24] [nvarchar] (50) NULL ,
[Info25] [nvarchar] (50) NULL ,
[Status] [tinyint] NULL ,
[AllowBuy] [char] (1) NULL ,
[PageName] [varchar] (200) NULL ,
[Locality] [int] NULL ,
[Location] [int] NULL ,
[CreatedBy] [int] NULL ,
[CreatedOn] [datetime] NULL ,
[UpdatedBy] [int] NULL ,
[UpdatedOn] [datetime] NULL,
[BrandId] int,
[CategoryId] int,
[ModelId] int,
[ModelName] varchar(255),
[BrandName] varchar(255),
[CategoryName] varchar(255)
);
DECLARE @EndRow int
DECLARE @reccount int


select @reccount=count(*) from (SELECT tbItemMaster.id
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id' + @Condition + '
Union
SELECT tbItemMaster.id
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + ') tbl


set @EndRow= + ' + @StartRow + '+' + @StopRow + '
--Set RowCount @EndRow;
insert @t_table
(
[id] ,
[category] ,
[model] ,
[mtype],
[itemclass],
[itemcode] ,
[ItemCondition] ,
[SourcingType],
[Title] ,
[Description] ,
[InternalUse],
[CompatibleModels],
[Quantity] ,
[MinOrder] ,
[Warranty] ,
[Price1] ,
[Price2] ,
[Price3] ,
[Price4] ,
[LotSale] ,
[PricingRule],
[Weight] ,
[PackageSize] ,
[promo] ,
[SmallImage] ,
[BigImage] ,
[StartDate] ,
[EndDate] ,
[Info1] ,
[Info2] ,
[Info3] ,
[Info4] ,
[Info5] ,
[Info6] ,
[Info7] ,
[Info8] ,
[Info9] ,
[Info10] ,
[Info11] ,
[Info12] ,
[Info13] ,
[Info14] ,
[Info15] ,
[Info16] ,
[Info17] ,
[Info18] ,
[Info19] ,
[Info20] ,
[Info21] ,
[Info22] ,
[Info23] ,
[Info24] ,
[Info25] ,
[Status] ,
[AllowBuy] ,
[PageName] ,
[Locality] ,
[Location] ,
[CreatedBy] ,
[CreatedOn] ,
[UpdatedBy] ,
[UpdatedOn] ,
[BrandId] ,
[CategoryId] ,
[ModelId],
[ModelName],
[BrandName],
[CategoryName]
)
select * from (SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels,
tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize,
tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4,
tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12,
tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18,
tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24,
tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName,
tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn,

tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id' + @Condition + ' Order By tbItemMaster.UpdatedOn desc) tbl
Union
SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels,
tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize,
tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4,
tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12,
tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18,
tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24,
tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName,
tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn,

tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + @Cond + ' Order By UpdatedOn desc;

SELECT *,@reccount as ItemsCount FROM @t_table WHERE SpecialId >=' + @StartRow + ' and SpecialId<@EndRow
ORDER BY UpdatedOn DESC
')


RETURN


can anybody help me pls. ITs very urgent.

Thanks in advance.

View 1 Replies View Related

Can I Print The Results Of A Condition Based On The Condition?

Feb 9, 2006

For example..

select * from mytable where MyNum = 7

If this brings back more than 1 row, I want to display a message that says,

Print 'There is more than one row returned'

Else (If only 1 row returned), I don't want to print anything.

Can I do this? Thx!

View 1 Replies View Related

COndition Spli - Error Date Condition

Apr 19, 2007

Dear friends,

I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...



I need to filter rows in a dataflow...

I created a condition spli to that... maybe there is a better solution...

And the condition is: Datex != NULL(DT_DATE)

(Some DATE != NULL)





[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.



What is wrong??

Regards,

Pedro

View 4 Replies View Related

Reporting Services :: SSRS IIF One Condition Or IIF Another Condition

Jun 22, 2015

I  am trying to write an visibility function to have message shown based on two different IIF  conditions:

If behavior is to Add a customer ( if message =NAME ALREADY EXISTS, return " NAME ALREADY EXISTS",    otherwize return " NAME CREATED")If behavior is to  Delete a customer (( if message =NAME DOES NOT EXIST, return "NAME DOES NOT EXIST",    otherwize return "NAME SUCCESSFULLY DELETED")
I tried the following which doesn't work:
=IIF((UCase(First(Fields!Message.Value, "DataSetName")) = "NAME ALREADY EXISTS"), "WARNING: NAME ALREADY EXIST", "NAME  CREATED"),
IIF((UCase(First(Fields!Message.Value,  "DataSetName")) = " NAME DOES NOT EXIST"), "WARNING: NAME DOES NOT EXIST", " NAME DELETED")

View 6 Replies View Related

Transact SQL :: How To Add Condition In Where Clause According To Another Condition

Oct 17, 2015

I write a query to get some data as the following. but i need when a user check specified condition a query parameter change to specified condition :

create proc proc_ReservationDetails
(
@status nvarchar(50) = null
)
as
begin
select reservationId, reservationStatus, reservationDesc

[Code] .....

View 3 Replies View Related

Re-establishing Mirror

May 8, 2006

When witness and mirror were down, mirroring was removed at primary. Now when I try to re-establish mirroring I get the following error

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.


On the witness server the following sql

select * from sys.database_mirroring_witnesses

returns 1 row with the previous mirroring information.



How do I remove old information from witness server? Thanks.

View 16 Replies View Related

GROUP BY Precedence

Mar 13, 2008

How is the column precedence determined when more then one column is included in the group by clause? I am using T-SQL.

for example:

SELECT VendorName, VendorState, SUM(Invoices.InvoiceTotal) AS InvoiceSum
FROM Vendors JOIN Invoices ON
Vendors.VendorID = Invoices.InvoiceID
GROUP BY VendorState, VendorName

I would expect this statement to group by VendorState first but my result set is grouping by VendorName and then VendorState.

Am I missing something?

View 4 Replies View Related

Precedence Constrains

Jan 31, 2007

Hi there!

I've a few tasks in my dataflow. Some of them are executed depending on an precedence constrain. Works fine.

The problem is following: I want to write a success message (execute SQL task), when all the tasks who have been executed have done this with success. For example, i ve ten tasks, and 2 of them are executed (successfully). This is good, i want to execute my task to write the success message.

I tried to set these precedence constraints with EvalOP = ExpressionOrConstraint, Values = Success and an expression, what returns TRUE, when the task above is not executed (the condition from a precedence constraint above). But the task to write my message will never be executed, because it seems the task does'nt give any result (neither success nor failure) when they ar not executed, and so my AND-Constraint for the success message task will only get TRUE, when ALL task are executed (and successful).

Is there any chance to get somethink like "isSuccess OR IsNotExecuted" into a precedence constraint?

Thanks, Torsten

View 2 Replies View Related

Precedence Constraint Being Ignored?

May 22, 2008

hi all - this is the first time i've worked with SSIS / Business Intelligence, and I'm having an issue with a piece of basic functinoality.

I'm working with some dtsx jobs which were all conversions from Sql Server 2000 DTS jobs. The conversion process "seemed" to go smoothly. I used the Migration Wizard, and it successfully converted my outputted DTS job files into DTSX files.

In the Business Intelligence design environment (Control Flow) for a specific dtsx package, I have two tasks that are linked together by a Precedence Constraint.

* Task A Truncates all data in Database B --> Table A
* the precedence constraint says that if Task A is successful, then go on to Task B (or at least i think it does)
* Task B copies the newest data from Database A --> Table A over to Database B --> Table A

Everything appears to be correct, but if i right-click Task A and perform an "Execute Task" command, Task A will run successfully, but then stops short without running Task B.

Are there any common "gotchas" or issues that I might be missing which would cause this behavior? I deleted the existing precedence constraint, and added back a new one with the same settings, but Task B still will not run.

thanks for any help on this

View 9 Replies View Related

Precedence Puzzle

Aug 7, 2006

Hello folks,

I have probably a dumb newbie question but I can't find the answer anywhere.

On my Control Flow design pane I have two objects: a SQL task object and a Data flow task object. The first 'points' to the second. From my digging I believed that by indicating with the arrow from 1 to 2 that 1 would execute to finish before 2 was started.

My SQL taks is to truncate a table to receive the new data coming from the data flow task object. Instead 2 executes first and then 1. You can imagine producing an empty table was not my goal for this package.

Can anybody give me a clue?

Thanks....

Jim

View 9 Replies View Related

Precedence Constraints

Nov 7, 2007

Hello,
I've recently begun exploring Integration Services and have a question regarding a Control Flow element.

I'm trying to understand precedence constraints. Specificially, the logical AND and logic OR components. If I have two constaints A and B where A is using a logical AND and B is using a logical OR, how are the constraints evaluated? The Evaluation Operation is set to "Expression and Constraint" for both constraints.

If my understanding is correct, path A would be followed if both A AND B are true. Path B would be followed if A OR B is true. Is this correct?

Regards,
Orlanzo

View 1 Replies View Related

Establishing Logfile Size And % Used Using Sql

Apr 27, 2001

Hi there,

I want to write a sql query (for SQL7 & 2K) that gives the size and space used of the log file for a given database. I can do it for data files using sysfiles/sysindexes, but can see how I can do it for the logfile.

I know I can use DMO or dbcc sqlperf(logspace), but I want to understand how and where the information is kept in system tables.

View 2 Replies View Related

Newbie Need Help:establishing A Relationship

Sep 7, 2004

I have a simle question for you:
I created a employee table:

create table employees (
employeeid int primary key,
lastname nvarchar(20) not null,
firstname nvarchar(20) not null,
birthdate datetime,
city nvarchar(15),
country nvarchar(15)
)

and then I created table orders and established a relationship between them:

create table orders (
orderid int primary key,
employeeid int references employees(employeeid),
orderdate datetime
)

The relationship was created successfully.

But I don't know how to establish a relationship between two tables if they were already exist.
Could you tell me how to do it?
many thanks :p

View 2 Replies View Related

Establishing A Remote Connection..!

Feb 18, 2006

How can I establish a remote connection for using remote database in SQL Server 2K5?

Thanks.

View 4 Replies View Related

Establishing A Connection To The Server (Need Help )

Apr 12, 2008

Hello,

I have a problem connecting to my SQL SERVER 2005 express. I followed carefully the instructions to open a database connection but I still get the same error down below

Error message received:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I have changed the permission of the database by granting all access to myself but it didn't, I have also changed all the Pipes to enable but nothing. Here is the code

Software: Visual Basic.net Express 2008

Dim Strconnection As String
Dim con As SqlConnection
Dim ds As New DataSet

Strconnection = "Data Source=localhost;Initial Catalog=Regina;Integrated Security=True"

con = New SqlConnection(Strconnection)
con.Open() ==> Error happened here.
MsgBox("Database opened")


con.Close()


I DON'T KNOW WHERE TO LOOK AND FIND AN ANSWER TO CORRECT THAT MISTAKE.

Thank you in advance.


Ailiam A.

View 7 Replies View Related

Help Needed In Establishing Connection

Apr 29, 2006

I recently migrated SQL Server 2005 which we use for our internet site and other internal applications, to it's own server. SQL used to be on the development PC. However now I am having security issues.

I keep getting an error message when I try to connect to the server from the development PC that the SQL server is not set up or does not allow remote connections. I have made sure that ASPNET is a user. I have also run aspnet_regsql to set up the internet users database.

What other security features do I need to check? I am more of a VB developer than a SQL admin so any help is appreciated.

FYI - This is a small network that does not run any form of Windows Server. I am using IIS 5.1. TCP/IP and Named Pipes is enabled.

Thanks

View 3 Replies View Related

Establishing A Remote Connection..! (Again)

Jun 6, 2007

Hi,

I am trying to connect via the Management Studio Express. I share a LAN with the server. In Netowrk connections I can see the server and I can even access the "Shared" folders. However,when I try to connect I get an error that says the error may be caused by the server not allowing remote connections. I have already done the following:

With the Surface Area Configuration facility I have set the server to allow remote connections
I have selected TCP/IP as the preferred mode of connection.
I have selected Windows authentification as mode of authorisation

What else can I do??



Please help.

Derekprinsloo

View 1 Replies View Related

Establishing A Baseline With Perfmon

Dec 13, 2007

I am training to be a DBA in a company running about 30 machines with MS SQL Server (2000 and 2005). Last week I went to a class where the instructor recommended establishing a performance baseline using windows performance monitor. He also advised to run perfmon remotely so as to not effect the performance.

What I am wondering is since I have so many different machines to baseline, can I run perfmon on one box, using a seperate counter log for each server? I would like to get a nice week-long baseline for each machine, but I also don't want to get bad data by running too many logs at the same time.

My plan is to do a small set of counters for processor, memory, disk, and the SQL server instance(about 10 counters total).

If anyone has experience in this area, I would appreciate any advice that you might have.

Thanks,

Matt

View 1 Replies View Related

Operator Precedence Challenge

Apr 29, 2004

I'm having problems with the query below. It works fine until the "NOT IN" part and I'm not sure why. Basically, I'm getting records where their educ_audio field is set to "no". I've messed with the parens to try to force SQL to process the NOT IN part before the other clauses without avail. Can someone shed some light?

TIA

SELECT distinct contacts.fname, contacts.lname, contacts.company, contacts.contact_id, contacts.business_phone, contacts.emailAddress, contacts.dateLastContact FROM journal INNER JOIN contacts ON journal.contact_id = contacts.contact_id INNER JOIN products ON journal.product_code = products.product_code WHERE ( journal.product_code IN ('ABLE') ) OR (( journal.product_code IN ('JOBS') ) AND ( journal.product_status IN ('12','14','15') )) OR (( products.prod_design IN ('audio') ) AND ( products.library_code IN ('hrss') )) AND (journal.journal_id NOT IN (SELECT journal.journal_id FROM journal INNER JOIN contacts ON journal.contact_id = contacts.contact_id INNER JOIN products ON products.product_code = journal.product_code where ( contacts.educ_audio IN ('no') ) )) ORDER BY contacts.lname asc, contacts.fname

View 3 Replies View Related

Precedence Constraint Problem

Dec 20, 2007

I have two packages - one works, the other doesn't. Both have two tasks: an Execute SQL Task and a dependent Send Mail Task. I am using a precedence constraint where the evaluation operation is an expression evaluating a variable created by a select count(*) as variable in the query. Both variables are defined as int32, initial value of 0 with a scope for the entire package.

The expression that works tests for an error_counts > 0 condition; the one that doesn't tests for load_counts < 24. The > 0 sends mail when the error_count is greater than 0 but doesn't send mail when it is 0, which is as I intended. The less than 24 package always sends mail even though the variable resultset_singlerow is usually 24. When I look at both properties for the Execute SQL Task steps side by side, they have no differences. When I look at the properties for the constraint, the only difference is in the EvaluatesTrue, which is set to False for the one that works and True for the one that doesn't. Unfortunately, that property is grayed out (not modifiable) and doesn't seem to be an option when I edit via the Precedence Constraint Editor.

I've looked a Books Online & Google with no references to a similar situation. Does anyone have a solution? Is the EvaluatesTrue the problem? And if it is, how can I change the setting? If not, what is the problem?

Thanks in advance for any help.

View 1 Replies View Related

String Matching Precedence

May 2, 2006

An application filters records based on names found in them. Forexample, looking through property buyer names, looking for banks andrelocation companies.I have a table of names and patterns:CREATE TABLE #Filters (Pattern varchar(100), IfWildCard int, Categoryint)INSERT #Filters SELECT 'Bank Of America', 0, 1INSERT #Filters SELECT '% Bank %', 1, 2INSERT #Filters SELECT 'Bank %', 1, 2INSERT #Filters SELECT '% Bank', 1, 2INSERT #Filters SELECT 'Credant Reloc%', 1, 3INSERT #Filters SELECT '%Relocation%, 1, 3The filtering matches the table of candidate names against the filters,and returns Category, where the where clause or join is(Candidate = Pattern AND IfWildCard = 0)OR (Candidate LIKE Pattern AND IfWildCard = 1)"Bank of America" matches an exact pattern, and also a wildcardpattern, and the two different matches give different values forCategory. Is there a way to control which match takes precedence, oris necessary to do it multiple times in the desired order, removingthose that hae already matched from consideration?Thanks,Jim Geissman

View 5 Replies View Related

Disappearing Precedence Constraints

Jan 25, 2007

Has anyone seen precedence constraints disappear in a package after closing and opening again? In this case, it's not package-wide. Only constraints inside one Foreach Loop container disappeared. Any idea what causes this to happen?

p.s: Yes, I did Save All. Most of these contraints were saved in the package for more than a week anyway.

View 14 Replies View Related

Problem With Precedence Expression

Aug 21, 2007

Hello,
I have a SQL Task the executes the following statement;

SELECT COUNT(SettleDate) AS CountResult
FROM SettleDateCount

I have the SQL Task Editor configured for a Single Row result set. The Result Name is CountResult, and the variable it populates is RowCountRes. RowCountRes is Int32 with EvaluateAsExpression set to False.

I have a boolean variable called RowCountStatus. It evaluates as the following expression;

@[User::RowCountRes] > 1

From the SQL Task, I have two different constraints, both of which evaluate with an expression. One constraint evaluates this way; @[User::RowCountStatus] = True
The other evaluates this way; @[User::RowCountStatus] = False

The idea is that if the count comes back from the sql statement greater than 1 then execution continues down one path, else it goes down the other path. Currently, the result comes back = 1. I expect that the 'False' path will execute, but the 'True' path executes instead.

What am I doing wrong?

Thank you for your help!

cdun2

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved