Query Help: Need To Return 2nd From Top Record

Jul 20, 2005

i need to retrieve the most recent timestamped records with unique
names (see working query below)

what i'm having trouble with is returning the next-most-recent records
(records w/ id 1 and 3 in this example)

i also need to return the 3rd most recent, 4th, 5th and 6th most recent
- i figure if i can get the 2nd working, 3rd, 4th, etc will be cake

thanks,
brett

-- create and populate table
drop table atest
create table atest(id int not null, name char(10), value char(10),
timestamp datetime)

insert into atest values (1,'a','2','1/1/2003')
insert into atest values (2,'a','1','1/1/2004')
insert into atest values (3,'b','2','1/1/2003')
insert into atest values (4,'b','3','1/1/2002')
insert into atest values (5,'b','1','1/1/2004')

-- select most recent records with distinct "name"s
select a.* from atest as a
where a.id = (select top 1 b.id from atest as b
where b.name = a.name
order by timestamp desc )

/*
query results for above query (works like a charm)
2a 1 2004-01-01 00:00:00.000
5b 1 2004-01-01 00:00:00.000
*/

View 6 Replies


ADVERTISEMENT

Query To Return Record From Hierarchy?

Oct 21, 2013

I am wanting to run a SQL statement whereby i return the ID of any employee's Director.

The database for employees has a reports to field which enables me to see the hierarchy of managers above any employee.

There is also a IsDirector flag that indicates a director.

So essentially i want to run sql that would return the first instance of a director in the hierarchy above any employee.

eg if A reports to B and B reports to C (who is a director) then it returns C.

I basically want the script to run until a director is found.

how would i do this?

View 5 Replies View Related

How Do You Write A Query To Return All Record With Parameters?

Jan 24, 2006

For example:

Select * From Customers Where CustomerName = "Tom" and CustomerLocation = @Location

I know MS Access you can use a "*" to retrieve all records when there is a parameter. What does SQL Sever query use to retrieve all record?

View 14 Replies View Related

Return Last Valid Record When Query Is Outside The Range?

Dec 18, 2012

Using MSSQL 2008 R2

Given the following table

Code:
{ID, PropClass, OffSet, Amount}
{1, 1, 1, .30}
{2, 1, 2, .45}
{3, 1, 3, .50}
{4, 2, 1, .26}
{5, 2, 2, .15}

If I know the exact offset I can query easily enough using PropClass and the exact offset. But what if the offset is not included in the range for a given PropClass? How can I get a query to return the last valid record for a given PropClass from within a join?

For example, if my query contained PropClass = 1 and offset = 4, it should return the Amount of .50 from Record with ID 3

This is a query that I am trying to work on:

Code:
SELECT v.District, v.PropClass, YearAquired, SUM(cost * cnt), SUM(v.Cost * v.Cnt * t.Amount), SUM(v.Tax), COUNT(*)
FROM UPValue v
INNER JOIN UPMaster m on m.Year = v.year and m.Account = v.account
INNER JOIN UPTable T on t.PropClass = v.PropClass and t.Offset = v.Year - v.YearAquired
WHERE v.Year = 2012 and LeaseType = 2
group by v.District, v.PropClass, YearAquired
order by v.District, v.PropClass

Using <= will not work because that would return multiple records from UPTable when the offset is < the max offset.

View 4 Replies View Related

Trying To Return Total Record Count With Query

Feb 26, 2007

I'm trying to return the total records with my query, but I'm getting the following error:

"Item cannot be found in the collection corresponding to the requested name or ordinal."

Here's my query:


set rsFind = conn.Execute ("Select Count(Incident_ID) as TotalCount, Incident_ID, ProblemDescriptionTrunc, Action_Summary, RootCause, Problem_Solution002, " _
& " AssignedTechnician, DATEADD(s, dbo.TTS_Main.DateClosed, '1/1/1970') AS DateClosed, DATEADD(s, dbo.TTS_Main.Date_Opened, '1/1/1970') AS DateOpened, AssignedGroup From tts_main Where ProblemDescriptionTrunc LIKE '%" & prob & "%' And Last_Name LIKE '%" & l_name & "%' " _
& " AND AssignedTechnician LIKE '%" & assigned_tech & "%' And Incident_ID LIKE '%" & ticketnum & "%' AND assignedgroup LIKE '%" & assigned_group & "%' " _
& " Order By DateClosed DESC ")

