Database

venkat11612-Apr-11 2:40

venkat116

12-Apr-11 2:40

yaah... it is working

I got what I want

thank u very much.

Chris Meech12-Apr-11 3:02

Chris Meech

12-Apr-11 3:02

One of the problems you are going to have is with the ChName column. It appears as though you want CH5 to appear before CH24. In your order by clause you may have to have it

order by .... ,length(ChName),ChName, ...

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

venkat11612-Apr-11 3:47

venkat116

12-Apr-11 3:47

Hi,

Luc Pattyn
solved my problem

thank you for the reply

List of common misconceptions

Jörgen Andersson12-Apr-11 9:31

Jörgen Andersson

12-Apr-11 9:31

That's a good solution for a common problem.
A five from me even if it wasn't what the OP wanted.

Chris Meech12-Apr-11 9:43

Chris Meech

12-Apr-11 9:43

Thanks Jörgen.

Jörgen Andersson wrote:
even if it wasn't what the OP wanted.

Oddly, in the original post he indicated what output he was after and so I'm pretty sure he is going to have to do something similar to what I offered.

List of common misconceptions

Jörgen Andersson12-Apr-11 9:46

Jörgen Andersson

12-Apr-11 9:46

I thought so too, and was contemplating looking up a stringsplit function that does the same job in a much more tortuous way.

David Skelly12-Apr-11 22:23

David Skelly

12-Apr-11 22:23

I have a feeling it's only going to work if all the name values start with the same prefix.

Supposing I have:

A1
A20
A999
C1
C99

Sorting them by length(name), name would give:

A1
C1
A20
C99
A999

So it would solve the OP's specific problem where all names appear to start with CH but I'm not sure it works as a general solution.

List of common misconceptions

Jörgen Andersson12-Apr-11 23:06

Jörgen Andersson

12-Apr-11 23:06

I believe you're quite correct.

We have exactly this issue and have solved it with a separate sortcolumn.
But this isn't always a viable solution, and then one has to build a natural sort function[^].

Dealing with apostrophe in stored procedure

Prasanta_Prince13-Apr-11 1:31

Prasanta_Prince

13-Apr-11 1:31

just the following,-
order by LruName, ChName, Type DESC

berba11-Apr-11 22:15

11-Apr-11 22:15

When a variable in my stored procedure contains an apostrophe, it returns me an error.

I've tried the function REPLACE() but no luck.

waiting for your suggesions.

Thanks

Eddy Vluggen12-Apr-11 0:02

Eddy Vluggen

12-Apr-11 0:02

berba wrote:
I've tried the function REPLACE() but no luck.

waiting for your suggesions.

I suggest you post the error-message that you got when you tried that function, or the code that you tried.

I are Troll Suspicious | :suss:

Chris Meech12-Apr-11 2:52

Chris Meech

12-Apr-11 2:52

Without seeing the code that uses the variable and how the variable is applied to the code, it's difficult to know what is happening. And also knowing what the error return is will help out as well.

Just a guess, but usually embedded apostrophes within strings will need to be escaped somehow. Smile | :)

thatraja12-Apr-11 7:23

thatraja

12-Apr-11 7:23

You must post your query in your question.

Replace single quote in sql server[^] BTW enjoy those brief explanations there

thatraja

My Tip/Tricks
My Dad had a Heart Attack on this day so don't...

berba12-Apr-11 21:35

12-Apr-11 21:35

CREATE PROCEDURE sp_GetEZQueryList @@sUser as nchar(500), @@sSupervisor as char(1), @@sAllowed as varchar(8000) AS

--set @@sUser = REPLACE(@@sUser, '''''','''''')
IF @@sSupervisor <> 'Y'
SELECT
EzqDateMod, EzqFlxID, EzqUserName, EzqName, EzqData, EzqDescrip
FROM EZQuery
WHERE (EzqUserName=@@sUser)
OR (EzqUserName='SYSTEM' AND Cast(EzqFlxID as varchar(10)) IN (select * FROM intlist_to_tbl(@@sAllowed)))
ELSE
SELECT
EzqDateMod, EzqFlxID, EzqUserName, EzqName, EzqData, EzqDescrip
FROM EZQuery
GO

The SP is retrieving @@sUser from VB and its getting the value Do'Sullivan.
When I ran a trace I found it is executing the SP like this

exec sp_getezquerylist 'DO' + CHAR(39) + 'SULLIVAN', 'n', '100'

Think + CHAR(39) + is causing the error

Thanks

berba12-Apr-11 21:34

