SQL Statement To Compare And Return One Aggregated Value

Oct 18, 2006

Hello,

In my table, I have two columns - ForecastSales and ActualSales. I need
to write a query that returns me just one aggregate value (one row and
one column). If sum(ActualSales - ForecastSales) is negative, I need to
return "red." Otherwise, I need to return green.

I looked at CASE statement. However, I could not figure out an
efficient way to build this query. I would appreciate your help.

Thank you in advance for your help.

Pradeep

View 3 Replies

'Return' Statement In A Function,Get,or Operator Must Return A Value....Question

Aug 28, 2007

I'm a Reporting Services New-Be.

I'm trying to create a report that's based on a SQL-2005 Stored Procedure.

I added the Report Designer, a Report dataset ( based on a shared datasource).

When I try to build the project in BIDS, I get an error. The error occurs three times, once for each parameter on the stored procedure.

I'll only reproduce one instance of the error for the sake of brevity.

[rsCompilerErrorInExpression] The Value expression for the query parameter 'UserID' contains an error : [BC30654] 'Return' statement in a Function, Get, or Operator must return a value.

I've searched on this error and it looks like it's a Visual Basic error :

http://msdn2.microsoft.com/en-us/library/8x403818(VS.80).aspx

My guess is that BIDS is creating some VB code behind the scenes for the report.

I can't find any other information that seems to fit this error.

The other reports in the BIDS project built successfully before I added this report, so it must be something specific to the report.

BTW, the Stored Procedure this report is based on a global temp table. The other reports do not use temp tables.

Can anyone help ?

Thanks,

View 5 Replies View Related

Compare Rows In 2 Tables, Return Them If Different

Jan 9, 2008

Rows in table A that have a business status of 'open' are written to table B (on a different db) on a daily basis.

I have managed to extract the relevant rows in table A and table B.

What I now need to do is compare table A, row N with table B row N and return them both if the values differ.

What is the best way of doing this

e.g.

Table A

customerId amount
1 200
2 106 *return this row*
3 412

Table B (filtered by MAX(audit date))

customerID amount
1 200
2 100 *return this row*
3 412

?

View 6 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

Need A Compare Statement

Jan 26, 2005

Hello,
I have a table called INVENTORY.

The fields in the table are:
inventory_id
warehouse_id
item_id
slot
qty
on_hand_qty

The support tables are the item table and the warehouse table

I am trying to create a report showing the inventory of items of warehouse_id 1 with warehouse_id 2. If there is no inventory for either warehouse then suppress it. That in itself isn't that hard to do, but I would like to put them side by side, and that I'm having a hard time with.

I hope i didn't confuse the question, and any help would be greatly appreciated!

View 1 Replies View Related

SQL Statement To Compare Between Date

Nov 30, 2007

Hi Guys,
I have a table "HOLIDAY" with column "HOLIDAYSTART" and "HOLIDAYEND" in date/time type.User can apply new holiday on a form with value "STARTDATE" and "ENDDATE", but how do I check if the new holiday dates already exist in the table??
I looked around and found you can use something similar to following to check for single date,select * from holiday where ('1-12-2007' >= HOLIDAYSTART) AND ('1-12-2007' <= HOLIDAYEND)
but how would I do it with my situation above with between 2 dates? any help? Thanks in advance.

View 3 Replies View Related

String Compare In Select Statement

Apr 19, 2007

is there a way to have a select statement which compares a value is like ('%a%','%b%','%c%','%d%','%f%','%l%')

so :

select address
from customers
where address like ('%a%','%b%','%c%','%d%','%f%','%l%')

???

View 6 Replies View Related

Compare Two Table And Update - Join Statement

Apr 27, 2015

I compare two table with JOIN statement. Now I would like to update one of them base on result. How it to do?

View 2 Replies View Related

Primary Key In Aggregated View

Apr 1, 2004

Been pulling my hair out with this one for some time now ... hope someone out there can help :)

I have a database view which is an aggregated view of a number of tables. Trouble is I need to create what would effectively be a primary key for the view.

Can anyone suggest a sound way of doing this other than moving to Oracle :)

View 5 Replies View Related

How To Create An Aggregated Field

Apr 11, 2006

View 1 Replies View Related

Aggregated Members And Attributes

May 29, 2008

Hi,

