I Want A Function Like IfNull Function To Use In Expression Builder
Hi,
I wonder if there a function that i can use in the expression builder that return a value (e.g o) if the input value is null ( Like ifnull(colum1,0) )
i hope to have the answer because i need it so much.
Maylo
View Complete Forum Thread with Replies
Related Forum Messages:
Using Cint Function In Expression Editor
Hi, I am a new to Reporting Services... I am using RS2000 I am trying to apply cint function in the expression editor for the textbox eg. i type in =cint(Fields!firstfield.Value/Fields!secondfield.Value). This is returning error The value expression for the textbox €˜HoldDuration€™ contains an error: Arithmetic operation resulted in an overflow. Could you please let me know what I did wrong in this and how to correctly apply VB or any custom functions in the expression editor where the expession output would be a decimal value and you are interested in getting an integer value by rounding the fractional part.
View Replies !
Function With Expression To Return Values
I have created a function to return values, which works fine, but I can't do calculations in it. CREATE FUNCTION [dbo].[tf_Asset_Portfolio](@deal_id int, @as_of_date datetime) RETURNS TABLE AS RETURN ( SELECT DISTINCT dbo.Assets.issue_id, SUM(DISTINCT dbo.Assets.par_amount) AS par_amount, SUM(DISTINCT dbo.Assets.par_amount) AS market_value FROM dbo.Issue INNER JOIN dbo.Assets ON dbo.Issue.issue_id = dbo.Assets.issue_id INNER JOIN dbo.Issuer_Rating_History ON dbo.Issue.issuer_id = dbo.Issuer_Rating_History.issuer_id WHERE (dbo.Issuer_Rating_History.as_of_date <= @as_of_date) GROUP BY ALL dbo.Assets.issue_id, dbo.Assets.deal_id, dbo.Issue.default_date HAVING (dbo.Assets.deal_id = @deal_id) ) I need to do calculations on market value based on the default date. If default date isn't specified then it should be 100% of par amount. If default date is less than one year ago - 65% of the par_amount. If default date is one or more years ago - 0. I have no idea about how to do this and everything I try wont work. I created another function to do the calculations and this seems to work, but it only does one record instead of all of them. CREATE FUNCTION dbo.tf_Asset_Portfolio2 (@deal_id int, @as_of_date datetime) RETURNS @Market TABLE (issue_id int, par_amount money, market_value money) AS BEGIN DECLARE @ReturnDate datetime DECLARE @DD datetime DECLARE @PA money DECLARE @MV money DECLARE @ID int DECLARE @DateD int SELECT TOP 1 @ReturnDate = LAST_BATCH FROM master..sysprocesses WHERE SPId = @@SPID SELECT @ID = issue_id FROM Assets WHERE Assets.deal_id = @deal_id SELECT @PA = SUM(DISTINCT par_amount) FROM Assets WHERE Assets.issue_id = @ID AND Assets.deal_id = @deal_id SELECT @DD = default_date FROM Issue WHERE Issue.issue_id = @ID SET @DateD = DateDiff("yyyy", @DD, @ReturnDate) If @DD = Null BEGIN SET @MV = @PA END Else If @DD > @ReturnDate BEGIN SET @MV = @PA END Else If @DateD < 1 BEGIN SET @MV = @PA * .65 END Else If @DateD >= 1 BEGIN SET @MV = 0 END insert into @Market (issue_id, par_amount, market_value) values (@ID,@PA,@MV) RETURN END I need to combine the functionality of being able to return mutliple records that isn't in the 2nd function and being able to calculate the market value which isn't in the first one. Please help. Thank you in advance.
View Replies !
Report Builder Conditional IF Function Problem
Hi everyone, I have created a view from the exisiting table to use in the report builder model and in the table whenever there is a null(when users does not enter a date for this particular field in the application) for datetime field either of these 2 ("1/1/1753" and "12/31/9999") dates are stored . Since I am creating my views based on these tables these dates will be in my views as well. Checking for this at the application level and cleaning up is not a option for me at this time. So what I am trying to do is to check for these values and replace it with Null or blank in the model designer expression property. So that when user creates a report using this field they will not see these sql standard dates. I tried using conditional IF in Model designer . EX: IF(Next MCR Review Date = 1/1/1753,EMPTY,Next MCR Review Date) IF(Next MCR Review Date = 1/1/1753,NULL,Next MCR Review Date) Both of these gives me errors. Can anyone tell me what I am doing wrong and also are there any other ways to get to what I want. I have already spent a lot of time digging for documentation but no luck any help is appreciated. Thanks a Lot Ashwini
View Replies !
Report Builder: RowCount Or Ranking Function
I have a end users that is in need of a some way to rank results in a Report Builder report. I have noticed that neither the RowCount or Ranking function is listed in the avaliable functions or expressions. Does anyone have an idea or solution to perform some type of ranking. Example: Name Count Rank Brad 10 1 Bill 9 2 John 8 3 Seems to be a pretty simple function that was overlooked in Report Builder by MS.
View Replies !
SUM Of Report Field With Expression Which Has COUNT Aggregate Function
Hi everyone, I have created a report which has 3 groups. The report output as shown below. I am having trouble getting the SUM of Total Credtis for each Org. Can't seem to get the total 42 and 16 (highlighted), but can get total unists 11 and 13. I get expression contains aggregate function. This is because Units assessed is the Count of IDs (details hidden from the report). Report has three groups Org , Assessor and Unit. Can someone please help me with this? Appreciate help. Thank you, Ski Org 1(Group1) Unit Credits Units Assessed(# of Trainees) TotalCredits Assessor 1 Unit 1 2 4 (Count of Ids) 8 (2*4) Assessor 2 Unit 2 1 2 2 (1*2) Assessor 3 Unit 3 5 2 10 (5*2) Unit 4 2 1 2 Assessor 4 Unit 5 10 2 20 -------------------------------------------------------------------------------------------------------- 11 42 ----------------------------------------------------------------------------------------------------------- Org 2 Assessor 3 Unit 1 2 3 6 Assessor 4 Unit 6 1 10 10 -------------------------------------------------------------------------------------------------------- 13 16 --------------------------------------------------------------------------------------------------------
View Replies !
How To Find Terms In A File Name: Regular Expression OR A String Function
hi, i am using a forEach look to import each file within a folder, but i also need to calculate the dataset related ot these files. the file are named as: ff_inbound_20071008_1.csv ff_inbound_20071008_2.csv where for file ff_inbound_20071008_1.csv: ff => flat file inbound => dataset of this csv 20071008 => date 1=> file count having in mind that they are store in the variable as a full path: z:myFlatFilesexportsproj01ff_inbound_20071008_2.csv i need to extract the dataset and the date for each file. how can i extract these terms from the file name? many thanks, nicolas
View Replies !
How To Convert A Returning String From Custom Code To A Function In Expression?
I have a custom code function that return string like: "SUM(Fields(Parameters!dept.Value + ""_1HeadCount"").Value) + SUM(Fields(Parameters!dept.Value + ""_2HeadCount"").Value) + SUM(Fields(Parameters!dept.Value + ""_3HeadCount"").Value) " How do I convert this string to a function inside the expression (in this case "SUM", "Fields", "Parameters!", etc.)? The expression of my TextBox show the string as a string instead of converting them to proper function and display the corrent value. The reason I need to build the string is because the Fields number is dynamic and I need a loop to increase the number in the field name. Any suggest is highly appreciated! Thanks, Tabbey
View Replies !
Can I Apply A Database Function Or Assembly Call In An Expression For Filter Data?
I need to translate a user€™s regional setting into one of our own language codes before I send it through as a filter to the model query. If our language codes were the same, the filter would look like this in the report filter - Language Code = GetUserCulture() Which translates to this in the database query (for us english) - table.language_code = 'EN-us' And of course I need it to look like this - table.language_code = 'ENG' I would like the logic to be globally available to all report writers (ie not forcing each report writer to have an iif or case stataement). I was thinking custom assemblies or maybe a database function, but at this level of the filter, I cannot seem to figure out how to embed a database function call to apply to the filter criteria like this Language Code = dbo.ConvertFcnIWrote(GetUserCulture()) Or how I would access the custom assembly in the filter expression. Do you have a recommended implementation for this situation? Thanks, Toni Fielder
View Replies !
Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark
View Replies !
Help Convert MS Access Function To MS SQL User Defined Function
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
View Replies !
In-Line Table-Valued Function: How To Get The Result Out From The Function?
Hi all, I executed the following sql script successfuuly: shcInLineTableFN.sql: USE pubs GO CREATE FUNCTION dbo.AuthorsForState(@cState char(2)) RETURNS TABLE AS RETURN (SELECT * FROM Authors WHERE state = @cState) GO And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database. I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script: shcInlineTableFNresult.sql: USE pubs GO SELECT * FROM shcInLineTableFN GO I got the following error message: Msg 208, Level 16, State 1, Line 1 Invalid object name 'shcInLineTableFN'. Please help and advise me how to fix the syntax "SELECT * FROM shcInLineTableFN" and get the right table shown in the output. Thanks in advance, Scott Chang
View Replies !
Expression: !&&"No Exceptions Should Be Raised By This Code&&" When Selecting From System Function
One of my users is receiving the error below when selecting from this function: select * from sys.dm_db_index_operational_stats (null,null,null,null) I have already granted him "grant view server state" rights and this did not change anything. However, as a test I temporarily granted him sysadmin rights and he did not receive the error. I am curious as to what missing rights are causing the error. The server is sql version is 2005 standard edition 64 bit SP2: Microsoft SQL Server 2005 - 9.00.3042.00 (X64) All the posts about this error that I have seen indicate that this error is resolved after applying SP2. Anyone have any other suggestions? Error Location: qxcntxt.cpp:956 Expression: !"No exceptions should be raised by this code" SPID: 58 Process ID: 2000 Msg 21, Level 20, State 1, Line 3 Warning: Fatal error 1203 occurred at Jun 15 2007 10:32AM. Note the error and time, and contact your system administrator. Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. The following is from the SQL Log: Process 58 unlocking unowned resource: DATABASE: 35 Error: 1203, Severity: 20, State: 1. Process ID 58 attempted to unlock a resource it does not own: DATABASE: 35 . Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator. Process 58 unlocking unowned resource: DATABASE: 35 Using 'dbghelp.dll' version '4.0.5' **Dump thread - spid = 58, PSS = 0x00000000A11EDBE0, EC = 0x00000000A11EDBF0 ***Stack Dump being sent to D:Datase-k-MSSQLServer-OCSQLOGSQLDump0027.txt * ******************************************************************************* * * BEGIN STACK DUMP: * 06/15/07 10:32:59 spid 58 * * Location: qxcntxt.cpp:956 * Expression: !"No exceptions should be raised by this code" * SPID: 58 * Process ID: 2000 * * Input Buffer 276 bytes - * execute as login = 'INTNPU22537' select suser_name() selec * t * from sys.dm_db_index_operational_stats (null,null,null,null) * * * MODULE BASE END SIZE * sqlservr 0000000001000000 0000000003501FFF 02502000 * ntdll 0000000077EC0000 0000000077FF7FFF 00138000 * kernel32 0000000077D40000 0000000077EB2FFF 00173000 * MSVCR80 0000000078130000 00000000781FEFFF 000cf000 * msvcrt 000007FF7FC00000 000007FF7FC85FFF 00086000 * MSVCP80 000000007C420000 000000007C530FFF 00111000 * ADVAPI32 000007FF7FEE0000 000007FF7FFE4FFF 00105000 * RPCRT4 000007FF7FD30000 000007FF7FED8FFF 001a9000 * USER32 0000000078C30000 0000000078D3BFFF 0010c000 * GDI32 000007FF7FC90000 000007FF7FD28FFF 00099000 * CRYPT32 000007FF7D180000 000007FF7D2DEFFF 0015f000 * MSASN1 000007FF7D150000 000007FF7D179FFF 0002a000 * Secur32 000007FF7E7F0000 000007FF7E811FFF 00022000 * MSWSOCK 000007FF76FE0000 000007FF7705BFFF 0007c000 * WS2_32 000007FF77150000 000007FF7717FFFF 00030000 * WS2HELP 000007FF77140000 000007FF7714BFFF 0000c000 * USERENV 000007FF7C4A0000 000007FF7C5A8FFF 00109000 * opends60 00000000333E0000 00000000333E7FFF 00008000 * NETAPI32 000007FF77370000 000007FF77407FFF 00098000 * SHELL32 000007FF7F190000 000007FF7FB97FFF 00a08000 * SHLWAPI 000007FF7EF60000 000007FF7EFFAFFF 0009b000 * comctl32 0000000000AF0000 0000000000C76FFF 00187000 * psapi 000007FF7E1B0000 000007FF7E1BFFFF 00010000 * instapi 0000000048060000 000000004806CFFF 0000d000 * sqlevn70 000000004F610000 000000004F7B8FFF 001a9000 * SQLOS 00000000344D0000 00000000344D5FFF 00006000 * rsaenh 000000000FFB0000 000000000FFEDFFF 0003e000 * AUTHZ 000007FF7E2C0000 000007FF7E2ECFFF 0002d000 * MSCOREE 000006427EE60000 000006427EED3FFF 00074000 * ole32 000007FF7ECE0000 000007FF7EF51FFF 00272000 * msv1_0 000007FF7E330000 000007FF7E373FFF 00044000 * iphlpapi 000007FF57250000 000007FF57280FFF 00031000 * kerberos 000007FF77410000 000007FF774C2FFF 000b3000 * cryptdll 000007FF7DAB0000 000007FF7DABEFFF 0000f000 * schannel 000007FF7DB70000 000007FF7DBB0FFF 00041000 * COMRES 000007FF7E920000 000007FF7E9E5FFF 000c6000 * XOLEHLP 000007FF5C560000 000007FF5C566FFF 00007000 * MSDTCPRX 000007FF67140000 000007FF67210FFF 000d1000 * msvcp60 0000000007420000 0000000007509FFF 000ea000 * MTXCLU 000007FF7B540000 000007FF7B569FFF 0002a000 * VERSION 000007FF7FBF0000 000007FF7FBFAFFF 0000b000 * WSOCK32 000007FF770F0000 000007FF770F9FFF 0000a000 * OLEAUT32 000007FF7E9F0000 000007FF7EB03FFF 00114000 * CLUSAPI 000007FF7B1C0000 000007FF7B1E3FFF 00024000 * RESUTILS 000007FF7B310000 000007FF7B32BFFF 0001c000 * DNSAPI 0000000007520000 000000000756DFFF 0004e000 * winrnr 000007FF7E820000 000007FF7E82AFFF 0000b000 * WLDAP32 000007FF7E780000 000007FF7E7E5FFF 00066000 * rasadhlp 0000000007610000 0000000007616FFF 00007000 * security 000007FF77530000 000007FF77534FFF 00005000 * msfte 0000000049980000 0000000049D2DFFF 003ae000 * dbghelp 0000000009630000 000000000978CFFF 0015d000 * WINTRUST 000007FF7E210000 000007FF7E25DFFF 0004e000 * imagehlp 000007FF7E2A0000 000007FF7E2B2FFF 00013000 * dssenh 000000000FF70000 000000000FFABFFF 0003c000 * hnetcfg 000007FF6D1F0000 000007FF6D280FFF 00091000 * wshtcpip 000007FF76FA0000 000007FF76FAAFFF 0000b000 * NTMARTA 000007FF7E2F0000 000007FF7E32BFFF 0003c000 * SAMLIB 000007FF76F80000 000007FF76F95FFF 00016000 * ntdsapi 000007FF7DAD0000 000007FF7DAF3FFF 00024000 * xpsp2res 000007FF5C210000 000007FF5C4D6FFF 002c7000 * CLBCatQ 0000000003510000 00000000035ECFFF 000dd000 * sqlncli 00000000337A0000 0000000033A5FFFF 002c0000 * COMCTL32 0000000003610000 00000000036FDFFF 000ee000 * comdlg32 000007FF7D360000 000007FF7D3D6FFF 00077000 * SQLNCLIR 0000000035000000 0000000035032FFF 00033000 * msftepxy 000000000AA10000 000000000AA30FFF 00021000 * xpsqlbot 000000004A7C0000 000000004A7C7FFF 00008000 * xpstar90 0000000053C30000 0000000053CB5FFF 00086000 * SQLSCM90 0000000053AD0000 0000000053ADBFFF 0000c000 * ODBC32 000007FF63EB0000 000007FF63F16FFF 00067000 * BatchParser90 00000000520C0000 00000000520ECFFF 0002d000 * ATL80 000000007C630000 000000007C64DFFF 0001e000 * odbcint 000007FF63DC0000 000007FF63DD7FFF 00018000 * xpstar90 000000000B440000 000000000B465FFF 00026000 * xplog70 0000000034730000 000000003473FFFF 00010000 * xplog70 000000000B480000 000000000B482FFF 00003000 * odsole70 0000000033380000 0000000033397FFF 00018000 * SQLDMO 000000001C0B0000 000000001C6E2FFF 00633000 * odbcbcp 000007FF63E90000 000007FF63E97FFF 00008000 * SQLDMO 000000001C000000 000000001C08DFFF 0008e000 * SXS 000007FF7CA80000 000007FF7CC6CFFF 001ed000 * SQLSRV32 000007FF5FD60000 000007FF5FE05FFF 000a6000 * sqlsrv32 00000000093C0000 00000000093D6FFF 00017000 * odbccp32 000007FF63E20000 000007FF63E44FFF 00025000 * sqlevn70 000000000C5D0000 000000000C7D4FFF 00205000 * sqlevn70 000000000C7E0000 000000000C9E2FFF 00203000 * sqlevn70 000000000C9F0000 000000000CB98FFF 001a9000 * sqlevn70 000000000CBA0000 000000000CD8BFFF 001ec000 * sqlevn70 000000000CD90000 000000000CF87FFF 001f8000 * sqlevn70 000000000CF90000 000000000D173FFF 001e4000 * sqlevn70 000000000D180000 000000000D33FFFF 001c0000 * sqlevn70 000000000D340000 000000000D51FFFF 001e0000 * sqlevn70 000000000D520000 000000000D6F9FFF 001da000 * sqlevn70 000000000D700000 000000000D8A8FFF 001a9000 * sqlevn70 000000000D8B0000 000000000DA58FFF 001a9000 * sqlevn70 000000000DA60000 000000000DC08FFF 001a9000 * * P1Home: 0000000000000001: * P2Home: 000000000FAD3E00: 0030004600420044 000000000000000A 0000000000000000 0000000000000000 0000000500000030 0000000000000000 * P3Home: 0000000000000000: * P4Home: 0000000077EF40D2: 00000320249C8B48 0000031824B48B48 0000031024BC8B48 0000030824A48B4C 0000030024AC8B4C 000002F824B48B4C * P5Home: 0000000000000000: * P6Home: 0000000000000000: * ContextFlags: 000000000010000F: * MxCsr: 0000000000001FA0: * SegCs: 0000000000000033: * SegDs: 000000000000002B: * SegEs: 000000000000002B: * SegFs: 0000000000000053: * SegGs: 000000000000002B: * SegSs: 000000000000002B: * EFlags: 0000000000000206: * Rax: 000000000FAD3E88: 0000000077D67D4D 0000000009307780 0000000000000000 000000000FAD4140 00000000A11EDBE0 00000000000042AC * Rcx: 000000000FAD39B0: 0000000000000001 000000000FAD3E00 0000000000000000 0000000077EF40D2 0000000000000000 0000000000000000 * Rdx: 0000000000000000: * Rbx: 0000000000000000: * Rsp: 000000000FAD3E90: 0000000009307780 0000000000000000 000000000FAD4140 00000000A11EDBE0 00000000000042AC 0000000000000000 * Rbp: 000000000FAD5620: FFFFFFFF000003BC 000000000FAD9324 000003E500000001 0000000000000001 0000000000000007 000000000FAD935C * Rsi: 000000000FAD4140: 0000000009307780 0000000000000000 0000000000000000 0000000000000000 0000000000000001 0000000000000000 * Rdi: 00000000A11EDBE0: 00000000A11ECB00 0000000000000000 00000000A11EDBE0 0000000000000000 0000000000000000 00000000A11EDC08 * R8: 0000000000000000: * R9: 0000000000000000: * R10: 0000000000000000: * R11: 000000000FAD4680: 0000000000000001 000000000FAD3E00 0000000000000000 0000000077EF40D2 0000000000000000 0000000000000000 * R12: 000000000000003F: * R13: 00000000A11EDBF0: 00000000A11EDBE0 0000000000000000 0000000000000000 00000000A11EDC08 00000000A11EDC08 00000000A11EDC18 * R14: 000000000FAD5530: 00610063006F004C 006E006F00690074 007100200009003A 0074006E00630078 0063002E00740078 0039003A00700070 * R15: 0000000000000001: * Rip: 0000000077D67D4D: C3000000C8C48148 15FF984B8D49D233 8699E990FFFD971F 9090909090900000 834853C08B909090 0C0D8A158B4830EC * ******************************************************************************* * ------------------------------------------------------------------------------- * Short Stack Dump 0000000077D67D4D Module(kernel32+0000000000027D4D) 0000000001D345DE Module(sqlservr+0000000000D345DE) 0000000001D3F3A9 Module(sqlservr+0000000000D3F3A9) 000000000247E28C Module(sqlservr+000000000147E28C) 00000000015D069F Module(sqlservr+00000000005D069F) 00000000781CF4B0 Module(MSVCR80+000000000009F4B0) 000000007816AA10 Module(MSVCR80+000000000003AA10) 0000000077EF3722 Module(ntdll+0000000000033722) 00000000010DEBF1 Module(sqlservr+00000000000DEBF1) 00000000010DE6EF Module(sqlservr+00000000000DE6EF) 00000000010DF551 Module(sqlservr+00000000000DF551) 00000000010E2B62 Module(sqlservr+00000000000E2B62) 00000000010E4115 Module(sqlservr+00000000000E4115) 00000000010E4695 Module(sqlservr+00000000000E4695) 00000000010E375D Module(sqlservr+00000000000E375D) 00000000781CF4B0 Module(MSVCR80+000000000009F4B0) 000000007816AA10 Module(MSVCR80+000000000003AA10) 0000000077EF3722 Module(ntdll+0000000000033722) 000000000102F94A Module(sqlservr+000000000002F94A) 000000000102F375 Module(sqlservr+000000000002F375) 0000000001030872 Module(sqlservr+0000000000030872) 0000000001270CE7 Module(sqlservr+0000000000270CE7) 000000000100685E Module(sqlservr+000000000000685E) 0000000001006F11 Module(sqlservr+0000000000006F11) 0000000001006A01 Module(sqlservr+0000000000006A01) 00000000010EF7A7 Module(sqlservr+00000000000EF7A7) 00000000010F051A Module(sqlservr+00000000000F051A) 00000000010F03F1 Module(sqlservr+00000000000F03F1) 00000000010F0289 Module(sqlservr+00000000000F0289) 00000000781337A7 Module(MSVCR80+00000000000037A7) 0000000078133864 Module(MSVCR80+0000000000003864) 0000000077D6B69A Module(kernel32+000000000002B69A) Stack Signature for the dump is 0x00000000A0A891FD External dump process return code 0x20000001. External dump process returned no errors. Error: 17066, Severity: 16, State: 1. SQL Server Assertion: File: <qxcntxt.cpp>, line=956 Failed Assertion = '!"No exceptions should be raised by this code"'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
View Replies !
CASE Function Result With Result Expression Values (for IN Keyword)
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function. WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END ) I kept on getting errors, like Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'. which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
View Replies !
Cannot Perform An Aggregate Function On An Expression Containing An Aggregate Or A Subquery.
Can any1 tell me why i am getting an error SELECT DISTINCT --p.voucher, --p.amount, p.siteID, b.siteID, SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS OutStandingBalance, SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS CashCheque, SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS Vouchers FROM BillingTotal b, Payment p --WHERE -- s.sitename=@cmb1 --AND p.siteid = s.siteid -- p.voucher = 0 -- p.voucher = 1 GROUP BY p.siteID,b.siteID Msg 130, Level 15, State 1, Line 1 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
View Replies !
Function To Call Function By Name Given As Parameter
I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz
View Replies !
Expression Builder
Hi All, I want to build following code into expression builder . I don't know how to write that things into expression builder. so can you help me out from this problems. 1.Daily stock Inventory by stores " & FormatDateTime( date() , vbshortdate ) 2. " Weekly stores Queue Productivity for " & date()-8 3.Customer Data Entry Monthly Report for stores " & Month( now()-27) & "/" & Year(now()-27) 4."Customer of stores " & FormatDateTime( now() , vbshortdate ) & " - stock" 5.customers opens account " & FormatDateTime( now() -8, vbshortdate ) & " (STOCK1) Thanks Aric
View Replies !
Using A Scalar Valued Function As A Parameter Of A Table Valued Function?
Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't... I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it. So the first thing I do, is I need to grab the primary key fields of the table. I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table. Great. I pass in the table name, and sure enough, it comes back with a record set, 1 row per column. That's exactly what I need. Umm... This is the part where I'm at a loss. The stored procedure outputs the resultset as a resultset (Not as an output param). Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work. But... How do I use the resultset from the stored procedure? You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the syntax like: DECLARE @table table@table=EXEC sp_pkeys MyTable That of course just returns you the RETURN_VALUE instead of the resultset it output. Ugh. Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys. Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key. Ok, I test it and it works great. Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out... SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT) Syntax Error. Ugh. Eventually, I even try: SELECT *FROM Split(substring('abc,def',2,6),DEFAULT) Syntax Error. Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function? SELECT *FROM Split('bc,def',DEFAULT) works just fine. So my questions are: Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it? Is there any way to pass a scalar-valued function as a parameter into a table-valued function? Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround: DECLARE @tmp varchar(8000) SET @tmp=(SELECT dbo.fn_pkeys('MyTable')) SELECT * FROM Split(@tmp,DEFAULT)
View Replies !
SSIS-Expression Builder
am trying to do the following in SSIS. I have Execute Process Task, in which I have the values are set for Executable, Arguments and working Directory manually. I keep changing this if the Connection Manager points to Dev or when it points to QA or to Prod. Instead I want to do like this. If my Connection Manager points to Dev, the Executale , Arguments and Working Directory should automatically change. Else if it points to QA, the Executale , Arguments and Working Directory should automatically change and similarly for Prod. Is this possible in Expression Builder in Execute Process Task. If so, how?. Thank you
View Replies !
Conditions In Expression Builder
Nobody answered my previous question. So, I am putting it this way. How to write an IF condition in Expression builder. I want to do something like this. IF (variable1='ABC' then value = '.......' else variable1 ='DEF' then value = '.......' else variable1='GHI' then value = '.......' END Thank you.
View Replies !
Expression Builder Question
Hi all of you, When you're making expressions with your own variables and system variables and so on. How could I see the contents for each variable? When you have an expression like this: "Cargas de Hacienda. " + SUBSTRING( @[System:ackageName], 7,20) + " " You can perfectly click above "Evaluated value" and see the resutl but I mean, individually over each variable on the variables listview is not possible. Thanks for any input or advice, Enric
View Replies !
Bug In Report Builder When Using Expression
I ran in to a strange problem in Report Builder. I drop a few fields from my Report Model on a simple talbe report. Some of them are straight forwards attributes and some of them are expressions. Now in the table layout of a report I have say a attribute named as "Account Number". This attribute is actually a field from a table. I change the column header to a two line column header with €œAccount€? on one line and €œNumber€? on second line. No problem, piece of cake. Now tried the same thing on an expression type attribute, say "Principal Balance". When I split the column header on two line I get this following error. ============================================================== Semantic query compilation failed: e MeasureNotFound One of the SubtotalMeasures.MeasureName properties of the SemanticQuery refers to the Measure Expression 'Principal Balance', which does not exist. (SemanticQuery ''). ---------------------------- An error has occurred during report processing. ============================================================== If you read the error you will realize that the query is trying to find an expression named "Principal" (newline char) "Balance" and it fails. This expression is nothing but a sum aggregation on a field in the underlying table. If I put the "Principal Balance" back on one line report runs like a charm. Also note that exact same report in Designer no issues at all. FYI: I am in SQL Server 2005 with SP2. (tried it both on Standard and Enterprise versions.)
View Replies !
Expression Builder Elipse Button Does Not Appear
Hello, I am attempting to create an expression for a package variable in a SQL Server Integration Services project, but the elipse button in the expression property for the variable does not appear (Visual Studio 2005). I've tried two different screen resolutions, no luck. Why would the elipse not be there? Is there a keystroke I can use to open the exrpression builder? Thank you for your help! cdun2
View Replies !
String Parsing And Expression Builder....
I can't figure this one out. I don't have enough knowledge of the string functions I guess. I need to pull a value out of a variable I setup in a for each loop. The value is the filename/path of each source file being processed. Let's say the variable that has the source file path is called VAR1. One sort of off topic thing I've noticed is when watch the variable in bebug mode and I look at the value of VAR1 it has double back slashes. Here's an example of the value of VAR1: "\\L3KRZR6.na.xerox.net\C$\Documents and Settings\ca051731\Desktop\Project4\DPT_20070926.ver" How come the back slashes have been doubled? And do I need to account for that when I start parsing the string value? Anyway, I need to grab part of the filename from VAR1 and I need the value populated at the start of the for each loop container - ideally when I capture VAR1 in the for each container. I'll be using the string in drop table, create table and create index statements before the actual Data Flow task within the overall package In the above example I need to grab the characters before the underscore and after the last \. So I'd need the string "DPT" captured in this example. The actual string could be 1 to 3 characters long, even though this example has it as 3 long. Underscores could exist anywhere in the actual UNC path once this package is moved to our actual system environments so I can't key off of the underscore. Because I can't count on the string being a fixed lenght I can't just use a positional string function and grab specific text starting/ending at specific points. Is there a way to use the various string functions in the expression builder to grab the text between the right most underscore and the right most back slashes or something like that? Ideally I'd like to setup a new expression based packed scope variable called VAR2 and build it using string functions applied to VAR1.
View Replies !
Use Getdate Function In A Own Function
Hi,I have written a stored proc with some temporary tables and also useda getdate() in my stored proc. When i try to call the sproc the erroris that we can only use extended sprocs or function inside a sproc.Now if try to write the stored proc directly inside a fuction ie copypaste after changing my temp tables to tables the problem is , i get aerror invalid use of getdate in sproc.What do i do to get somethingfor my results inside a table.Thanks in advance.RVG
View Replies !
Calling A Function From A Function?
Hi All Yesterday Peso was gracious enough to help me with creating function/views/sp's I took those examples and extended what had from excel into function in SQL however I see myself repeating certain parts of the query and i'm wondering if there is a way to call a function (in part or in whole) from another function? Here are excerpts two functions I have: We'll call this function UserUsage() ------------------------------------ RETURN( SELECT ut.LastName, ut.FirstName, CEILING(Sum(hu.session_time)/ 60000) AS [Time Spent(MIN)], Max(hu.time_stamp) AS [Last Log Date], pct.Title, cat.topic_name FROM ZSRIVENDEL.dbo.UserTable ut, ZSRIVENDEL.dbo.history_usage hu, ZSRIVENDEL.dbo.pc_CourseTitles pct, ZSRIVENDEL.dbo.cam_topics cat WHERE ut.student_id = hu.student_id AND hu.course_id = pct.CourseID AND hu.topic_id = cat.topic_id AND ((ut.ClientID=@ClientID) AND (pct.ClientID=@ClientID) AND (ut.GroupID=3400) AND (hu.time_stamp>= @StartDate And hu.time_stamp< @EndDate) AND (hu.session_time<21600000)) GROUP BY ut.LastName, ut.FirstName, pct.Title, cat.topic_name ) and will call this function UserSummary(): ----------------------------------------- RETURN ( SELECTut.LastName, ut.FirstName, CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)] FROM ZSRIVENDEL.dbo.UserTable AS ut INNER JOIN ZSRIVENDEL.dbo.History_Usage AS hu ON hu.Student_ID = ut.Student_ID WHERE ut.ClientID = @ClientID AND ut.GroupID = 3400 AND hu.Time_Stamp >= @StartDate AND hu.Time_Stamp < @EndDate AND hu.Session_Time < 21600000 GROUP BY ut.LastName, ut.FirstName ) As you can see the first part of the both query are simlar. In particular the: SELECTut.LastName, ut.FirstName, CEILING(SUM(hu.Session_Time) / 60000.0) AS [Time Spent(MIN)] and also the variables @StartDate and @EndDate. In C# it would create a method and just call that method as well as the variables. However i'm not sure how to do that with sql functions. Could someone shed some light please? Thank you!
View Replies !
Quick SSIS Expression Builder Question
This works: len ((DT_WSTR,2)DATEPART("mm", getdate()))==1 ? (DT_WSTR,1)0 + (DT_WSTR,1)DATEPART("mm", getdate()) : (DT_WSTR,4)DATEPART("mm", getdate()) This doesn't: "dataware/"+ len ((DT_WSTR,2)DATEPART("mm", getdate()))==1 ? (DT_WSTR,1)0 + (DT_WSTR,1)DATEPART("mm", getdate()) : (DT_WSTR,4)DATEPART("mm", getdate()) I tried a couple of different things at this point, where am I missing the cast? Before len? Cast to what?
View Replies !
Using Expression Builder To Access A Dialy File Minus One Day
Hello, I am using the following expression to access a daily file. "D:\importdata\peregrinedata\ACD_DATA_" + (DT_WSTR, 4) YEAR( GETDATE() ) + (DT_WSTR, 2)MONTH( GETDATE() ) + (DT_WSTR, 2) DAY( GETDATE() ) + ".txt" output: D:importdataperegrinedataACD_DATA_2008123.txt I need to have this file by current day -1. I've tried dateadd and can't figure out how to get it to work. Thanks
View Replies !
Both Foreground And Background Color Appear To Be White In Expression Builder...
I have a co-worker who applied build 3161 to SQL Server 2005. The original problem was that the print preview was showing up as black.This is a documented issue with Microsoft here: http://support.microsoft.com/kb/935436 Now, when she goes into expression builder, the foreground AND background color for what you type in there appears to be white... so everything shows up as invisible. You can highlight the text you type and it shows up but otherwise it is obviously invisible. Anyone know how to fix this?
View Replies !
Cannot Access Expression Builder Via Variable Properties Window (no Ellipsis)
Has anyone encountered this before? My colleague opens up an SSIS package with variables, many of which use expressions built with the Expression Builder. She cannot bring up the Expression Builder by clicking the ellipsis (...) in the "Expression" Property of the Property Window, regardless of whether "EvaluateAsExpression" is set to True or False. I can open up this same package and use the Expression Builder just fine. Any ideas? Apologies if this has been answered already, I did many searches to try to find the solution before posting here. Thanks much! Brian Pulliam
View Replies !
Enabling Expression Builder For Custom SSIS DataFlow Source Component
Hi, I have implemented a custom source component that can be used as the data source in the Data Flow task. I have also created a custom UI for this component by using the IDtsComponentUI . But my component does not have the capability of setting the custom properties via the DTS Variables using the Expression Builder. I have looked around for samples on how to do this, but I can only find samples of how to do this for custom Control Tasks, i.e. IDtsTaskUI. My question is, How can implement the Expression Builder in my custom Source component + custom Source UI. Or do you know of any samples which I can look at. Thank you, Jameel.
View Replies !
Using SQL MOD Function
DECLARE @TotalAmount AS INTEGER DECLARE @TotalPoints As INTEGER SELECT @TotalAmount = SUM(Amount) From Rewards WHERE MemberID = @MemberID AND Redeemed = 1 @TotalPoints = @TotalAmount MOD 20 I have the code above but I keep getting an error message around the last line that I am not sure what it means. Can anyone please help?? Thanks in Advanc3e
View Replies !
Use Function In Asp.net
I have this scalar -valued function in sql called dbo.GetGoodCustomer ALTER function [dbo].[GetGoodCustomer](@client_id int)returns numeric(10, 2)asbegin declare @getgoodcustomer as numeric(10, 2) declare @review_type as int select @review_type = item_num from customers where client_id = @client_id if @review_type = 0 begin select @getgoodcustomer = getgoodcustomer from customers where client_id = @client_id end if @review_type > 0 begin select @getgoodcustomer = l.g from customers c inner join detail l on c.client_id = l.client_id where c.client_id = @client_Id and c.item_num = l.item_num endReturn @getgoodcustomerendNow, how can I use this function in asp.net (vb.net) ?Because whatever is getgoodcustomer would give me I need to display that in datagrid's text box.Loop thru datagrid If CType(dgItem.FindControl("txtID"), TextBox).Text = 13 ThenDim txtgoodcustomer As TextBox = CType(dgItem.FindControl("txtcust"), TextBox)basically amount returns from function would be showing in txtcust textbox.I don't know I have never done this before..
View Replies !
SQL AVG Function
I have a table:tblRateUserUserIDJudge intUserIDJudged intscore int this table contains the userid of the person giving a score (judge) and the person receiving the score (judged) and the score itself. Each user can only score another user once. tblRateUser content:judged judge score6 5 87 5 107 6 515 7 415 5 9 When a new score is inserted I want to get the average score for the rated user by counting all scores for that user and divide by the number of records. declare @avgscore decimal(4,1) set @avgscore=(select avg(score) from tblRateUser WHERE UserCodeJudged=@ID) print 'avg score: '+ cast(@avgscore as nvarchar(15)) in the above example this results for usercode judged 7 in an average score of 7.0 whereas I would expect 7.5 (10+5)/2 what am I doing wrong?
View Replies !
Last Function
SQL Server has no Last() and Max() pulls the Max not the last. Is there a way to pull the Last payment amount with the date of that payment, in SQL Server???? tblClients - ClientID (PK) tblPayments - PaymentID (PK) Thanks for any help,
View Replies !
SQL Function
hello everyone,I created a function in MSSQL that builds an html coupon based upon thecoupon ident (more complex than that, but that's not where i'm havingproblems!)When the function builds the coupon, it returns the stream asVarChar(8000)I am calling this function in a sproc that passes in the coupon ident.But when I execute this sproc, I get the following error:Syntax error converting the varchar value '<html>......"What am I doing wrong? Are there problems with the datatypes I"musing?Thanks in advance for any help on this?
View Replies !
Need A Function
I'm looking for a string function that is similar to the INSTRfunction in VB. I haven't seen anything in the help files that I canuse. Does anyone have any suggestions?Here's what I'm trying to do:There is a field in a table that will look something like this -"XXXXXX - YY".I want to separate it on the dash and get two strings out of it -"XXXXXX" and "YY". I'm trying to keep it all in a stored procedureand avoid a vb script or exe.I'm envisioning something like this:declare @CDT datetimeselect @CDT = createdatetime from imOrderHdrwhere VendorCode = 'SYG' and createdatetime is not nulland status in (1,2,3)select d.VendorStockNumber, substring(i.ItemDescription, 1,instr(iItemDescription, '-') - 1),substring(i.ItemDescription, instr(iItemDescription, '-') + 1),d.QtyOrdered, d.PurchasePrice, (d.QtyOrdered * d.PurchasePrice) asExtensionfrom imOrderDetail djoin imItem i on i.ItemCode = d.ItemCodewhere d.CreateDateTime = @CDTI'd write my own function, but the computers this will be run on haveSQL 7.Any suggestions will be appreciated.Thanks!Jennifer
View Replies !
Function
Good Morning,I have a question.Is there a function like ISNULL but for the space value(example ISSPACE)?THANKS
View Replies !
SP Vs FUNCTION
Hi, When we talk about the difference between SP and Function, it is said that Function returns a value where as SP doesn't. My Question is, Then what does the "OUTPUT" parameter Stands for in SP? Kindly make my doubts clear on this. Thanks In Adv. Rahul Jha
View Replies !
Last Function
Access has a last function available for when you are grouping on a summary level. What this does is return the last record for that particular grouping. Does sql have function of that nature? Example: Item_Code Time 123456 11:40 123456 11:41 123456 11:42 By grouping on item code and using the last function on the Time column, the third record would be returned. Item_Code Time 123456 11:42 All, help is appreciated.
View Replies !
Using A Function
I have found this very practicle function CREATE FUNCTION FN_TRANSLATE (@VALIN VARCHAR (8000), @FROM VARCHAR(256), @TO VARCHAR(256)) RETURNS VARCHAR (8000) AS BEGIN -- effets de bord IF @VALIN IS NULL RETURN NULL IF @FROM IS NULL OR @TO IS NULL RETURN NULL IF LEN(@VALIN) = 0 RETURN @VALIN -- initialisation DECLARE @I INTEGER DECLARE @OUT VARCHAR(8000) SET @OUT = '' -- lecture caractère par caractère SET @I =1 WHILE @I <= LEN(@VALIN) BEGIN IF PATINDEX('%' + SUBSTRING(@VALIN, @I, 1)+ '%', @FROM) > 0 BEGIN IF LEN(@TO) >= PATINDEX('%' + SUBSTRING(@VALIN, @I, 1) + '%', @FROM) SET @OUT = @OUT + SUBSTRING(@TO, PATINDEX('%' + SUBSTRING(@VALIN, @I, 1)+ '%', @FROM), 1) END ELSE SET @OUT = @OUT + SUBSTRING(@VALIN, @I, 1) SET @I = @I + 1 END RETURN @OUT END but how can I use it in MS SQL 2000-25000 ? FN_TRANSLATE(dbo.Users.Name, 'àâäçéèêëîïôöùûüÿ', 'aaaceeeeiioouuuy') doesnt work I get an error on the colum name dbo.Users.Name thank you
View Replies !
|
TRACKER
