Parameter Information Cannot Be Derived From SQL Statements With Sub-select Queries

Apr 24, 2006

Parameter Information cannot be derived from SQL statements with sub-select queries. Set Parameter information before preparing command.

Here's the query:

update GCDE_SEQ
set LAST_NO = (select max(FLD_NO)
from PONL_FLD)
,UPDT_USER = ?
,UPDT_DT = getdate()
where SEQ_NM = 'FLD_NO'

Why can't Execute SQL Task handle this simple query? I figure i can use 2 SQL Execute SQL Task, one to get the max into a var, and the other to do the updating. However, this is alot of trouble since i'm having this almost exact query in alot of places. Any way around this?

View 8 Replies

Combine Two SQL Queries With Separate Where Statements

Jun 18, 2008

I have two SQL queries that I would like to combine. Each query is dependent on the same table, and the same rows, but they each have their own WHERE statements. I've thought about using some JOIN statements (left outer join in particular) but then I run into the problem of not having two separate tables, and don't see where I can put in two separate WHERE statements into the final query. I've read into aliasing tables, but I'm not quite sure how that works (how to put it into code or a JOIN statement) , or if it would solve my question. Do you have any ideas or examples of how to solve this scenario?

View 9 Replies View Related

How To Get ID Columns Of Inserted Statements And Use Across Different Queries

Nov 5, 2015

Currently I have this query which I am inserting into budterminals table

--Insert
INSERT INTO budterminals([TERMINALNUMBER], [ACCOUNTINGUNIT], [TERMINALNAME], [MBFTERMINALNAME], [SOURCESYSTEM], [COMPANYCODE], [STATUSID], [CREATEDUSERID], [CREATEDDATETIME], [LASTMODIFIEDUSERID] , [LASTMODIFIEDDATETIME])
SELECT CAST(left(TerminalName, patindex('%[^0-9]%', TerminalName+'.') - 1) as int)
,''
,SUBSTRING(TerminalName , CHARINDEX('-' , TerminalName) + 1, LEN(TerminalName))

[Code].....

Next I am inserting into budcustomers table