the query below (from Adventure Works) displays the sales amount for three products and a custom member "aggregation" which is the aggregate of these three products, and it cross joins with the attribute "colour".

Code Snippet

with member [Product].[Product Categories].[Subcategory].&[31].[aggregation] as 'AGGREGATE({ [Product].[Product Categories].[Product].&[214], [Product].[Product Categories].[Product].&[215], [Product].[Product Categories].[Product].&[220] })'

SELECT { [Date].[Calendar].[All Periods] } ON COLUMNS ,

NON EMPTY { { { [Product].[Product Categories].[Product].&[214], [Product].[Product Categories].[Product].&[215], [Product].[Product Categories].[Product].&[220],[Product].[Product Categories].[Subcategory].&[31].[aggregation] } * { [Product].[Color].[All Products].CHILDREN } } } ON ROWS

FROM [Adventure Works]

WHERE ( [Measures].[Reseller Sales Amount] )

Can someone please explain me why I'm getting this result:

All Periods

Sport-100 Helmet, Red
Red
39328.1586

Sport-100 Helmet, Black
Black
12098.0788

Sport-100 Helmet, Blue
Blue
13331.5816

aggregation
Black
64757.819

aggregation
Blue
64757.819

aggregation
Red
64757.819 (note that 64757.819 is the total of the three products)

instead of something like this:

All Periods

Sport-100 Helmet, Red
Red
39328.1586

Sport-100 Helmet, Black
Black
12098.0788

Sport-100 Helmet, Blue
Blue
13331.5816

aggregation
Black
12098.0788

aggregation
Blue
13331.5816

aggregation
Red
39328.1586

and also if anyone knows of a possible way of getting the second type of result?

please note that if I create a custom member that aggregates members of any other level of the Product Category hierarchy, the problem doesn't exist (see code and results below)

Code Snippet
WITH MEMBER [Product].[Product Categories].[Category].&[4].[Aggregation] as
'AGGREGATE({ [Product].[Product Categories].[Subcategory].&[31],
[Product].[Product Categories].[Subcategory].&[32] })'
SELECT { [Date].[Calendar].DEFAULTMEMBER } ON COLUMNS ,
NON EMPTY { { { [Product].[Product Categories].[Subcategory].&[31],
[Product].[Product Categories].[Subcategory].&[32],
[Product].[Product Categories].[Category].&[4].[Aggregation]} * { [Product].[Color].[All Products].CHILDREN } } } ON ROWS
FROM [Adventure Works]
WHERE ( [Measures].[Reseller Sales Amount] )

All Periods

Helmets
Black
87915.3689

Helmets
Blue
91052.8681

Helmets
Red
79744.6953

Hydration Packs
Silver
65518.7485

aggregation
Black
87915.3689

aggregation
Blue
91052.8681

aggregation
Red
79744.6953

aggregation
Silver
65518.7485

View 1 Replies View Related

Non Aggregated Fields In Group By Clause

Jul 8, 2013

I'd like to have all distinct recordIDs with relevant text associated with them. Each record has 3 text boxes in different languages. Each text in different language is defined by an AttributeDefinitionID. This is my query:

Select a.entryID, g.GroupName, c.CategoryName as ExperienceType,
e.AttributeValue as EnglishWording,
e1.AttributeValue as GermanWording,
e2.AttributeValue as RussianWording,
From Entry as a
inner join entrycategory as b on b.entryid = a.entryid

[Code] ....

but in the results I get additional rows for each record even if the record doesnt have all three text boxes populated and there is only EnglishText for example.

EntryID GrouPName EnglishWording GermanWording RussianWording
1586 Red abc NULL NULL
1586 Red NULL NULL NULL
3566 Yellow NULL Hallo Welt NULL
3566 Yellow NULL NULL NULL
3566 Yellow Hello world NULL NULL
3566 Yellow Hello world Hallo Welt NULL

1586 should only return the first line with English wording.
3566 should return the last line that shows both English and German wording populated

View 19 Replies View Related

How Do I Best Make A Comparison Of Two Aggregated SELECT’s

May 9, 2006

Hi,

I have a question about how to best make a comparison of two aggregated SELECT��s. I'm using SQL Server 2005 on a Windows XP machine.

