A Function Smilar To DECODE Function In Oracle
I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...
ali
View Complete Forum Thread with Replies
Related Forum Messages:
DECODE Function
Is there anyone know a similar function in Sql Server 7 work as the same way that 'decode' function in Oracle does? Thanks is advanced! -Christine
View Replies !
Decode Function
HI, Is there any way to replicate the decode function in SQL Server 7.0? I have a problem where I have to write a query such that if the value in a column is '0' , I need to output it as 'No' and if it is '1', I need to output it as 'Yes'. An example of my problem is as follows: select col_1+","+(value 'No' if col_2='0' and 'Yes' if col_2='1')+ ","+col_3+........... I appreciate your help in advance. thanks, Sravan.
View Replies !
Urgent!!!!!!!! Decode Function In Sql
how can I write decode function in sql such that i have a feild in my table called "file type" based on its content i want to pass a value to sql datasource such that if file type = doc i want to pass "~/Images/ico_filetype_10.gif" an so on
View Replies !
Oracle NVL Function
Hi, I want to know if there is a function in SQL Server that do the same thing than the Oracle function NVL() In Oracle we can do the following thing : select nvl(last_name,first_name) from table_test; This have the effect that if the last_name field is NULL, it will display the first_name Is there a function like this in SQL 7 Thank Martin
View Replies !
Equallent To Decode In Oracle?
Hi, Here is my oracle statement. How can I change it to be compatible with SQL Server? update propertytable set visible =decode(propertyid, 1,0, 2,0, 3,1, 5,1, 6,1, 7,0, 9,1, 10,1, 11,0, 14,1, 30,1, 38,1, 60,0, 232,0, 233,0, 415,1, 605,0) where parentid between 2000006001 and 2000006020 Thanks...
View Replies !
How To Translate Oracle Decode
Hi, How to translate oracle Decode without changed code I mean: I have one application and instead to change all decode to case when I would like just replace decode for dbo.decode, so I Wrote this function select dbo.fnDecode( 1 , 2 , 3 ) first parameter always int, and the others parameters could be char, int or float. I would like to test first value and return second or third value -> sql_variant for all parameters, ok but I can use + or - I can't do this select dbo.fnDecode( 1 , 2 , 3 ) + dbo.fnDecode( 1 , 2 , 3 ) If I put cast ok, but I is there another way, overload this call? With clr doesn't work, because Sql Server doesn't accept function overload calls from C# Any ideia???????????????????? cheers,
View Replies !
Oracle Translate Function Equivalent In SQL Server
Hi I want to know the equivalent of the Oracle translate function in SQL Server. eg : select translate('entertain', 'et', 'ab') from dual. I tried the SQL Server Replace function , but it replaces only one character or a sequence of character and not each occurrence of each of the specified characters given in the second argument i.e 'et'. Please let me know if there is some other equivalent function in SQL Server thanks.
View Replies !
How To Use The OLEDB Command To Call A Oracle Function?
HI, I want to use the OLEDB command to call a oracle function, but i havnt found any materials about how to do that, my oracle function is as below: CREATE OR REPLACE function GET_ZONEID_FROM_SYFZ(ycz varchar2,xc varchar2,strat_id varchar2) return varchar2 IS zone_id_result varchar2(10) ; begin PKG_DM_DQ.GET_ZONEID_FROM_SYFZ(ycz,xc,strat_id,zone_id_result); return zone_id_result; end; In OLEDB command transformation component, i fill the sql command with "select GET_ZONEID_FROM_SYFZ(?,?,?) from dual", but i dont have it worked. The error message is :provider can not derive parameter information and setparameterinfo has not been called. Who have any idea about how to make it work? Thanks ~~
View Replies !
Oracle Translate Function Equivalent In SQL Server
Hi I want to know the equivalent of the Oracle translate function in SQL Server. eg : select translate('entertain', 'et', 'ab') from dual. I tried the SQL Server Replace function , but it replaces only one character or a sequence of character and not each occurrence of each of the specified characters given in the second argument i.e 'et'. Please let me know if there is some other equivalent function in SQL Server thanks.
View Replies !
Timeout Error While Executing Oracle Function
Hi, I have developed reports for Oracle database. I wanted to avoid use of OUT REF cursor hence am using function & calling it from reports. The logic in SP/function is so complex that it takes 5 mins to execute n get the result. I see the results on the report server, But when I call that report from web application, I get System.Net.WebException - Time out error. My questions: 1. What setting I make on report server to avoid timeout? --Are these settings applied on report server restart? 2. Is there any setting needed for Datasource also? TIA Tanmaya
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 !
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 !
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 !
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 !
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 !
SUM Function
I have this query: SELECT SUM(st_Count) FROM Queue WHERE st_ID = 1483 It works, but how can I change it, to see SUM for many ID's, which I have in list? I try this: SELECT SUM(st_Count) FROM Queue WHERE st_ID = List.tw_ID But there is error: The multi-part identifier "List.tw_ID" could not be bound. How to display SUM for meny ID's?
View Replies !
Len() Function
We have len() function in some of our stored procedures. Why one of our SQL servers (6.5) will not recognize this function while others do? While we transfer the stored procedures, the stored procedure can not be transfered to the database of that server. Your immediate help will be appreciated. Su
View Replies !
MTD Function
Is there a SQL MTD function ! Let's I want to delete MTD rows. Need something like: delete from abc where date_entered = mtd Suggestions are appreciated.
View Replies !
Max Function
Hi, I am having some difficulty with the Max function. I would expect it to return one row when you use it in a select statement (as long as there is truly one unique row) Executing the query below give me the following result select * from lead_with where id= 80430 ORDER BY 1 DESC id firm covacc avgFinal ----------- ----------- ----------- ---------------------------------------- 80430 122 0 8.000000 80430 122 29353 9.000000 80430 505 NULL 8.500000 80430 2045 NULL 7.500000 80430 122 18815 9.000000 80430 223 NULL 8.500000 80430 625 NULL 8.000000 Executing the next query give me the following results select MAX(avgFinal), IBFirm from lead_with where [ID] = 80430 group by avgFinal,IBFirm order by avgFinal desc IBFirm ---------------------------------------- ----------- 9.000000 122 8.500000 223 8.500000 505 8.000000 122 8.000000 625 7.500000 2045 Not only does it return more than one row, it actually misses a row from above 80430 122 18815 9.000000 I guess I am missing something. What I am trying to do is get the row with the maximum value in the avgFinal column. Any help is appreciated. Thanks, James
View Replies !
DTS Function
Hi I'm creating DTS packages to load data from text files. I'm finding that a lot of the data needs to be transformed in the same way (e.g trailing "." removed). Rather than writing individual activex scripts to do this, is it possible to write my own DTS function which I call from my DTS packages? Thanks Andy
View Replies !
VBA Function In SQL ??
Hi, I am upgrading an AccessXP database to SQL 2000 and have an issue with week numbers. There is currently a piece of VBA that calculates the week number for time recording purposes using the DatePart function as DefaultVal_WeekNo = DatePart("ww", TaxDate, vbMonday, vbFirstFourDays) I have tried to get this replicated in SQL by using declare @TaxDate as datetime declare @TheWeek float set @TaxDate = @TheDate -90 -7 set @TheWeek = cast(datepart("ww",@TheDate) as numeric (30)) - cast(datediff("wk", @TaxDate, @TheDate) as numeric (30)) but this is not reurning the correct values, i belive this is because the VB function has the vbMonday argument. Can anyone tell me how i can get wround this please? TIA
View Replies !
Function
I have a function dbo.Validlan which is takes 4 paramentes: par1,par2,par3,par4 I would like to select all 4 at run time: SELECT par1,par2,par3,par4 FROM table1 t1 INNER JOIN table2 t2 ON t1.enrl=t2.enrl
View Replies !
Help With MAX Function ?
I have the following problem statement: Quote: What was the most expensive merchandise item sold in July? Display sale date, sale price and merchandise description. Sort output by descending sales price. (hard-coding date is OK). Here's my query statement: Code: SELECT saleDate AS "Sale Date", salePrice AS "Sale Price",description AS "Merchandise Description"FROM Sale, SaleItem, MerchandiseWHERE salePriceAND DATENAME(MONTH, dateBorn) = 'July'ORDER BY salePrice DESC I want to know how I would use the MAX function on my query since I know this would give me the most expensive item. Am I writing my query statement correctly? Do I have to declare the MAX function in the SELECT statement and on the WHERE statement of just on the WHERE statement? Any clarifications/help would be appreciated
View Replies !
How To Use IN Function..
Hi.. I need to use IN function in a sql prodecure.. my procedure is below Code: CREATE PROCEDURE [dbo].[Cat] @gets nvarchar(200) AS begin select * tbl where id in (@gets) order by id desc end GO the type of id field is identity number also I tried @gets int gets is called from my asp page. in asp page, I use prodecure like below Code: ids="11,12,13" Rs.Open "Cat '"&ids&"'", db,1,3 it doesn't work! I get the following error Code: Microsoft OLE DB Provider for ODBC Drivers (0x80040E07) [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value '11,12,13' to a column of data type int.
View Replies !
Like Function
Hello Is there a way to do something like this? select column1 from tablename where column1 not like '%(select col1 from table2)%' i am trying to save my time with not writing too many not like condition Help Appreciate
View Replies !
Max Function
Hi Guys How can i change data_type of a table from varchar to timestamp. I have also lot of data in that table. When i create a the table i just put vchar for that column and now i have to perform max function on that date column. for example table_1 ID serial employee_id date vchar name vchar query select max(date) from table_1 group by employee_id I don't get the max date. Any help Thanks
View Replies !
Use MAX Function ???
I have to use Select MAX(COL) FROM TABLE I have to increment ID column value each time by 1 so that each row has a unique identity. Please let me know how do we do that. using above statement Thanks
View Replies !
Sum Function
I have a cube which as a time dimension by year (2001 through 2007). I have set up a report that allows user to select which years to see...However, I also have some additional calculations that require that I sum the number of accounts over the time period that they selected. Sum([Time Dimension].[Close Year].[All],[Measures].[Number of Accounts])...If the user selected years 2003 through 2006, I was expecting that this formula would give me only the sum of those accounts for those specific years, however it gives me the sum of accounts for all years in the cube (2001-2007) even though 2001,2002 and 2007 were not selected. I assume it is because of the [All], but is there a function that I could use to only look at what was selected in the parameter.
View Replies !
SUM Function
I'm trying to sum a string column that looks like this hhhh:mm 152:20 103:15 - 101:45 Trouble is there are "-" in the result set being returned Anyone have ideas how i could do this? The result has to be in the same format Thanks Dave
View Replies !
Top N Function
Hi, I am using Reporting Services to display the Top N with N set by the user through a Parameter. It works fine to the extent when the values are not the same so will take top 10 but when there are several values the same say for instance Row Id Value 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 10 9 11 9 12 9 At present If i wanted the Top 10 it would also include all the 9's rather than just the 2 Nine's that would give me top 10 values. Is there any way in Reporting Services in which this can be done? Many Thanks
View Replies !
Function?
Hey all.... It is possible to get the ID on this way? or how can i do that? Code SnippetSELECT dtPartition.partitionid FROM dtPartition WHERE dtPartition.status like ('0') The reason is: "c34f5321_95d9_41fc_b98a_192eda1ea0d3" but now my problem....how do i write the ID behind a command? like this: dtEvent_c34f5321_95d9_41fc_b98a_192eda1ea0d3 my query: Code Snippet SELECT dtEvent.EventNo as "EventID:", dtPrincipal.struser as "User:", FROM dtEvent_HereShouldStayTheId AS dtEvent, dtMachine, dtPrincipal_HereShouldStayTheIdToo AS dtPrincipal WHERE dtMachine.id = dtEvent.agentmachineid AND dtPrincipal.id = dtEvent.primaryuserid AND dtEvent.eventno IN ('529') I think it is possible with a function but i don't know how can i realize it... Mhh thats also a half solution...: Code Snippetdeclare @table1 nvarchar(max); declare @table2 nvarchar(max); Set @table1 = (SELECT dtPartition.partitionid FROM dtPartition WHERE dtPartition.status like ('0')) Set @table2 = (SELECT dtPartition.partitionid FROM dtPartition WHERE dtPartition.status like ('0')) SELECT dtEvent.EventNo as "Event ID:", dtEvent.creationtime as "Erstellt am:", dtPrincipal.struser AS "User Account:", dtMachine.description AS "Computer:", FROM (dtEvent_+@table1) AS dtEvent, dtMachine, (dtprincipal_+@table2) AS dtPrincipal, WHERE dtMachine.id = dtEvent.eventmachineid AND dtPrincipal.id = dtEvent.primaryuserid AND dtEvent.eventno IN ('528') Thanks!
View Replies !
|
TRACKER
