Showing posts with label return. Show all posts
Showing posts with label return. Show all posts

Wednesday, March 28, 2012

Precision Problem

Hi,

I can't seem to get a stored procedure to return decimal places. Here are the steps to re-create the problem:

--first create the following procedure
CREATE PROCEDURE test_precision
AS
BEGIN
RETURN 5.2
END

--then run the following code
DECLARE @.x DECIMAL(18,2)
EXECUTE @.x = test_precision
SELECT @.x

I would like this code to return 5.20, but instead it returns 5.00. Any assistance would be greatly appreciated. Thanks.if you are using SQL 2005 that is the problem. Your stored Procedure would work correctly in SQL 2000.

The work around
declare @.x FLOAT(18,2)

give that a try.|||ooops!! allow me to take that back - wrong thought process. sorry.

you need to edit your proc to read 5.20sql

Friday, March 23, 2012

Powerbuilder : how to get height value

how can i get height value on autosize column but not RowHeight() 'couse RowHeight() return a row height value not a column height value.
i already try use dw_report.object.MyColumn.height , but its retrun a height value before it resize...
pleasee help
sory my english not goodWrong Thread
Please Delete And Post In Correct Thread

Wednesday, March 21, 2012

Postcode Formula Error

Hi,

I want to return everything up to and including, the first letter of the second half of UK postcodes. They take the form "BN2 6DE" or "DN28 7YT". As you can see the first section can be either three or four characters. What I am looking for is a formula that locates the space in the string and then returns the adjacent character and everything preceding and including the space.

The Postcodes are stored in {T_OrderHeader.ShipPostcode}.

I had tried removing the space and returning a set number of digits but this still leaves me with extra data as the first section can be either 3 or four characters.

Please can anyone help? You will be saving me a lot of time manually adjusting entries in Excel!Try this:

Left({T_OrderHeader.ShipPostcode},InStr({T_OrderHeader.ShipPostcode}," ")+1)|||This worked a treat ! thanks very much!

Friday, March 9, 2012

possible to link 2 stored procedures to produce only 1 recordset?

Hello everybody!

trying to do the following:

-create a report in access project

-got 3 stored procedures which return data that shall be shown on report

-need one recordset as datasource (or can i use more than one here?)

Problem:

Data was unrelated before, now needs to be on same report, that's why until now i have 3 different pretty complex stored procedures returning a recordset each.

I could of course copy and paste the whole 3 into 1 new stored proc, but when one changes i had to change the newly created one too (which might get messy when doing a lot of maintenance and changes on the others)

Can create a stored procedure that simply integrates those 3 into one recordset something like this (in pseudo-code):

CREATE PROCEDURE IntegrateSPs AS

INTEGRATE
SP1,SP2,SP3
INTO myRecordset

Anything like this possible?

thx in advance,

KumaHow about this:

create a new "wrapper" procedure. that does the something like:

create #temp (with a buch of fields)

insert into #temp exec proc1
insert into #temp exec proc2

select *
from #temp|||How about this:

create a new "wrapper" procedure. that does the something like:

create #temp (with a buch of fields)

insert into #temp exec proc1
insert into #temp exec proc2

select *
from #temp

Well that would work nicely if the results of each sproc had the same number of columns and the same datatypes, and that each sproc only returns 1 result set...

Otherwise it won't...

Can you show me the result sets of each?|||Example:
SPs look somewhat like this (but huger with more calculation involved)

CREATE PROCEDURE SP1
@.PersonIDStart nvarchar (10),
@.PersonIDEnd nvarchar (10)

AS

SELECT
a + b + c AS fld1,
d + e + f AS fld2

FROM tbl
WHERE PersonID BETWEEN @.PersonIDStart AND @.PersonIDEnd

SP1 would return:
PersonID, Fld1, Fld2, Fld3

SP2:
PersonID, Fld4, Fld5, Fld6

SP3:
PersonID, Fld7, Fld8, Fld9

Of course there are more fields in each recordset. All FldX-fields are smallints.
PersonID is text.

