'SELECT Clause Does Not Return Rowset From Sp'
I have sp20, simplified, as:
ALTER PROCEDURE dbo.sp20 (@CustomerID int, @aDate as datetime) AS
SELECT Customers.* INTO #EndResult1 FROM Customers WHERE Customers.CustomerID >= @CustomerID
SELECT Orders.* INTO #EndResult2 FROM Orders Where Orders.[TakenDate] >= @aDate
SELECT #EndResult1.*, #EndResult2.*
FROM #EndResult1 INNER JOIN #EndResult2 ON #EndResult1.CustomerID = #EndResult2.CustomerID
This works fine in EM.
When I try to execute it from MS Access ADP Project I get
'Stored Procedure excuted succesfully, but did not return any records'
Although, in EM it returns the right number of records.
Thank you in advance - Rehman
View Complete Forum Thread with Replies
Related Forum Messages:
Select .. NOT IN Clause Doesn't Return Anything?
Hi there, It's a very strange thing! I havea a table called invoices, and a table calle customer payments which has the invoiceID of the payment. I have many invoices that haven't been paid (so they don't have a record on the customer payments). I know this, as i can for example do: select * from invoices where invoiceID = 302247 (and i'll get one result) select * from customer_payments where invoice = 302247 (and i'll get none results) however, if i do the following: select * from invoices where invoice_id not in (select invoice_id from customer_payments) I get nothing!!!??? It doesn't make any sense, as I should get at least 300 (including the 302247) - both invoiceids fields are int... so i just don't understand what's wrong? thank you so much for any help! Grazi
View Replies !
Using Output Clause And Return Timestamp And Return Value
I am trying to bring my stored proc's into the 21st century with try catch and the output clause. In the past I have returned info like the new timestamp, the new identity (if an insert sproc), username with output params and a return value as well. I have checked if error is a concurrency violation(I check if @@rowcount is 0 and if so my return value is a special number.) I have used the old goto method for trapping errors committing or rolling back the transaction. Now I want to use the try,catch with transactions. This is easy enough but how do I do what I had done before? I get an error returning the new timestamp in the Output clause (tstamp is my timestamp field -- so I am using inserted.tstamp). Plus how do I check for concerrency error. Is it the same as before and if so would the check of @@rowcount be in the catch section? So how to return timestamp and a return value and how to check for concurrency all in the try/catch. by the way I read that you could not return an identity in the output clause but I had no problem. Thanks for help on this SM haig
View Replies !
Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
View Replies !
Return Result Not Working With IN Clause
I have a form that has many checkboxes (more than 40) that provide information about companies. For example. Company ABC Checkbox 1 (Windows XP) Checkbox 2 (Windows Vista) Checkbox 3 (Windows NT4) etc. I save these in a SQL table like the following Row 1 - Col 1 (CompanyID), col2 (checkbox value) Row 2 - Col 1 (CompanyID), col2 (checkbox value) etc.. so a comapany can have multiple checkbox selected.. I am currently developing a report where the end user can select one or many of the checkbox to see if the company exist. This is where I get stuck - I only want to return the results of the companies that meet the report selections. So if I want to see all the companies that current have Windows NT and Windows Vista...how would I build that querry based on the data model above. I have tried using the IN clause - however that use the OR connector and doesnt show ONLY the compaines that meet. I have tried using a UNION and INTERSECT, and running the select statement many times.. (SELECT CompanyID from xx WHERE CheckboxVal = xx) INTERSECT (SELECT CompanyID from xx WHERE CheckboxVal = xx) etc.. howevert this creates a very large quesry that is unable to be handled by SQL 2005. I get a error message asking to minimize the query. Any suggestions on this please...this is my 4th day working on this..
View Replies !
Best Way To Return Records In A Date Range Using Where Clause?
Say I want to return only records with dates that fall within the next 6 months. Is there some straight-forward, simple way of doing so?As of now, I'm explicitly giving it a date 6 months in the future, but I'd like to replace it with some sort of function. SELECT DateField1WHERE (DateField1 < CONVERT(DATETIME, '2008-06-03 00:00:00', 102)) Any help is greatly appreciated... btw I'm using SQL 2005.
View Replies !
SELECT Then DELETE Versus Extra Clause In SELECT
Far below (in section "original 3 steps"), you see the following:1. a temp table is created2. some data is inserted into this table3. some of the inserted data is removed based on a join with the sametable that the original select was made fromIn my opinion, there is no way that the join could produce more rowsthan were originally retrieved from viewD. Hence, we could get rid ofthe DELETE step by simply changing the query to be:INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )AND INMESS NOT LIKE '2__' ---- the added line===== original 3 steps (mentioned above) =====CREATE TABLE #details (rec_id UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,orig VARCHAR(35) NULL,bene VARCHAR(35) NULL,orig_corr TINYINT NULL,bene_corr TINYINT NULL)INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )DELETE dFROM #details dJOIN viewD v ON ( d.rec_id = v.rec_id )WHERE INMESS LIKE '2__'
View Replies !
Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause
2 examples: 1) Rows ordered using textual id rather than numeric id Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v order by v.id Result set is ordered as: 1, 11, 2 I expect: 1,2,11 if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine. 2) SQL server reject query below with next message Server: Msg 169, Level 15, State 3, Line 16 A column has been specified more than once in the order by list. Columns in the order by list must be unique. Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v cross join ( select 1 id union select 2 id union select 11 id ) u order by v.id ,u.id Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine. It reproducible on Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) and Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) In both cases database collation is SQL_Latin1_General_CP1251_CS_AS If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again. Could someone clarify - is it bug or expected behaviour?
View Replies !
Select With IN Clause
Hi SQL Experts, i have a strange problem i have a variable which stores some values(ID) with single quote (so that i can use directlt inside the IN Clause of SELECT Declare @DMSIDs AS VARCHAR(1000) -- variable declare, Select @DMSIDs = '''DMS00046847'',''DMS00048305''' -- for test putting 2 correct values with escape characters Select * from issue where id in (@DMSIDs) -- valid statment, but does not return any data Select * from issue where id in ('DMS00046847','DMS00048305') -- same above constant value this returns data, but putting the values in varaible then trying fails. The reason is i have a master table called issue and have another table [delta] where a particular column will store all the ID's of the issue table comma separated with single quote and i wanted to use something like below in my actual application Select * from issue where ID in (Select distinct delta_ID from Delta_branch where date = getdate()) but since the above example with variable is not returning any data i wonder if such is possible in any other ways. thank you for reading and helping me.
View Replies !
Select To Clause
I'm having trouble using the To clause in my select statement. The following errs out with Incorrect syntax near the keyword 'to'. use pubs select * from view_Rates to 'c: est.csv' I also tried this and got same error: use pubs select * to 'c: est.csv' from view_Rates
View Replies !
How SELECT In WHERE Clause Works?
Please help I'm trying to do a select command but doesn't return any record I have two tables one is "lists" another one is "list_records" in the liss table I have 4 records and in the list_records I have only one record which is tell who is already visited the site so I created a query to get people who is NOT visited the site Here is my query (I got 0 record return) SELECT *FROM lists list_reccords WHERE NOT EXISTS (SELECT * FROM lists, list_records WHERE list_records.is_visited = 1 AND lists.list_id = list_records.list_id)
View Replies !
Insert Into ....... Select Clause
Hi, I have a question about Insert into ..... Select clause in a SP. I need to insert some rows into a temperary table in a specific order. For example, insert into #TempTable { ....... ........ } Select * from products order by @SortBy @SortDirection. // First of all, the order by clause does not take variables. I have tried to use declare @query varchar (1000) set @query = 'Select * from products order by " + @SortBy + ' ' + @SortDirection exec (@query) // However, I get an error message because I should supply a Select statement. How can I solve this problem?? Thanks for your help!
View Replies !
Using @column For Where Clause In Select
Hi, I want to do something like "select range from mytable where @ColumnToUse = 1" then "select range from mytable where @ColumnToUse = 2" etc. and @columnToUse lists a different column heading each time. I know I can put this into an "exec ('select range...')" type statement, but it's really helpful if I don't evaluate all my variables at this stage in my scripts. Can anyone help? THanks Dan
View Replies !
Problems With Select Clause With Except?
Hi there, I'm preparing a query and am having problems displaying the right content. There is only one table i am dealing with, however there are 2 fields that a check needs to be made on to see if they exist or not. Working portion of the query: SELECT * FROM tblVehicles WHERE (Make = 'audi') AND (Model = 'A4') AND (YearRange = '02-05') AND (SubModel = '3.0L') AND (Factory_Wheel_Diameter = '17') this returns 2 records: record 1 74, AUDI, A4, 3.0L, 02-05, fwd and 4X4, 17, 5-112, 16,X43659,19, null, null, null, 16, AVW659A(9), 219 record 2 73, AUDI, A4, 3.0L, 02-05, fwd and 4X4, 17, 5-112, null, null, null, null, null,null,17, AVW759A(9), 219 the second record is the one i need to have eliminated. i have tired adding: and (MAW_Wheel is not null) and (OEMWheel is not null) to my sql statement, but this returns no records. I have also tried using the except, not in, etc.. Seems like i need to combine those 2 values and select records when those 2 values are not both null only, but no matter what I try, i get it wrong. Can anyone point me in the right direction? Thanks,
View Replies !
Using Greater Than '&>' In SELECT Clause
Here is the first part of a query for MySQL that I am trying to get working on MSSQL: Code: SELECT n.*, round((n.rgt-n.lft-1)/2,0) AS childs, count(*)+(n.lft>1) AS level, ((min(p.rgt)-n.rgt-(n.lft>1))/2) > 0 AS lower, (( (n.lft-max(p.lft)>1) )) AS upper FROM table n ... But, I get this error message: Server: Msg 170, Level 15, State 1, Line 3 Line 3: Incorrect syntax near '>'. Is there a way to convert this? Thanks
View Replies !
CURSOR Select Clause
I need to dynamically construct the field order of a cursor based on fixed labels from another table, but when I put that resulting query I receive the error: Server: Msg 16924, Level 16, State 1, Line 78 Cursorfetch: The number of variables declared in the INTO list must match that of selected columns. I have 6 fields defined in the cursor select, and 6 parameters in the fetch. The results of running the @sql portion returns valid data. Should this be possible to define a parameter containing the select clause of the cursor? select colnum, coldesc, colname into #ae_defs from ae_adefs select @Sql = (select colname from #ae_defs where coldesc = 'PATIENT NAME') + ', ' + (select colname from #ae_defs where coldesc = 'PATIENT NUMBER') + ', ' + (select colname from #ae_defs where coldesc = 'ACCOUNT NUMBER') + ', ' + (select colname from #ae_defs where coldesc = 'VISIT DATE') + ', ' + (select colname from #ae_defs where coldesc = 'VISIT TYPE') + ', DocID from ae_dtl1' DECLARE myCursor CURSOR FOR Select @SQL OPEN myCursor print @@Cursor_rows FETCH NEXT FROM myCursor into @var1, @var2, @var3, @var4, @var5, @DocID
View Replies !
Question About Where Clause In A Select
Hi, I have a simple select statement that joins a master and a detail tables using a single field. Looks somthing like this: Master: Field1 (Unique key) more fields... DateField (Index field) Detail: Field1 (unique key) Field2 (unique key) more fields.... The master has 100 thousand records and the detail has 100 million records. If I had a statment "Select.....From Master, Detail" what would be the best way to write the where clause? Would one of the following where clauses run faster than the other based on the number of records in the tables? Where1: where Master.DateField = value and Master.Field1 = Detail.Field1 Where2: where Master.Field1 = Detail.Field1 and Master.DateField = value
View Replies !
IF Statement In SELECT WHERE Clause
Hi Everyone, I have the following stored procedure, I would like to use IF statement or something of the sort in the where clause i.e. The last line in the SP is: AND (category.categoryID = @categoryID), I only want to check this, if @categoryID is not = 12. So can I do something like this: IF @categoryID <> 12 AND (category.categoryID = @categoryID) STORED PROCEDURE: CREATE PROCEDURE sp_get_total_risk_patients @categoryID int AS SELECT COUNT(DISTINCT patient.patientID) AS total_patients FROM patient INNER JOIN patient_record ON patient.patientID = patient_record.patientID INNER JOIN sub_category ON sub_category.sub_categoryID = patient.sub_categoryID INNER JOIN category ON category.categoryID = sub_category.categoryID WHERE risk = 6 AND (completed_date = '' OR completed_date IS NULL) AND (category.categoryID = @categoryID)
View Replies !
Use Of User Defined In SELECT Clause
I'm having this query:SELECTss.subscription_id AS SubscriptionId,s.id AS ScopeId,s.[name] AS ScopeName,s.base AS ScopeBase,dbo.iqGetShapesByScopeAsString(s.id) AS ShapesAsStringFROMsubscription_scope ss,scope sWHEREss.subscription_id = @subscription_idANDss.scope_id = s.idORDER BYs.[name]The select only returns a single row but my database (SQL Server 2005CTP) seems to execute the "iqGetShapesByScopeAsString" function foreach row in the subscription_scope and scope tables. This is a bug,right? The function should be executed only once for each *returned*row in the SELECT, right? I believe that was the case in SQL 2k thoughI can't check it at the moment.// pt
View Replies !
Grouping Select Statements With Where Clause
Hello What I need to do is be able to group the results of my select statements in different columns. And end having the result work like this. campaign Col1 Col2 <<Data>> <<Select counT(*) where field= value>> <<Select counT(*) where field= value>>
View Replies !
Optimising Select Statements Which Has A ‘LIKE’ Where Clause.
Hi all I have been doing some development work in a large VB6 application. I have updated the search capabilities of the application to allow the user to search on partial addresses as the existing search routine only allowed you to search on the whole line of the address. Simple change to the stored procedure (this is just an example not the real stored proc): From: Select Top 3000 * from TL_ClientAddresses with(nolock) Where strPostCode = ‘W1 ABC’ To: Select Top 3000 * from TL_ClientAddresses with(nolock) Where strPostCode LIKE ‘W1%’ Now this is when things went a bit crazy. I know the implications of using ‘with(nolock)’. But seeing the code is only using the ID field to get the required row, and the database is a live database with hundreds of users at any one time (some updating), I think a dirty read is ok in this routine, as I don’t want SQL to create a shared lock. Anyway my problem is this. After the change, the search now created a Shared Lock which sometimes locks out some of the live users updating the system. The Select is also extremely SLOW. It took about 5 minutes to search just over a million records (locking the database during the search, and giving my manager good reason to shout abuse at me). So I checked the indexes. I had an index set on: strAddressLine1, strAddressLine2, strAddressLine3, strAddressLine4, strPostCode. So I created an index just for the strPostCode (non clustered). This had no change to the ‘Like select’ what so ever. So I am now stuck. 1)Is there another way to search for part of a text field in SQL. 2)Does ‘Like’ comparison use the index in any way? If so how do I set this index up? 3)Can I stop a ‘Shared Lock’ being created when I do a ‘like select’? 4)Do you have any good comebacks I could tell the boss after his next outburst of abuse (please not so bad that he sacks me). Any advice truly appreciated.
View Replies !
UDefined Functions In Select/ Where Clause
Hi, Is there any way of emulating Oracle's capability of passing output of user-defined functions in the select statement or better still in the Where clause in SQL server 7.0? If not then could we hope for it in SQl server 2000? Regards, Vikas..
View Replies !
Use A Variable Along With The FROM Clause In SELECT Statement
I have a table 'table_list' which contains two columns, table_name and a record_count. This table stores a list of tables and their corresponding record counts. What I am trying to do is, to be able to write a select statement, that can read each table name in the 'table_name' column, execute a select count(*) for the same, and update its record_count with the result of select count(*). This is the code in my procedure.. DECLARE @tab_list CURSOR set @tab_list = CURSOR FOR select * from table_list OPEN @tab_list DECLARE @tab_name varchar(256) DECLARE @rec_cnt int FETCH NEXT FROM @tab_list INTO @tab_name, @rec_cnt select count(*) from @tab_name This select is looping around along with FETCH till all the table names are exhausted and their counts are updated from the cursor back into the table. Problem is that, I am not able to use select count(*) from @tab_name, and its not accepting a variable there. Please help me to construct the select statement that is similiar to x=<table name> select * from x where x is a variable and the table name gets substituted. what is the syntax for it ?
View Replies !
Select Inner Join With Where Clause Problems
Hello All, I have a question about a Select over 2 Tables, with the Following Scenario (Not all Products (ARTICULOS) haves CARAC's on the CFG_CARAC_ARTICULOS table): Picture of the tables here: http://www.pci-baleares.com/pantallazoSql.jpg We have per example 7 Slots (Motherboard, CPU, VGA Card, RAM, TOWER, etc...) When we fill the Slot with a CPU-> Then we open the Slot for VGA CARD, we do the Followin Select: SELECT dbo.ARTICULOS.* FROM dbo.CFG_CARAC_ARTICULOS INNER JOIN dbo.ARTICULOS ON dbo.CFG_CARAC_ARTICULOS.ID_ARTICULO = dbo.ARTICULOS.ID_ARTICULO Ok it brings up ALL Graphic Cards because they dont depends on CPU Now we go to the Motherboard Slot And we make the following Select to obtain the compatible Motherboards: SELECT dbo.ARTICULOS.* FROM dbo.CFG_CARAC_ARTICULOS INNER JOIN dbo.ARTICULOS ON dbo.CFG_CARAC_ARTICULOS.ID_ARTICULO = dbo.ARTICULOS.ID_ARTICULO WHERE ((dbo.CFG_CARAC_ARTICULOS.ID_CARAC = 7) AND (dbo.CFG_CARAC_ARTICULOS.VALOR = 'PCI-E')) AND ((ID_CARAC = 1) AND (VALOR = '775')) We check the motherboards if they support PCI-E (because we selected a Graphic card of that, and SOCKET 775 because the CPU) But SQL return 0 Rows, if we do the following Select: SELECT dbo.ARTICULOS.* FROM dbo.CFG_CARAC_ARTICULOS INNER JOIN dbo.ARTICULOS ON dbo.CFG_CARAC_ARTICULOS.ID_ARTICULO = dbo.ARTICULOS.ID_ARTICULO WHERE ((dbo.CFG_CARAC_ARTICULOS.ID_CARAC = 7) AND (dbo.CFG_CARAC_ARTICULOS.VALOR = 'PCI-E')) OR SELECT dbo.ARTICULOS.* FROM dbo.CFG_CARAC_ARTICULOS INNER JOIN dbo.ARTICULOS ON dbo.CFG_CARAC_ARTICULOS.ID_ARTICULO = dbo.ARTICULOS.ID_ARTICULO WHERE ((ID_CARAC = 1) AND (VALOR = '775')) It return Rows, it happens just if the Where clause haves more as 1 specifications... Any solution for it? It drives me crazy :D Thanks and regards Marc Hägele
View Replies !
Is It Possible To Use A Where Clause In Select Statments When You Use Intersection?
Lets say that Dealers have ZipCodes, and that a Dealer can have more than one zipCode, and we want the list of dealers that have both 90210 and 90211 zip codes. BUT we don't want any dealers that have only one of the two ZipCodes in question What I want to do is something like this Select DealerID from DealerZips where Zip = '90210' intersection Select DealerID from DealerZips where Zip = '90211' but I get this error msg: Line 2: Incorrect syntax near 'intersection' The following sql is silly, but it does run without error Select DealerID from DealerZips intersection Select DealerID from DealerZips So I am pretty sure my problem is with the Where clauses. help!
View Replies !
Select Return Nothing
Hi...I have table who contain words in Hebrew (right to left language), and when i try to select:WHERE (Topic LIKE '%Hebrew word%')I get no rows...Any idea? thanks...
View Replies !
'Select *' Vs 'Return Top'??
Is there any difference in the way Query Analyser will look at a table when you use a 'select * from table' statement compared to using the Open Table, then Return Top from within Enterprise Manager? We have a 41MB table which contains 50,000 rows. When I try 'select * ...' in QA it seems to run and run (over 5 mins before I have to stop it). One of my users seems to think that using the Open Table, Return Top 50,000 in EM gives pretty immediate results. I'm not so sure however, since if you then attempt to scroll down to the last row it appears to take just as long as the above select statement. Any ideas, suggestions? Thanks Derek
View Replies !
Using A UDF To Return Values For A Dynamic &"WHERE IN ()&" Clause
Greetings, I've search around quite extensively on the net and found a few examples that touch on this subject, but the only definitive one that seemed to solve this problem used a temp table in the UDF, which, to my knowledge, is impossible... The problem is thus: I want to create either a stored procedure or a user defined function to return a list of values I can intersperse to use in a WHERE AccountID IN (<values>). This way, if someone were to create a new stored procedure and they wanted to either only select accounts with those IDs or perform a NOT IN and use it to filter. The Solution I'm attempting: My idea is best represented in psuedo-code: - Create a Function that stores all account Ids we relate to a particular account type, in this case, let's say accountsids "100, 101, 102, 407" are all accounts we want to consider "cash". - The function would look something like: CREATE FUNCTION CashAccountIDs() RETURNS TABLE AS BEGIN DECLARE TABLE @t1 (account INT) INSERT INTO @t1 VALUES (100) INSERT INTO @t1 VALUES (101) INSERT INTO @t1 VALUES (102) INSERT INTO @t1 VALUES (407) RETURN @t1 END Then I could call this function by doing something such as: SELECT * FROM Accounts WHERE AccountId IN (dbo.CashAccountIds()) I would presumably do this for other collections of accounts as well, so that I would end up with say 5 functions I could call to filter various types of accounts. Not too certain if I am approaching this the correct way or not, I've been receiving a myriad of errors trying different methods. If I use the function above it tells me "Must declare @t1", so I modified it so @t1 is declared in the RETURNS statement, and the syntax checks then work, but when I attempt to save the function it tells me "Cannot perform alter on fn_cashaccountids because it is an incompatible object type" (The code I use to generate this error is: CREATE FUNCTION fn_cashaccountids () RETURNS @t1 TABLE (i INT) AS BEGIN INSERT INTO @t1 VALUES (100) RETURN END Hopefully I've provided enough but not too much info to sift through, it seems to me this would be something encountered a bit before. Any help is very much appreciated. - Jeff
View Replies !
Does The Group By Have To Include All Fields From The SELECT Clause?
hey all, say i have the following function SELECT GLF_CHART_ACCT.DESCR1, F1ADR_ADDRESS.ADDR1, F1ADR_ADDRESS.ADDR2, F1ADR_ADDRESS.ADDR3, F1ADR_ADDRESS.ADDR_CITY, F1ADR_ADDRESS.ADDR_STATE, F1ADR_ADDRESS.POST_CODE, F1ADR_ADDRESS.PHONE_NBR, F1ADR_ADDRESS.FAX_NBR, F1ADR_ADDRESS.EMAIL_ADDR_NAME, F1ADR_ADDRESS.CONTACT_NAME, F1ADR_ADDRESS.CONTACT_TITLE, GLF_CHART_ACCT.ACCNBRI, F1ADR_ADDRESS.ENTITY_UNIQUE_NBR FROM GLF_CHART_ACCT INNER JOIN F1ADR_ADDRESS ON (GLF_CHART_ACCT.CHART_NAME = F1ADR_ADDRESS.ENTITY_KEY1) AND (GLF_CHART_ACCT.ACCNBRI = F1ADR_ADDRESS.ENTITY_KEY2) GROUP BY GLF_CHART_ACCT.DESCR1, F1ADR_ADDRESS.ADDR1, F1ADR_ADDRESS.ADDR2, F1ADR_ADDRESS.ADDR3, F1ADR_ADDRESS.ADDR_CITY, F1ADR_ADDRESS.ADDR_STATE, F1ADR_ADDRESS.POST_CODE, F1ADR_ADDRESS.PHONE_NBR, F1ADR_ADDRESS.FAX_NBR, F1ADR_ADDRESS.EMAIL_ADDR_NAME, F1ADR_ADDRESS.CONTACT_NAME, GLF_CHART_ACCT.ACCNBRI, F1ADR_ADDRESS.CONTACT_TITLE, GLF_CHART_ACCT.CHART_NAME, F1ADR_ADDRESS.ENTITY_UNIQUE_NBR, GLF_CHART_ACCT.SELN_TYPE1_CODE HAVING CHART_NAME='ARCHART' AND GLF_CHART_ACCT.DESCR1 <> '' AND GLF_CHART_ACCT.SELN_TYPE1_CODE = 'Trade' AND GLF_CHART_ACCT.DESCR1 LIKE '%" + Search + "%' ORDER BY GLF_CHART_ACCT.DESCR1; I get errors if not all the fields are included in the group by clause. what i dont get is why i have to create seperate groups for this query...or am i reading it wrong?? Cheers, Justin
View Replies !
SELECT Clause To Determine If A Field Is Japanese
I am currently trying to find a way in which I can determine if a column in a Select clause is Japanese. The column currently supports English and Japanese Kanjis and other kanas. Is there a way to determine if this column is not English or if it is Japanese without physically looking at it.? Thanks .... Chris
View Replies !
How To Return 1 If Select Count(*) Is &> 0
I'm ashamed! I'm stuck on something so simple.... I want to return a value of 1 if count(*)>0 AND 0 if COUNT(*) is 0 I have currently have this below, but isn't there a better way? SELECT cnt=CASE WHEN (SELECT COUNT(*) FROM MyTable)>0 THEN 1 ELSE 0 END (The full code is rather more complex than this, but the problem is the same) Any suggestions welcome. Cheers! Mark
View Replies !
Select Does Not Return Values
I am new to SQL and I am trouble-shooting a problem with a home-grown app someone else wrote using PERL. It has a web interface with names of boards. I found the program where i need to add the board names into and did that. The new board names show up in the drop-down list in the Web page for the app. Alerts are sent to the new board names and show up on the new boards. Users are granted access to the new boards and they can clear items off the new boards. Yet when i try to use a report function for the new boards i added, nothing is returned. I even ran a simple SQl select statement specifying the new board names and nothing is returned. The older board names, some of which i added myself, return values. I don't know what is going on. Any help is appreciated.
View Replies !
Need To Pass 'UserName' To SQL Select WHERE Clause In The Data Source
Hi.I bet this is a 101 question, but i'd appreciate any help!I am in the 'where...' section of the configure data source wizzard .Column: I grab 'UserName' Operator: I select '='BUT how do I get the UserName (The user is signed into the app) Is it from the Form? Profile? Session?Ive tried profile.name..... THANKS In advance.... Dan
View Replies !
SQL Select Command With Where Clause Variable Based On ASP.NET TextBox
All, Please can someone advise how to use SQL select statement with where clasue which is based on a textBox.text value. ex. below example I set the textbox.text value to a C# variable called TextBoxValue1 but I receive error this is not a valid This is all done in Page_Load event backend code. string strCommandtext = "Select Type.TypeName, Type.TypeDesc FROM Type Where Type.TypeName = TextBoxValue1";
View Replies !
Select Statement Using Multi-list Box Values For WHERE IN SQL Clause
I have a gridview that is based on the selection(s) in a listbox. The gridview renders fine if I only select one value from the listbox. I recive this error though when I select more that one value from the listbox: Syntax error converting the nvarchar value '4,1' to a column of data type int. If, however, I hard code 4,1 in place of @ListSelection (see below selectCommand WHERE and IN Clauses) the gridview renders perfectly. <asp:SqlDataSource ID="SqlDataSourceAll" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT DISTINCT dbo.Contacts.Title, dbo.Contacts.FirstName, dbo.Contacts.MI, dbo.Contacts.LastName, dbo.Contacts.Suffix, dbo.Contacts.Dear, dbo.Contacts.Honorific, dbo.Contacts.Address, dbo.Contacts.Address2, dbo.Contacts.City, dbo.Contacts.StateOrProvince, dbo.Contacts.PostalCode FROM dbo.Contacts INNER JOIN dbo.tblListSelection ON dbo.Contacts.ContactID = dbo.tblListSelection.contactID INNER JOIN dbo.ListDescriptions ON dbo.tblListSelection.selListID = dbo.ListDescriptions.ID WHERE (dbo.tblListSelection.selListID IN (@ListSelection)) AND (dbo.Contacts.StateOrProvince LIKE '%') ORDER BY dbo.Contacts.LastName"> <SelectParameters> <asp:Parameter Name="ListSelection" DefaultValue="1"/> </SelectParameters> </asp:SqlDataSource> The selListID column is type integer in the database. I'm using the ListBox1_selectedIndexChanged in the code behind like this where I've tried using setting my selectparameter using the label1.text value and the Requst.From(ListBox1.UniqueID) value with the same result: Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged Dim Item As ListItem For Each Item In ListBox1.Items If Item.Selected Then If Label1.Text <> "" Then Label1.Text = Label1.Text + Item.Value + "," Else Label1.Text = Item.Value + "," End If End If Next Label1.Text = Label1.Text.TrimEnd(",") SqlDataSourceAll.SelectParameters("ListSelection").DefaultValue = Request.Form(ListBox1.UniqueID) End Sub What am I doing wrong here? Thanks!
View Replies !
Trying To Use The Results Of A Case Statement In My Select List In My WHERE Clause?
I am fairly new with SQL and still learning. I have used a case statemtent for a column in my select list and want to use the results of that statement's field in my WHERE clause but it is not working for me. Here is the code I have so far: SELECT l.loanid, p.investorid, l.duedate, case when pc.duedate >= l.duedate then pc.duedate end as RateDueDate, pc.interestrate FROM loan l inner join participation p on p.loanid = l.loanid inner join paymentchange pc on pc.loanid = l.loanid where p.investorid = '12345' and RateDueDate is not null order by l.loanid, pc.duedate I want to put the results of this case statment in my where clause like highlighted above but it is not working because RateDueDate is not an actual column in the table. Any help would be greatly appreciated. Thanks!
View Replies !
Return The Results Of A Select Query In A Column Of Another Select Query.
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs. I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID. I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages. When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID). Any thoughts as to how I could accomplish such a daring feat?
View Replies !
Two Select Statements In SP Return No Value In Web Form (only)
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 Replies !
Help With Select To Return Duplicate Rows
Can someone look at this and tell me where I went wrong? I'm trying to return all duplicate rows that have the same lastName and Address. It returns rows but they don't look like dups.SELECT TOP (100) PERCENT dbo.tblClient.LastName, dbo.tblClientAddresses.Address FROM dbo.tblClient INNER JOIN dbo.tblClientAddresses ON dbo.tblClient.Client_ID = dbo.tblClientAddresses.Client_ID GROUP BY dbo.tblClient.LastName, dbo.tblClientAddresses.Address HAVING (COUNT(dbo.tblClientAddresses.Address) > 1) ORDER BY dbo.tblClientAddresses.Address
View Replies !
Select Subquery To Return COUNT
I have 2 tables, Jobs and Categories.Each job belongs to a category. At present, I am returning all categories as follows:SELECT categoryID, categoryName FROM TCCI_CategoriesWhat I'm trying to do, is also return the number of jobs assigned to each category, so in my web page display, it would show something like this:Engineering(5)Mechanical(10) etc.My db currently has 5 categories, with only one job assigned to a category. I tried the following sub-query, but instead of returning all the categories with their job counts, it just returns the category that has a job assigned to it:SELECT c.categoryID, c.categoryName, COUNT(j.jobID)FROM TCCI_Categories c, (SELECT jobID, categoryID FROM TCCI_Jobs) jWHERE j.categoryID = c.categoryIDGROUP BY c.categoryID, c.categoryName, j.jobIDThis is the output when I run the query:categoryID categoryName Column1 ---------------- ---------------------- ------------------------------32 Engineering 1 How would I fix this?
View Replies !
Select 3 Values From One Table To Return In One Rs
I need to access a table and return 3 values from it in the samerecordset - ie one iteration of the recordset will have 3 values fromthe same database, I have looked at sub queries but they dont seem tobe able to do what i want.I would be grateful for any guidanceS
View Replies !
Select 3 Values From One Table To Return In One Rs
I am having trouble accessing a table and am seeking out some adviseon how to do it.I have a select statement that joins several tables and everythingworks ok , however I want to access a further table to retrieve 3 rsvalues, so basically I want to retrieve 3 values from the same table -identifying each by a value in that table.Any help or pointer would be very welcome.Simon ChristiestrSearch is a dynamically created search stringCopy of select statement belowsql = "SELECT Master.Master_Key,Master.First_App,Original_Info_D ate,Last_Info_Date,Area.Area_NameAS Area_Name, State_Cntry.State_Cntry_Name, Company.Oper_Name1 ASOper_Name1, Company.Oper_Name2 AS Oper_Name2, " _& " Company.Oper_Name3 AS Oper_Name3, Location.City AS City,Projects.Project_Name AS Project_Name, " _& " Expansion.Expansion_Code AS Expansion_Code,Master.Cap_Size AS cap_size, Cap.Cap_Unit AS Cap_Unit, Master.Cost ASCost, " _& " Master.Status AS Status, Master.Estimated_Year_CompletedAS Estimated_Year_Completed " _& " FROM Master INNER JOIN " _& " Area ON Master.Area_Key = Area.Area_Key INNER JOIN " _& " Company ON Master.Company_Key = Company.Company_Key INNERJOIN " _& " Location ON Master.Location_Key = Location.Location_KeyINNER JOIN " _& " Expansion ON Master.Expansion_Key =Expansion.Expansion_Key INNER JOIN " _& " Cap ON Master.Cap_Key = Cap.Cap_Key INNER JOIN " _& " Projects ON Master.Project_Key = Projects.Project_KeyINNER JOIN " _& " State_Cntry ON Location.State_Cntry_Key =State_Cntry.State_Cntry_Key " _& " WHERE " & strSearch _& " ORDER BY Area.Area_Key, State_Cntry_Name,Oper_Name1,City"The table I need to access is called LECLEC_Key (PK)Master_Key - Database Master KeyCompany_Key - matches the companyLEC_Type_Key - 3 types per company (values L,E or C)Each Master Key will have 3 values in the table, one each of L,E and CHope this makes some sort of senseThanks in advanceSimon Christie
View Replies !
Select 3 Values From One Table To Return In One Rs
strSearch is a dynamically created search stringCopy of select statement belowsql = "SELECTMaster.Master_Key,Master.First_App,Original_Info_D ate,Last_Info_Date,Area.Area_NameAS Area_Name, State_Cntry.State_Cntry_Name, Company.Oper_Name1 ASOper_Name1, Company.Oper_Name2 AS Oper_Name2, " _& " Company.Oper_Name3 AS Oper_Name3, Location.City AS City,Projects.Project_Name AS Project_Name, " _& " Expansion.Expansion_Code AS Expansion_Code,Master.Cap_Size AS cap_size, Cap.Cap_Unit AS Cap_Unit, Master.Cost ASCost, " _& " Master.Status AS Status, Master.Estimated_Year_CompletedAS Estimated_Year_Completed " _& " FROM Master INNER JOIN " _& " Area ON Master.Area_Key = Area.Area_Key INNER JOIN " _& " Company ON Master.Company_Key = Company.Company_Key INNERJOIN " _& " Location ON Master.Location_Key = Location.Location_KeyINNER JOIN " _& " Expansion ON Master.Expansion_Key =Expansion.Expansion_Key INNER JOIN " _& " Cap ON Master.Cap_Key = Cap.Cap_Key INNER JOIN " _& " Projects ON Master.Project_Key = Projects.Project_KeyINNER JOIN " _& " State_Cntry ON Location.State_Cntry_Key =State_Cntry.State_Cntry_Key " _& " WHERE " & strSearch _& " ORDER BY Area.Area_Key, State_Cntry_Name,Oper_Name1,City"The table I need to access is called LECLEC_Key (PK)Master_Key - Database Master KeyCompany_Key - matches the companyLEC_Type_Key - 3 types per company (values L,E or C)Each Master Key will have 3 values in the table, one each of L,E and CHope this makes some sort of senseThanks in advanceSimon Christie
View Replies !
Return SP In A SELECT Statement Column
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 Replies !
|
TRACKER
