SELECT WHERE RowID Is Not From Results Of Another SELECT

Nov 16, 2007

I have one query which uses a join query to gather all the projects that should show up in someone's list over a period of time (returns and id (int) and name (varchar) paired dataset). I want to do a separate query that takes that list and selects all projects (same paired set ... id and name) EXCEPT where it matches an id on a row of the given result set.
 The one query looks like this ..
DECLARE @startDate datetime
DECLARE @endDate datetime
DECLARE @userId UNIQUEIDENTIFIER

SELECT @startDate = ppStartDate FROM ppTablewhere payPeriodID = @payPeriodID
SELECT @endDate = ppEndDate FROM ppTable WHERE payPeriodID = @payPeriodID
SELECT @userId = userID FROM usersTable WHERE userName = @userName

SELECT DISTINCT p.projectID, p.projectName
FROM projectsTable p
LEFT JOIN projectMemberhsip m ON m.ProjectId = p.ProjectId
LEFT JOIN timeEntryTable t ON t.ProjectID = p.ProjectId
WHERE
t.TimeEntryUserId = @userID AND t.TimeEntryDate >= @startDate AND t.TimeEntryDate <= @endDate
OR
m.UserId = @userID
 
I want to get the same selection from projectsTable WHERE it's not anything from this result set.
Haven't been able to get it by modifying the WHERE logic.  Is there a way to select all WHERE id != (resultSet from this SELECT)?
 
TIA!

 

View 5 Replies


ADVERTISEMENT

SELECT-Using Correlated Subqueries: Just Name In Results &&amp; 0 Row Affected In One Of MSDN2 SELECT Examples

Jan 11, 2008

Hi all,
I copied and executed the following sql code in my SQL Server Management Studio Express (SSMSE):
--SELECTeg8.sql from SELECT-Using correlated subqueries of MSDN2 SELECT Examples--

USE AdventureWorks ;

GO

SELECT DISTINCT Name

FROM Production.Product p

WHERE EXISTS

(SELECT *

FROM Production.ProductModel pm

WHERE p.ProductModelID = pm.ProductModelID

AND pm.Name = 'Long-sleeve logo jersey') ;

GO

-- OR

USE AdventureWorks ;

GO

SELECT DISTINCT Name

FROM Production.Product

WHERE ProductModelID IN

(SELECT ProductModelID

FROM Production.ProductModel

WHERE Name = 'Long-sleeve logo jersey') ;

GO

=========================================
I got:
Results Messages
Name o row affected
========================================
I think I did not get a complete output from this job. Please help and advise whether I should search somewhere in the SSMSE for the complete results or I should correct some code statements in my SELECTeg8.sql for obtaining the complete results.

Thanks in advance,
Scott Chang

View 5 Replies View Related

Implement SELECT Starement On A Results Of Prev SELECT

Dec 22, 2002

Hi,
im getting from my first select a list of pairs of codes (let say the codes r of products.)
so i have something like:

FirstCode SecondCode
1 1
2 5
4 2
... ...
now i want to get the name of each product so it whould be like:

FirstCode,FirstName,SecondCode,SeconeNam

the names stored in other table.
how can i do it?
thanks

Dovalle

View 1 Replies View Related

Transact SQL :: Pass Results From Select To Another Select

Oct 13, 2015

I've got a select as follows:

select computer, count(*) as MissedCount  from WInUpdates_Neededreq
WHERE LoggedDate BETWEEN DATEADD (DAY, - 5, GETDATE()) AND GETDATE() and LastReportTime !< DATEADD (DAY, -5, GETDATE())
group by computer

I need to make a join onto another table but don't want to lose the coutn(*) as MissedCount.

How can I join to another table and still keep the count form the original table.  I want ot join to tblogons.workstationname and return computer from the original query...

View 16 Replies View Related

Is There A Way To Hold The Results Of A Select Query Then Operate On The Results And Changes Will Be Reflected On The Actual Data?

Apr 1, 2007

hi,  like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right?  so, is there something that i can use to hold those records so that i can do the delete and update just on those records  and don't need to query twice? or is there a way to do that in one go ?thanks in advance! 

View 1 Replies View Related

Return The Results Of A Select Query In A Column Of Another Select Query.

Feb 8, 2008

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 9 Replies View Related