SP1 to SP3 would get the same parameters passed for PersonID and retrieve a number of recordsets accordingly.

Reports will be created for each PersonID with all values from SP1 to SP3 for this PersonID on one sheet.

like this:

Results for PersonID: XYZ123

SP1 SP2 SP3
DimA 4 8 1
DimB 6 8 3
DimC 9 2 7

If i were to put data in a temp table or any go-between permanent table, it had to be one record per PersonID with all the data from the three SPs in it.

SPs are set up to retrieve recordsets only (SELECT). Would i be able to make them dump those into a table without altering them completely (i need the recordset approach elsewhere) and without having to duplicate them into INSERT SPs?

thx

Kuma|||You could use something like:CREATE PROCEDURE p_Wrapper
@.piPersonStart INT
, @.piPersonEnd INT
AS

CREATE TABLE #t1 (personid INT, f1 INT, f2 INT, f3 INT)
INSERT INTO #t1 EXECUTE sp1 @.piPersonStart, @.piPersonEnd

CREATE TABLE #t2 (personid INT, f4 INT, f5 INT, f6 INT)
INSERT INTO #t2 EXECUTE sp2 @.piPersonStart, @.piPersonEnd

CREATE TABLE #t3 (personid INT, f7 INT, f8 INT, f9 INT)
INSERT INTO #t3 EXECUTE sp3 @.piPersonStart, @.piPersonEnd

SELECT *
FROM #t1
FULL JOIN #t2 ON (#t2.personid = #t1.personid)
FULL JOIN #t3 ON (#t3.personid = #t1.personid)

RETURN-PatP|||thx a lot.

hoped for something even simpler, but i can live with that.

Think I'll forego the CREATE TABLE thingy and make permanent tables that I empty after I'm finished. Dont like this temp table stuff.

glad i got the syntax for the INSERT statement!

ty again

Kuma|||Just hope the sproc is executed at the same time then...

I would suggest a table variable if you don't like temp tables...

My question to you then, is how BIG is the result set?

Because if it's not then temp is not a problem...

still I'd use a table variable...|||Just beware if you use permanent tables that you can only allow one user at a time to run the p_Wrapper procedure. Temp tables or table variables dodge that bullet.

-PatP|||thx for the info in the last two posts.

table variable sounds very good. i'll try that.

didnt think about the the issue of "one-user-at-a-time",
since this is a 1-user desktop DB (i'd make it multi-user
intranet, but user dont like it...). I'll change it anyway.
Who knows if and when it goes multi-user...

:)

Kuma

*edited for typo|||Good plan to allow for the possibility of multiple users! I can't count the number of databases I've set up that would "never" be used by more than one person, at least one of which is used by 5000+ people every day!

-PatP|||If structure of permanent tables is thought through then it is definitely an advantage over temp tables (BTW, table variables will not work...should I say why? ;) ).|||Temp tables work nicely though, and have every benefit needed for this problem.

-PatP|||rdjabarov: yes pls u should say why :)

ty|||rdjabarov: yes pls u should say why :)

tyThe source for your INSERT is EXECUTE|||To expand on rdjabarov's answer a bit, you can use INSERT INTO #temp when the source is an EXECUTE, but you can't use INSERT INTO @.temp with an EXECUTE. The temp table works, but the table variable does not because the syntax isn't accepted.

-PatP

Monday, February 20, 2012

Possible bug in RS -Using the Now function in expression in a Snap

