Two Select Statements In SP Return No Value In Web Form (only)

Mar 29, 2008

Hello. I  a SP with 2 select statements. If the first returns no results it does the second select. This works fine if I test it on the sql server. But when I try it in a webform using a sqldatasource it only returns a result if the first Select returns rows. If not It is an empty set (where I know it should not be. It returned resultrs on the SQL server). This is the code:1 ALTER PROCEDURE [SPName]
2
3 @SearchFor varchar(150) -- search string to compare with
4
5 AS
6
7 SELECT p.UserName,
8
9 (Select ImageName from ProfileImages Where IsMainImage = 1 And ProfileID = p.ProfileID) as ImageName,
10
11 (SELECT COUNT(*) FROM auditions WHERE UserID = p.ProfileID) AS AuditionCount
12
13 FROM Profiles p WHERE UserName = @SearchFor
14
15 IF (@@ROWCOUNT = 0)
16
17 Begin
18
19 SELECT p.UserName,
20
21 (Select ImageName from ProfileImages Where IsMainImage = 1 And ProfileID = p.ProfileID) as ImageName,
22
23 (SELECT COUNT(*) FROM auditions WHERE UserID = p.ProfileID) AS AuditionCount,
24
25 DIFFERENCE(p.UserName, @SearchFor) as Similarity
26
27 FROM profiles p WHERE SOUNDEX(p.UserName) = SOUNDEX( @SearchFor )
28
29 ORDER BY Similarity
30
31 END
32
33 RETURN

Now If i change the SP to test it to the code below, it does work in  both the webform and on the sql server: 1 ALTER PROCEDURE [SPName]
2
3 @SearchFor varchar(150) -- search string to compare with
4
5 AS
6
7 DECLARE @RowCount int
8 SET @RowCount = 0
9
10 IF (@ROWCOUNT = 0)
11
12 Begin
13
14 SELECT p.UserName,
15
16 (Select ImageName from ProfileImages Where IsMainImage = 1 And ProfileID = p.ProfileID) as ImageName,
17
18 (SELECT COUNT(*) FROM auditions WHERE UserID = p.ProfileID) AS AuditionCount,
19
20 DIFFERENCE(p.UserName, @SearchFor) as Similarity
21
22 FROM profiles p WHERE SOUNDEX(p.UserName) = SOUNDEX( @SearchFor )
23
24 ORDER BY Similarity
25
26 END
27
28 RETURNOnce again, Everything works as it should (for both versions), if I am testing it directly on the SQL server. Can anyone Help me on this? I am stumped and cannot find any answers about this. 


 

 

View 3 Replies


ADVERTISEMENT

Transact SQL :: Declaring Cursor Causing Select Statements Included Within A Function Cannot Return Data To Client?

Sep 29, 2015

I cannot find the problem with this function.

ALTER function [Event].[DetermineTrackTime](@TrialID varchar(max)) returns int as
begin
Declare @ret int;
Declare @EnterVolumeTime int;
Declare @ExitVolumeTime int;
Declare @StartTrackTime int;

[code]....

I am getting the following error on line 75:

Select statements included within a function cannot return data to a client.

This is happening when declaring TrackUpdateCursor

The compiler has no problem with the VolumeTimesCursor. What is causing this and what can I do about it?

View 20 Replies View Related

How To Make The SSMSE To Return Whole Records Without Any Close Query Form And Re-create Query Form Operation?

Dec 25, 2007

Hi,
I got a problem.
I installed Microsoft SQL Server Management Studio Express 2005 version.
And I created a Compact database.
I created an connection in SSMSE to connect the database and opened a query form.
then, i run the following sql:

Select * from Table1

It returned 3 records to me.
After that, I used program to insert record into this table.
Then i ran this sql again, it still show me 3 records.
I closed the query form, and re-created a new query form, then run the sql, it returned 4 records to me.

Why? It's very strange and difficult to operate, right?
Is there anyone know how to make the SSMSE to return whole records without any close query form and re-create query form operation?

Thanks a lot!

And Merry X'max!!!

View 4 Replies View Related

Return Statements In Scalar Valued Functions Must Include An Argument

Jan 20, 2006

I'm trying to create a SQL server 2000 function that returns a scalar value, but I keep getting the error "Return statements in scalar valued functions must include an argument". Online clarification of this error message is no help at all.I've tried all sorts of combinations of the following, without much luck. Can someone point out my dim-witted mistake, please?ALTER FUNCTION dbo.intCoursesPublic (@intCatID as int)  RETURNS  intASBEGIN RETURN     SELECT COUNT(intCourseID) AS Expr1        FROM    dbo.tbl_guru_course_list            WHERE     (intCatID = @intCatID)END