--Customer
INSERT INTO [dbo].[BudCustomers]
([LegalName]
,[EffectiveDate]
,[LawsonCustomerNumber]
,[ChangeReason]
,[ImportedRecord]

[Code] .....

Next I am inserting into budcontracts table but here in this table to insert i want the inserted terminalid and customerid column which are unqiue identity based.

So how to do that here in this query

--Contracts
INSERT INTO [dbo].[BUDCONTRACTS]
([CONTRACTNAME]
,[CONTRACTNUMBER]
,[VERSIONSTARTDATE]
,[VERSIONTERMINATIONDATE]

[Code] .....

View 3 Replies View Related

Queries With Different Statements - Show Results In Datagridview

Oct 19, 2015

I have made a couple of queries I want to use into a Visual studio project, Now is my problem:

All my queries have different statements. I believe the best is to show the results in a Datagridview

How to view them there? I know how to do it... but I have got about 30 queries

Here is my code so far:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ComboBox1.DataSource = MyDB_DevDataSet.DataSetName.
Dim connectionString As String = "Data Source=myDBmySRV;Initial Catalog=Teknotrans_dev;Integrated Security=True"
Dim sql As String = "SELECT * FROM CompanyMain"

[Code] ....

View 2 Replies View Related

Parameter For Multiple Where Statements

Sep 5, 2007

I'm hoping someone out there has a creative solution for my scenario. I have the report code below. I want to create a parameter that allows the user to select which where statement to use in the report. Then I want to use the corresponding where description below ( ex: @WHEREDESC1 ) in my header which will describe (in laymen's terms) which parameter was chosen and what is being reported on.

Has anyone achieved this or have an idea how it may be done?

DECLARE @WHEREDESC1 VARCHAR(50), @WHEREDESC2 VARCHAR(50), @WHEREDESC3 VARCHAR(50),

@WHEREDESC4 VARCHAR(50), @WHEREDESC5 VARCHAR(50)

SET @WHEREDESC1 = 'SRM Admitting and Principal Diagnosis do not match'

SET @WHEREDESC2 = 'SRM and AMPFM Admit dates do not match'

SET @WHEREDESC3 = 'SRM and AMPFM Discharge dates do not match'

SET @WHEREDESC4 = 'SRM and AMPFM Visit Type Codes do not match'

SET @WHEREDESC5 = 'SRM and AMPFM DRG codes do not match'

SELECT LEFT(srm.EPISODES.ADMISSION_DATE, 11) AS SRM_Admit_DT, ampfm.rpt_AdtVisit.AdmitDate AS AMPFM_Admit_DT,

LEFT(srm.EPISODES.EPISODE_DATE, 11) AS SRM_Disch_DT, ampfm.rpt_AdtVisit.DischDate AS AMPFM_Disch_DT,

srm.EPISODES.EPISODE_TYPE AS SRM_Visit_Type, ampfm.rpt_AdtVisit.VisitTypeCode AS AMPFM_Visit_Type,

srm.EPISODES.CHARGES AS SRM_Charges, ampfm.rpt_VisitFinancial.TotCharges AS AMPFM_Charges,

ampfm.rpt_VisitFinancial.VisitChargesActive AS AMPFM_Active_Charges, ampfm.rpt_AdtVisit.PatientFullName,

srm.CDMAB_DRG_INFO.DRG_NUMBER AS SRM_Drg, ampfm.rpt_InsuranceDrg.InsDrg AS AMPFM_Drg,

ampfm.rpt_AdtVisit.Complaint AS AMPFM_Admit_Dx, srm.cdmab_base_info.ADM_DX_CODE, srm.cdmab_base_info.ADM_DX_DESC,

srm.cdmab_base_info.PRIN_DX_CODE, srm.cdmab_base_info.PRIN_DX_DESC, srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.MEDREC_NO

FROM srm.EPISODES INNER JOIN

ampfm.rpt_AdtVisit ON srm.EPISODES.ACCOUNT_NUMBER = ampfm.rpt_AdtVisit.AccountNumber INNER JOIN

ampfm.rpt_VisitFinancial ON ampfm.rpt_AdtVisit.IntOOS = ampfm.rpt_VisitFinancial.IntOOS INNER JOIN

ampfm.rpt_InsuranceDrg ON ampfm.rpt_AdtVisit.IntOOS = ampfm.rpt_InsuranceDrg.IntOOS AND

ampfm.rpt_AdtVisit.VisitTypeCode = ampfm.rpt_InsuranceDrg.VisitTypeCode INNER JOIN

srm.CDMAB_DRG_INFO ON srm.EPISODES.EPISODE_KEY = srm.CDMAB_DRG_INFO.EPISODE_KEY INNER JOIN

srm.cdmab_base_info ON srm.EPISODES.EPISODE_KEY = srm.cdmab_base_info.EPISODE_KEY

--WHERE srm.cdmab_base_info.ADM_DX_CODE <> srm.cdmab_base_info.PRIN_DX_CODE

--WHERE srm.EPISODES.ADMISSION_DATE <> ampfm.rpt_AdtVisit.AdmitDate

--WHERE srm.EPISODES.EPISODE_DATE <> ampfm.rpt_AdtVisit.DischDate

--WHERE srm.EPISODES.EPISODE_TYPE <> ampfm.rpt_AdtVisit.VisitTypeCode

--WHERE srm.CDMAB_DRG_INFO.DRG_NUMBER <> ampfm.rpt_InsuranceDrg.InsDrg

Order By srm.EPISODES.ACCOUNT_NUMBER

View 7 Replies View Related

Help With Optional Parameter Query With IN Statements

Aug 24, 2007

I have a query with 17 separate, optional, parameters. I have declared each parameter = NULL so that I can test for NULL in the case that the user didn��t not pass in the parameter.

I am new enough to SQL Server that I am having difficulty building the WHERE clause with all of these optional parameters.

One solution I was advised on by a well paid SQL programmer, was to use a string in the stored proc and dynamically build the WHERE clause and exec it at the end of the sp. But the whole point of a stored proc is that it can be compiled and cached to make it faster, yet the string approach makes it have to compile every time it��s run! Not a good solution, but maybe it��s the best I can do . . .

I have tried many different approaches using different functions, etc. but I��ve hit a brick wall. Any help in sorting it out with YOUR techniques would be greatly appreciated:

1. To add the parameter to the WHERE clause and test for NULL I��ve used the COALESCE function such as ��WHERE table.fieldname = COALESCE(@Param, table.fieldname)�?. This works well if there is only one item in the parameter, but in the case that I pass multiple items to the parameter, it completely fails.

2. To handle multiple items, for example, if @Param = ��3,7,98�� (essentially, a csv separated list of keys)

Code SnippetWHERE table.fieldname IN(COALESCE(@Param, table.fieldname))

doesn��t work because @Param needs to be parsed from a string into an array of integers in the parameter. So, I am using a UDF I discovered to parse the multi-item parameter. The UDF can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp and it returns a table variable that can be used in an IN statement. So I��m using

Code SnippetISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@Param,��,��))

