Retrieving Scalar Or Calculated Values From Stored Procedures With C#
Feb 4, 2007
I am trying to build an Sql page hit provider. I am having trouble getting a count back from the database. If I use ExecuteScalar it doesn't see any value in the returned R1C1. If I use ExecuteNonQuery with a @ReturnValue, the return value parameter value is always zero. Ideally I would like to use a dynamic stored proceudre if there are any suggestions for using them with C#. My table has rvPathName, userName and a date. I have the AddWebPageHit method working so I know data connection and sql support code in provider is working. I think the problem is either in how I am writing the stored procedures or how I am trying to retrieve the data in C#. Any help with this will be greatly appreciated.
View 5 Replies
ADVERTISEMENT
Jan 24, 2008
Hi all,
I tried to create a CSV file using Bulk Copy Program (BCP) and Stored Procedures: BCP executed from T-SQL using xp_cmdshell. I have the following sql code executed in my SQL Server Management Studio Express and error message:
--scBCPcLabResults.sql--
declare @sql varchar(8000)
select @sql = 'bcp ChDbLabResults out
c:cpChDbLabResults.txt -c -t, -T -S' + @@.SQLEXPRESS
exec master..xp_cmdshell @sql
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@@".
=========================================================================================
--scBCPcLabResults.sql--
declare @sql varchar(8000)
select @sql = 'bcp ChDbLabResults out
c:cpChDbLabResults.txt -c -t, -T -S' + @@SQLEXPRESS
exec master..xp_cmdshell @sql
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@@SQLEXPRESS".
===================================================================
I copied this set of code from a tutorial article that says "@@servername". My Sql Server is SQLEXPRESS, so I put @@.SQLEXPRESS or @@SQLEXPRESS in the code of scBCPcLabResults.sql.
I do not know why I got an error {Must declare the scalar variable "@@"} or {Must declare the scalar variable "@@SQLEXPRESS"}!!!??? Please help and advise me how to solve this problem.
Thanks in advance,
Scott Chang
View 3 Replies
View Related
Dec 20, 1999
Help!
I have converted my Access Jet database to a SQL Server database. In Access, I had many functions that I created to use in my queries. SQL Server does not allow for functions to be created. So, I had to create procedures in SQL Server to perform what my functions did.
I am trying to use the procedures I created on calculated fields within my SQL statement in my view. It returns an error stating that the procedure is not recognized.
How can I create functions to be used in my query?
Thanks.
View 1 Replies
View Related
Apr 8, 2008
Hello there!
I've been sitting here thinking how to work through this problem but I am stuck. First off I have two tables I am working with.
Request TablerqstKey (this is the primary key and has the identity set to yes)entryDte datetimesummary nvarchar(50)etryUserID nvarchar(50)rqstStatusCdeKey bigint
ReqStatusCode TablerqstStatusCdeKey bigintstatusCode nvarchar(50)
I have webforms that are using Session variables to store information from the webpages into those variables and insert them into these tables. However, I am wanting to insert the rqstStatusCdeKey (from the ReqStatusCode Table) value into the Request Table. The rqstStatusCdeKey value i am wanting to insert is '3' which stands for "in Design" (((this is going to be the default value))). I need help :-/ If you need me to explain more/ clarify, and show more information I can. Thank you so much!!!
View 4 Replies
View Related
May 1, 2006
I created one stored proc, then copied it to create another similar stored proc with just some filtering changes in the second. Now I want to obtain the results from both like this:
ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Group Name PostedAmount
---------------------------------------------------------------------------------------------------------------------------
Row# 1 from current stored proc
Row # 2 from called stored proc
so something like this I should get in the end for example when both results are combined:
ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Group Name PostedAmount
---------------------------------------------------------------------------------------------------------------------------
100000 33455 200 300 345555 4455555 5666666 Arizona 56000
103400 22455 900 700 777555 3333555 5444666 Illinois 660000
The first stored proc (CurrentMonthCollections_AZ) attempts to include the results of the second stored proc at the end (CurrentMonthCollections_IL) by calling it and inserting the fields into a temp table.
I was told by Angel to instead convert the second stored proc into a UDF...but having trouble with this.
What is the best approach to get the desired 2 rows back that I need in that fasion?
Here is the code for the 2 stored procs (very long so I will post as as links):
http://www.webfound.net/CurrentMonthCollections_AZ.txt
http://www.webfound.net/CurrentMonthCollections_IL.txt
Look at the end of CurrentMonthCollections_AZ.txt to see where I'm stuck in trying to select the results (ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Var1 PostedAmount
) from both stored procs.
I don't think UNION is what I want because it will combine stuff...I just want 2 separated rows
View 7 Replies
View Related
May 12, 2007
Hi frdz, I m creating my web-application in asp.net with C# 2005 and using sql server 2005. I have created the stored procedure for the insert,update. I want to know how to write the mathematical calculations in the stored procedure.. Pls tell me from the below stored procedure were i m making the mistake ?? As the discount and the total amount are not calculated by itself....and stored in the database How to convert the @discpercent numeric(5,2) to@discpercent ="NoDiscount" should be displayed when no discount is being given to the customers.... ALTER PROCEDURE CalculationStoredProcedure
@accountid int output,
@accountname varchar(20),
@opbal numeric(10, 2),
@opbalcode char(2),
@total numeric(10, 2),
@clbal numeric(10, 2),
@clbalcode char(2),
@discpercent numeric(5,2),
@discamt numeric(10, 2)
as
begin
set nocount on
if @opbal IS NULL OR @opbal = 0
begin
select @opbal=0
select @opbalcode= ' '
select @clbal= 0
select @total= 0
select @clbalcode= ' '
@discpercent ="NoDiscount"
@discamt=0
end
select @accountid = isnull(max(accountid),0) + 1 from accountmaster
select @total=@opbal - @clbal from accountmaster
select @discamt=@total* @discpercent/100 from accountmaster
begin
insert into accountmaster
(
accountname,opbal,opbalcode,clbal,clbalcode
)
values
(
@accountname,@opbal,@opbalcode,@clbal,@clbalcode
)
end
set nocount off
end
Thanxs in adv...
View 7 Replies
View Related
Mar 3, 2008
Please excuse me if this question has been asked before; I couldn’t find it. Perhaps someone could point me to the solution.
A few years ago I wrote an order-entry application in classic ASP that I am now re-writing in ASP.NET 2.0. I am having very good success however I can’t figure out how to retrieve data from a stored procedure.
I am using the FormView & SqlDataSource controls with a stored procedure to insert items into an order. Every time an item is inserted into the order the stored procedure does all kinds of business logic to figure out if the order qualifies for pricing promotions, free shipping, etc. If something happens that the user needs to know about the stored procedure returns a message with this line (last line in the SP)
SELECT @MessageCode AS MessageCode, @MessageText AS MessageText
I need to retrieve both the @MessageCode and the @MessageText values in my application. In classic ASP I retrieved these values by executing the SP with a MyRecordset.Open() and accessing them as normal fields in the recordset.
In ASP.NET I have specified the SP as the InsertCommand for the SqlDataSource control. I have supplied all the parameters required by my SP and told the SqlDataSource that the insert command is a “StoredProcedure�. Everything actuly works just fine. The items are added to the order and the business logic in the SP works fine. I just have no way of telling the user that something important has happened.
Does anyone know how I can pickup these values so that I can show them to my users? Bassicly if @MessageCode <> 0 I want to show @MessageText on the screen.
View 10 Replies
View Related
Jul 23, 2005
I'm generating a list of parameters needed by stored procedures, and
I'd like to know which ones have default values assigned to them.
To retrieve the parameter information I use:
sp_sproc_columns @Procedure_Name='InsertUser''
However, the column that is supposed to give the default value,
'COLUMN_DEF' always returns as NULL, even when that column has a
default value assigned to it.
i.e.
CREATE PROCEDURE InsertUser@UserID INT = 10,.....
And then if I do a sp_sproc_columns @Procedure_Name='InsertUser'', the COLUMN_DEF value for the @UserID column is still NULL.
Does anyone know what I'm doing wrong and how I can retrieve the default value?
Thanks
View 1 Replies
View Related
Sep 3, 2007
Hi allI hv made a stored procedure which printsvarious messages using Print statement(shown in bold)------------------------------------------------------------------------------ .....if (@current_date<@ed) and (@current_date>@sd) begin print 'Date Lies Between Boundary Limits' select * from membership where uid=@uid end else begin if(@pipe=1) begin if(@plan_id=1) begin print 'Monthly Plan Activated' update membership set start_date=@opt_sd,end_date=DateAdd(M,1,@opt_sd),status=@opt,pipeline=0,user_option='',plan_id=null,download_limit=20 where uid=@uid select * from membership where uid=@uid end else begin print 'Weekly Plan Activated' update membership set start_date=@opt_sd,end_date=DateAdd(D,7,@opt_sd),status=@opt,pipeline=0,user_option='',plan_id=null,download_limit=10 where uid=@uid select * from membership where uid=@uid end end end --------------Now I want to retrieve the messages disp by these Print statements in my asp.net page where i m calling this stored proc.Pls suggest RegardsMunish
View 2 Replies
View Related
Jun 23, 2015
In a calculated column I am trying to get a scalar text value from a lookup to another table. This works quite well when getting numerical values with the following formula:
=MinX(Filter(LookUpTable;LookupTable[from]<=MySourceTable[Day] && LookupTable[to]>=MySourceTable[Day]);LookUpTable[numericalColumn])
But as soon as I substitute the numerical column by a string column, #error results.
I also want to mention that the above query yields only one row as a result. It should be simple to return the value of one of the columns but after searching for quite some time, I could not find any function for that.
View 5 Replies
View Related
Aug 17, 2006
I need to somehow filter the results of my stored procedure to return the distinct "OrderNum" values. I'm using SQL database and I'm look for a way to alter the results from the stored procedure. My stored procedure rptOpenOrderLines currently returns all invoices (items under a OrderNum). I want to somehow filter those results to return one and only one of those "OrderNum" variables from the invoices. The tricky part is that I need to somehow find a way to do this without going into my database and directly altering the SQL stored procedure. I would be happy for any recommendations/ideas. Thanks!
View 3 Replies
View Related
Aug 29, 2006
Version: ASP.Net 2.0Language: C#Hello Everyone!I'm saving data from a function to an SQL Server 2000 db via a stored procedure. There are a few fields which are conditional and thus need not be passed to the procedure everytime. Under those circumstances I need to store null values in the fields.The problem is if I the value of the parameter as null, VS throws the error that a required parameter is not passed. I tried setting 'IsNullable' property of the parameter to true but it works for only those fields who are declared NULL in the stored procedure (For e.g. @DOB DATETIME = NULL).Strangely, this works well in classic ASP.Does anyone know how to accomplish this? Note, altering stored procedures is not an option for me.Thanks a lot!
View 4 Replies
View Related
Jan 29, 2004
Hi, i am trying to return two values from SQL 2000 using a single stored procedure. The stored working fine in Query Analyser and returns the two values and two grids in the results window.
My problem is that when i execute the stored procedure using ADO.Net the dataset only has one of the values. e.g TId : 2, where it should read 'TId' : 2, 'ConfigPath': 'C:lah'
Please could anyone shed ligth on this problem?
here the code for the stored procedure:
CREATE PROCEDURE dbo.GetTillInfo
(
@TillIdR varchar(50),
@Password varchar(50)
)
AS
declare @TillId int
declare @configpath varchar(150)
IF Exists (SELECT Id FROM Tills WHERE TillRef=@TillId and TillPassword=@Password)
BEGIN
set @TillIdR = (SELECT Id FROM Tills WHERE TillRef=@TillId and TillPassword=@Password)
select @TillIdR as 'TId'
set @configpath = (SELECT configpath from customer,tills where
tills.customerid = customer.id and tills.id = @login)
select @configpath as 'ConfigPath'
END
ELSE
BEGIN
set @TillIdR = 0
select @TillIdR as 'TId'
set @configpath =''
select @configpath as 'ConfigPath'
END
GO
View 1 Replies
View Related
Oct 25, 2000
If I have a stored proc which has 2 input variables and for example
the 2nd one is null or not provided what is the best way to turn this null
into a wildcard. If the second one is an int and not provided should I
but a checknull statement in the proc and convert this to a wildcard %. I believe if I do I need to change the where clause "where field1 = @var "
to where field1 like @var". Any input appreciated
View 1 Replies
View Related
Jul 4, 2006
Hi All,i hv created a sp asCreate proc P @iClientid varchar (100)asBeginselect * from clients where CONVERT(VACHAR(100),iClientid) in(@iclientid)endwhere iclientid = int data type in the clients table.now if i pass @iclientid as @iclientid = '49,12,112'but this statement throws an conversion error ( int to char error).is there any way to fetch records from a select statement using astring???Thanks in Advance.
View 3 Replies
View Related
Jun 22, 2007
How to return multiple values from stored procedures to reports in sql server 2005
View 5 Replies
View Related
Feb 11, 2008
Hi all,
In my SQL Server Management Studio Express (SSMSE), pubs Database has a Stored Procedure "byroyalty":
ALTER PROCEDURE byroyalty @percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @percentage
And Table "titleauthor" is:
au_id title_id au_ord royaltyper
172-32-1176
PS3333
1
100
213-46-8915
BU1032
2
40
213-46-8915
BU2075
1
100
238-95-7766
PC1035
1
100
267-41-2394
BU1111
2
40
267-41-2394
TC7777
2
30
274-80-9391
BU7832
1
100
409-56-7008
BU1032
1
60
427-17-2319
PC8888
1
50
472-27-2349
TC7777
3
30
486-29-1786
PC9999
1
100
486-29-1786
PS7777
1
100
648-92-1872
TC4203
1
100
672-71-3249
TC7777
1
40
712-45-1867
MC2222
1
100
722-51-5454
MC3021
1
75
724-80-9391
BU1111
1
60
724-80-9391
PS1372
2
25
756-30-7391
PS1372
1
75
807-91-6654
TC3218
1
100
846-92-7186
PC8888
2
50
899-46-2035
MC3021
2
25
899-46-2035
PS2091
2
50
998-72-3567
PS2091
1
50
998-72-3567
PS2106
1
100
NULL
NULL
NULL
NULL
////////////////////////////////////////////////////////////////////////////////////////////
I try to do an ADO.NET 2.0-VB 2005 programming in my VB 2005 Express to get @percentage printed out in the VB Form1. I read some articles in the websites and MSDN about this task and I am very confused about "How to Work with Output Parameters & Report their Values in VB Forms": (1) Do I need the Form.vb [Design] and specify its properties of the object and classes I want to printout? (2) After the SqlConnectionString and the connection.Open(), how can I bring the value of @percentage to the Form.vb? (3) The following is my imcomplete, crude draft code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI;"
Dim connection As SqlConnection = New
SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("byroyalty", connection)
command.CommandType = CommandType.StoredProcedure
...................................................................
..................................................................
etc.
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From the above-mentioned (1), (2) and (3), you can see how much I am lost/confused in attempting to do this task. Please help and give me some guidances and good key instructions for getting the output parameter printed out in the FORM.vb in my VB 2005 Express project.
Thanks in advance,
Scott Chang
View 11 Replies
View Related
Mar 27, 2006
Hello to all!
I have a table name stored in a scalar variable (input parameter of my stored procedure). I need to run SQL statement: SELECT COUNT (*) FROM MyTable and store the result of my query in a scalar variable:
For example:
declare @countRows int
set @countRows = (select count(*) from MyTable)
The problem is that the name of MyTable is stored in the input variable of my stored procedure and of corse this does not work:
declare @countRows int
set @countRows = (select count(*) from @myTableName)
I also tried this:
declare @sqlQuery varchar(100)
set @sqlQuery = 'select count(*) from ' + @myTableName
set @countRows = exec(@sqlQuery)
But it looks like function exec() does not return any value...
Any idea how to solve this problem?
Thanx,
Ziga
View 3 Replies
View Related
Nov 14, 2006
What is the best code pratice to use do the following code,
SELECT
fo.no as LNum,
fo.name as LName,
sum(CASE fo.docnome WHEN "In" THEN fo.etotal ELSE 0 END) as In1,
sum(CASE fo.docnome WHEN "In2" THEN fo.etotal ELSE 0 END) as In2,
sum(In1+In2)/10 as inDec,
from fo group by fo.no,fo.name order by fo.name
instead of
SELECT
fo.no as LNum,
fo.name as LName,
sum(CASE fo.docnome WHEN "In" THEN fo.etotal ELSE 0 END) as In1,
sum(CASE fo.docnome WHEN "In2" THEN fo.etotal ELSE 0 END) as In2,
((sum(CASE fo.docnome WHEN "In" THEN fo.etotal ELSE 0 END))+sum(CASE fo.docnome WHEN "In2" THEN fo.etotal ELSE 0 END)))/10 as inDec,
from fo group by fo.no,fo.name order by fo.name
I cant use functions and procedures. Is there any better and cleaner way to code this, reusing the calculated values?
View 1 Replies
View Related
Dec 11, 2001
Can someone please reply w/ example syntax on how to receive calculated variables from an invoked SP. DESCRIPTION:
sp_caller invokes sp_calc_values (passing @var1, @var2) via:
exec sp_calc_values @var1, @var2
sp_calc_values receives @var1, @var2 then calculates @var3, @var4
HOW does sp_caller receive the calculated values @var3, @var4 ???
thx in advance
View 2 Replies
View Related
Jul 20, 2005
Hi everybody,I need some help on calculated values in my database design.I'm creating an accounting / business management application with theusual modules like A/R, A/P, Inventory, etc.I don't know how to handle calculated values in fields like "CustomerBalance", "Inventory Item Qty on Hand", "Inventory Item Qty Last cost"and other similar.I don't want to create fields in the database to store these valuesbut I want to create them on the fly instead. Another thing I preferto do is use code in my application (vb.net) not T-SQL to do this(stored procedure or views), because this way I'm moving businesslogic into SQL; so far all the logic is located in a Business RulesDLL.My question is how can I do that and at the same time achieve "easy"joins.For example:Every time I retrieve some customer's info I need his balance. Let'ssay if my query is:SELECT CustomerID, CustomerName, Address from CustomersWhere CustomerID = ‘xxxxxx'How can I retrieve the balance, when is calculated from other fieldsin other tables, such as Invoices, Credit Memos and Cash Receipts?I thought to use views and add a join to the above query. Is that goodor bad, performance wise?What other options I have?Looking forward for your answer.Thanks in advanced.Stefanos
View 3 Replies
View Related
May 22, 2008
Hi all
I have a calculated fields in report designer such as a + b + c + d. In sql server if I run this query
select a + b + c + d from table1
and any of a,b,c or d is null, the result is null.
whereas in calculated fields, it does not return null but infact ignores the null value and treats it as zero.
I want my calculated field to be null if any of the values are null.
Is it possible? I cannot use the isNothing function because I have too many fields and it will be quite cumbersome.
Thanks
View 4 Replies
View Related
Jan 24, 2008
Hi,
Does anyone know how the key influencers impact values are calculated? Thanks!!
Kate
View 3 Replies
View Related
Dec 12, 2007
Hi,
is there any way to fill up tables with calculated valued? My Source Table is build-on like this example:
CustNo / Year / Variable / Value
100 / 2005 / var1 / 321.90
100 / 2005 / var2 / 44.20
100 / 2006 / var1 / 12.09
100 / 2006 / var2 / 7.91
101 / 2005 / var1 / 23.78
101 / 2005 / var2 / 67.22
My Target Table should have this result:
KPI / CustNo / Year / Value
KPI1 / 100 / 2005 / 366.10
KPI1 / 100 / 2006 / 20.00
...
What I want to accomplish? I want to add var1 and var2 from Customer 100 in Year 2005, but is this possible with standard tasks in SSIS?
bye
Jonas
View 6 Replies
View Related
Sep 21, 2015
We have a client that wants to take the Max value of a count at the Product level, but then for each Brand (Parent of product), sum the Max values for that particular slice. The example below shows the Max sales per month over the last 6 months.Â
Prod1 had it's best month in Month2 (20 units) and Prod2 had its best month in Month4 (30 units). Brandx should roll up to 50 units. Can DAX handle this in a single Calc Measure?
Brandx         50
   Prod1       20
   Prod2       30