I��m doing a comparison between donor/gift information from one table and donor/gift information from another table. I have a difference of 7 records/gifts between the two tables. For whatever reason one table is supposedly Header level data and the other is Line level data and they are supposed to have the same number of records/gifts (not an issue here).

The aggregated SELECT��s, which will show the donor and the number of gifts each made, are listed below:

SELECT
[Account Number]
,count(*) as [Count]
FROM
Zk_HeaderMulti
GROUP BY
[Account Number]

--

SELECT
[Account Number]
,count(*) as [Count]
FROM
Zk_LineMulti
GROUP BY
[Account Number]

Is there a more efficient way to do this than the below listed method in which I use temp tables, join them and find the donor(s) with differing number of gifts between the header table and the line table?

=================================================

SELECT
[Account Number] as Line_Donor
,count(*) as LineGift_Num
INTO
#Line
FROM
Zk_LineMulti
GROUP BY
[Account Number]

--

SELECT
[Account Number] as Head_Donor
,count(*) as HeadGift_Num
INTO
#Header
FROM
Zk_HeaderMulti
GROUP BY
[Account Number]

--

SELECT
Line_Donor
,LineGift_Num
,Head_Donor
,HeadGift_Num
FROM
#Line A
JOIN
#Header Z
ON a.Line_Donor=z.Head_Donor
WHERE
LineGift_Num<>HeadGift_Num

==================================================

I thought I might be able to try and do it in a single query that doesn��t require two temp tables, but took the easy way out. Any suggestions?

View 3 Replies View Related

Get Value Of A Single Record Instead Of Aggregated Value With GROUP BY

Oct 12, 2007

How to get

Code Block

a record value instead of aggregated value with GROUP BY?

Assume that I have a PRODUCT_COMMENT table defined as below. It logs
the multiple comments for products. A product may have multiple
comments logged at different time.

Code Block