Select Query Based Upon Results Of Another Select Query??

Sep 6, 2006

Hi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return.
This is implemented as a stored procedure

declare @OwnerIdent varchar(7)
set @OwnerIdent='A12345B'

SELECT table1.val1 FROM table1 INNER JOIN table2
ON table1. Ident = table2.Ident
WHERE table2.Ident = @OwnerIdent

'Now for each result of the above I need to run the below query

SELECT Clients.Name , Clients.Address1 ,
Clients.BPhone, Clients.email
FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey
WHERE Growers.PIN = @newpin)

'@newpin being the result from first query

Any help appreciated

View 4 Replies View Related

SQL Server 2012 :: Adding RowID To Existing Table - Inconsistent Results

May 6, 2015

I am getting inconsistent results when BULK INSERTING data from a tab-delimited text file. As part of my testing, I run the same code on the same file again and again, and I get different results every time! I get this on SQL 2005 and SQL 2012 R2.

We have an application that imports data from a spreadsheet. The sheet contains section headers with account numbers and detail rows with transactions by date:

AAAA.1234 /* (account number)*/
1/1/2015 $150 First Transaction
1/3/2015 $24.233 Second Transaction
BBBB.5678
1/1/2015 $350 Third Transaction
1/3/2015 $24.233 Fourth Transaction

My Import program saves this spreadsheet at tab-delimited text, then I use BULK INSERT to bring the data into a generic table full of varchar(255) fields. There are about 90,000 rows in each day's data; after the BULK INSERT about half of them are removed for various reasons.

Next I add a RowID column to the table with the IDENTITY (1,1) property. This gives my raw data unique row numbers.

I then run a routine that converts and copies those records into another holding table that's a copy of the final destination table. That routine parses though the data, assigning the account number in the section header to each detail row. It ends up looking like this:

AAAA.1234 1/1/2015 $150 First Purchase
AAAA.1234 1/3/2015 $24.233 Second Purchase
BBBB.5678 1/1/2015 $350 Third Purchase
BBBB.5678 1/3/2015 $24.233 Fourth Purchase

My technique: I use a cursor to get the starting RowID for each Account Number: I then use the upper and lower RowIDs to do an INSERT into the final table. The query looks like this:

SELECT RowID, SUBSTRING(RowHeader, 6,4) + '.UBC1' AS AccountNumber
FROM GenericTable
WHERE RowHeader LIKE '____.____%'

Results look like this:

But every time I run the routine, I get different numbers!

Needless to say, my results are not accurate. I get inconsistent results EVERY TIME. Here is my code, with table, field and account names changed for business confidentiality.

TRUNCATE TABLE GenericImportTable;
ALTER TABLE GenericImportTable DROP COLUMN RowID;
BULK INSERT GenericImportTable FROM 'SERVERGeneralAppnameDataFile.2015.05.04.tab.txt'
WITH (FIELDTERMINATOR = ' ', ROWTERMINATOR = '', FIRSTROW = 6)
ALTER TABLE GenericImportTable ADD RowID int IDENTITY(1,1) NOT NULL
SELECT RowID, SUBSTRING(RowHeader, 6,4) + '.UBC1' AS AccountNumber
FROM GenericImportTable
WHERE RowHeader LIKE '____.____%'

View 3 Replies View Related

Rounding Down In SQL Select Results

Aug 23, 2006

I am trying to round this data, but can't find a  good resource on this.  If my results are 15.6 I need it to round down to 15.5 and if my results are 15.4 I need it to round down to 15.0.ROUND(DATEDIFF(mm, employee.emp_begin_accrual, GETDATE())
* employee.emp_accrual_rate - SUM(ISNULL(request_1.request_duration, '0')), .5) Any help would be greatly appreciated.  A link to a good reference on rounding would help too.Thanks in advance!!!

View 8 Replies View Related

How To Use Results Of Select As SP Input

Oct 17, 2012

I need to insert in TableB the results of a select over TableA so i used:

Insert into TableB (col1, col2, col3) select c1, c2, c3 from TableA

and works OK.

But now i need to use a Stored Procedure "Insert_in_TableB" that handles all the process of checking values, inserting data, logging steps, etc etc. That receives via its parameters the values for validate, etc and then insert them in TableB.