<%response.write rsfind("TotalCount")%>


Thanks for any help!
Dale

View 10 Replies View Related

How To Return A Numeric Value Based Upon If A Record Is Returned From My Query/Stored Proc.

Oct 2, 2007

 
I need to call the stored procedure below. Basically what I need to know is if the query returns a record?
Note I would be happy if I could just return the number Zero if no records are returned. Can someone please help me out here?Here is my query so far in SQL Server. I just don't know how to return a value based upon the result of the records returned from the query.
GOCREATE PROCEDURE [dbo].[GetNameStatus]( @CountryId decimal, @NameId decimal, @DescriptionId decimal)AS SELECT     Name.Active FROM       Name INNER JOIN               NameDescription ON Name.NameId = NameDescription.NameId WHERE Name.CountryId=@CountryId AND               Name.NameId=@NameId AND NameDescription.DescriptionId=@DescriptionId AND Name.Active='Y'
 

View 3 Replies View Related

How To Write A Query To Return Null For Non-exist Record In An Outer Join.

Jun 2, 2004

I have two tables:

1. tblProducts
columns: ProductID, ProductName

2. tblProductCodes
columns: ProductID, CustomerID, ProductCode

The 2nd table is for storing product codes for customers, in other words, one product can have different ProductCode for different customers. But some customers do not have ProductCode for a ProductID.

I want to create a query to return all the Products and its ProductCode (null is valid) for a specific customer.

I tried:

SELECT dbo.tblProductCodes.ProductCode, dbo.tblProductCodes.CustomerID,
dbo.tblProducts.ProductName,
dbo.tblProducts.ProductID
FROM dbo.tblProducts LEFT OUTER JOIN
dbo.tblProductCodes ON dbo.tblProducts.ProductID = dbo.tblProductCodes.ProductID
WHERE dbo.tblProductCodes.CustomerID = 2

But the query left out all products that does not have ProductCode value in tblProductCodes table for CustomerID = 2. I want all the ProductName returned from query and render null or empty string for ProductCode value if the code does not exist in tblProductCodes table for the customer.

Any help is highly appreciated.

View 4 Replies View Related

Trouble With An ASync Query To Insert A Record And Return The Auto Number Field.

Aug 31, 2007

I get this error when I look at the state of my SQLresults object. Have I coded something wrong?Item = In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.  conn.Open()
Dim strSql As String

strSql = "INSERT INTO contacts (companyId, sourceId, firstName, lastName, middleName, birthday, dateCreated)" _
& "VALUES ('" & companyId & "', '" & sourceId & "', '" & firstName & "', '" & lastName & "', '" & middleName & "', '" & birthday & "', '" & now & "') SELECT @@IDENTITY AS 'contactId'"

Dim objCmd As SqlCommand
objCmd = New SqlCommand(strSql, conn)

Dim aSyncResult As IAsyncResult = objCmd.BeginExecuteReader()

If aSyncResult.AsyncWaitHandle.WaitOne() = True Then


Dim sqlResults As SqlClient.SqlDataReader

sqlResults = objCmd.EndExecuteReader(aSyncResult)

Dim cid As Integer



cid = sqlResults.Item("contactId")
Me.id = cid
conn.Close()
Return cid
Else
Return "failed"


End If  

View 3 Replies View Related

How To Return First Record Child Record And Count

Jan 31, 2006

I've been looking for examples online to write a SPROC to get some data. Here are the tables.

Album_Category
AlbumCategoryID (PK, int, not null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)

Albums
AlbumID (PK, int, not null)
AlbumCategoryID (int, null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)

I need to return:
-[Album_Category].[AlbumCategoryID]
-[Album_Category].[Caption]
-[Albums].[Single AlubmID for each AlbumCategoryID]
-[Count of Albums in each AlbumCategory]

I hope I was fairly clear in what I'm trying to do. Any tips or help would be appreciated. Thanks.

View 3 Replies View Related

SQL Challenge - How To Return A Record Set Starting At A Particular Record?

Feb 25, 2007

I have a directory of user information. What I would like to do isallow someone to search for person X and then return not only theinformation for person X, but also the information for the next 15people following person X sorted alphabetically by lastname.So if someone searched for the lastname = "Samson", it would return:Samson, JohnSaxton, GregScott, HeatherSears, Rebecca.... (15 names following "Samson) ...How do you in SQL return a record set of X records starting atparticular record (e.g. lastname = "Smith)?Thanks in advance.