View 4 Replies View Related

Multiple CASE Statements - Three Fields To Be True In Order To Return Y

Feb 19, 2014

How do I properly write a CASE statement for the following business logic:

I want three of the following fields to be true in order to return 'Y'.

For example:

If field name 'Accepted' = 1 AND field 'StepNo' = '1' and field 'Visit' = 'V1'

Otherwise, I want it to return 'N'.

I have tried the following code below and it is not working.

, CASE WHEN Accepted = '1' AND StepNo ='1' AND Visit ='V1'
THEN 'Y' ELSE 'N' END AS 'StatusQ (Col N)'

View 2 Replies View Related

RETURN Statements In Scalar Valued Functions Must Include An Argument ERROR

Aug 16, 2006

Hi,

I am trying to write a function which takes a string as input and returns the computed value.

I need to use the output of this function as a coulmn in another select query.

Here is the code (Example: @Equation = '(100*4)+12/272')

create function dbo.calc(@Equation nvarchar(100))
returns float
as
begin

return exec('SELECT CAST('+@Equation+' AS float)')
end

I am getting this error when i compile it

"RETURN statements in scalar valued functions must include an argument"

Any suggestions would be appreciated.

Please respond

Thanks

View 6 Replies View Related

SQL Server 2014 :: Error - O2SS0245 / The Conversion Of Cursors In Return Statements Is Not Supported

Jun 25, 2015

fix the below SP which is having cursor output with an alternate logic which can make the SP work.(may be using temp table or any other option)

CREATE PROCEDURE Get_IDS
/*
* SSMA warning messages:
* O2SS0356: Conversion from NUMBER datatype can cause data loss.
*/
@p_uid float(53),
@return_value_argument varchar(8000) OUTPUT

[code]....

View 1 Replies View Related

Select Form Table And Exclude Another

Mar 18, 2005

Hello,

I've a query wich with I select buildings form a table.
I've a second table with a few building I don't want in my select (exclude form select).
How can I make this?

Thx a lot.
Dom

PS: I'm a sql newbie...

View 2 Replies View Related

Select Form 1 Table And Insert Into Another

Jan 30, 2004

Can someone point me in the right direction or show me a sample that may assist me in the following.

Thanks,
-dw


I need to select some data from 1 table and insert it into another using a stored procedure.

I also would like to select data from table 1 and insert a new record into table 1 modifying a field with new data passed by parameter.

Is it something like:

parameters passed = @selectJobNumber, @newJobNumber


declare @DeliveryMethodIDint

SELECT
@DeliveryMethodID=DeliveryMethodID,
... etc - more fields

FROM
jobDeliveryAddress

WHERE
(JobNumber= @selectJobNumber)


INSERT INTO [jobDeliveryAddress]
(
[JobNumber],
[DeliveryMethodID],
... etc - more fields

)
VALUES
(
@newJobNumber,
@DeliveryMethodID,
... etc

)

View 2 Replies View Related

Select Form 1 Table And Insert Into Another

Jan 30, 2004

Can someone point me in the right direction or show me a sample that may assist me in the following.

Thanks,
-dw


I need to select some data from 1 table and insert it into another using a stored procedure.

I also would like to select data from table 1 and insert a new record into table 1 modifying a field with new data passed by parameter.

Is it something like:

parameters passed = @selectJobNumber, @newJobNumber


declare @DeliveryMethodIDint

SELECT
@DeliveryMethodID=DeliveryMethodID,
... etc - more fields

FROM
jobDeliveryAddress

WHERE
(JobNumber= @selectJobNumber)


INSERT INTO [jobDeliveryAddress]
(
[JobNumber],
[DeliveryMethodID],
... etc - more fields

)
VALUES
(
@newJobNumber,
@DeliveryMethodID,
... etc

)

View 6 Replies View Related

How To Form SQL Select Queries Using Drop Down Lists??

Nov 8, 2006

Hi
 Will somebody please explain how  to combine asp.net dropdown lists to write
a  SQL database select query. I am using VWdeveloper and C Sharp.
For example, say I have  3 dropdownlists on my  webpage  as below,
List 1, Cities, London, Rome,  Barcelona etc
List 2, Restaurants by  Type, Italian, chinese, Indian etc
List 3, Number of tables/ seats 10-20,  20- 40, 50  -100
I want someone to be able to  search for a restaurant by selecting  an  item from  each dropdownlist
such as, "Barcelona" "Italian" "50-100"
This search query would return all the Italian restaurants in Barcelona with  50-100  tables/seats.
I  would also like the select query to work even if one of the dropdownlists items  is not selected.
Hope  somebody can clear this up?
Also would sql injection attacks be a threat by doing it this way?
Thanks all
 
 
 