CREATE TABLE [dbo].[PRODUCT_COMMENT](
[COMMENT_ID] [int] IDENTITY(1,1) NOT NULL,
[PRODUCT_ID] [int] NOT NULL,
[COMMENT] [nvarchar](50) NULL,
[UPDATED_ON] [datetime] NOT NULL,
CONSTRAINT [PK_PRODUCT_COMMENT] PRIMARY KEY CLUSTERED
(
[COMMENT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[PRODUCT_COMMENT] WITH CHECK ADD CONSTRAINT
[FK_PRODUCT_COMMENT_PRODUCT] FOREIGN KEY([PRODUCT_ID])
REFERENCES [dbo].[PRODUCT] ([PRODUCT_ID])
GO
ALTER TABLE [dbo].[PRODUCT_COMMENT] CHECK CONSTRAINT
[FK_PRODUCT_COMMENT_PRODUCT]

I would like to use the following SQL statement to get the latest
comments for all products.

Code Block

SELECT PRODUCT_ID, COMMENT, UPDATED_ON
FROM PRODUCT_COMMENT
GROUP BY PRODUCT_ID
HAVING UPDATED_ON = MAX(UPDATED_ON)

But this leads to the following error:

Code Block

Column 'PRODUCT_COMMENT.UPDATED_ON' is invalid in the HAVING clause
because it is not contained in either an aggregate function or the
GROUP BY clause.

Is there a way to do that?

Thanks!

View 5 Replies View Related

Please Solve:-Row Summation...Multiple Column Values To Be Aggregated

Aug 27, 2005

Hello All:

I am not sure if I wrote the question's title appropriatly or not.....but anyways I wud try to explain the problem.

I am facing a problem solving the following task in MS Access (I believe it can surely be solved in access or SQL server).I am posting a sample table format here for reference and also postin the expected result table there-after.

The problem goes like this.(Read Carefully)

I have a table which contains a payment history with one row per account (each account number is unique) and related payment history for each account.

Sample Table:

AcctNo Dt1 P1 Dt2 P2 Dt3 P3 Dt4 P4 .........
------------------------------------------------------------------------------------------------------------
101 | 11/07/2002 | 10.00 | 11/07/2002 | 20.00 | 01/17/2003 | 120.00 | 01/25/2003 | 20.00
201 | 10/28/2003 | 30.00 | 10/28/2003 | 25.00 | 03/22/2004 | 130.00 | 03/22/2004 | 75.00
301 | 04/07/2005 | 40.00 | 04/23/2005 | 25.00 | 01/07/2001 | 140.00 | 01/07/2001 | 65.00
401 | 01/13/1999 | 50.00 | 01/13/1999 | 35.00 | 10/29/2002 | 150.00 | 10/17/2002 | 55.00
501 | 05/23/2001 | 60.00 | 05/02/2001 | 45.00 | 02/13/2000 | 160.00 | 02/13/2000 | 25.00
601 | 09/17/1998 | 70.00 | 09/15/1998 | 55.00 | 07/07/1998 | 170.00 | 07/07/1998 | 15.00
701 | 12/11/2000 | 80.00 | 12/29/2000 | 65.00 | 11/27/1999 | 180.00 | 11/28/1999 | 15.00

where Dt is date and P1/2/3/... is payment.There cud be 1 or more than 1 payments in a particular month as shown.

The original file has more than 200 columns for the payment i.e till Dt200,P200

I need to see aggregated monthly payment history for all accounts. i.e. a table for all accounts with related payments for each month (its OK if a day vary in particular month...consolidation shud be monthly) starting from the earliest to the latest possible.

Result:-

Acct Month Pay Month Pay ...........
----------------------------------------------------
101 | 11/2002 | 30.00 | 01/2003 | 140.00 ...........
201 | 10/2003 | 55.00 | 03/2004 | 205.00 ...........
301 | 04/2005 | 65.00 | 01/2001 | 205.00 ...........
401 | 01/1999 | 85.00 | 10/2002 | 205.00 ...........
501 | 05/2001 |105.00 | 02/2000 | 185.00 ...........
601 | 09/1998 |125.00 | 07/1998 | 185.00 ...........
701 | 12/2000 |145.00 | 11/1999 | 195.00 ...........

I hope I have tried to explain the problem in as much detail as possible.

Please help me with your valuable solutions to the above task ASAP.If u want i can also send in as attachment the original file i am workin at to ur email id

Thanks

View 1 Replies View Related

Statement Return Error

Jan 12, 2000

Hi!
I want to select just 1 day older rows from table.
I run next statement:

select*from report
where Date = ltrim (DATEPART (dd,GETDATE())>1)+'/'+
ltrim (DATEPART(mm,GETDATE()))+'/'+ltrim (DATEPART(yyyy, GETDATE()))

the datetime in the column Date is: dd/mm/yyyy

and here is error message:

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '>'.

I tried everything , but can't get rid of this error message.
Please help!

View 1 Replies View Related

The Statement Did Not Return A Result Set

Oct 28, 2007

I am using MSSQL 2000, SQL Server Developer Edition and product version is : 8.00.760 (SP3)

Application Server: JBoss v 4.0.4
Driver : sql 2005 driver.

Hi,

I am running my application in JBOSS and hibernate to do all the query to the DB. While we are testing our application, it is working fine for the 1st day of stress testing, however after running for one days, it starts throwing the following exception. This can occur in a number of method call with no fixed pattern. Anyone has any idea what is going on?

used by: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:392)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:333)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1114)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:756)
at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:63)
... 88 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:236)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
... 97 more

View 2 Replies View Related

Problem With Isnull. Need To Substitute Null If A Var Is Null And Compare It To Null And Return True

Sep 20, 2006

Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you

set ansi_nulls off

go

declare

@inFileName VARCHAR (100),

@inFileSize INT,

@Id int,

@inlanguageid INT,

@inFileVersion VARCHAR (100),

@ExeState int

set @inFileName = 'A0006337.EXE'

set @inFileSize = 28796

set @Id= 1

set @inlanguageid =null

set @inFileVersion =NULL

set @ExeState =0

select Dr.StateID from table1 dR

where

DR.[FileName] = @inFileName

AND DR.FileSize =@inFileSize

AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)

AND DR.languageid = isnull(@inlanguageid,null)

AND DR.[ID]= @ID

)

go

set ansi_nulls on

View 3 Replies View Related

Okay, I Thought I Had The Return Value From A Statement Figured Out...

Nov 15, 2007

  So, Jimmy G helped me out with it in showing a little bit how to do it. SqlCommand command = new SqlCommand(, object>)
SqlParameter param = new SqlParameter();
param.ParameterName = "@return";
param.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(param);

command.ExecuteNonQuery();