View 4 Replies View Related

Return Every Nth Record?

Apr 1, 2008

Is there a SQL command that returns every other result or every 3rd result. The reason being is i am using the data to plot a graph with many thousands of points, it would be useful if not every result was return. I.e. too much data to fit within the screens resolution.

Regards Dan Walmsley

View 4 Replies View Related

Return Full Max Record

Mar 24, 2008

Is there a t-sql shortcut/ quick way to return the full max record.

So basically if I had:
prog_area glh description
HS 200 health and social care
EN 300 engineering
HS 400 health care
EN 250 engineering and construction

so grouping by prog_area and taking max glh would return appropriate descriptions like:
HS 400 health care
EN 300 engineering

This is quite a simple example and I would be using it on much larger datasets. I know how to do it by doing a join to itself with max glh but it seems to me there should be an easier way to return the appropriate full record set. Hopefully somethin fast in t-sql

View 2 Replies View Related

Return Record With MAX Column Value

Feb 26, 2008

Hello,
The following query:

SELECT submitRep1 AS Rep, tc, COUNT(tc)AS TCCount
FROM tbl_CYProcessedSales
WHERE
tc NOT LIKE 'T%'
AND
tc NOT LIKE 'R%'
AND ISNUMERIC(TC) = 0
AND NOT submitrep1 = ''
AND Submitrep1 = 'along'
GROUP BY submitrep1, tc

Returns a result like this:

ALONG KL 65
ALONG KP 35

How can I return the one record that contains the MAX(TCCount)?

Thank you for your help!

CSDunn

View 10 Replies View Related

SQL Statement To Return First Unique Record

Jan 19, 2007

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

View 4 Replies View Related

Inserting Carriage Return At The End Of A Record

Jun 15, 2007

Hey everybody!

How do i insert a carriage return at the end of an record that's being sent to a flat file? Currently, I get one long string, and would like for SSIS to put carriage returns at the end of each line.

Any ideas?

Thanks!

Jim Work

View 3 Replies View Related

Return Only The Most Recent Record/row In A Table

Sep 3, 2007

Given the Patients and PatientVisits tables as per below, how do I obtain the most recent (latest) Weight and Height for each patient as per http://www.hazzsoftwaresolutions.net/selectStatement.htm
The result of the query should only return 3 rows/records,not 5. Thank you. Greg


Code Snippet
select p.ID, p.FirstName,p.LastName,DATEDIFF(year, p.DOB, getdate()) AS age
,pv.WeightPounds, pv.HeightInches
from Patients as p
inner join PatientVisits as pv
ON p.ID = PV.PatientID
order by pv.VisitDate desc

INSERT INTO Patients (ID, FirstName,LastName,DOB)
select '1234-12', 'Joe','Smith','3/1/1960'
union
select '5432-30','Bob','Jones','3/1/1960'
union
select '3232-22','Paul','White','5/12/1982'
INSERT INTO PatientVisits (PatientID, VisitDate,WeightPounds,HeightInches)
select '1234-12', '10/11/2001','180','68.5'
union
select '1234-12', '2/1/2003','185','68.7'
union
select '5432-30','11/6/2000','155','63.0'
union
select '5432-30','5/12/2001','165','63.0'
union
select '5432-30','4/5/2000','164','63.5'
union
select '3232-22','1/17/2002','220','75.0'

CREATE TABLE [dbo].[Patients](
[PID] [int] IDENTITY(1,1) NOT NULL,
[ID] [varchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[PatientVisits](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PatientID] [nvarchar](50) NULL,
[VisitDate] [datetime] NULL,
[WeightPounds] [numeric](18, 0) NULL,
[HeightInches] [decimal](18, 0) NULL
) ON [PRIMARY]

View 11 Replies View Related

Query Timeouts When Updating A Record Retrieved Through A Websphere JDBC Datasource - Possible Record Locking Problem

Apr 7, 2008

Hi,

We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.

If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.

Thanks,
Sarah

The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.

This is running on a Websphere Application Server v6.1.

Code snippet - getting the record thru JDBC:


DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();


// wsCon.setAutoCommit(false); //have tried with and without this flag - same results

Statements stmt = wsCon.createStatement();


String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);


if(rs.next()){
System.out.println(rs.getString("lastName"));
}