View 9 Replies View Related

Select Query Form Multiple Databases

Dec 14, 2007

How do I write an sql statement to link tables in two databases and query them? For example: SELECT db1.table1.field1, db2.table1.field1 FROM Table1 INNER JOIN db1.table1.field1 ON db2.table1.field1 = db1.table1.field1
 
Thanks

View 6 Replies View Related

Several Select Statements?

Jan 16, 2007

Hello, how can i merge together several select statements?
I have something like this:
CREATE PROCEDURE Forum_GetThreads @ID int,@AscDesc bitASBEGINSET NOCOUNT ON;SELECT * FROM forum_ansageSELECT * FROM forum_topics WHERE (status = 0) ORDER BY (created) DESCIF (@AscDesc = 0)BEGIN      SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) DESCENDELSEBEGIN      SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) ASCENDHere i want to merge them all together and return only one SELECT statement with all the data

View 5 Replies View Related

How To Use Two Select Statements

May 12, 2008

Both of these work fine separately; How do I join these two Select Statements?

SELECT MPI.CREATE_DT,MPI.MPI_NBR, MPI.LAST_NM,MPI.FIRST_NM,
MPI_CURRENT_ADDR.ADDR_NBR, MPI_CURRENT_ADDR.ADDRESS_1, MPI_CURRENT_ADDR.ADDRESS_2,
MPI_CURRENT_ADDR.CITY,MPI_CURRENT_ADDR.STATE_CD,MPI_CURRENT_ADDR.ZIP_CD,MPI_CURRENT_ADDR.PHN_NBR,
MPI_PERSON.BIRTHDAY,MPI_PERSON.SEX

FROM MPI,MPI_CURRENT_ADDR,MPI_PERSON

WHERE (MPI.MPI_NBR=MPI_PERSON.MPI_NBR) AND (MPI.ADDR_NBR=MPI_CURRENT_ADDR.ADDR_NBR)
AND
MPI.CREATE_DT>=20070101
ORDER BY MPI.CREATE_DT

SELECT PATIENT.PAT_NBR,PATIENT.PHYS_NBR, PHYSICIAN.FIRST_NM,PHYSICIAN.LAST_NM,PHYSICIAN.DE_NBR, PHYSICIAN.SALUTATION_CD
FROM PATIENT, PHYSICIAN
WHERE PATIENT.PHYS_NBR=PHYSICIAN.PHYS_NBR


Thanks!
Lisa

View 1 Replies View Related

Two Select Statements

Sep 13, 2007

I have a table that list Canadian provinces and American States it looks something like this:

ID | ProvState

Under ID 1-13 lists the Canadian provinces and everything over 13 lists the American states. I want to create 1 query that will list the Canadian provinces first in alphabetical order then the States in alphabetical order.

I have tried using UNION but it's not returning what I want and it does not allow me to use order by for the first statement.

SELECT * FROM SPProvince WHERE ID < 14 ORDER BY ProvState
UNION
SELECT * FROM SPProvince WHERE ID > 13 ORDER BY ProvState

Anyone have any suggestions to this problem?

View 4 Replies View Related

Select Statements

May 13, 2008

Arnie and All others. Thanks for your help.
The previous case became lenghty and then just mixed up a lot.

To make it easy I have created two temp tables and wrote to test select statement .

You will notice that I tired two select statement but they are giving different set of result however the 2nd Select statement not giving the result as should be looking at the following requirement.


--Selected record must RaType='b'
-- PlanID='H321'
-- Gender='0'
--
--And not to include in select if any one of these meets:
-- Hosp='1' in other words it has to be 0
-- ESRD='1' or Rafctor Type in ('g','f') in other words ESRD should be 0 and rafctorType in ('h','i')
-- Dod is not null in other words Dod has to be null
--


--copy from here

GO
Create table #MyTable

( RowID int IDENTITY,
RD varchar(10),
RAType varchar(5),
History varchar(15)
)

INSERT INTO #MyTable VALUES ( '1', 'A', '1111' )
INSERT INTO #MyTable VALUES ( '2', 'S','2222' )
INSERT INTO #MyTable VALUES ( '3', 'D', '2345')
INSERT INTO #MyTable VALUES ( '4', 'I2','1234' )
INSERT INTO #MyTable VALUES ( '5', 'C','3333' )
INSERT INTO #MyTable VALUES ( '1', 'B','4444' )
INSERT INTO #MyTable VALUES ( '2', 'X','5555' )
INSERT INTO #MyTable VALUES ( '1', 'D' ,'66666')
GO

