Please correct syntax

hi there

RS2005 - Layout tab

I have a field that says

txtTrackerTrue=Sum(Iif ((Fields!Tracker.Value = Fields!RecvdBy.Value), 1,0 ))

I want another field that says

txtAllTrue=Sum(Iif ((Fields!Tracker.Value, ClosedBy.Value = Fields!RecvdBy, 1,0 ))

obviously this is going to error

how would I do this please

thanks

jewelfire,

I am not 100% what you are trying to get help with here.

I assume you have a field (txtTrackerTrue) that has the expression (starting with an equal sign)

I think you need a double equals sign like this...

=Sum(Iif (Fields!Tracker.Value == Fields!RecvdBy.Value , 1,0 ))

In your second example you were missing closing paren. and I am not sure what you are trying to do,

Can you explain in words what you are wanting to do in your second example and I will try to help you out!

Thanks

|||

Hi,

you are missing a ")" before the 1,0.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

hey there thanks for jumping in, Reading back I didn't explain myself very well at all - sorry about that

RS2005 - layout tab

Name of txt box = txtTrackerTrue

Value of this txt box = Sum(Iif ((Fields!Tracker.Value = Fields!RecvdBy.Value), 1,0 ))

This is correct - this is just for guidelines.

I need another field

Lets call the txt box = txtAllTrue

Value of this txt box to be

Sum If Tracker/RecvdBy/ClosedBy all the same then true.

I thought if I could reference the 1st txt box as part of the sum is already done and then just add the closedby into the equasion?

But not sure how to reference a txt box.

thanks

Jewel

|||

Hi,

You will have to use the prefix ReportItems!SomeItem to use the value of that field, I am not sure if that works for expressions, but It hink the processor will take care of the informationen and the precendence that the expression has to be evaluated.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

thanks Jens

I tried that but it came up with

ReportItem is a type and cannot be used as an expression.

any other suggestions?

cheers

|||You need to use the Value property on the ReportItem. For example, ReportItems!txtTrackerTrue.Value|||

thanks Ian

I thought I could as well but as mentioned when Jens suggested same I am getting an error (Report Item is a type and cannot be used as an expression)

this is what I need to do

=Sum(Iif ((ReportItem!txtTrackerTrue.Value = Fields!ClosedBy.Value), 1,0 ))

with ReportItem!txtTrackerTrue - already a combination of two fields being equal.

cheers

Jewel

|||Jens suggested that you do the same thing, but Jens did not mention that you need to use the .Value property. Make sure that the .Value property is used on the ReportItem, otherwise you will get the this error.

Ian|||

thanks but still get the error with using the .Value

cheers

J

|||

I noticed that in the expression "ReportItem" is used, try using "ReportItems" instead.

Ian

|||

thanks Ian

yes I tried that as well - I noticed that and thought I would give it a go - error is:

Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.

so am just trying to see if there is anything else I can do.

cheers for your help though

jewel

|||

hey there

thanks all for participating but I still have not solved this problem. Is this something I cannot do?

so I have a text box called txttrackertrue

the expression in this box is

=Sum(Iif ((Fields!Tracker.Value = Fields!RecvdBy.Value), 1,0 ))

I have another text box called txtClosedtrue

the expression needed for this box is

=Sum(Iif ((ReportItems!txtTrackerTrue.Value = Fields!ClosedBy.Value), 1,0 ))

error is

[rsAggregateReportItemInBody] The Value expression for the textbox 'txtClosedTrue' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.

[rsReportItemReference] The Value expression for the textbox ‘txtClosedTrue’ refers to the report item ‘txtTrackerTrue’. Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.

any suggestions please.

thanks

jewel

|||
Where is the textbox 'txtClosedtrue' in relation to the textbox 'txtTrackerTrue'? The textbox 'txtClosedtrue' needs to be in the same scope or a child scope of the scope that contains the textbox 'txtTrackerTrue'. In other words, textboxes can only refer to other text boxes above them in the grouping hierarchy--Only one-to-one or many-to-one relationships can exist.

Also, you cannot refer to textboxes in an Aggregate function in the body of the report. This is only allowed in the page header and footer.

Ian|||

thanks Ian for your input

both text boxes are on a details line of a table - there is no group in this table

txtTrackerTrue comes before txtClosedTrue

I am presuming by Aggregate you mean the (sum) part.

so if this is so is there another way I should do this then?

cheers

Jewel

sql

please check this SP syntax - need more eyes!

when I try to create this SP I get: "incorrect syntax near @.MyResult"
I have tried INT and different variable names, but get same error.

CREATE PROCEDURE sp_IsValidLogon
@.UserName varchar(16),
@.Password varchar(16) ,
@.MyResult varchar(3) OUTPUT
As
if exists(Select * From User_Table
Where UserName = @.UserName
And
Password = @.Password)
begin
@.MyResult = 1
end
else
begin
INSERT INTO FailedLogons(UserName, Password)
values(@.UserName, @.Password)

declare @.totalFails int
Select @.totalFails = Count(*) From FailedLogons
Where UserName = @.UserName
And dtFailed > GetDate()-1

if (@.totalFails > 5)
UPDATE User_Table Set Active = 0
Where UserName = @.UserName

@.MyResult = 0
endThe answer is

SET @.MyResult = ...|||Thanks George,
That's sorted it.
I seem to have two threads in this forum now tho' :o sorry guys.|||No worries - I will remove the dupe :)

Please Check my store Procedure !

I have a store Procedure modify structTable but check syntax is error !

Please help me ?
--
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME ='MODISTRCTTABLE ' AND TYPE='P')
BEGIN
DROP PROCEDURE MODISTRUCT_TABLE
END
GO