if (rs != null){
rs.close();
}
if (stmt != null) {

stmt.close();
}
if (wsCon != null) {

wsCon.close();
}

View 1 Replies View Related

Return A Record Depend Of Group Result

Apr 12, 2015

I have a table with records like that.

Group | Value
Team 1 | 0
Team 1 | 0
Team 1 | 1
Team 1 | 1
Team 2 | 0
Team 2 | 0
Team 2 | 0

I want a script that return 0 if all the values of the group are 0 and return 1 if the records of the value is mixed with 0 and 1.

View 1 Replies View Related

Return Value Based On Record With Multiple Types

Jan 15, 2015

I want to return Order records which are one type and don't have the other type.

The issue is I have Orders with which has 2 distriubtion types .

Example
Order 12345 has Type S and Type X.
Order 67891 has Type S

I only want to return Order 67891 that are s Type and does not have type X

View 1 Replies View Related

SP Return Multiple Records For A Single Record

May 3, 2007

I have two tables
TermID, Term
1--- Abc
2--- Test
4--- Tunic

and
TermID, RelatedTermID
1 --- 2
1--- 4
2--- 4

I need to get back something like this

TermID, Term, RelatedTermsInformation
1--- test--- test,tunic#1,4


that above was my solution, get the relatedterms information and comma separate, and then put a # and get all the ids comma separate them and then put the in one field. then I can later parse it in the client

this does not seem like a very good solution ( or is it?)
If posible it would be nice to get something like this

TermID, Term, RelatedTermsInformation
1 test RelatedTermsTwoDimentionalArray

but I am not sure how this idea could be implemented using the capabilities of SQL.

my other option is have the client make one call to the database to get the terms and then lots of another calls to get the relatedTerms, but that will mean one trip to the DB for the list term, and one call for every single term found.

any ideas in how to make this better ?

View 8 Replies View Related

Transact SQL :: Return One Of Two Existing Values For A Record With Same ID

Apr 28, 2015

I have been researching BOL and other online resources but cannot seem to get a definitive answer.

Current Output:
[MemberID][Category][Type]
12345ABCtest
12345XYZtest
12777ABCtest
12888FGDtest

Desired Output:
[MemberID][Category][Type]
12345ABCtest
12777ABCtest
12888FGDtest

Query:
SELECT m.MemberID,
vw.Category,
vw.Type,
FROM dbo.TestVW vw JOIN
dbo.TestMember m ON vw.MemberKey = m.MemberKey
WHERE vw.Type = 'test'
GROUP BY m.MemberID,

[Code] ...

but cannot seem to be able to return one record with its corresponding value criteria.

View 21 Replies View Related

I Need A Quick Way To Return Place/rank/postion Of A Record?

Nov 14, 2007

I run an ASP.NET website with a monthly contest.  Members can enter 1 script for each contest.  Scripts can then be voted on.
There are three relevant SQL tables:
Contest with the field: ContestIDScript with the fields: ContestID, ScriptID, and TitleScriptComment with the fields: ScriptID and Score
Scripts are grouped by contest and come in place (first, second, third, etc.) based on the average of all the scores they received.
I need a QUICK stored procedure that will accept ScriptID as a parameter and return back the place that script came in, in that particular contest.
Currently, I get this information by creating a virtual table and populating it with joined data from Script and ScriptComment.  I order the table by average Score desc.  I then use a select statement against that table using the ScriptID and return back the row number.  This works, but it is very slow.  There are times where I might need to list 300 scripts and I need to do this lookup for each script to display what place they came in.
I need a much faster way to get the place of any script in a contest.  I think the Virtual Table is killing the speed.  I have been trying to find a solution using a view or the new SQL 2005 command Row_Number(), but I have been unsuccessful.
If anyone has a speedy solution, I would love to hear it.
Thanks a lot,
Chris Messineo

View 3 Replies View Related

Joining Tables - Return Name / Description And Keyword For Every Record ID

May 7, 2012

I have 3 tables I'm trying to join:

Table 1: Contains ID, Name, Description
Table 2: Contains ID, Keyword
Table 3: Is lookup table for tables 1 & 2

I need a query to return a table having columns of:
Name, Description, Keyword for every record ID in Table 1

The issue is that table 2 contains multiple keywords for each key ID and my query returns an error: Subquery returned more than 1 value.

When I run this query using a specific key, it returns the correct information the way I want it:

DECLARE @ServiceID VARCHAR(10)
SET @ServiceID = '35'
DECLARE @Name VARCHAR(8000) DECLARE @Desc VARCHAR(8000) DECLARE @Keywords VARCHAR(8000)
SELECT @Name = [DefService].[Name],@Desc = [DefService].[Description],@Keywords = COALESCE(@Keywords + ', ', '') + [DefKeyword].[Name] FROM [DefService] INNER JOIN [DefKeywordServices] ON [DefService].serviceid = [DefKeywordServices].[ServiceID] INNER JOIN [DefKeyword] ON [DefKeyword].[KeywordID] = [DefKeywordServices].[KeywordID] WHERE [DefService].[ServiceID] = @ServiceID PRINT @Name + ' | ' + @Desc + ' | ' + @Keywords
SELECT @Name AS Name,@Desc AS Description,@Keywords AS Keywords

Results:
NameDescriptionKeywords
GoToMyPC - Account RequestRequest a Citrix GoToMyPC account.GTMPC, GoTo, application, software, install, Installation, applications

When I run the same query without a specific key it fails. The results only return a single row containing Name, Description and then ALL keywords for every key ID...very odd behavior.

BTW, I need to do this in a single SQL query and not a stored proc or other method.

View 10 Replies View Related

Return PkID Of Last Saved Record - ASPUpload && @@Identity

Nov 26, 2005

We're using ASPUpload as a tool to upload files to our server and savethe details to SQLServer. However, I have an application where I needto return the pkID of the just saved file. I'm assuming that I coulduse the @@Identity command but cannot get this to function.Has anyone used this command with ASPUpload with an success, or anyother methods that could be used?Thanks.

View 8 Replies View Related

How To Stop Running DTS Package If My View Return Zero Record?

Jan 4, 2007

Hi everyone,

I've created a DTS package runs on every day and night, but now my boss was asking if I can insert an exception code to check the view file.

So.. I need help from you guys, cause I don't know How.

This is my DTS description.

My DB will generate a view called "Calls to Add", then it will run the Transform Data Task and insert into a txt file. once it finished, it will run the Batch file. that is it.

Now My boss wants me to add a checking code between "View to Txt" procedure. If the view has no record inside, than the DTS package should stop and not run.

BUT How??? Can someone please help?? Thanks

View 10 Replies View Related

How To Excute Stored Procedure That Insert Record And Return Last Inserted Value

Jul 18, 2007

Dear all,
I am using C# , asp.net and sql server 2005.
Let me explain the situation.
I have written procedure to insert data into the table and return last inserted value by @@identity variable. Now my question is how do I execute this process so that I can
Get last inserted variable values      
Please help 
thanks 
 

View 3 Replies View Related

T-SQL (SS2K8) :: Return Primary Key From (updated) Record Of Merge Statement

Apr 16, 2014

I'm using a Merge statement to update/insert values into a table. The Source is not a table, but the parameters from a Powershell script. I am not using the Primary Key to match on, but rather the Computer Name (FullComputerName).

I am looking on how-to return the Primary Key (ComputerPKID) of an updated record as "chained" scripts will require a Primary Key, new or used.As an aside: the code below does return the newly generated Primary Key of an Inserted record.