Brandy         60
   Prod5       40
   Prod6       20
View 2 Replies
View Related
Jan 14, 2008
Hi all,
I put "Northwind" Database in the Database Explorer of my VB 2005 Express and I have created the following stored procedure in the Database Exploror:
--User-defined stored procedure 'InsertCustomer'--
ALTER PROCEDURE dbo.InsertCustomer
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
)
AS
INSERT INTO Customers
(
CustomerID,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
Region,
PostalCode,
Country,
Phone,
Fax
)
VALUES
(
@CustomerID,
@CompanyName,
@ContactName,
@ContactTitle,
@Address,
@City,
@Region,
@PostalCode,
@Country,
@Phone,
@Fax
)
=================================================
In my VB 2005 Express, I created a project "KimmelCallNWspWithAdoNet" that had the following code:
--Form_Kimmel.vb--
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Kimmel
Public Sub InsertCustomer()
Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _
"Initial Catalog=northwind;Data Source=NAB-WK-EN12345"
Dim connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()
Try
Dim command As SqlCommand = New SqlCommand("InsertCustomer", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@CustomerID", "PAULK")
command.Parameters.Add("@CompanyName", "Pauly's Bar")
command.Parameters.Add("@ContactName", "Paul Kimmel")
command.Parameters.Add("@ContactTitle", "The Fat Man")
command.Parameters.Add("@Address", "31025 La Jolla")
command.Parameters.Add("@City", "Inglewoog")
command.Parameters.Add("@Region", "CA")
command.Parameters.Add("@Counrty", "USA")
command.Parameters.Add("@PostalCode", "90425")
command.Parameters.Add("@Phone", "(415) 555-1234")
command.Parameters.Add("@Fax", "(415 555-1235")
Console.WriteLine("Row inserted: " + _
command.ExecuteNonQuery().ToString)
Catch ex As Exception
Console.WriteLine(ex.Message)
Throw
Finally
connection.Close()
End Try
End Sub
End Class
==============================================
I executed the Form_Kimmel.vb and I got no errors. But I did not get the new values insterted in the table "Custermers" of Northwind database. Please help and tell me what I did wrong and how to correct this problem.
Thanks in advance,
Scott Chang
View 10 Replies
View Related
Nov 1, 2004
Anyone know if it's possible to retrieve a parameterized value from dynamically executed SQL???
Code Example
Declare @table varchar(25)
Declare @somevalue varchar(3)
Set @table = 'sometable'
Set @somevalue = 'somevalue'
Declare @sqlBuild varchar(2000)
Set @sqlBuild = 'DECLARE @return varchar(3); ' +
' SELECT @return = COUNT(COLUMNAME) ' +
' FROM ' + @table +
' WHERE ' +
' value = ' + @somevalue
exec (@sqlBuild)
i want to be able to extract the value of @return for later use. this procdure works fine but I need to grab that value somehow.
Any suggestions?
View 3 Replies
View Related
Oct 26, 2015
I have the following MDX Query:
Select {measures.[Dollars]} on 0,
non empty
[Divisions].[Division].[All].Children *
[Cost Centres].[Cost Centre].[All].Children
[Locations].[Locations].[All].Children
on 1
From MyCube
which produced the following table:
Division
 Cost Centre
 Location
 Dollars
AA
1
X
$30.00
[code]....
What I am hoping to do is create a set out of the Union of specific values in the [Cost Centres].[Cost Centre] and [Locations].[Locations] hierarchies into a single set and use that new set in my MDX query across the columns.
Using the table and query from above, I have the following conditions that would determine the value in the set (lets call the new set 'NewSet')
When Cost Centre = 1 and Location = X Then "CustomType1"
When Cost Centre = 1 and Location = Y Then "CustomType2"
When Cost Centre = 1 and Location = Z Then "CustomType3"
When Cost Centre = 2 and Location = Y Then "CustomType4"
When Cost Centre = 2 and Location = Z Then "CustomType5"Else "Default"
Then, if I was to execute the new query:
with
set NewSet as "Some Unknown Magic Here"
Select {measures.[Dollars]} on 0,
non empty
[Divisions].[Division].[All].Children *
{NewSet}
on 1
From MyCube
I would end up withÂ
Division
 NewSet
Dollars
AA
CustomType1 Â
$166.64
AA
CustomType3Â
$64.24
BB
[code]....
View 2 Replies
View Related
Oct 8, 2007
Hi,
I have a table with 3 colums and col1 and col2 are datetime columns as follows
ID Col1 Col2
----------------------------------------
1 D1 NULL
2 D2 D3
3 NULL D4
I want to retrive the ID along with the latest date from either of remaining columns.
Can any one let me know the query please??
~Mohan
View 6 Replies
View Related
Jul 23, 2005
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies
View Related
Sep 10, 2015
Question in review today is Creating a Report showing the FName, LName of all Employees not Specified in a region; I would assume "No Value to be Null" Correct?
Or is there another way for me to do this?
This is what I have so far...What am I missing that this is not showing me results?
Select Firstname, Lastname, Region
From Employees
WHERE Region LIKE 'null%';
View 3 Replies
View Related
Feb 25, 2008
Hi - I'm new to SSRS/SQL and have a situation I can't figure out. I've tried a number of things, but I'm not sure the best way to return a calculated column based on the structure of my tables.
I have a primary Detail table that tracks product testing results. The Detail records need to be calculated against a "Year" table that contains a set of values by year. This table contains a single record for each year. There isn't a direct key that joins these two tables.
The Detail table looks like:
Record ID: 9999
PO Number: 12345
Date: 12/1/2007
Test Result: 1.52
The Year table looks like:
Record ID: 1
Start Date: 10/6/2007
End Date: 6/14/2008
LowVal1: 1.0
HighVal1: 1.49
Incentive1: .05
LowVal2: 1.50
HighVal2: 1.59
Incentive2: .06
and so on...
The Detail records need to find the correct Year record based on the Detail date, find the correct value within the LowVal - HighVal range based on the Test Result value, and then multiply the Test Result by the correct Incentive value and return that value.
I was able to find the correct LowVal-HighVal range using an Expression in the report column. Now that I need multiple years, I can't figure out the best way to configure the query.
Any suggestions would be appreciated. Thanks in advance!
View 3 Replies
View Related
Jul 16, 2015
I have a nested query that retrieves a min value. I would like to know how do i retrieve the corresponding time with that.
my data looks like this
valuevaluetime
1212/31/14 14:51
12.42/19/15 23:30
[highlight=#ffff11]10.92/21/15 6:40[/highlight]
13.21/25/15 20:47
My min value is 10.9 and i need the date 02/21/15
my nested query is as follows
( select min(cast(f.valuestr as float))
from bvfindings f
where f.ObjectName = 'Hematocrit'
and f.sessionid = s.sessionid
and f.ValueTime > s.open_time)
the above returns me the 10.9
i modified the query
select min(cast(f.valuestr as float)),
f.valuetime
from bvfindings f
where f.ObjectName = 'Hb'
but i get all values then.
View 6 Replies
View Related