CREATE PROCEDURE MODISTRUCT_TABLE
@.TABLE_NAME VARCHAR(60),
@.COLUMN_NAME VARCHAR(60),
@.COLUMN_TYPE VARCHAR(60),
@.COLUMN_SIZE INT(10)
AS
BEGIN
IF EXISTS (SELECT @.COLUMN_NAME FROM syscolumns)
BEGIN
ALTER TABLE @.TABLE_NAME ALTER @.COLUMN_NAME + ' ' + @.COLUMN_TYPE+'('+ @.COLUMN_SIZE +')'
END
ELSE
BEGIN
ALTER TABLE @.TABLE_NAME ADD @.COLUMN_NAME + ' ' + @.COLUMN_TYPE+'('+ @.COLUMN_SIZE +')'
END
END

-

CREATE PROCEDURE ENCRYPTION getString()

AS
SELECT * FORM CUSTOMER

-

Funtion getString() return a string is " With Encryption"?

Do I call function getString() in here ?

You need to use dynamic sql for this.

Code Snippet

IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME ='MODISTRCTTABLE ' AND TYPE='P')
BEGIN
DROP PROCEDURE MODISTRUCT_TABLE
END
GO

CREATE PROCEDURE MODISTRUCT_TABLE
@.TABLE_NAME VARCHAR(60),
@.COLUMN_NAME VARCHAR(60),
@.COLUMN_TYPE VARCHAR(60),
@.COLUMN_SIZE INT(10)
AS
BEGIN
IF EXISTS (SELECT @.COLUMN_NAME FROM syscolumns)
BEGIN
exec ('ALTER TABLE '+@.TABLE_NAME+' ALTER '+@.COLUMN_NAME + ' ' + @.COLUMN_TYPE+'('+ @.COLUMN_SIZE +')' )
END
ELSE
BEGIN
exec('ALTER TABLE '+@.TABLE_NAME+' ADD '+@.COLUMN_NAME + ' ' + @.COLUMN_TYPE+'('+ @.COLUMN_SIZE +')' )
END
END

The other proc is also incorrect. It should be like so.

Code Snippet

CREATE PROCEDURE getString()
with ENCRYPTION
AS
SELECT * FROM CUSTOMER

|||Thanks Oj my idea is not clearly ! I have a Store procedure :
--
Create procedure MyEnCryption With Encryption
AS
Begin
Select * from Customer
End

I want replace string " With Encryption" with calling Function GetString(); Function GetString() return a string is "With Encryption"

this store rewrite :
-
Create procedure MyEnCryption GetString()
AS
Begin
Select * from Customer
End

--

Is this store run ?|||

What you're trying to do is not possible. The syntax to create a procedure is like so:

CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @.parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]

[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

|||

While creating the objects you have to use the proper syntax what is already defined..

You can achive your desired output if you create your object using dynamic SQL...

But it is not recommanded to do this.. & I didn’t understand why you need this logic..

Note:

When you encrypt the procedure/function/view/trigger you have to store your soruce code on filesystem or source safe for future reference, you never get back the soruce text from the SQL Server. So, it is really bad idea to have dynamic sql to create encrypted objects.

Code Snippet

Declare @.sql as varchar(8000)

Set @.sql = 'Create procedure MyEnCryption ' +GetString() + '

AS

Begin

Select * from Customer

End'

exec (@.sql)

|||

Thanks Admin !

Now I'am understading !

PL/SQL cursors and index tables

I'm trying to write a script that stores values in an index table using a cursor. My syntax looks write but i'm getting the following message:

Please enter the number of rows to be selected: 5
last_name_tbl last_name_tbl_type;
*
ERROR at line 9:
ORA-06550: line 43, column 27:
PL/SQL: ORA-00923: FROM keyword not found where expected
ORA-06550: line 43, column 4:
PL/SQL: SQL Statement ignoredOriginally posted by bbk
I'm trying to write a script that stores values in an index table using a cursor. My syntax looks write but i'm getting the following message:

Please enter the number of rows to be selected: 5
last_name_tbl last_name_tbl_type;
*
ERROR at line 9:
ORA-06550: line 43, column 27:
PL/SQL: ORA-00923: FROM keyword not found where expected
ORA-06550: line 43, column 4:
PL/SQL: SQL Statement ignored
Show your code