Go
Create Table #MYTable2

(
RowID int IDentity,
RD varchar(10),
RaType varchar(5),
History varchar(15),
PlanID varchar(6),
Hosp varchar(2),
ESRD varchar(2),
RafctorType varchar(3),
gender varchar(5),
dod varchar (5) NULL

)

INSERT INTO #MyTable2 VALUES ( '1', 'A', '1111', 'H321', '0','0', 'g', '0' ,NULL)
INSERT INTO #MyTable2 VALUES ( '2', 'b', '2222', 'H321', '0','0', 'e', '0',NULL )
INSERT INTO #MyTable2 VALUES ( '2', 'b', '3333', 'H321', '0','0', 'f', '0',NULL )
INSERT INTO #MyTable2 VALUES ( '2', 'b', '4444', 'H321', '0', '0','d', '0',NULL )
Go


Select #MYtable2.History from #MYTable2
INNER JOIN #mytable on #myTable2.History=#mytable.history
Where #MyTable2.RaType='b' And PlanID='H321' And Gender='0' And Hosp<>'1' And ESRD<>'1' AND RafctorType Not in ('g','f') AND Dod is NULL

Select #Mytable2.History from #MyTable2
INNER JOIN #mytable on #mytable2.History=#mytable.history
where #mytable2.Ratype='b' AND PlanID='H321' AND Gender='0' AND(Hosp<>'1') or ((ESRD<>'1') or (RafctorType Not in ('g','f')) OR DOD is NULL)


Go

drop table #Mytable2
Drop table #MYtable

View 5 Replies View Related

How To Create SELECT QUERRY FOR A CHANGE ORDER FORM In ASP?

Jun 6, 2007

 Hello.
Hope you are all well and having a good day.
I've got a problem i would like you to help me out with. My
company has got a client who sells meat and my senior tech lead
has customized the cart to fit their needs and have created a
despatch table with a "simple asp form" that allows them
to scan their products in with a wi-fi scanner.

The despatch table has the following fields:
1. OrderID
2. Product
3. Quantit
4. sellbydate
5. traceabilitycode and
6. Weight
Question 1: how do i create a form in asp that allow a user to
view the order no, product and quantity
celibate,traceabilitybycode and Weight after scanning the
details into the simple form created?
Question 2: How can i create an asp form that allows users to
search for the Order that populates the orderid, Product,
quantity?
Question 3: How can i create an asp for that allows users to
search for Product with orderid,traceabilitycode and quantity?
Question 4: How can i create an asp form that allows users to
Trace: Orderid, traceabilitycode, sellbydate and Quantity?

Please find below the source code of both the:
despatchLotsRowsSimpleForm.asp that allow them to scan details
into the table and the despatchLotsExec.asp:

THE FORM:
<!#include file="../includes/settings.asp">
<!#include file="../includes/getSettingKey.asp">
<!#include file="../includes/sessionFunctions.asp">
<!#include file="../includes/databaseFunctions.asp">
<!#include file="../includes/screenMessages.asp">
<!#include file="../includes/currencyFormat.asp">

<%
on error resume next

dim mySQL, connTemp, rsTemp

' get settings
pStoreFrontDemoMode =
getSettingKey("pStoreFrontDemoMode")
pCurrencySign = getSettingKey("pCurrencySign")
pDecimalSign = getSettingKey("pDecimalSign")
pCompany = getSettingKey("pCompany")

pAuctions = getSettingKey("pAuctions")
pAllowNewCustomer = getSettingKey("pAllowNewCustomer")
pNewsLetter = getSettingKey("pNewsLetter")
pStoreNews = getSettingKey("pStoreNews")
pSuppliersList = getSettingKey("pSuppliersList")
pRssFeedServer = getSettingKey("pRssFeedServer")

%>
<b><%=getMsg(10021,"despatch")%></b>
<br>
<%=getMsg(10024,"Enter despatch ...")%>


The despatchExec.asp Page
<%
' WebShop 3.0x Shopping Cart
' Developed using ASP
' August-2002
' Email(E-Mail address blocked: See forum rules) for further information
' (URL address blocked: See forum rules)
' Details: add e-mail to newsletter list
%>