//get the return value
int val = int.Parse(command.Parameters[0].ToString());
Where I get lost is in the declaring of a new sqlcommand and sqlparameter. Can you please spell out where to use this and if I need to change my SQLdataSource. I currently was trying to use it in the OnClick of a button. What I had did the following
Protected Sub CreateIssue_Click(ByVal sender As Object, ByVal e As System.EventArgs)        dim returnValue as integer        'how do I get a return value from the stored procedure executed in 'insertissue.insert() here to a variable?             InsertIssue.Insert()        Response.Redirect("/addarticletoissue")            End Sub

again, thank you for your help and patience with such a beginner =)

View 2 Replies View Related

RETURN Of IF NOT EXISTS INSERT Statement

Feb 20, 2006

Hi @ all

I'm using MSSQL and PHP.
I've got the following sql statement:

$msquery = IF NOT EXISTS (SELECT SerienNr FROM tbl_Auftrag a WHERE a.SerienNr='PC8') INSERT INTO tbl_Auftrag (BMS_AuftragsNr, SerienNr, AuftraggNr, Zieltermin, Kd_Name) VALUES ('455476567','PC8','1','2006-3-2','Fritz')

The Statement itself works fine, but i've got a problem getting a return value whether the insert has succeed, or not. :confused:
mssql_query() always returns true if there occured no error in the statement. But i need to know if the insert procedded or not.
I tried:

$result = mssql_query($msquery);
$succeed = mssql_rows_affected ($result);

And:

$result = mssql_query($msquery);
$succeed = mssql_num_rows($result);

to get the rows, affected by the statement, but both return:

supplied argument is not a valid MS SQL-Link resource

Anyone an idea?

View 2 Replies View Related

Return SP In A SELECT Statement Column

Jan 13, 2005

I have a stored procedure which contains a complex scripting that is not an option to rewrite as a single SELECT statement.

I want the following output:

CatID | CatTitle | CatTree
001 | News | exec sp_DisplayTree(@CatID)

My code I tried doesn't work:

SELECT
C.CatID As CatID,
C.CatTitle As CatTitle,
CatTree = (exec sp_DisplayTree C.CatID)
FROM
Cats As C WITH (nolock)

I cannot find a solution to my solution, please help...

View 3 Replies View Related

SQL Case Statement To Return 0 For Empty Row

Jun 11, 2008

Hi,

I have some sql and i expect a row back with some information even if there is nothing. What i want to happen is when it returns an empty row then give me a 0 so at least i get something back I am filling a dataset here and it isnt populating the fields when an empty row is returned.