So, how can i execute that procedure (and define its parameters) in a easy way? Something like

Exec Insert_in_TableB (select c1, c2, c3 from TableA)
or
Select c1, c2,c3 into Insert_in_TableB...

Don't want to use cursors.

View 1 Replies View Related

Select 4 Most Recent Results

Mar 29, 2013

I am creating a database in SQL2K for collating test results taken at regular intervals across several different sites and frequencies. The table will look something like this:

SiteDate Data
A01/01/2013 ...
B02/01/2013 ...
C03/01/2013 ...
A04/01/2013 ...
B05/01/2013 ...
C06/01/2013 ...
And so on...

In total there will be about 300 sites, with up to 12 records per site every year. I want to be able to create a view showing the 4 most recent results for each site. Is there a simple way of doing this? Obviously getting the most recent result for each site is quite straight forward, but I can't work out how to get the last four. Unfortunately this is on a 2k box, although I could (at a push) use a 2k8 box if needed.

View 3 Replies View Related

Can You Filter SELECT Results?

Jul 23, 2005

I want to create a stored procedure that returns a list of records froma table. But depending on a userID value given only certain recordswill be returned that they have access to.I think this might be hard to do in a single SELECT statement becausethe user might also belong to a group that might have permission, etc.Can you do something like this pseudo code in a T-SQL procedure?DECLARE cur CURSOR FOR SELECT * FROM myTableOPEN curFETCH NEXT FROM curWHILE @@FETCH_STATUS = 0BEGINif( accessGranted(curRecord.id) ){ addRecordToResultSet() }else { ommitRecordFromResultSet() }END

View 11 Replies View Related

Select Like And Order Results

Apr 25, 2008

Hello there,

I have two tables I selecting name using like with %string% from the two tables but I need to order the result comes from the two table:
1- the exact match for the search string come first from the two table.
2- and the partial match comes last after the exact match.

this is my DDL for the two tables :