<!--#include file="../includes/settings.asp"-->
<!--#include file="../includes/getSettingKey.asp"-->
<!--#include file="../includes/databaseFunctions.asp"-->
<!--#include file="../includes/stringFunctions.asp"-->
<!--#include file="../includes/miscFunctions.asp"-->
<!--#include file="../includes/screenMessages.asp"-->
<!--#include file="../includes/sendMail.asp"-->

<%

on error resume next

dim mySQL, connTemp, rsTemp, pEmail

' get settings

pStoreFrontDemoMode = getSettingKey("pStoreFrontDemoMode")
pCurrencySign = getSettingKey("pCurrencySign")
pDecimalSign = getSettingKey("pDecimalSign")
pCompany = getSettingKey("pCompany")

pEmailSender= getSettingKey("pEmailSender")
pEmailAdmin= getSettingKey("pEmailAdmin")
pSmtpServer= getSettingKey("pSmtpServer")
pEmailComponent= getSettingKey("pEmailComponent")
pDebugEmail= getSettingKey("pDebugEmail")
pGiftRandom= getSettingKey("pGiftRandom")
pPercentageToDiscount= getSettingKey("pPercentageToDiscount")
pStoreLocation = getSettingKey("pStoreLocation")

' form
pidOrder = lcase(getUserInput(request.form("idOrder"),50))
pProduct = lcase(getUserInput(request.form("product"),50))
pQuantity = lcase(getUserInput(request.form("quantity"),50))


pPriceToDiscount = 0

' insert despatch

mySQL="INSERT INTO despatch2 (idOrder, product, quantity ) VALUES ('"
&pidOrder& "', '" &pProduct& "', '" &pQuantity&
"')"

call updateDatabase(mySQL, rstemp, "despatchExec")

pBody=getMsg(10025,"Despatch confirmed...") &VBcrlf&
getMsg(311,"To opt-out click at:") & " (URL address blocked: See
forum rules)="&pEmail

response.redirect "redit_message.asp?message="&Server.Urlencode(getMsg(10025,"Despatch confirmed"))

call closeDb()
%>

Thank you for your help in advance..
Regards,
philip

View 2 Replies View Related

How To Select All Articles Form A Category Without An Article That Is Selected?

Jun 7, 2004

Hello,
i want to Select all articles form a category without an article that is selected.

Thanx a lot !

View 1 Replies View Related

Two Select Statements In Asp.net 2.0 Problem

Jul 26, 2006

 
    Hi Everyone,
    Can you please let me know what is wrong with the following code? I run the following code using path:    http://localhost/jimmy/may_30th_2006/vcalendar_cs/sb_PAYMENTS_page.aspx?LoginID=admin
    public void searchResultsWithClinic()    {              SqlConnection myConnection;           string conString;        conString = ConfigurationManager.AppSettings["calendarString"];        myConnection = new SqlConnection(conString);
        String cmdStr1, cmdStr2, cmdStr3;          cmdStr1 = "SELECT sb_clinic_name FROM sb_client_and_clinic WHERE sb_client_id = '" + Request.Params.Get("LoginID") + "'";
        cmdStr2 = "SELECT sb_client_id FROM sb_client_and_clinic WHERE sb_clinic_name = '" + cmdStr1 + "'";                                  SqlDataAdapter myCommand = new SqlDataAdapter(cmdStr2, myConnection);        DataSet DS = new DataSet();        myCommand.Fill(DS, "SearchPaymentResults");
        repeaterSearchPaymentResults.DataSource = DS;        repeaterSearchPaymentResults.DataBind();              myConnection.Close();
               }
 
Incorrect syntax near 'admin'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'admin'.Source Error:



Line 90: SqlDataAdapter myCommand = new SqlDataAdapter(cmdStr2, myConnection);
Line 91: DataSet DS = new DataSet();
Line 92: myCommand.Fill(DS, "SearchPaymentResults");
Line 93:
Line 94: repeaterSearchPaymentResults.DataSource = DS;Source File: d:Inetpubwwwrootjimmymay_30th_2006vcalendar_cssb_SearchPaymentResults.ascx.cs    Line: 92 ---------------------------------------------------------------------------------
Please note that the 'admin' in the error message comes from http://localhost/jimmy/may_30th_2006/vcalendar_cs/sb_PAYMENTS_page.aspx?LoginID=admin
thanks,May

View 7 Replies View Related

Combining Two Select Statements

Apr 2, 2007

I have a SP returning the following result The select statement for this is



Code:

          SELECT dbo.TEST1.[OFFICE NAME], COUNT(dbo.TEST1.[ACCOUNT ID]) AS AccountCount