SELECT 3 row_id,
'2003' year,
'Mar' period,
(SELECT count(*) FROM news WHERE news.id IN (
SELECT news.id
FROM news
WHERE news.announced_date >= '2003-03-01' AND ........

So ive tried variations of the below in a CASE statement WHEN NUll or WHEN 0 but i still get nothing back. I would like the information to return 3 2003 Mar 0 if its empty.

SELECT 3 row_id,
'2003' year,
'Mar' period,
(SELECT CASE count(*) WHEN NULL THEN 0 ELSE count(*) END FROM news WHERE news.id IN (
SELECT news.id
FROM news
WHERE news.announced_date >= '2003-03-01' AND ............

Can anyone help me please??

View 8 Replies View Related

SQL Statement To Return First Unique Record

Jan 19, 2007

I've been searching and trying out SQL statements for hours and I'mstill having a problem.I'm querying 3 tables... and I only want the first unique recordreturned.Currently, the data returned looks like this..............New York New York ANew York New York BNew York New York CLos Angeles California ALos Angeles California BLos Angeles California CI want the select statement to return this instead...New York New York ALos Angeles California AI'm using MS SQL server.please help?thanks for your help.

View 4 Replies View Related

Return Primary Key On INSERT Statement

Jul 20, 2005

I am inserting a record into a table that automatically generatesunique ids (i.e. Primary Key). Is there anyway to return this id. As Iam using this on ASP.net page and I really need the ID to update thepage with the new details.I think on mysql there is something called LAST_INSERT_ID which doesthis.

View 10 Replies View Related

Return Number Of Rows In Select Statement As Value

Sep 3, 2007

Can anyone just point me in the right direction. All I want to do is add some T-SQL to an existing stored procedure to return the number of rows selected into a return value.Does anyone know how to do this?

View 4 Replies View Related

Return Select Statement Or Values Using SqlDataSource?

Sep 21, 2007

Hello all,
I have been working with a DetailsView control for the past week and it is a great control, but also lacks on some departments. Anyhow I need to know what the best approach for this scenerio would be?
I have a SqlDataSource"
<asp:SqlDataSource ID="SqlUpsertAffiliateDetails" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT am.affiliate_id AS AffiliateId, am.member_id AS MemberId, m.First_Name, m.Last_Name, am.category_id AS CategoryId, ac.category_name, am.profile_web_address AS WebAddress, am.profile_email_1 AS Email, am.comments AS Comments, am.date_modified FROM tAffiliateMaster AS am WITH (NOLOCK) INNER JOIN tAffiliateCategories AS ac WITH (NOLOCK) ON am.category_id = ac.category_id INNER JOIN rapdata..Member AS m WITH (NOLOCK) ON am.member_id = m.Member_Number WHERE (am.affiliate_id = @AffiliateId)"
UpdateCommand="spUpsertAffiliateProfile" UpdateCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="AffiliateId" QueryStringField="affiliate_id" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="Action" Type="Byte" DefaultValue="2" />
</UpdateParameters>
</asp:SqlDataSource>
And my SP:/* 09-19-07 Used to update affiliate profile */

CREATE PROCEDURE spUpsertAffiliateProfile
@Action tinyint,
@AffiliateId int,
@MemberId int = -1,
@CategoryId int,
@WebAddress varchar(50),
@Email varchar(50),
@Comments varchar(1500)
AS

SET NOCOUNT ON

-- Find errors first, check is not needed if deleting
IF @Action <> 3
IF NOT EXISTS (SELECT Member_Number FROM rapdata..Member_Association WHERE Member_Number = @MemberId AND Status = 'A' AND Association_ID = 'TRI' AND Bill_Type_Code LIKE '%AF%')
BEGIN
SELECT retval = 'A qualified member ID was NOT found. Action Failed.', errorcount = 1, 0 AS affiliate_id
RETURN
END
IF @Action = 1
IF EXISTS (SELECT member_id FROM tAffiliateMaster WHERE member_id = @MemberId)
BEGIN
SELECT retval = 'This member has already been listed. Action Failed.', errorcount = 1, 0 AS affiliate_id
RETURN
END

IF @Action = 1 AND @AffiliateId = 0-- insert
BEGIN
INSERT INTO tAffiliateMaster
(member_id, category_id, profile_web_address, profile_email_1, comments)
VALUES
(@MemberId, @CategoryId, @WebAddress, @Email, @Comments)

SELECT retval = 'Record Entered', errorcount = 0, @@IDENTITY AS affiliate_id
RETURN
END

ELSE IF @Action = 2 AND @AffiliateId > 0-- update
BEGIN
UPDATE
tAffiliateMaster

SET
category_id= @CategoryId,
profile_web_address=@WebAddress,
profile_email_1=@Email,
comments=@Comments

WHERE
affiliate_id = @AffiliateId AND member_id = @MemberId

SELECT retval = 'Record Updated', errorcount = 0, @AffiliateId AS affiliate_id
RETURN
END

ELSE IF @Action = 3 AND @AffiliateId > 0-- delete
BEGIN
DELETE
tAffiliateMaster

WHERE
affiliate_id = @AffiliateId

SELECT retval = 'Record Deleted', errorcount = 0, 0 AS affiliate_id
RETURN
END
GO

My question is how will I be able to return the retval? Will I need to do it within the code behind of the SqlDataSource Updated Event?
Thanks!

View 3 Replies View Related

Specifying Carriage Return/line Feed In An SQL Statement

Oct 19, 2001

Hi there, I hope you can help us.

We're using this statement to import some values into a database:
SQLStatement = "INSERT MyTable (ID, VALUE) VALUES ('" & IDString & "', '" & VALUEString & "')"

The problem is that the strings might contain carriage returns/line feeds, i.e.
VALUEString = "Line 1." & vbCrLf & "Line 2."

It only imports up until the first vbCrLf. We have tried replacing vbCrLf with "", but this doesn't work either. What is the correct format for inserting a value that contains new line characters?

Thanks for your time,
Lasse.

View 3 Replies View Related

Return Variable Name As Part Of Select Statement.

Mar 9, 2008

hey all,

I have the following query:

ALTER PROCEDURE [dbo].[sp_SelectMostRecentArticle]

AS
BEGIN

DECLARE @article_id INT
SELECT @article_id = (
SELECT TOP 1 article_id
FROM article
ORDER BY article_id DESC
)

DECLARE @comment_count INT
SELECT @comment_count = (
SELECT COUNT(comment_id)
FROM comment
JOIN article ON article_id = comment_article_id
GROUP BY article_id
HAVING article_id = @article_id
)

SELECT TOP 1 article_id, article_author_id,
article_title, article_body, article_post_date,
article_edit_date, article_status, article_author_id
article_author_ip, author_display_name,
category_id, category_name--, comment_count AS @comment_count

FROM article

JOIN author ON author_id = article_author_id
JOIN category ON category_id = article_category_id

GROUP BY article_id, article_title, article_body, article_post_date,
article_edit_date, article_status, article_author_ip,article_author_id,
author_display_name, category_id, category_name

HAVING article_id = @article_id

END
GO

as you can see, im trying to return a comment_count value, but the only way I can do this is by defining the variable.

I have had to do it this way, because I cannot say COUNT(comment.comment_id) AS comment_count or it returns an error that it cant reference the comment.comment_id.

But when change it to FROM article, comment; I get errors about the article_author_id and article_comment_id.

And i cant add a join, because it would return the amount of rows of the comment...

unless someone could help with what i Just decribed (as i would prefer to do it this way), how would i return the variable value as part of the select statement?

Cheers

View 6 Replies View Related

SQLServerException: The Statement Did Not Return A Result Set When Sp Uses Cursors

Jun 8, 2007

I'm trying to migrate to the 2005 JDBC driver against SQL Server 2000, but I'm getting the following exception: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set. This is similar problem to a past thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=413019&SiteID=1
The difference is that my stored procedure uses cursors. This same stored procedure works fine with the 2000 JDBC driver.

Does anybody have any ideas how to resolve this problem?

View 14 Replies View Related

Getting A Variable That Has A SELECT Statement To Return Results

May 21, 2006

I have concatenated a long Select Statement and assigned it to a variable. (i.e.)

@a = Select * from foo ---Obviously mine is much more robust

How do I execute @a to actually Select * from foo?

View 3 Replies View Related

Overloading Select Statement Return Values

Nov 14, 2007

Hey guys,

This is what I think and hope is a fairly straight forward SQL question

Essentially, I have a table which has the following columns that are relevent to my question:

PROJID
ACTIVITY_NAME
COMPLETION_DATE

Rows in this table are, for example:

PROJID ACTIVITY_NAME COMPLETION_DATE
1 Prepro 10/12/2007 3:42:30
2 Prepro 10/13/2007 9:16:27
2 QA 10/13/2007 10:00:01
2 Delivery 10/14/2007 09:31:12
etc.

So, really the key is the PROJID & the ACTIVITY_NAME (really, there's a unique column ID, but for this question, I'll leave it at that).

(Though this should be much easier to accomplish in code, the system is not built that way so) Is there a good way that I could return a status for a given PROJID based on whether a row exists for a given PROJID). In other words, ultimately, I would like to return something like this:

PROJID LAST_ACTIVITY
----------------------------------------------
1 Prepro
2 Delivery

where the activity order (in this case) is Prepro, QA, Delivery. So because a Delivery row exists for PROJID 2, then the LAST_ACTIVITY would return "Delivery" and because only Prepro exists for PROJID 1, the LAST_ACTIVITY returned would be Prepro

I really appreciate the help

Thanks,
Steve

View 3 Replies View Related

Why These Two SQL Statement Return Differently? Using ROUND() Function In Transact-SQL

Sep 19, 2007

Hello, DECLARE @x DECIMAL
SET @x = 65.554
SELECT ROUND(@x, 1)--this returns 66

SELECT ROUND(65.554, 1)--this returns 65.600 can someone explain to me why is like that?
Thank you

View 3 Replies View Related

Return NULL Values In SELECT Statement With INNER JOIN ?

May 16, 2005

If I try to run the code below, and even one of the values in the INNER
JOIN statements is NULL, the DataReader ends up with zero rows.
What I need is to see the results even if one or more of INNER JOIN
statements has a NULL value. For example, if I want info on
asset# 2104, and there's no value in the DriverID field, I need the
rest of the data to display and just have the lblDriverName by
blank. Is that possible?

<code>
    Sub BindSearchGrid()
        Dim searchUnitID As String
        Dim searchQuery As String
        searchUnitID = tbSearchUnitID.Text
        lblIDNum.Text = searchUnitID
        searchQuery = "SELECT * FROM Assets " & _
        "INNER JOIN Condition ON Condition.ConditionID = Assets.ConditionID " & _
        "INNER JOIN Drivers ON Drivers.DriverID = Assets.DriverID " & _
        "INNER JOIN Departments ON Departments.DepartmentID = Assets.DepartmentID " & _
        "INNER JOIN AssetCategories
ON AssetCategories.AssetCategoryID = Assets.AssetCategoryID " & _
        "INNER JOIN Store ON
Store.[Store ID] = Assets.StoreID WHERE RTRIM(Assets.[Unit ID]) = '"
& searchUnitID & "'"

        Dim myReader As SqlDataReader
        myReader = Data.queryDB(searchQuery)
        While myReader.Read
            If
Not IsDBNull(myReader("Store Name")) Then lblStrID.Text =
myReader("Store Name")
            If
Not IsDBNull(myReader("AssetCategory")) Then lblAsstCat.Text =
myReader("AssetCategory")
            If
Not IsDBNull(myReader("Condition Description")) Then lblCondID.Text =
myReader("Condition Description")
            If
Not IsDBNull(myReader("DepartmentName")) Then lblDepID.Text =
myReader("DepartmentName")
            If
Not IsDBNull(myReader("Unit ID")) Then lblUnID.Text = myReader("Unit
ID")
            If
Not IsDBNull(myReader("Year")) Then lblYr.Text = myReader("Year")
            If
Not IsDBNull(myReader("Make")) Then lblMk.Text = myReader("Make")
            If
Not IsDBNull(myReader("Model")) Then lblMod.Text = myReader("Model")
            If
Not IsDBNull(myReader("Mileage")) Then lblMile.Text =
myReader("Mileage")
            If
Not IsDBNull(myReader("Vin Number")) Then lblVinNum.Text =
myReader("Vin Number")
            If
Not IsDBNull(myReader("License Number")) Then lblLicNum.Text =
myReader("License Number")
            If
Not IsDBNull(myReader("Name")) Then lblDriverName.Text =
myReader("Name")
            If
Not IsDBNull(myReader("DateAcquired")) Then lblDateAcq.Text =
myReader("DateAcquired")
            If
Not IsDBNull(myReader("DateSold")) Then lblDtSld.Text =
myReader("DateSold")
            If
Not IsDBNull(myReader("PurchasePrice")) Then lblPrPrice.Text =
myReader("PurchasePrice")
            If
Not IsDBNull(myReader("NextSchedMaint")) Then lblNSM.Text =
myReader("NextSchedMaint")
            If
Not IsDBNull(myReader("GVWR")) Then lblGrVWR.Text = myReader("GVWR")
            If
Not IsDBNull(myReader("GVW")) Then lblGrVW.Text = myReader("GVW")
            If
Not IsDBNull(myReader("Crane Capacity")) Then lblCrCap.Text =
myReader("Crane Capacity")
            If
Not IsDBNull(myReader("Crane Certification")) Then lblCrCert.Text =
myReader("Crane Certification")
            If
Not IsDBNull(myReader("Repair Cost")) Then lblRepCost.Text =
myReader("Repair Cost")
            If
Not IsDBNull(myReader("Estimate Replacement")) Then lblEstRep.Text =
myReader("Estimate Replacement")
            If
Not IsDBNull(myReader("SalvageValue")) Then lblSalVal.Text =
myReader("SalvageValue")
            If
Not IsDBNull(myReader("CurrentValue")) Then lblCurVal.Text =
myReader("CurrentValue")
            If
Not IsDBNull(myReader("Comments")) Then lblCom.Text =
myReader("Comments")
            If
Not IsDBNull(myReader("Description")) Then lblDesc.Text =
myReader("Description")

        End While
    End Sub</code>

View 1 Replies View Related