We have created a report that creates a historical snapshot. In the header,
we use the Now vb function to return the current date/time. However, the Now
function does not evaulate at the report execution time. Rather, it seems to
evaluate upon the first opening of the report for viewing. Thus, the snapshot
might have been taken yesterday, but if I open the report in a browser (for
the first time) today, the Page Header now says today (and will always say
that).
Additionally, when you export the report to Excel, the Now evaluates
again... so you get the current date/time in the page header of the Excel
cell.
I will try using the Execution time to get around this, but thought I'd pass
this along.Currently, page headers and footers are calculated at rendering time. This
is so that we can recalculate the page numbering when the report is
repaginated. In SQL 2005, we will change this so that the HTML and GDI
renderers will do this at processing / snapshot time while page oriented
formats will do this at rendering time. In a future version, we will change
these to be static as well.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"dbconsulting" <dbconsulting@.discussions.microsoft.com> wrote in message
news:AF88D084-DE6E-4E01-B2AE-2BFB26C1B821@.microsoft.com...
> We have created a report that creates a historical snapshot. In the
> header,
> we use the Now vb function to return the current date/time. However, the
> Now
> function does not evaulate at the report execution time. Rather, it seems
> to
> evaluate upon the first opening of the report for viewing. Thus, the
> snapshot
> might have been taken yesterday, but if I open the report in a browser
> (for
> the first time) today, the Page Header now says today (and will always say
> that).
> Additionally, when you export the report to Excel, the Now evaluates
> again... so you get the current date/time in the page header of the Excel
> cell.
> I will try using the Execution time to get around this, but thought I'd
> pass
> this along.
>|||Well said by Brian. In case you really wanna show snapshot date/time in page
header do the following-
1. Make textbox named as TextBox1 in report and make it invisble. now put
=now as expression in it.
2. In page header make new text box and use expression =ReportItems!TextBox1.Value
Above code works cause reportItems collection is evaluated and rendering
time but it picks the value from TextBox1 which was processed at the time of
snapshot creation.
Problem solved! :o)
-Suneet Mohan
"dbconsulting" wrote:
> We have created a report that creates a historical snapshot. In the header,
> we use the Now vb function to return the current date/time. However, the Now
> function does not evaulate at the report execution time. Rather, it seems to
> evaluate upon the first opening of the report for viewing. Thus, the snapshot
> might have been taken yesterday, but if I open the report in a browser (for
> the first time) today, the Page Header now says today (and will always say
> that).
> Additionally, when you export the report to Excel, the Now evaluates
> again... so you get the current date/time in the page header of the Excel
> cell.
> I will try using the Execution time to get around this, but thought I'd pass
> this along.
>|||Why not use Globals.ExecutionTime ?
On Fri, 1 Apr 2005 13:17:02 -0800, dbconsulting
<dbconsulting@.discussions.microsoft.com> wrote:
>We have created a report that creates a historical snapshot. In the header,
>we use the Now vb function to return the current date/time. However, the Now
>function does not evaulate at the report execution time. Rather, it seems to
>evaluate upon the first opening of the report for viewing. Thus, the snapshot
>might have been taken yesterday, but if I open the report in a browser (for
>the first time) today, the Page Header now says today (and will always say
>that).
>Additionally, when you export the report to Excel, the Now evaluates
>again... so you get the current date/time in the page header of the Excel
>cell.
>I will try using the Execution time to get around this, but thought I'd pass
>this along.|||Yes, using =Globals.ExecutionTime instead of =Now would be the right
solution.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matthew Brown" <octavius@.gmail.com> wrote in message
news:k8e251tn28vut7uk22e16h1koe1h4h94bk@.4ax.com...
> Why not use Globals.ExecutionTime ?
>
> On Fri, 1 Apr 2005 13:17:02 -0800, dbconsulting
> <dbconsulting@.discussions.microsoft.com> wrote:
>>We have created a report that creates a historical snapshot. In the
>>header,
>>we use the Now vb function to return the current date/time. However, the
>>Now
>>function does not evaulate at the report execution time. Rather, it seems
>>to
>>evaluate upon the first opening of the report for viewing. Thus, the
>>snapshot
>>might have been taken yesterday, but if I open the report in a browser
>>(for
>>the first time) today, the Page Header now says today (and will always say
>>that).
>>Additionally, when you export the report to Excel, the Now evaluates
>>again... so you get the current date/time in the page header of the Excel
>>cell.
>>I will try using the Execution time to get around this, but thought I'd
>>pass
>>this along.
>