FROM dbo.Test2 INNER JOIN
dbo.test3 INNER JOIN
dbo.Test4 ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code] INNER JOIN
dbo.TEST1 ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID] ON dbo.Test2.[Model ID] = dbo.test3.ID INNER JOIN
dbo.[Inquiry Details] ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryID
WHERE (dbo.Test2.InquiryDate BETWEEN CONVERT(DATETIME, @startDate, 102) AND CONVERT(DATETIME, @endDate, 102)) AND dbo.Test1.[Account ID] IN(SELECT [account id] FROM test5 WHERE [Contact ID] = @contactId)
GROUP BY dbo.TEST1.[OFFICE NAME]
ORDER BY COUNT(dbo.TEST1.[ACCOUNT ID]) DESC  name id count case1 226 320 case2 219 288 case3 203 163 case4 223 90 case5 224 73 i have another select stnat which returns like this The select statement is



Code:

Select  test1.[office name], count(test1.[office name]) From test1 inner join test4 on test1.[account id]=test4.[office id] inner join test3 on test4.[accounting Code]=test3.[accounting Code] Group by test1.[Office Name] order by count(test1.[office name]) DESCname count case6 10 case2 56 case4 66 case1 74 case3 88 case7 100 case5 177 How can i combine this select stament with the SP, so that, i get a fourth column with case1 226 320 74 case2 219 288 56 .......................... ........................... Hope i am not confusing you all Please help me, if someone knows how to combine this? Thanks

View 2 Replies View Related

Joining Two Select Statements

Feb 4, 2008

I only want to count the titleids that are on loan can I join these two statements or write the stored procedure a different way?  I hope this makes sense.
select count(libraryrequest.titleid) as [Presently on Loan], libraryrequest.titleid, media.[description]as Media
from libraryrequestjoin titles on titles.titleid = libraryrequest.titleidjoin resources on resources.titleid = titles.titleidjoin media on media.mediaid = resources.mediaidgroup by libraryrequest.titleid , media.[description]
select distinct requestors.Requestorid, titles.title, resources.quantityowned,requestors.requestorEmail,Requestors.requestdate, fname, lname, phonenum,StreetAddress1, City, State, Zip, libraryrequest.shipdate,libraryrequest.duedate, libraryrequest.returndate
from Requestorsjoin Titles on titles.Titleid = requestors.Titleidjoin libraryRequest on LibraryRequest.requestorid = Requestors.requestoridjoin resources on resources.titleid = titles.titleid

View 5 Replies View Related

USING MULTIPLE SELECT STATEMENTS

Apr 23, 2008

how can take codes below and put them into one store procedure to supplie a gridview. also i will like to define the row name on the left like i did to the column on the top using the 'AS'
 Code1....
SELECT
SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN site_descr = 'SITE1' THEN 1 ELSE 0 END) AS AllTotal
FROM dbo.V_results
WHERE (site_descr = 'SITE1')
 
Code2.....
 
SELECT
SUM(CASE WHEN Month = 'January' THEN 1 ELSE 0 END) AS January, SUM(CASE WHEN Month = 'February' THEN 1 ELSE 0 END) AS February,
SUM(CASE WHEN Month = 'March' THEN 1 ELSE 0 END) AS March, SUM(CASE WHEN Month = 'April' THEN 1 ELSE 0 END) AS April,
SUM(CASE WHEN Month = 'May' THEN 1 ELSE 0 END) AS May, SUM(CASE WHEN Month = 'June' THEN 1 ELSE 0 END) AS June,
SUM(CASE WHEN Month = 'July' THEN 1 ELSE 0 END) AS July, SUM(CASE WHEN Month = 'August' THEN 1 ELSE 0 END) AS August,
SUM(CASE WHEN Month = 'September' THEN 1 ELSE 0 END) AS September, SUM(CASE WHEN Month = 'October' THEN 1 ELSE 0 END) AS October,
SUM(CASE WHEN Month = 'November' THEN 1 ELSE 0 END) AS November, SUM(CASE WHEN Month = 'December' THEN 1 ELSE 0 END) AS December,
SUM(CASE WHEN site_descr = 'SITE2' THEN 1 ELSE 0 END) AS AllTotal
FROM dbo.V_results
WHERE (site_descr = 'SITE2')
 
 thanks in advance

View 10 Replies View Related

SELECT EXEC Statements In SQL

Jun 6, 2008