12-Apr-11 21:34

berba14-Apr-11 20:38

14-Apr-11 20:38

Any solutions ??

Cheers

refresh values after each row update [modified]

S Douglas28-Apr-11 11:47

S Douglas

28-Apr-11 11:47

Little Debugging..

CREATE PROCEDURE sp_GetEZQueryList
(
       @sUser AS nchar(500) ,
       @sSupervisor AS char(1) ,
       @sAllowed AS varchar(8000)
)
AS 

IF @sSupervisor <> 'Y'
   SELECT
       EzqDateMod ,
       EzqFlxID ,
       EzqUserName ,
       EzqName ,
       EzqData ,
       EzqDescrip
   FROM
       EZQuery
   WHERE
       ( EzqUserName = @sUser ) 
		OR 
	   ( EzqUserName = 'SYSTEM' /*AND CAST(EzqFlxID AS varchar(10)) IN ( SELECT * FROM intlist_to_tbl WHERE(@sAllowed) ) */)

ELSE
   SELECT
       EzqDateMod ,
       EzqFlxID ,
       EzqUserName ,
       EzqName ,
       EzqData ,
       EzqDescrip
   FROM
       EZQuery
GO

exec sp_getezquerylist 'DO''SULLIVAN', 'N', '100'

Executes fine, without the IN clase to look up

The source of your issues appear to be in this function (I assume its a function based on the nomenclature) intlist_to_tbl. If intlist_to_tbl is really a function why are you sending that data to the db, and converting it instead of storing it in the db?

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.

SSRS Problem [modified]

Kevin Marois10-Apr-11 10:32

Kevin Marois

10-Apr-11 10:32

I'm fairly new tyo SSRS, so bear with me...

I created a report that is getting its data from a stored procedure. When I run the stored procesure in the data tab it is returning 68 rows, yet only 1 rows shows up on the report in preview, and, it's the last row in the dataset.

If I export the data as a CSV to the desktop, there is only one row in the data.

I have no clue how to debug this. Could use some help.

Thanks

Everything makes sense in someone's mind

modified on Sunday, April 10, 2011 4:49 PM

Re: SSRS Problem

S Douglas13-Apr-11 11:50

S Douglas

13-Apr-11 11:50

I have run into this a couple of times, look for a {file name}.rdl.data file and delete it it. That contains a cached set of data for the report.

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.

mersad0010-Apr-11 3:52

mersad00

10-Apr-11 3:52

hi everybody,
I have a big problem with update statement, let me mention the situation by exact script and results!
first create trans table=>

create table trans(
id bigint identity(1,1) not null,
price decimal(18,0) null,
relatedid bigint null)

Then fill it by this script

Insert into trans (price,relatedid)values(100,NULL) 
Insert into trans (price,relatedid)values(100,NULL) 
Insert into trans (price,relatedid)values(500,NULL)
Insert into trans (price,relatedid)values(1,NULL)
Insert into trans (price,relatedid)values(1,3)

Now run this query!

Update trans
set price=(case when relatedid is null then (select avg(price) from trans where id=<t.id) 
else (select top(1) price from trans where id=t.relatedid) end)
from trans t

After execution of query I expect this result:
id-----price-----relatedid
1 100 NULL
2 300 NULL
3 200 NULL
4 200 3 <======== realy expected!!!!

But this is the result!!!
id-----price-----relatedid
1 100 NULL
2 300 NULL
3 200 NULL <======= #3 row
4 1 3 <===== wrong value, i mean this is the value before update

row with id 3 has been updated before but update doesnt know this!!!!!

WHAT I NEED i need to get expected result from this execution!!!! plzzzzzzzzzzz

modified on Tuesday, April 12, 2011 10:34 AM

Mycroft Holmes10-Apr-11 4:07

Mycroft Holmes

10-Apr-11 4:07

It sounds like you have processing half in your database and half in you application. Change the design to move all the processing into one platform or make sure the database returns the results from the update (possibly with a simple select from updated record changing the return type to a datatable)

Never underestimate the power of human stupidity
RAH

Wendelius10-Apr-11 5:05

Wendelius

10-Apr-11 5:05

So are you saying that the function doesn't see the refreshed situation. If that's the case, perhaps you're running the update in a different transaction or you are feeding 'old' values as parameters to the function.

The function should be able to fetch the current data from the database even if the transaction is still going on as long as you use the same transaction.

The need to optimize rises from a bad design.My articles[^]

mersad0013-Apr-11 1:06

mersad00

13-Apr-11 1:06

plz take look at edited message...