How To Write Expression For This...

Feb 22, 2007

=Fields!net_comissionable.Value-(Val(ReportItems!textbox14.value) * Val(ReportItems!staffed_hrs.Value))*14.43

this is in a text box that is calculating the referanced box's and I am looking for the expresion that would be if the result of this is <than 0 to return as a 0
if thenn statment or a case statment how does this work.

Thanks

View 1 Replies


ADVERTISEMENT

How To Write A Comparison Expression

Dec 8, 2006

Hi,i need a column in a SELECT statement which returns always "true" (As a boolean value, not as a string)so I first tried SELECT field1, field2, true AS field3but this is not working. If I use "0 AS field3" this returns fine the 0, but this is not a boolean but a int.I also tried to make something like     "0 = 0 AS field"but also errors. In that context I discoverd that I could not compare values in the select field - I can use +,-+,* ... for having something like "2 * field1 AS field3" but I'm not clever enough to figure out how I would have a field "field1 < 12 AS field3" (ok I would use CASE, but I need the boolean return value...)Any ideas?Thanks in advance, Klaushow could I write something

View 3 Replies View Related

Write A Date Back To A Table In An Expression

Mar 12, 2008

Help,

I have a package that maps the current getdate to a variable.

I then want to be able to write that variable back to a table using a sql task using the following expression:

"INSERT INTO CONTROL_BATCH_HEADER VALUES (
" + (DT_STR, 10, 1252) @[Control::intAdtBatchId] + ",
'" + @[Control::strSubjectArea] + "',
convert(datetime, '" + (DT_STR, 30, 1252) @[Control::dteRunDate] + "',131))"


The problem is the date is being written back in the wrong format. I've tried changing the mask to 121 but the month is being written as the day.

Help, how can I make this work regardless of the locals. In oracle I would just put a to_char(date,'dd-mm-yyyy') but not sure what to do here.

Thanks for your help

Stapsey

View 1 Replies View Related

URGENT - My Error Or Bug? The Result Of The Expression Cannot Be Written To The Property. The Expression Was Evaluated, But

Feb 8, 2007

Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1


"C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"

Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls
as connectionString works