which works brilliantly in my WHERE statement AS LONG AS @Param ISN��T NULL. So how do I test for NULL first and still use this approach to multi-item parameters?

I��ve tried

Code SnippetWHERE @Param IS NULL OR ISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@Param,��,��))

and though it works, the OR causes it to slow way down as it compares every record for the OR. (It slows down by approximately 800%.) The other thing I tried was

Code SnippetISNULL (table.fieldname, 0) IN (CASE WHEN @Param IS NULL THEN ISNULL(table.fieldname, 0) ELSE (SELECT value FROM dbo.fn_Split(@Param,��,��)))

This fails with ��Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression�? due to the multiple values in the parameter. (I can��t understand why the line without the CASE statement works, but the CASE line doesn��t!)

Am I even on the right track, cuz this is driving me mad and I just need a way to deal with optional multi-item parameters in an IN statement? HELP!

View 4 Replies View Related

Evaluating Derived Columns Within A Select

Sep 19, 2006

Hi
I'm updating an old Access application to SQL Server and am currently trying to decipher one of the reports on the old application. It appears to be evaluating a derived column from one query (qryStudentSuspGroup.Suspension) in the Select statement of another. I have tried to put the query that creates the derived column in as a nested query into the other query but can't get it to work. This is all a bit beyond my rudimentary SQL skills! Any help would be greatly appreciated!

The original Access SQL appears below:

SELECT [Enter the academic year (4 digits)] AS [input], ResearchStudent.Department, ResearchStudent.DateAwarded,
ResearchStudent.StudentNumber, Person.Forenames AS fore, Person.Surname AS Sur, ResearchStudent.Mode,
ResearchStudent.RegistrationDate, StudentExamination.Decision,
IIf(([Suspension]) Is Null Or [Suspension]=0,([DateAwarded]-[RegistrationDate])/365,(([DateAwarded]-[RegistrationDate])-([Suspension]))/365) AS CompDate,
ResearchStudent.EnrollmentCategory, qryStudentSuspGroup.Suspension
FROM ((ResearchStudent LEFT JOIN Person ON ResearchStudent.ResearchStudentID = Person.PersonID)
LEFT JOIN qryStudentSuspGroup ON ResearchStudent.ResearchStudentID = qryStudentSuspGroup.ResearchStudentID)
LEFT JOIN StudentExamination ON ResearchStudent.ResearchStudentID = StudentExamination.ResearchStudentID
WHERE (((Year([DateAwarded]))>=[Enter the academic year (4 digits)]
And (Year([DateAwarded]))<=([Enter the academic year (4 digits)]+1))
AND ((IIf(Year([DateAwarded])=[Enter the academic year (4 digits)],Month([DateAwarded])>8,Month([DateAwarded])<9))<>False))
ORDER BY ResearchStudent.Department, ResearchStudent.Mode, ([DateAwarded]-[RegistrationDate])/365

View 15 Replies View Related

T-SQL (SS2K8) :: Select Statement With Derived Order Status

Aug 28, 2015

Here is my requirement

Table 1 Order
order ID, Sales order ID order date, order type

Table 2 Order details
Order Details ID, Order ID, Order Stage

Table 3 Related Order details
Order ID(FK to Order ID), Related Order Details ID(FK to Order Details ID), Related Order ID( FK to Order ID)

Here is example

Table 1 Order
1, 1234, 2015-01-01, Refill
2, 1234, 2015-02-02, Extension

Table 2 Order Details
1, 1, Approved
2, 1, Approved
3, 2, Rejected

Table 3 Related Order Details
2, 1, 1
2, 2, 1

I have to Select Order, Order Details and Order Status

Order Status is determined from Order Stage as follows:

If, at least one order detail line(from Order Details and Related Order details table) is approved, that Order status=Approved.

For the example, Order Status of Order ID=2, is Approved based on order status for order details lines 3(from table 2) and order details ID 1 and 2 (from table 3)

How to combined order stage from table 2 and table 3 and then compute order status.

View 3 Replies View Related

Concatenating Parameter Values && Text; Nested CASE Statements

Sep 27, 2007

Hello. I'm trying to reduce some code in my stored procedure and I'm running into lots of errors. I'm somewhat of a novice with SQL and stored procedures so any help would be beneficial.
I have a SP that gets a page of user data and is also called when sorting by one of the columns (this data is placed in a repeater, btw). I quickly learned that I wasn't able to pass in string parameters the way I had hoped in order to handle the ORDER BY and direction (ASC/DESC) so I'm trying to work around this.
So far I've tried the following with many errors.WITH Users AS (
SELECT ROW_NUMBER() OVER (ORDER BY CASE WHEN @OrderBy='FirstName' AND @Direction='DESC' THEN (FirstName + ' DESC')
WHEN @OrderBy='FirstName' THEN FirstName
WHEN @OrderBy='LastName' AND @Direction='DESC' THEN (LastName + ' DESC')
WHEN @OrderBy='LastName' THEN LastName
END
) AS Row,
UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName
FROM
vRF_Users
)