USE [Northwind]
GO
/****** Object: Table [dbo].[Person] Script Date: 04/25/2008 14:33:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Person](
[PersonID] [int] NULL,
[Type] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


second table:
USE [Northwind]
GO
/****** Object: Table [dbo].[Members] Script Date: 04/25/2008 14:33:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Members](
[MemberID] [int] NULL,
[Type] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF


and this my search query I have it in a stored Proc.


select *

from

(





SELECT PersonID, Type, Name, email


FROM Person
WHERE (Name LIKE '%'@Name'%')
union all

SELECT PersonID, Type, Name, email


From Members
WHERE (Name Like '%'@Name'%' )

) Y



Order by

Case[Name] when @Name Then 1 Else 2 End,

Case[Name] when 'm' Then 1 Else 2 End

Thank you for your time
Sms

View 5 Replies View Related

Concatinating Select Results

Jan 18, 2006

Hey everyone,

I have an SSIS conversion issue. I'm pulling two tables from a DB2 database into SQL 2005. One table has a list of work orders, and the other has a list of work order comments. There is a unique identifier between the two tables so that a join can be used, however, due to size limitations, I need to be able to combine both tables.

The end result will be replicated out for SQL Mobile Edition and the file is too large when both tables exist so I am wanting to concatinate all the comments for each work order into a single text field in the work orders table.

Here is what I am wanting to accomplish:

UPDATE tblWorkOrdersSET Comments = (SELECT Comments
FROM tblComments
WHERE tblWorkOrders.ReqNum =
tblComments.ReqNum)

I know that this statement will not work because there is a one-to-many relationship between the tables so each work order could get multiple results.

I would appreciate any suggestions.

Thanks,

Lee.

View 2 Replies View Related

Displaying Select Results On The Page

Aug 28, 2006

I have the following select statement on my page: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:prbc_hrConnectionString %>"
SelectCommand="SELECT emp_lname + ', ' + emp_fname + ' ' + emp_minitial + '.' AS emp_fullname FROM employee WHERE (emp_username = @emp_username)">
<SelectParameters>
<asp:SessionParameter Name="emp_username" SessionField="Username" Type="String" />
</SelectParameters>I want to say "Welcome, emp_fullname" at the top of the page, but can't figure out how to write the results to the page.  I am moving to ASP.NET 2.0 from PHP and am banging my head against the wall trying to figure out how to do these little things. I appreciate any help you can give.

View 1 Replies View Related

What All I Can Bind To My Select Query Results?

Nov 7, 2007

my question is what all can i bind my results of a select query to.i know we can use Dataset and Sqldatareader. can we use something else like hashtables. for example we can have:reader = cmd.ExecuteReader();  can we have something likeHashtable = cmd.ExecuteReader();

View 5 Replies View Related

Select And Then Inserting Results Into Two Different Tables

Dec 2, 2003

Hello!
I've got the following procedure:
ALTER PROCEDURE [GetTimeDiff2] (@ID int) AS
select
A_ProspectPipeline.ID,
(case when [Completion Date] is null
then '13'
else
case when YEAR([Completion Date])>year(GETDATE())
then '13'
else
case when YEAR([Completion Date])<year(GETDATE())
then '1'
else month([Completion Date])
end
end
end)-
(case when YEAR([Start Date])=year(GETDATE())
then month([Start Date])
else
case when YEAR([Start Date])<year(GETDATE())
then '1'
else '13'
end
end)as [CY],

(case when [Completion Date] is null
then '13'
else
case when YEAR([Completion Date])>year(GETDATE())+1
then '13'
else
case when YEAR([Completion Date])<year(GETDATE())+1
then '1'
else month([Completion Date])
end
end
end)-
(case when YEAR([Start Date])=year(GETDATE())+1
then month([Start Date])
else
case when YEAR([Start Date])<year(GETDATE())+1
then '1'
else '13'
end
end)as [NY]

from a_ProspectPipeline where A_ProspectPipeline.ID = @ID

What i need to do is insert the two returned values [NY] + [CY] into two different tables.
Can anyone help me with this?

View 2 Replies View Related

Stored Procedures (SELECT Then Use Results)

Jun 17, 2004

Hi,

I need to keep track of the number of hits on a particular page. Im using a stored Procedure

What I want to do is get the number of hits and increment it by one :)

ie: Sub Procedure should be like below

SELECT noOfHits WHERE pageName = 'bla bla'

noOfHits = noOfHits + 1 etc.

Also, some of the pages will be added and deleted all the time, so before I increment the noOfHits variable I need to check that the pageName 'bla bla' exists. AND if it doesnt I need to create a pageName called 'bla bla'


What I need to do in essence is:

1. Check that a particular row exists. if it doesnt create it.
2. Increment a value (by one) to a column in this particular row.

Phew. Hope you got that. Any ideas much appreciated,

Thanks,

Pete

View 4 Replies View Related

Select Top N Results In A Group; Count(*)&<=n

Jan 18, 2003

Two tables: CompanyPrices(CompanyID, ProductID, Price), CompanyRegion(CompanyID, Region)
ProductID is the primary key.

I want to get 10 smallest prices in each Region. In other words, I am looking for 10 cheapest prices in each region. So, if there are 20 regions, I should get excatly 200 rows having prices for products from 200 companies if there were at least 10 companies in each region.

I tried the follwoing, but get incorrect results.

select S1.Region, S1.Price from (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join
(select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2 on S1.Region = S2.Region
group by S1.Region, S1.Price having count(*)<=10 order by S1.Region, S1.Price

However, if I want to get 10 cheapest products for each company, the above sql works by modifying the join condition. Instead of S1.Region = S2.Region , I use S1.CompanyID = S2.CompanyID and I get correct results for 10 cheapest products for each company.

select S1.CompanyID, S1.Price from (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join
(select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2 on S1.CompanyID = S2.CompanyID
group by S1.CompanyID, S1.Price having count(*)<=10 order by S1.CompanyID, S1.Price

I am not sure what is wrong in the first query and why it does not work when the second one works. Could someone help in making the first query work to give me correct results?

View 2 Replies View Related

Cycling Through Results Of A Select Statement

Aug 30, 2004

I am new to stored procedures and T-SQL so please stick with me. I have a table that holds information about companies. I am trying to write a stored procedure that when run will query that table and find out if there are more than one entry of that company. All company names in that table must be unique (they can only occur once), if they occur more than once I need to flag it for reporting.

So what is the best way to go about this? Essentially what i was thinking was doing a select * on the table and then going from the first entry to the last and at each entry running a select * from table where companyname = @nameofcompany. @nameofcompany would be the name for that entry. If the select statement revealed more than one entry then i would know there was a problem.

Like I said I am new and this is probably very simple but i need a little help getting started

thanks

View 6 Replies View Related

How To Produce A Summary Row For Select With No Results

May 28, 2013

way to insert a summary row where there are no query results - in effect a row to say there were no results for today. I am using SQL Server 2008.

I have created a report which uses 2 tables to store all daily transactions & a one row summary of those daily transactions for upto 4 countries - both tables are updated nightly by 2 SP and the unique identifier is a field called ID which contains the country code and date;

table1_detail;
store all daily transactions for 4 countries and the unique identifier is an ID which stores the country code and date (XX_ddmmyy) eg GB_280513. There may be transactions for upto 4 countries(GB/FR/IE/DE) per day or there may be none.

Table1:

Code:
ID DATE CUST VALUE ERROR ...etc
GB_280513 280513 101 10.50 YES
GB_280513 280513 102 90.00 NO
FR_280513 280513 201 25.00 NO
IE_280513 280513 301 60.00 NO
FR_280513 280513 202 10.50 YES
FR_280513 280513 203 10.50 NO
GB_280513 280513 103 20.00 YES
GB_280513 280513 104 5.00 YES

table2_summary;
summary of daily transactions per unique identifier (XX_ddmmyy) in table1_detail. When there are transactions per unique identifier in table1 the SP inserts a row summarising quantity, value, error count etc.

Table2:

Code:
ID DATE NO OF TRANS VALUE NO OF ERROR ...etc
GB_280513 280513 4 125.50 3
FR_280513 280513 3 46.00 1
IE_280513 280513 1 60.00 0

I need the insert into table2_summary to be able to insert a row everyday for each country showing zeros where there were no transactions for that day (ID) in table1;

Table2 expected results:

Code:
ID DATE NO OF TRANS VALUE NO OF ERROR ...etc
GB_280513 280513 4 125.50 3
FR_280513 280513 3 46.00 1
IE_280513 280513 1 60.00 0
DE_280513 280513 0 0.00 0

When there are no transactions in table1 for a day (ID) then the summary needs 4 zero rows inserted.

View 3 Replies View Related

Select And Format Zip Code Results

May 19, 2008

In my select statement I concatenate city+state+zipcode. In some cases the zipcode is 9 digits but is missing the '-'. How can I format zipcode to be '#####-####' when > 5 characters from the select statment.
Thanks,
Ken

View 1 Replies View Related

Count # Of Results From SELECT Statement

Jun 6, 2008

Hey all - VERY new to SQL so I apologize if I butcher normally trivial things :)

Looking to run a query that will retrieve the number of results returned from a select statement...

Currently have a LicenseID table with a Software column...the statement that works on it's own that i've got is:

SELECT * FROM Software WHERE LicensesID = 2

Currently when I run that with the data so far I get 4 results returned to me...how can I add to that statement so that instead of displaying the results themselves, I just get the number 4 returned as a total number of results?

Thanks all!

View 2 Replies View Related

Select Statement That Groups Results

Dec 16, 2013

I have a table with three columns. Name, book#, category.

I need to write a select statement that looks at the book# and category and returns all the names that match that into one joined our combined result. So if my name column had john, Jane, Jim, jack, and Steph in unique rows that matched it would bring back a resulted formatted as. John, Jane, Jim, jack, Steph. So I could place that in a field for reference.

View 4 Replies View Related

Select Distinct Results From Where Clause?

Jul 14, 2014

Just wondering if it's possible to select distinct results from the where clause?

View 3 Replies View Related

Send Per Email SELECT Results With More Than 1 Row

Nov 20, 2014

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"..It is about a Stored Procedure, which not only should output the number of opportunities with status "Pending" or "Accepted" but also the name of the opportunities with these statuses. Following points:

1. The SELECT that outputs the names of the opportunities may in some cases return more than one value. I created then the CURSOR CUR_TEST.

2. The code should return the names of Opportunities, namely after "The opportunities are:" and these must be sent to the recipients of an email. How can I do this, so the Names of the Opportunities and the rest of the email can be sent by email?. I mean, the concatenation with SET or SELECT doesn't work. I get as email only the output of the last row of the SELECT with cursor CUR_TEST.

3. I add an example how the output should be.

The following is the code:

BEGIN

declare
@V_USER VARCHAR(20),
@V_NAME VARCHAR(100),
@V_QUANTITY INT,
@V_EMAIL VARCHAR(60),
@V_BODY VARCHAR(MAX),
@V_QUANTITY_ACTIVE int,

[code]...

An example how the Output in the email body should be:

Dear MyName,
You have 47 Opportunities with status Pending.
The Opportunities are:

Name of Opportunity 1
Name of Opportunity 2
Name of Opportunity 3

View 1 Replies View Related

How Do You Limit The Number Of Results In Select?

May 29, 2007

In other SQL programs I have seen the use of Limit to tell it how many rows to return.

Is there an equivalent in MS-SQL that will let me do a quick Select clause and tell it how many rows to return.

In other words if I just wanted to see the first 10 rows what would I add to Select * from tableA

Thanks!

View 2 Replies View Related

Using Select Results In A Stored Procedure

Jul 23, 2005

I'm sure this is an obvious question but much of SQL is new to me.I have a stored procedure and I want to use the results of a selectstatement elsewhere in the stored prcedure. The select statement willreturn at most one record and only one column, here's an example :select top 1 Sequence from MyTablewhere ParentId=4 and Sequence > 200 order by sequenceCan I put this result in a variable? Do I have to use SELECT INTOTempTable and refer to TempTable and then drop the table at the end ofthe stored procedure? I have read a little about cursors, are thesethe way to go?I'm confused as to what my options are, any help or links to help willbe appreciated.Thanks for reading.

View 1 Replies View Related

Control Over Select Output Results

Jul 20, 2005

Hi All !Is it possible to get rid of these dash symbols which are underliningthe column name when recordset is returned after query execution ?For example, using isql.exe:SELECT 'blah'goproduces the following results:----blahWhat I want to achieve is justblahI know that SET NOCOUNT ON switches the "X row affected" thing. Buthow about column headers ?Thanks for your time,Seeker

View 5 Replies View Related

How Do I Print The Results Of My SELECT Statement?

Mar 15, 2008



After running a select statement in vb 6, how do i send the results to the printer. Here is the code i've written so far:

Private Sub ProcSelectRecords()
Dim MyConn As ADODB.Connection

Set MyConn = New ADODB.Connection
Dim MyRecSet1 As New ADODB.Recordset

MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:DocumentsA2 Computing Courseworkdb1.mdb"
MyConn.Open

Set MyRecSet1 = MyConn.Execute("SELECT * FROM tblItem_Sales")

MyConn.Close
End Sub


Im using MS Access as my backend

any help would me much appreciated.

View 1 Replies View Related

Weird INSERT INTO SELECT Results

May 20, 2006

Hi all,

I am having a weird problem occur when trying to insert missing records based on an INSERT INTO...SELECT statement.

Basically, I am populating a temporary table with data, then inserting into a main table those records from the temporary table that aren't in the main table. In addition, I am updating those records that are in the main table.

When I call the following SQL, all of the records from the temporary table are inserted even though those records already exist. Bizzarrely the update statement works perfectly.

INSERT INTO
AffiliateCategories (Identifier, AffiliateID, NetworkID, AffiliateCategoryName, NetworkCategoryName)
SELECT
Identifier,
AffiliateID,
1,
AffiliateCategoryName,
NetworkCategoryName
FROM
#TempTradeDoublerCategories
WHERE
#TempTradeDoublerCategories.AffiliateCategoryName NOT IN
(
SELECT
AffiliateCategoryName
FROM
AffiliateCategories
)


I am not sure why it is doing this and it's driving me crazy. If anyone has any ideas why this is happening, or knows of a better way to accomplish this then please let me know.

Are there some issues with string comparisons going on here?

The table that populates the temp table is defined as:

CREATE TABLE [dbo].[Staging_TradeDoubler_Categories](
    [id] [int] NULL,
    [name] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
    [merchantName] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
    [TDCategories_Id] [int] NULL,
    [AffiliateID] [int] NULL
) ON [PRIMARY]


Full code listing:

IF OBJECT_ID('tempdb..#TempTradeDoublerCategories') IS NOT NULL
DROP TABLE #TempTradeDoublerCategories

CREATE TABLE #TempTradeDoublerCategories
(
Identifier INT,
AffiliateID INT,
AffiliateCategoryName VARCHAR(255),
NetworkCategoryName VARCHAR(255)
)



INSERT INTO
#TempTradeDoublerCategories (AffiliateCategoryName)
SELECT DISTINCT
CAST(merchantName AS VARCHAR(255)) AS MerchantName
FROM
Staging_TradeDoubler_Categories
ORDER BY
MerchantName



UPDATE
#TempTradeDoublerCategories
SET
#TempTradeDoublerCategories.Identifier = Staging_TradeDoubler_Categories.id,
#TempTradeDoublerCategories.AffiliateID = Staging_TradeDoubler_Categories.AffiliateID,
#TempTradeDoublerCategories.NetworkCategoryName = Staging_TradeDoubler_Categories.[name]
FROM
Staging_TradeDoubler_Categories
WHERE
Staging_TradeDoubler_Categories.merchantName = #TempTradeDoublerCategories.AffiliateCategoryName



UPDATE
#TempTradeDoublerCategories
SET
#TempTradeDoublerCategories.AffiliateID = Staging_TradeDoubler_Affiliates.AffiliateID
FROM
Staging_TradeDoubler_Affiliates
WHERE
#TempTradeDoublerCategories.AffiliateID = Staging_TradeDoubler_Affiliates.TradeDoublerAffiliateID

Print 'Inserting records'

INSERT INTO
AffiliateCategories (Identifier, AffiliateID, NetworkID, AffiliateCategoryName, NetworkCategoryName)
SELECT
Identifier,
AffiliateID,
1,
AffiliateCategoryName,
NetworkCategoryName
FROM
#TempTradeDoublerCategories
WHERE
#TempTradeDoublerCategories.AffiliateCategoryName NOT IN
(
SELECT
AffiliateCategoryName
FROM
AffiliateCategories
)

Print 'Updating records'

UPDATE
AffiliateCategories
SET
AffiliateCategories.AffiliateCategoryName = #TempTradeDoublerCategories.AffiliateCategoryName,
AffiliateCategories.NetworkCategoryName = #TempTradeDoublerCategories.NetworkCategoryName,
DateModified = GETDATE()
FROM
#TempTradeDoublerCategories
WHERE
#TempTradeDoublerCategories.AffiliateCategoryName IN
(
SELECT
AffiliateCategories.AffiliateCategoryName
FROM
AffiliateCategories
)



PRINT 'Dropping table'


IF OBJECT_ID('tempdb..#TempTradeDoublerCategories') IS NOT NULL
DROP TABLE #TempTradeDoublerCategories

View 1 Replies View Related

Select Statement Returns No Results

Jun 6, 2006

I am using the following conditional select statement but it returns no results.


Declare @DepartmentName as varchar
Set @DepartmentName = null
Declare @status as bigint
Set @status = 4
IF (@DepartmentName = null) BEGIN

SELECT CallNumber AS [Call Number], Problem, Solution, Note
FROM AdminView
WHERE (Status = @status)
ORDER BY CallLoggedDT
END
ELSE IF (@DepartmentName <> null) Begin

SELECT CallNumber AS [Call Number], Problem, Solution, Note
FROM dbo.AdminView
WHERE (Status = @status) AND
(DepartmentName = @DepartmentName)
ORDER BY CallLoggedDT
end



when i run the 2nd half by itself it tells me to declare @status but not @departmentname. whats going on???



Chris Morton

View 3 Replies View Related

SELECT Statement Not Giving Me Desired Results

Jun 27, 2006

I have the following SELECT statement attached to a dropdown box:
SELECT [Workshop] FROM [Workshops] WHERE Workshop <> (SELECT Workshop FROM Workshop_Registration WHERE FullName = @FullName AND Completed = @Completed) ORDER BY [Workshop]
I am trying to get all workshops (50 or more) from the WORKSHOPS table that the logged in user is not already registered for.  This works perfectly as long as the student is registered for at least 1 class.  It populates the dropdown with all of the other classes.  If they aren't registered for a class then it doesn't list any classes.  The problem is definitely the subquery, but how do I make it to where if the subquery doesn't return any results (student not registered for anything), I get all of the workshops in the dropdown?  Any help is appreciated!
MikeD

View 2 Replies View Related







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