However - I'm trying to deploy the package - and trying to use expression:
@[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls"
which causes the same error to occur

(Same error with other Excel source also:
Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
)

View 4 Replies View Related

Reporting Services :: Running Value Expression Within Lookup Expression In SSRS?

Oct 28, 2015

I have created 1 report with 2 datasets. This report is attached to the 1st dataset.For example,1st one is "Smallappliances", 2nd is "Largeappliances".

I created a tablix and, the 1st column extracts Total sales per Sales person between 2 dates from 1st dataset (Small appliances). I used running values expression and it works fine.

Now, I would like to add another column that extracts Total sales per sales person between 2 dates from 2nd dataset (Large appliances). I am aware that I need to use Lookup expression and it is giving me the single sales value rather than the total sales values. So, I wanted to use RunningValue expression within lookup table to get total sales for large appliances.

This is the lookup expression that I added for the 2nd column.

=Lookup(Fields!salesperson.Value,Fields!sales_person.Value,RunningValue(Fields!sales_amount.Value,
sum, " sales_person"),
"Largeappliances").

I get this error when I preview the report.An error occurred during local report processing.The definition of the report is invalid.An unexpected error occurred in report processing.

(processing): (SortExpression ++ m_context.ExpressionType)

View 7 Replies View Related

Backup Master Key, Cannot Write Into File 'c: Empmaster'. Verify That You Have Write Permissions, That The File Path Is Valid.

Jul 12, 2006

Hi,



I tried to backup the master key by the following syntax :

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'

BACKUP MASTER KEY TO FILE = 'c: empmaster' ENCRYPTION BY PASSWORD = 'mypassword'

but it failed and i got the following message:

Cannot write into file 'c: empmaster'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.

NB: I am using the "sa" user to execute this command.

I know that we have a security permission issue , but where and how ?



Regards,

Tarek Ghazali

SQL Server MVP

View 12 Replies View Related

How To Write This SQL

Jul 27, 2006

I have a table T1
ID Desc
1 aaaa
1 bbbb
1 cccc
2 aaaa
2 cccc
3 cccc
 
Now, I need to group by ID and make Desc column as follows in a new table
ID Desc
1 aaaa, bbbb, cccc
2 aaaa, cccc
3 cccc
 
How can I do this?
 

View 2 Replies View Related

How To Write This Sql

Jul 28, 2006

I have table T1 with fields T1.ID, T1.CheckBoxCol
 
The T2 tabel has the same ID as T2.ID and T2.CheckBox1, T2.CheckBox2, T2. CheckBox3
 
Now, I need to check T1 and
if T1.CheckBoxCol=1 then set T2.CheckBox1=1
else if T1.CheckBoxCol=2 then set T2.CheckBox2=1
else if T1.CheckBoxCol=3 then set T2.CheckBox3=1
 
for each T1.ID
 
How can I do this?
 

View 2 Replies View Related

How To Write This

Jan 1, 2007

Hello,
I have MyTable with ID, IsYesNo fields
ID is duplicated so I need perform select on MyTable with the following conditions:
1. Select all the ID distinct where IsYesNo=’Yes’ first
2. Then select all the ID distinct where IsYesNo=’No’ if ID is not in the first selection
Combine these two and return the result
 

View 1 Replies View Related

How To Write To SQL

Apr 29, 2007

I'm sure this is really basic. I've created a simple form that on submittal, I would like the text boxes to be submitted to the database. I have successfully created the Datasource and it connects ok, but what code would i use to submit one text box for email address and a simple submit button. Is there an easy way to do this with VWDE?
 Thanks

View 5 Replies View Related

Need Help To Write SQL

Aug 29, 2007

Hi,I
have a hotel reservation system.I need to implement Check availability
(Room checking) function for the project.But I dont understand how do I
start and write SQL for this.Here is table structureTblRooms------------RoomsIDRoomNameNoteunitPriceSeasonalOffersTblReservation----------------ReservationIDArrivalDateDepartureDateArrivalFromFlightNoPurposeOfVisitTblRoomsInventory-------------------RoomsInventoryIDTotalRoomsBookedRooms Should I add more fields or table to implement this or this is enough .please any body can help me 

View 2 Replies View Related

How To Write This SQL

Sep 14, 2007

 How to write this SQLSelectCommand="SELECT DISTINCT TblOrder.CustomerUID, TblOrder.OrderHiddenID, TblPayment.PaymentAmount, TblPayment.Result, TblOrder.OrderID FROM TblOrder CROSS JOIN TblPayment WHERE (TblOrder.CustomerUID = @IsCustomerID) AND (TblOrder.OrderHiddenID = @IsHiddenID) AND (TblPayment.Result = 'Pending')"How to get latest order id from this.I need to combine it with above sql.I mean i want to select above records but based on max orderid record.such as select latest records from ....above SQL where max orderid

View 2 Replies View Related

How To Write This?

Jul 7, 2004

I need to compare two column in two different tables but I do not know how to write this query.

Example:


Table1
-------------
EmployeeSSN
EmployeePayRate

Table2
-------------
EmployeeSSN
EmployeePayRate

I need the result set to look like this

Result Set

-----------------------------------------------------
EmployeeSSN T1Rate T2Rate Same?
-----------------------------------------------------
111-11-1111 8.95 8.95 True
222-22-2222 9.95 8.95 False
333-33-3333 8.95 NoMatch False
444-44-4444 NoMatch 8.95 False

and so on....

How do I write this in Transact-SQL also using a function to return the "NoMatch" and True or False statements?

Thanks,
Roger

View 1 Replies View Related

Hot To Write This SP

May 9, 2002

Hey all.. Sorry I have never written a SP in my life.
so this would be a newbie question for sure.
Using sql server 7.

Here is my table field setup with sample values all fields are numeric
except date

weekending size effic dailycap workdays planutil demand
variance
5/172002 8 80 85 5 244
102 142

A file with source data will be made available in comma delimitted format to
supply a new weekending value, size and demand.

What I will need is first the dts to bring that in. then I am assuming a
stored procedure to be run (this is why I am here)
to add the data from the comma del. file into the table. If the data EXISTS
I would want it to UPDATE the values that are in the dest table but run a
calculation first which would be the planutil minus demand then the result to
be updated intot the record.
if the record from the tab del. file does not exisst in the dest table then
insert it.


another words the logic i have in mind
read the data from the temp tample (where the file gets imported into)
see if the record exists in the live table is it does update it with the calculation of planutil minus demand
if not create the new record in the live table.
I need it to compare..

Someone help me with some code
i thank you kindly in advance

p.s. And good books dedicated to stored procedures??

View 3 Replies View Related

Help To Write A Qry

Dec 1, 2006

Many thanks in advance for anyone that can help me write this qry:

To summarise - we have a database that links components to services. Components can have a 1 to many relationship with services. The components are held in a table:

dbo.components

compID compName compType
1 srv1 1
2 srv2 1
3 srv3 1

We also have a services table which hold all our various services we own:

dbo.services

svcID svcName
1052 svc1
1053 svc2
1054 svc3

We then have a tbl that shows the links between components and services

dbo.compUses

svcID compID
1052 310
1052 400
1053 122
1256 134

I would like to find out through the qry what components are currently NOT linked to a service. This will allow me to find out what components have no relationships.

This is in SQL2005.

Have I explained this well enough? Any help would be much appreciated!

View 1 Replies View Related

SQL To Write?

Dec 9, 2005

Hey peeps, fishkake's back, and he's more clueless than ever!

OK, after a few days of wrestling with books and experimenting, I now know all about reading SQL. Well, what that means is I understand Select From Where etc etc.

How do I write data? I have a reference guide, if somebody could give me literally a few high-level commands that are to do with writing data in a similar way to reading it with the SELECT statement, it would be very helpful...

This site is just making me lazy now...

View 10 Replies View Related

How Do I Write This

Apr 15, 2008

Good Afternoon all,
I was wondering how to write the TOP 5 records using Relational Algebra.

Dallr

View 6 Replies View Related

Best Way To Write This

Nov 29, 2007

Hi,
I have to implement a search functionality. In the various filters for the search, Store Number is one such filter.
The user should be able to enter range values for store numbers.
Like 1500-1600. So this should filter for all the stores between 1500 and 1600.
Similarly, all these also should be valid.
1550,1600
1550
1550 - 1580,6000,8000
etc.
I have function which identifies the commas or dashes and seperates out the store number and returns a string like
Stores.Storenumber in(1555,1600)
Store.StoreNumber between 1555 and 1600 etc...
i generate a sql at run time and append this piece and then execute the sql.
I have one of the query below.

declare @strQuery varchar(max)
declare @strConcat varchar(10)
declare @strAppend varchar(max)
set @strAppend=''
set @StrConcat ='And '

if @IsAdmin is null-- Not a Admin
set @StoreId =(select StoreNumber from Stores where Store_Id = @StoreId)

set @strQuery='
Select
(Select StoreNumber from Stores where Store_id=d.DestinationId) as StoreNumber,
CartonNumber,
ActualReceiptDate as [Scan Date],
isnull(Sum(QtyShipped),0) as [Total Units],
b.BatchNumber
from
Carton c
left outer join
CartonDetail Cd on Cd.Carton_Id = c.Carton_ID
inner join Batch b on b.General_Id = c.Carton_Id and b.BatchType=''Warehouse'' and b.TranTable=''Carton''
inner join Document d on d.Document_ID = c.document_Id
inner join Stores st on st.Store_ID = d.SourceID and st.StoreType =5
inner join Stores on Stores.Store_ID = d.DestinationID
inner join Codelist cl on cl.Codelist_Id = c.CartonStatus_ID
inner join Codes on Codes.Code_ID = cl.Code_id and Codes.CodeType=''Cartons Status Code''
where not c.cartonNumber is null '

if not (@StoreId) is null
begin
set @strAppend = @strConcat + '(' + dbo.DecodeStoreNo(@StoreId) + ')'
End

if not (@DateFrom) is null and not (@DateTo) is null
begin
set @strAppend = @strAppend + @strConcat + '(convert(varchar(50),c.ActualReceiptDate,101) between ''' + @DateFrom + ''' and ''' + @DateTo + ''')'
End

if not (@CartonNumber) is null
Begin
set @strAppend = @strAppend + @strConcat + '(c.CartonNumber = ''' + cast(@CartonNumber as varchar) + ''')'
End

if not (@Status) is null
Begin
set @strAppend = @strAppend + @strConcat + '(cl.Codevalue = ''' + @Status + ''')'
End


set @strAppend = @strAppend + ' group by
d.DestinationId,CartonNumber , ActualReceiptDate , b.batchnumber
order by ActualReceiptDate'

set @strQuery = @strQuery +@strAppend
execute(@strQuery)
This query takes time, if there a little over 1000 records.
I wanted to know, if there is any way to optimize this query? or any other way in which the above can be accomplished.

I hope i was able to explain my query fairly. Please let me know otherwise.

Thanks
Renu.

View 1 Replies View Related

How To Write An UDF And Run It

Mar 25, 2008

how to write an UDF and run it

View 2 Replies View Related

How To Write Log?

Mar 5, 2007

Hi There,

I have been working in ssis and i have a task to write a log of the all events in daily basis.

I mean writing a log for the entire package for each tasks.

Can some one help me how to do it?

Thanks



View 1 Replies View Related

How To Write This?

Nov 15, 2007



Hello all!
I'm triyng to update a NULL value to another value. I know I can do this, just can't seem to write it out right.

Something like
UPDATE Part_Order
SET Ser = 'NA'
WHERE Ser isnull

I know thats not right, but I know I'm close.
Any hints?

TIA!

Rudy

View 5 Replies View Related

How To Write This Sql

Jul 28, 2006

I have table T1 with fields T1.ID, T1.CheckBoxCol

The T2 tabel has the same ID as T2.ID and T2.CheckBox1, T2.CheckBox2, T2. CheckBox3

Now, I need to check T1 and
if T1.CheckBoxCol=1 then set T2.CheckBox1=1
else if T1.CheckBoxCol=2 then set T2.CheckBox2=1
else if T1.CheckBoxCol=3 then set T2.CheckBox3=1

for each T1.ID

How can I do this?

View 4 Replies View Related

How To Write This SQL

Jul 27, 2006

I have a table T1
ID Desc
1 aaaa
1 bbbb
1 cccc
2 aaaa
2 cccc
3 cccc

Now, I need to group by ID and make Desc column as follows in a new table
ID Desc
1 aaaa, bbbb, cccc
2 aaaa, cccc
3 cccc

How can I do this?

View 4 Replies View Related

Which Expression To Use ?

May 27, 2008

well I have this code. I connect to database and submit data.
now i dont know which expression to use to validate if data is submited redirect to next level else show some error.
    protected void Button1_Click1(object sender, EventArgs e)    {         SqlConnection con = new SqlConnection(@"somecode");
  SqlCommand comm = new SqlCommand();  comm.Connection = con;  comm.CommandText = "insertuser";  comm.CommandType = CommandType.StoredProcedure;some code...  comm.Connection.Open();  comm.ExecuteNonQuery();  con.Close();        }
i dont wanna validate the fields as i can do it using field validator i wanna check if data is inserted in database.

View 3 Replies View Related

Only One Expression Can Be Specified

May 28, 2008

Hi

When I try and create this procedure, I get the error below

CREATE PROCEDURE GetUserSearchTypesCount
-- OUTPUT parameter to hold the count.
@UserID int,
@ReturnVal int OUTPUT
AS

SET @ReturnVal = (SELECT TOP 100 percent searchType, COUNT(searchtype) AS SearchCount
FROM ttracksearchresults
WHERE userid=@UserID
GROUP BY searchtype
ORDER BY searchtype)

GO

Error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

View 3 Replies View Related

Expression In SET

Feb 7, 2007

I need a statement like:UPDATE tblQuestions SET QuestionNumber = QuestionNumber +1 WHERE QuestionNumber >=10 AND TestID = 1 AND SectionNumber = 1
(Required output is clear from statement itself)

I tried with
UPDATE tblQuestions SET QuestionNumber = EVAL(QuestionNumber +1) WHERE QuestionNumber >=10 AND TestID = 1 AND SectionNumber = 1
and
UPDATE tblQuestions SET QuestionNumber = (QuestionNumber +1) WHERE QuestionNumber >=10 AND TestID = 1 AND SectionNumber = 1

I've just started working with SQL. Please suggest correct approach.

View 3 Replies View Related

Expression

Feb 1, 2008

I have the following code in the expression builder for a ExecuteSQL Task in the Control Flow.
"INSERT dbo.StgFileList SELECT ' " + @[User::MyFile] + " ' "

When I evaluate it, it renders as INSERT Staging.StgXMLFileList SELECT ' '

What am I doing wrong in the expression builder?

View 5 Replies View Related

Expression Help

Feb 20, 2008



Is it possible to nest expressions within a script in a similar fashion to how they're nested within the expression editor?

I have a requirement to strip trailing digits from a string field, so that only two decimal places are left.

e.g.
1.12345 becomes 1.12
12345.6789 becomes 12345.67

I would do this in the expression editor but I need to use Script component because there are a multitude of conditions that need to be evaluated first which I will do inside a Select Case statement.

I'm wrestling with code that looks like


Newratefield = ratefield.Substring(1, Findstring(ratefield, ".", 1) + 2)

but VB doesn't like the 'Findstring(ratefield, ".", 1) + 2)' part.

Any suggestions on what I can do here?

View 3 Replies View Related

Expression

Jan 9, 2007

Hi,

In RunningValue expression, can we able to put the DataSet name.

Thanks

View 17 Replies View Related

Expression

Jul 27, 2007

Hi,
In ssis I would like to map a column from table a to a column in table b
Currently this is how I am doing it using sql.
I believe I have to use a derived column but not sure how to implement if statement inside it.
Thanks


case

when len([Column 5]) = 0 then ''

when lower([Column 5]) = 'unknown' then ''

when isdate([Column 5]) = 1 then substring(ltrim([Column 5]), 1, 4) + substring(ltrim([Column 5]), 6, 2) + substring(ltrim([Column 5]), 9, 2)

else

null

end,

View 4 Replies View Related

The Value Expression

Dec 20, 2006

The Value expression for the textbox €˜Name€™ uses an aggregate expression without a scope. A scope is required for all aggregates used outside of a data region unless the report contains exactly one data set....what does this mean...this an error that I have when i try and build my report i get this error for some reason...how do i fix this???

View 4 Replies View Related

Need Help With Expression

Sep 28, 2007

Hi,

I need to replace a field that might contain a certain character with another should the value occur.
To explain

If i get a hash # in the field, then I need to replace the whole field with a 0 since its a numeric field

I'm just a bit of a noob still with expressions

Regards

View 3 Replies View Related

Expression Sum And Iif Help?

Aug 17, 2007



Hi All,


SUM(IIF(Fields!NO.Value >= 1, Fields!tsHours.Value,0))


i write this expression inside the group footer row, it give a #error when generating report.


please help


Cheers

Nick

View 5 Replies View Related







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