CREATE PROCEDURE [dbo].[usp_ComputerInformation_UPSERT](
@FullComputerName varChar(50) = NULL
,@ComputerDescription varChar(255) = NULL
,@ComputerSystemType varChar(128) = NULL
,@ComputerManufacturer varChar(128) = NULL

[code]....

View 4 Replies View Related

Code That Return Correct Number Of Record When Run Without Aggregate Function

Nov 26, 2013

I have sql code that returns the correct number of record when run without an aggregate function like count(myfield) and group by myfield. It always returns 86 row which is correct when Select DISTINCT is used. As, expected when DISTINCT is not used I get double the number if rows or 172. But when I count(myfield) and group by myfield the count is 172 and not 86. The strangest thing about this is that when I am grouping a set of items

Group 1

Group 2

Group 3

The other group sum up correctly while others don't. What can explain this? Here is the code.

Select DISTINCT ws.p4Districtnumber, ws.cycle, ws.worksetid, count(msi.MeterSessionInputKey) as ASND
from fcs.dbo.WorkSet as ws
left outer join fcs.dbo.WorkAssignment as wa
on ws.WorkSetID = wa.WorkSetID
left outer join fcs.dbo.MeterSessionInput as msi
on wa.worksetkey = msi.worksetkey

[code]....

View 3 Replies View Related

Transact SQL :: Use FOR XML To Return Multiple Child Records Within Each Parent Record

Aug 7, 2015

I have a single complex query.

SELECT 
Col1, -- Header, 
Col2, -- Header, 
Col3, -- Detail
Col4, -- Detail 
Col5, -- Detail
FROM 
TableName;

The query repeats the Header row value for all children associated with the header.I need the output of the query in XML format such that..For every Header element in the XML, all its children should come under that header element//I am using - 

SELECT 
Cols 
FROM 
Table Names 
FOR XML PATH ('Header'), root('root') , ELEMENTS XSINIL 

This still repeats the header for each detail (in the XML) , but I need all children for a header under it.I basically want my output in this format - 

<Header >
  <detail 1>
   </detail 1>
  <Detail 2>
  </Detail 2>
  <detail 3>
  </detail 3>
</Header>

View 2 Replies View Related

How To Return Null Values For Non-exist Record On A Left Join Statement

Oct 16, 2004

I have table Products and Orders that has the following columns:

table Products: ProductID, ProductName
table Orders: OrderID, ProductID, OrderDate, Quantity, Price

The Orders table contains orders placed on all the dates. I want to obtain a list of orders for a particular date, if there is no order for a product on the requested date, I want to return null values for the Quantity and Price fields.

I tried the following select statement:

select Products.ProductName, Orders.Quantity, Orders.Price from Products left join Orders on Products.ProductID = Orders.ProductID where Orders.OrderDate = '10/16/2004'


Where, there are a total of three products (A,B,C) in table Products. Product-C has no order on 10/16/2004, but I want it to return :

ProductName / Quantity / Price
Product-A 5 1.89
Product-B 6 2.43
Product-C null null

Obviously, my sql statement won't work becaue the where clause will filter out Product-C.

Could anyone help me figure out how to modify my sql code to get the resultset I want?

Thanks in advance.

View 2 Replies View Related

SQL Server 2012 :: How To Return User-defined Row When A Record Doesn't Exists

Dec 29, 2014

What I want to do is return a row of data when my query doesn't return a record. I have two tables:

CREATE TABLE dbo.abc(
SeqNo smallint NULL,
Payment decimal(10, 2) NULL
) ON PRIMARY

[Code] ....

So when I run the following query:

SELECT 'abc' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM abc WHERE SeqNo = 1
UNION
SELECT 'def' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM def WHERE SeqNo = 1
abc-1-200.00
abc-1-500.00

As you can see since 1 doesn't exists in table 'def' nothing is returned as expected. However, if a row isn't returned I want to be able to enter my own row such as

abc-1-200.00
abc-1-500.00
def-0-0.00

View 4 Replies View Related

Attempt To Return Record Set In INSERT...EXEC Statement From ODBC Source(non MSSQL)

Jan 17, 2007

Greeting.

I use OdbcConnection inside clr procedure, for getting data. If I use simple EXEC dbo.clr_proc - all is OK. If I use INSERT...EXEC I recive error message: Distributed transaction enlistment failed.

I set MSDTC security options for No Authentification and Allow inbound and Allow outbound, but it's no use.

Have this problem solution? May be, I must use another method to get my data?



P.S. Linked Servers and OPENQUERY is not applicable. Sybase not describe columns in stored proc result set and one stored proc may return different result set by params.

P.S.S. Sorry for bad english.









View 1 Replies View Related

Return Result From One Query As A Column In Other Query

Jun 3, 2004

I'm having a bit of a trouble explaining what I'm trying to do here.

I have 3 "source" tables and a "connecting" table that I'm going to use

tblContacts - with contactID, ContactName etc
tblGroups - with GroupID, GroupName
tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups)

They are related in a table called
tblContactsGroupConnection - with ContactID, GroupID and SubGroupID

One contact can be related to many subgroups.
What I want is a list of all contacts, with their IDs, names and what groups they are related to:

ContactID, ContactName, [SubGroupName1, SubGroupName2, SubGroupName3]
ContactID, ContactName, [SubGroupName1, SubGroupName3]
ContactID, ContactName, [SubGroupName3]

I'm sure there's a simple solution to this, but I can't find it. Any help appreciated. :)

Kirikiri

View 1 Replies View Related







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