I  am writing a stored procedure to select some information from two tables and I would also like to Execute a function using the userid information from the processing in the where clause of the Select statement. Is the syntax below possible?? If yes, could you please help me understand exactly what I may be doing wrong here.. If no, can you please help with better syntax. Thanks in advance
 
SELECT M.UserID, M.FullName, (EXEC calcPoints M.UserID) as 'UserPoints'
 FROM MissionUsers M LEFT OUTER JOIN MissionUserInfo MU ON M.UserID = MU.UserID
WHERE  M.EMAIL = @UserEmail
 

View 5 Replies View Related

Two Select Statements On One Table

Apr 1, 2004

Hi,

newbie here.

Im trying to perform the following two select statements on the one table. I have been trying innerjoins etc but keep getting errors. The basis of what im trying to do is this.

SELECT column1
FROM table1
WHERE column2 = (select column2 from table1 where column3 = 14)

Any ideas greatly appreciated.

View 1 Replies View Related

Multiple Select Statements

Dec 22, 2005

Hi guys and gals,
I am trying to create a select statement that will return an INT that I will later have to use in another select statement. I have the following code, however, I keep getting an error that says:
'Error116: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.'
My Code is below:
//Start of sql
CREATE PROCEDURE ADMIN_GetSingleUsers( @userID  int) AS
DECLARE @userSQL intSET @userSQL = (SELECT User_ID, TITLE.TITLE AS TITLE,    Cast(Users.Active as  varchar(50)) as Active,   Cast(Users.Approved as  varchar(50)) as Approved,   Users.Unit_ID As usersUnitID,   *    From TITLE, Users   WHERE    User_ID = @userID AND   TITLE.TITLE_ID = Users.Title_ID )
Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @userSQL
//End of sql
Can you point to what I am doing wrong? Thanks in advance!

View 4 Replies View Related

Using IF In SELECT Statements? Beginner.

Mar 31, 2003

I am having problems using IF statements. In fact, I don't even know if it is possible to use "IF" in a SELECT statement. Here's my dilemma:

For each employee in my database, I have a numeric rating. I want to assign the rating to a category (green, yellow, red), based on the range the rating falls into.

My data currently looks like this:
EmpName &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Rating
employeeX &nbsp; 100
EmployeeY &nbsp; 85
EmployeeZ &nbsp; 25

After the magic of SQL, I want the data to end up looking like this (actually, my boss wants it like this):

EmpName&nbsp;&nbsp;&nbsp; Green Yellow Red
employeeX &nbsp; &nbsp;100
EmployeeY &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 85
EmployeeZ&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 25

The numeric rating would be inserted into the appropriate column, based on these ranges:
95 to 100 = green
80 to 94 = yellow
< 80 = red

I thought I could use a CASE statement, but I just learned that I can't do comparisons (I'm obviously a beginner). And I can't figure out how to use an If statement (or IIF).

Can anyone help?

Thanks!
Colleen

View 2 Replies View Related

Combining Select-Statements

Sep 5, 2006

Hi !

I have to divide and analyse a bigger table to get a smaller one. My target is to make this division in only one SQL-Statement.

My table looks like this:


Code:


ArtNo Code
16637 C
38827 A
16637 D
44883 C
44883 C
63853 H
24564 D
24564 A


(ArtNo is not the Primary Key)

My SQL-Statement should now find out how often every "code" belongs to a "ArtNo".

The result should be:

Code:



ArtNr A C D H
16637 1 1
38827 1
34343 1
44883 2
63853 1
24564 1 1


Does anyone have ANY Idea how I could realize this as easy as possible (without View etc.) ??

Regards
Gawan

View 1 Replies View Related

Is It Possible To Combine 2 SELECT Statements??

Jan 28, 2005

I am not sure if this is possible, but I was wondering if I can combine 2 SELECT statements so as to aquire a percentage..

I could be overthinking this....I am fairly new to SQL writing.

Here is an example of the 2 SELECT statements that I am using:


Code:

SELECT COUNT([Overall Rating]) FROM S526960.HDPIMaster WHERE Location = 'HBUS' AND [Overall Rating] = 'Good'



Code:

SELECT COUNT([Overall Rating]) FROM S526960.HDPIMaster WHERE Location = 'HBUS'

Within my output I am than taking the the data from the 1st query and dividing it by the 2nd query to get a percentage.

I was hoping that I could accomplish the same action within one SQL statement.

Thank you for your consideration!

View 4 Replies View Related

Nested Select Statements

Jan 26, 2005

I need help nesting select statements. Here's what I'm trying to do:

select (select e.emp_name_lf as employee, e.emp_id
from employee e, install_payroll_detail ipd
where e.emp_id = ipd.emplno)
e.emp_name_lf as username
from employee e, install_payroll_master ipm
where e.emp_id = ipm.entered_by

I just want one row with both the employee and username, however I cannot get the syntax. Any help is greatly appreciated.

Thanks!

View 3 Replies View Related

Difference Of Two Select Statements

Aug 19, 2004

Hi All,

I want to find difference of two Select statement in SQL Server 2000. As in Oracle we have "MINUS". Do we have the same thing in SQL Server also.

Thanks with Regards.

-Mohit.

View 2 Replies View Related

Joining Select Statements

Apr 23, 2008

Hi ,

If any one could help me in Joining these two select statements:

How Can I have Order by and Having Clauses in my final select list ?

***********
SELECTMIN(DISTINCT x.ProductionDate) as ProductionDay,COUNT(DISTINCT v.Number) as Timelost
FROMmaster..spt_values AS v
INNER JOIN
(
SELECTDATEDIFF(MINUTE, '00:00:00', Convert(varchar(10),StartDate,108)) AS StartMinute,
DATEDIFF(MINUTE, '00:00:00', Convert(varchar(10),EndDate,108)) AS EndMinute,
Convert(varchar(10),StartDate,102) as ProductionDate
--,COUNT(AlarmMessage) As Occurances
FROM RPMS.dbo.List_PaintShopAlarms
WHERE --Convert(varchar(10),StartDate,102) = @vcProductionDay and
Convert(varchar(10),StartDate,102) = Convert(varchar(10),EndDate,102)
AND (AlarmMessage LIKE '7FM%' OR AlarmMessage LIKE 'CONV%' OR AlarmMessage LIKE 'EMG%' )
)
AS x ON x.StartMinute <= v.Number
AND x.EndMinute > v.Number
WHEREv.Type = 'p' AND Number < 1440
and (x.ProductionDate BETWEEN Convert(varchar(10),@dtProductionDayStart,102) AND Convert(varchar(10),@dtProductionDayEnd,102))
group by x.ProductionDate
order by x.ProductionDate asc
***********
and
***********
SELECT Convert(varchar(25),[StartDate],102)as ProductionDay
,COUNT(AlarmMessage) As Occurances
FROM [RPMS].[dbo].[List_PaintShopAlarms]
WHERE
([AlarmMessage] LIKE '7fm%' or [AlarmMessage] LIKE 'Conve%') AND
Convert(varchar(25),[StartDate],102) BETWEEN Convert(varchar(25),@dtStartDate,102) AND Convert(varchar(25),@dtEndDate,102)
and DATEPART(dw, [ProductionDay]) <> 1

GROUP BY Convert(varchar(25),[StartDate],102)
HAVING SUM([Duration]) < 86400
ORDER BY Convert(varchar(25),[StartDate],102) asc
*************

View 2 Replies View Related

Join 2 Select Statements

Dec 22, 2014

how can i join these 2 queries to produce 1 result

Query 1:
select R.Name, T.Branchid, t.TradingDate,t. TransactionDate,
convert(varchar,T.Tillid)+'-'+convert(varchar,t.Saleid) as DocketNumber,
t.SubTotal, t.SalesRepPercent, t.SalesRepComAmount as CommissionAmt
from TransactionHeader T
join SalesRep R on
R.SalesRepid = T.SalesRepid
where T.SalesRepid is not null

Query 2 :
select C.TradingName,C.AccountNo
From Sale S
Join ClMast c on
C.Clientid = s.CustomerAccountID

The result should be R.Name,T.Branchid, t.TradingDate,t. TransactionDate,DocketNumber,t.SubTotal, t.SalesRepPercent, t.SalesRepComAmount, TradingName,Accountno..Field Saleid is present in Transactionheader Table and Sale table

View 5 Replies View Related

Track Select Statements.

Aug 29, 2007

different Users are running select statements under their own login. they have readonly permission to the database. My goal is to track all those queries and send notification which tables were opened and which columns were displayed...Looks like I need trigger on select statement for the table...( Unfortunetely it doesn't exists)
I collect all those statements in trace file and in the night time I stop trace, move file data to table and then step by step try to massage sql queries using VB.net... Most of those queries are so complicated, have derived tables inside and it is really hard to parse these sql statements...
1.If anybody has an idea how to say: User B opened table tbl_A col_B twice per today...using different methodology rather then go through SQL.(Other words how to create an event on a table which will be fired in a case of request select any column of that table?)
2.If somebody know where we can take good parser which will help with this job?
Thanks

View 14 Replies View Related







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