SELECT UserID, FirstName, LastName, EmailAddress, [Role], Active, LastLogin, DateModified, ModifiedBy, ModifiedByName
FROM Users
WHERE Row BETWEEN @StartRowIndex AND @EndRowIndex

I've tried a combination of similar things with parenthesises, without, doing "THEN FirstName DESC" without concatenating anything, etc.
I also tried: DECLARE @OrderByDirection varchar(32)
DECLARE @DESC varchar(4)
SET @DESC = ' DESC'

IF @Direction = 'DESC'
BEGIN
SET @OrderByDirection = (@OrderBy + @DESC)
END
And then writing my case statemet like this:ORDER BY CASE WHEN @Direction='DESC' THEN @OrderByDirection
ELSE @OrderBy
ENDObviously this didn't work either. Is there any way to gracefully accomplish this or do I just have to use a bunch of if/else statements and lots of redundant code to evaluate all my @OrderBy and @Direction parameters???

Thanks in advance,
Jen

View 26 Replies View Related

Select Parts Of Information

Apr 20, 2008

Hey guys,

I was wondering if this is possible I have to be able to just select the number before the '-' in acolumn which is formatted like 123-456 so I just need the the 123 part.

Any ideas how this can be achieved?

Thanks.

View 6 Replies View Related

Need Information For Before Select Trigger

Jun 8, 2006

hi guys!Currently i'm facing a bit odd situation. i cant modify my code so ihave to make changes in SQL Server.the problem is, i've modified one table by adding new column and now iwant only those row which has not null value in that column wheneverthe select statement gets fire. i mean in every select statement onlythose rows should be returned which has NOT NULL value in the newlyadded column.for this i've created one view and tried to run the application but thei'm facing some problem as my application is developed in .net 2.0,i've used lots of .net functionalities and now i cant make any changesin Code.another solution i think is triggers. if i can fire a trigger beforeany select statements, my problem can be solved instantly but i'mafraid i can not write andy "before select" trigger.if anyone has any idea or any other solution to my problem, please letme know.all suggestions are welcomed.thanks,Lucky

View 4 Replies View Related

Can A Column Be Derived Using Substring But The Parameters Are A Result Of A Select Statement?

Apr 6, 2007

I have a table which has a field called Org. This field can be segmented from one to five segments based on a user defined delimiter and user defined segment length. Another table contains one row of data with the user defined delimiter and the start and length of each segment. e.g.

Table 1

Org

aaa:aaa:aa

aaa:aaa:ab

aaa:aab:aa

Table 2

delim

Seg1Start

Seg1Len

Seg2Start

Seg2Len

Seg3Start

Seg3Len

:

1

3

5

3

9

2

My objective is to use SSIS and derive three columns from the one column in Table 1 based on the positions defined in Table 2. Table 2 is a single row table. I thought perhaps I could use the substring function and nest the select statement in place of the parameters in the derived column data flow. I don't seem to be able to get this to work.

Any ideas? Can this be done in SSIS?

I'd really appreciate any insight that anyone might have.

Regards,

Bill

View 23 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

Retrieve Select Information From A Database

Mar 31, 2008

If I had a bunch of paragraphs stored in a database field, is there a way, when displaying that data, that I can write instructions to only retrieve the very first paragraph?

View 4 Replies View Related

Bind Variables / Parameter Queries

Jan 27, 2005

Hi,

I'm writing an Access pass-through query against a SQL server backend and I need some advice on passing parameters. Currently I use vba to substitute the literal values for the parameters prior to passing the query to SQL Server. However, I am going through a loop thousands of times with different literals for these parameters which causes the server's cache to fill up. In Oracle, there is a way to use bind variables for the parameters so that only one copy of the query is cached.

Does anyone know how I can do this in SQL Server?

For instance, I have 20,000 employees and I'm pulling info by SS#:

Select * from EmpTable where SS_number = [SSN]

Is there a way I can pass this query to SQL Server and then pass the value of [SSN] as I loop through the dataset?

Thanks.

View 5 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       Rating
employeeX   100
EmployeeY   85
EmployeeZ   25

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

EmpName    Green Yellow Red
employeeX    100
EmployeeY               85
EmployeeZ                     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