SQL Call INNER JOIN Returns Too Many Records

Jan 25, 2006

I have my SQL call:

SELECT     CallLog.CallID, Journal.HEATSeq
FROM         CallLog INNER JOIN
                      Journal ON CallLog.CallID = Journal.CallID

There are multiple enteries in the Journal table for every entry in the CallLog table, so I receive multiple records:

CallID           HEATSeq
00000164     983290904
00000164     983291548
00000164     983295209
00000231     984818271
00000231     985194317
00000231     985280248

I only want to return the LAST record in the Journal table, so the output will be:

CallID           HEATSeq
00000164     983295209
00000231     985280248

Can this be done directly in the SQL call?

View 7 Replies

How To Call A Sproc From Within A UDF That Returns A Table?

Oct 5, 2007

Hi. This is a SQL question.
I am trying to wrap a stored procedure with a UDF so I can do selects against the result. The following doesn't work, but is it possible to do something like:
Create Function test()returns @tmp table ( myfield int)asbegin insert into @tmp (field1) Exec dbo.MySprocWhichRequires3ParmsAndReturnsATable 5, 6, 10 output returnend
Thanks

View 1 Replies View Related

Right Join Returns Same Results As Left Join

Feb 5, 2015

Why does this right join return the same results as using a left (or even a full join)?There are 470 records in Account, and there are 1611 records in Contact. But any join returns 793 records.

select Contact.firstname, Contact.lastname, Account.[Account Name]
from Contact
right join Account
on Contact.[Account Name] = Account.[Account Name]
where Contact.[Account Name] = Account.[Account Name]

View 3 Replies View Related

Can You Call A Stored Proc That Returns A Table Variable Using ADO?

Jan 8, 2004

I have a stored proc that inserts into a table variable (@ReturnTable) and then ends with "select * from @ReturnTable."

It executes as expected in Query Analyzer but when I call it from an ADO connection the recordset returned is closed. All the documentation that I have found suggests that table variables can be used this way. Am I doing somthing wrong?

View 1 Replies View Related

Join Only Returns The Read Rows :|

Nov 1, 2005

Hi all,

I am trying to build a association table (t2) to store a list of users
have viewed an item in my records table (t1). My goal is to send the
UserID parameter to the query and return to the user a read / not read
marker from the query so I can handle the read ones differently in my
.net code. The problem is that I cannot work out how to return anything
but the read data to the client. So far my stored proc looks like this

DECLARE @UserID AS Int -- FOR TESTING
SET @UserID = 219 -- FOR TESTING

SELECT t1.strTitle, t1.MemoID, Count(t2.UserID) AS ReadCount,t2.UserID

FROM t1
LEFT OUTER JOIN
t2 ON t1.MemoID = t2.MemoID

WHERE t2.UserID = @UserID

GROUP BY t1.MemoID, t1.strTitle,t2.UserID

It works fine but only returns those records from t1 that are read. I
need to return the records with null values also! I may have built the
assoc table wrong and would really appreciate some pointers on what I
am doing wrong. (assoc table has rID, MemoID and UserID columns)

Please help!

Many thanks

View 2 Replies View Related

Join Returns Duplicate Rows

Oct 23, 2007

Hi,
I'm having a little trouble with the following code:

SELECT DISTINCT cd1.*, cd2.*
FROM Table1 cd1 LEFT JOIN Table2 cd2
ON cd1.RegNr=cd2.RegNr
WHERE cd1.RegNr = $RegNr

I want it to return the 2 rows that is present in the tables but it returns 4.

1262007-10-20 10:14:00
1262007-10-20 10:14:00
1262007-10-20 10:17:00
1262007-10-20 10:17:00

View 18 Replies View Related

INNER JOIN Between A CLR-TVF And A Table Returns No Rows

Feb 24, 2007

I have the following query:

select sq.*, p.numero, p.nombre
from paf p right outer join dbo.GetListOfSquaresForShippingLot(@lot) sq on sq.number = p.numero and sq.version = p.numero

The @lot parameter is declared at the top ( declare @lot int; set @lot = 1; ). GetListOfSquaresForShippingLot is a CLR TVF coded in C#. The TVF queries a XML field in the database and returns nodes as rows, and this is completed with information from a table.

If I run a query with the TVF only, it returns data; but if I try to join the TVF with a table, it returns empty, even when I'm expecting matches. I thought the problem was the data from the TVF was been streamed and that's why it could not be joined with the data from the table.

I tried to solve that problem by creating a T-SQL multiline TVF that is supposed to generate a temporary table. This didn't fix the problem.

What can I do? Does anybody know if I can force the TVF to render its data somewhere so the JOIN works? I was thinking a rowset function could help, but I just can't figure out how.

PLEASE HELP!!!!

Let me know if you want the code for the CLR TVF. This is the code for the T-SQL TVF:

CREATE FUNCTION [dbo].[GetTabListOfSquaresForShippingLot]
(
@ShippingLot int
)
RETURNS
@result TABLE
(
Number int, Version int, Position smallint,
SubModel smallint, Quantity smallint,
SquareId nvarchar(5),
ParentSquareId nvarchar(5),
IsSash smallint,
IsGlazingBead smallint,
Width float,
Height float,
GlassNumber smallint,
GlassWidth float,
GlassHeight float
)
AS
BEGIN
INSERT INTO @result
SELECT *
FROM dbo.GetListOfSquaresForShippingLot(@ShippingLot)

RETURN
END

View 6 Replies View Related

Sp Returns Records In Qa, But Not In Adp

Dec 3, 2001

I have an sp (attached below) that returns records as desired when run in the query analyzer, but when run from within my adp I get only a "the sp ran successfully but did not return any records" message box. I would like to use it as a record source for a report, and when I try to do that I get a message box saying that the "Provider command for child rowset does not produce a rowset".

To repeat, the sp runs fine in QA, just not in access.

Any thoughts, anybody? Many thanks. David

Alter Procedure spDailyMicroLabel
@Date Datetime = null

AS
IF @Date IS NULL
BEGIN

SET @Date=getdate()

SELECT FERMID, fldfermtank AS "Tank", CONVERT(varchar(12), fldfilltime, 101) AS "Filled",
fldfermprod AS "Brand", CONVERT(varchar(15),fldfilltime, 108) AS "FillTime", CONVERT(varchar(12), @Date, 101) as "ReportDate",
fldfermidold AS "FermenterCode",
CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) AS "FillDay",
"Sample Test" = CASE
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int)=1
THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 3 THEN 'XTF'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 4 THEN 'HLP, TF'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 6 THEN 'TA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 7 THEN 'HLP/UBA, BU/Color'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 10 THEN 'HLP'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 13 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 16 THEN 'HLP'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 19 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 90 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 91 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 92 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 93 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 94 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 95 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 96 THEN 'HLP/UBA'
ELSE 'No Test Micro'
END
INTO #tblTempA
FROM vwUnfilteredFermenters

SELECT #tblTempA.FermenterCode AS "Fermenter ID", #tblTempA.Filled AS "Fill Date", #tblTempA.FillDay,
tblMicroTestDays.Test, tblMicroTestDays.HLPResults AS "HLP Results", tblMicroTestDays.UBAResults AS "UBA Results"
FROM tblMicroTestDays RIGHT JOIN #tblTempA ON tblMicroTestDays.TestDay = #tblTempA.FillDay

END

View 1 Replies View Related

Inner Join Returns Multiple Duplicated Rows

Dec 3, 2013

Here is my query which returns multiple rows

SELECT
R.name, R.age,R.DOB,
ISNULL(D.Doc1,'NA') AS doc1,
ISNULL(C.Doc2,'NA') AS doc2
FROM
REQ R
inner join RES S ON R.Request_Id=S.Request_Id
inner join RES1 D ON D.Response_Id=S.Response_Id
inner join REQ1 C ON C.Request_Id=R.Request_Id

select * from RES1 where Response_Id = 111 -- return 3
select * from REQ1 where Request_Id = 222 --- returns 2

So at last inner join retuns 3*2 = 6 records , which is wrong here and i want to show 3 records in doc1 row and 2 records in doc 2 rows ...

View 5 Replies View Related

QA Returns Different Records From SQL Server EM

Jul 20, 2005

SQL Server 7.0If I run the following in Query Analyzer I get no records returned:exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176, 183'If, however, I run either :exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176'orexec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '183'I get a single record returned in each case (which is what I wouldexpect).The SQL for GetLeadsOutcome_Dealer is:CREATE PROCEDURE GetLeadsOutcome_Dealer@FromDate smalldatetime,@ToDate smalldatetime,@OutcomeTypeID integer,@DealerCode varchar(8000)ASDECLARE @TotalLeads integerBEGINSELECT @TotalLeads=COUNT(fldLeadID) FROM tblLeads WHERE(tblLeads.fldDealerID IS NOT NULL)SELECT DISTINCT (dbo.tblDealers.fldDealerName),COUNT(dbo.tblLeads.fldLeadID) as LeadCount,@TotalLeads AS TotalLeadsFROM tblLeads LEFT OUTER JOINtblOutcome ON tblLeads.fldOutcomeID =tblOutcome.fldOutcomeID RIGHT OUTER JOINtblDealers RIGHT OUTER JOINtblRegion ON tblDealers.fldRegionID =tblRegion.fldRegionID ON tblLeads.fldDealerID = tblDealers.fldDealerIDWHERE (((dbo.tblLeads.fldEntered) BETWEEN @FromDate And @ToDate)) ANDtblOutcome.fldOutcomeID=@OutcomeTypeID AND(dbo.tblDealers.fldDealerCode in (@DealerCode))GROUP BY dbo.tblDealers.fldDealerNameORDER BY dbo.tblDealers.fldDealerNameENDGOHowever, if I open EM, open a table in query view, and paste this intothe SQL window,SELECT DISTINCT tblDealers.fldDealerName, COUNT(tblLeads.fldLeadID) ASLeadCountFROM tblLeads LEFT OUTER JOINtblOutcome ON tblLeads.fldOutcomeID =tblOutcome.fldOutcomeID RIGHT OUTER JOINtblDealers RIGHT OUTER JOINtblRegion ON tblDealers.fldRegionID =tblRegion.fldRegionID ON tblLeads.fldDealerID = tblDealers.fldDealerIDWHERE (tblLeads.fldEntered BETWEEN '1/1/2003' AND '12/2/2003') AND(tblOutcome.fldOutcomeID = 10) AND (tblDealers.fldDealerCode IN (176,183))GROUP BY tblDealers.fldDealerNameORDER BY tblDealers.fldDealerNameI get two records returned.What is happening?TIAEdwardTABLE DEFS:if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblLeads_tblDealers]') and OBJECTPROPERTY(id,N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblLeads] DROP CONSTRAINT FK_tblLeads_tblDealersGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblSystemUsers_tblDealers]') andOBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblSystemUsers] DROP CONSTRAINTFK_tblSystemUsers_tblDealersGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblLeadNotes_tblLeads]') and OBJECTPROPERTY(id,N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblLeadNotes] DROP CONSTRAINTFK_tblLeadNotes_tblLeadsGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblDealers_tblRegion]') and OBJECTPROPERTY(id,N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblDealers] DROP CONSTRAINT FK_tblDealers_tblRegionGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblDealers]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblDealers]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblLeads]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[tblLeads]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblOutcome]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblOutcome]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblRegion]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[tblRegion]GOCREATE TABLE [dbo].[tblDealers] ([fldDealerID] [int] IDENTITY (1, 1) NOT NULL ,[fldDealerCode] [varchar] (50) NOT NULL ,[fldDealerName] [varchar] (50) NULL ,[fldDealerTel] [varchar] (20) NULL ,[fldDealerEmail] [varchar] (100) NULL ,[fldDealerContact] [varchar] (50) NULL ,[fldRegionID] [int] NULL ,[fldDealerActive] [smallint] NOT NULL ,[fldHeadOffice] [smallint] NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[tblLeads] ([fldLeadID] [int] IDENTITY (1, 1) NOT NULL ,[fldAccountNo] [varchar] (50) NULL ,[fldDealerID] [int] NULL ,[fldStatusID] [int] NOT NULL ,[fldOutcomeID] [int] NOT NULL ,[fldContacted] [smallint] NOT NULL ,[fldDateContacted] [smalldatetime] NULL ,[fldAppointment] [smallint] NULL ,[fldShowRoom] [smallint] NOT NULL ,[fldTestDrive] [smallint] NOT NULL ,[fldSalesPersonID] [int] NULL ,[fldCustomerName] [varchar] (50) NULL ,[fldCHouseNo] [varchar] (50) NULL ,[fldCStreet] [varchar] (50) NULL ,[fldCDistrict] [varchar] (50) NULL ,[fldCTown] [varchar] (40) NULL ,[fldCCounty] [varchar] (50) NULL ,[fldCPostcode] [varchar] (50) NULL ,[fldPhoneInd] [varchar] (20) NULL ,[fldCTel] [varchar] (50) NULL ,[fldNewBusinessDate] [smalldatetime] NULL ,[fldAgreementType] [varchar] (50) NULL ,[fldCashPrice] [smallmoney] NULL ,[fldBalanceFin] [smallmoney] NULL ,[fldCustRate] [varchar] (10) NULL ,[fldOrigTerm] [int] NULL ,[fldPPPType] [varchar] (10) NULL ,[fldBalloonValue] [smallmoney] NULL ,[fldMonthlyInstal] [smallmoney] NULL ,[fldRegNo] [varchar] (10) NULL ,[fldRegDate] [smalldatetime] NULL ,[fldModel] [varchar] (50) NULL ,[fldDescription] [varchar] (50) NULL ,[fldTheoPIFDate] [smalldatetime] NULL ,[fldMonthsToGo] [int] NULL ,[fldBalanceOS] [smallmoney] NULL ,[fldLeadPrinted] [smallint] NULL ,[fldMergeFile] [varchar] (255) NULL ,[fldTPS] [varchar] (10) NULL ,[fldMPS] [varchar] (10) NULL ,[fldUpdated] [smalldatetime] NULL ,[fldUpdatedBy] [int] NULL ,[fldEntered] [smalldatetime] NULL ,[fldReasonLeadNotProgressed] [varchar] (5000) NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[tblOutcome] ([fldOutcomeID] [int] IDENTITY (1, 1) NOT NULL ,[fldOutcomeCode] [int] NULL ,[fldOutcome] [varchar] (100) NULL ,[fldConvertedSales] [smallint] NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[tblRegion] ([fldRegionID] [int] IDENTITY (1, 1) NOT NULL ,[fldRegionCode] [varchar] (10) NULL ,[fldRegion] [varchar] (50) NULL) ON [PRIMARY]GOALTER TABLE [dbo].[tblDealers] WITH NOCHECK ADDCONSTRAINT [PK_tblDealers] PRIMARY KEY CLUSTERED([fldDealerID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblLeads] WITH NOCHECK ADDCONSTRAINT [PK_tblLeads] PRIMARY KEY CLUSTERED([fldLeadID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblOutcome] WITH NOCHECK ADDCONSTRAINT [PK_tblOutcome] PRIMARY KEY CLUSTERED([fldOutcomeID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblRegion] WITH NOCHECK ADDCONSTRAINT [PK_tblArea] PRIMARY KEY CLUSTERED([fldRegionID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblDealers] WITH NOCHECK ADDCONSTRAINT [DF_tblDealers_fldActive] DEFAULT ((-1)) FOR[fldDealerActive],CONSTRAINT [DF_tblDealers_fldHeadOffice] DEFAULT (0) FOR[fldHeadOffice],CONSTRAINT [IX_tblDealers] UNIQUE NONCLUSTERED([fldDealerCode]) ON [PRIMARY]GOALTER TABLE [dbo].[tblLeads] WITH NOCHECK ADDCONSTRAINT [DF_tblLeads_fldOutcomeID] DEFAULT (10) FOR[fldOutcomeID],CONSTRAINT [DF_tblLeads_fldContacted] DEFAULT (0) FOR [fldContacted],CONSTRAINT [DF_tblLeads_fldAppointment] DEFAULT (0) FOR[fldAppointment],CONSTRAINT [DF_tblLeads_fldShowRoom] DEFAULT (0) FOR [fldShowRoom],CONSTRAINT [DF_tblLeads_fldTestDrive] DEFAULT (0) FOR [fldTestDrive],CONSTRAINT [DF_tblLeads_fldLeadPrinted] DEFAULT (0) FOR[fldLeadPrinted],CONSTRAINT [DF_tblLeads_fldTPSMatch] DEFAULT ('NO') FOR [fldTPS],CONSTRAINT [DF_tblLeads_fldMPSMatch] DEFAULT ('NO') FOR [fldMPS]GOALTER TABLE [dbo].[tblOutcome] WITH NOCHECK ADDCONSTRAINT [DF_tblOutcome_fldConvertedSales] DEFAULT (0) FOR[fldConvertedSales]GOALTER TABLE [dbo].[tblRegion] WITH NOCHECK ADDCONSTRAINT [IX_tblRegion] UNIQUE NONCLUSTERED([fldRegionCode]) ON [PRIMARY]GOALTER TABLE [dbo].[tblDealers] ADDCONSTRAINT [FK_tblDealers_tblRegion] FOREIGN KEY([fldRegionID]) REFERENCES [dbo].[tblRegion] ([fldRegionID])GOALTER TABLE [dbo].[tblLeads] ADDCONSTRAINT [FK_tblLeads_tblDealers] FOREIGN KEY([fldDealerID]) REFERENCES [dbo].[tblDealers] ([fldDealerID])GOalter table [dbo].[tblLeads] nocheck constraint[FK_tblLeads_tblDealers]GO

View 2 Replies View Related

Dataset Returns 0 Records

Feb 6, 2008

I am new to SSRS 2005 but find it to be a wonderful development tool. I discovered that when you run a report where the dataset returns no records the resulting report is blank as opposed to page header, table header, no data, and page footer. Is there a way to force these items to be displayed even when the dataset is empty? Or better yet is there a way to return a (custom) empty dataset message in place of the report body?

Thanks.

View 1 Replies View Related

Table Join Statement Returns Repeat Rows

Jul 22, 2007

Hello All.

I am struggling with the below join block in my stored procedure.
I can't seem to get the duplicate row problem to go away. It seems that SQL is treating each new instance of an email address as reason to create a new row despite the UNIONs.
I understand that if I am using UNION, using DISTINCT is redundant and will not solve the duplicate row problem.

Primary Keys: none of the email address columns are primary keys. Each table has an incrementing ID column that serves
as the primary key.

I am guessing I am encountering this problem because of how
I have structured my Join statements? Is it possible to offer advice without a deeper understanding of my data model or
do you need more information?

Thanks for any tips.

Code:

select emailAddress from Users union
select user_name from PersonalPhotos union
select email_address from EditProfile union
select email_address from SavedSearches union
select distinct email_address from UserPrecedence union
select email_address from LastLogin) drv
Left Join Users tab1 on (drv.emailAddress = tab1.emailAddress)
Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
Left Join PersonalPhotos tab2 on (drv.emailAddress = tab2.user_name)
Left Join LastLogin tab4 on (drv.emailAddress = tab4.email_address)
Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
Left Join SavedSearches tab6 on (drv.emailAddress = tab6.email_address

View 8 Replies View Related

Join Returns More Than One Row, Post Code Regular Expressions

Mar 23, 2006

Hi,I trying to write a select statement that will return each of my salesmen a region code based on a table of post codes using wildcards... eg.MK1 1AA would be matched in the region code table to MK1%SELECT dn.DEALER_CODE, dn.NAME AS DNAME, rc.REGION_ID,rc.POST_CODE, dn.POSTAL_CODEFROM REGIONAL_CODES rc CROSS JOINDEALER_NAW dnWHERE (dn.POSTAL_CODE LIKE rc.POST_CODE)The above statement works BUT there are some post code areas such asour friends in Milton Keynes that are split into two regions... eg MK1is region id 2 and MK10 is region 3.So a dealer with post code MK10 1AA would be matched to both rowsreturning duplicatesPOST_CODE REGION_IDMK1% 2MK10% 3I think the answer would lie in a subquery which returns the ID of theregion with the longest length of the postcode match (e.g.len(POST_CODE) for the rc table... return only the MAX....any ideas????Any help muchos appreciated, and I apologies now for the naming of thedealers name as a reserve word... not me!Ct

View 2 Replies View Related

Transact SQL :: Left Join Returns Too Many Rows Vertically

Jun 18, 2015

I have a query that based 2 tables. I wrote a query with a left join on the base table but the result set returns multiple rows for each occurrence in the second table because of the left join. I want but to return all records from on table A and only matching records from table B which id but I would wan tit to keep return them vertically as the because it make it difficult to read when put in a spreadsheet. It want it to return the values horizontally so the rows are not increasing for each occurrence on table b.

View 5 Replies View Related

Left Join Returns Values Where I Was Was Expecting Nulls

Nov 16, 2006

I have a query which is returning a different result set when it is run against identical tables in 2 different environments.

The query is like:

Select
F.LicenseeID, IsSpecialLicensee
from FactTable F
left join View_SpecialLicensee SL on F.LicenseeID = SL.LicenseeID

The Create Statement for the view is like

Create View [dbo].[View_SpecialLicensee]
as
Select LicenseeID, LicenseeName, IsSpecialLicensee = 1
from DimensionLicensee
where LicenseeName like '%ibm%'
or LicenseeName like '%cisco%'
or LicenseeName like '%hp%'

In my test environment, I get the query result I expected:
LicenseeID, IsSpecialLicensee
1 , 1 - (where LicenseeName = 'IBM')
2, null - (where LicenseeName = 'Juniper')
3, 1 - (where LicenseeName = 'Cisco')
4, null - (where LicenseeName = 'Microsoft')
5, null - (where LicenseeName = 'Oracle')
6, null - (where LicenseeName = 'Apple')

In my production environment, I get the following query result:
1 , 1 - (where LicenseeName = 'IBM')
2, 1 - (where LicenseeName = 'Juniper')
3, 1 - (where LicenseeName = 'Cisco')
4, 1 - (where LicenseeName = 'Microsoft')
5, 1 - (where LicenseeName = 'Oracle')
6, 1 - (where LicenseeName = 'Apple')

Ideas as to what changed gratefully received.

FYI the production environment which returned the 2nd dataset is SQL2000, I have got the result I expected in both SQL2000 and SQL2005 development environments.

View 6 Replies View Related

Query Returns More Records Than Expected

Nov 29, 2012

I am having a query

select INVOICE.TarrifHeadNumber, SUM(INVOICEITEMS.ItemQuantity) From invoiceitems,
invoice Where invoice.invoicenumber = invoiceitems.invoicenumber and
month(InvoiceDate)='11' and year(InvoiceDate)= '2012'
group by INVOICE.TarrifHeadNumber

tarrifheadno SUM(INVOICEITEMS.ItemQuantity)

84195030 9.00
84198910 5.00
84212190 223.00
84569090 247.00
84799040 1138.00
8481-80-1030 137.00
85433000 6177.20

tarrifheadno is unique

Now if i use below query and add invoicetypecode field

select INVOICE.TarrifHeadNumber,CETSH.GoodsDescription, SUM(INVOICEITEMS.ItemQuantity),INVOICE.invoicetype code From invoiceitems,
invoice , cetsh Where invoice.invoicenumber = invoiceitems.invoicenumber and
month(InvoiceDate)='11' and year(InvoiceDate)= '2012' and
cast(CETSH.CETSHNumber as varchar) = INVOICE.TarrifHeadNumber group by INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,in voicetypecode

This query return distinct of 11 records

84195030 84195030
9.00 1
84198910 84198910
5.00 2
84212190 84212190
157.00 1
84212190 84212190
42.00 2
84212190 84212190
24.00 3
84569090 84569090
189.00 1
84569090 84569090
58.00 2
84799040 84799040
166.00 1
84799040 84799040
972.00 2
85433000 85433000
3764.00 1
85433000 85433000
2413.20 2

How to get same 7 records

View 1 Replies View Related

T-SQL (SS2K8) :: BCP Query-out Returns No Records?

Sep 3, 2014

I have a SP that manipulates data for picking products and puts them into a temp table "#PickList" which is used for the basis of printing a picking note report.

I have also added code at the end of the SP to take the "#PickList" data and insert into a permanent table called "BWT_Lift_Transaction" and then use the bcp command to query it out to a text file. All this works fine, until the bcp command runs. Although the records are in the table, bcp returns nothing. Here is the code:

DECLARE @strLocation VARCHAR(50)
DECLARE @TransNum VARCHAR(50)
DECLARE @strFileLocation VARCHAR(1000)
DECLARE @strFileName VARCHAR(1000)
DECLARE @bcpCommand VARCHAR(8000)

[code].....

The earlier parts of the code create the filename for the text file and location to store it. If I insert a SELECT on the dbo.BWT_Lift_Transaction directly after the insert, I can see the data in there, but although the bcp command creates the file correctly, it returns no data:

output

NULL
Starting copy...
NULL
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
NULL

If I remove the delete statement at the end and run the code twice, it will insert the data into the table twice. On the first run, nothing is returned by bcp. On the second run, the first set is returned by bcp, not both sets.

I don't understand why this is, but I guess it's something to do with transaction commitment and the way bcp works.

View 1 Replies View Related

How Many Records A Stored Procedure Returns?

Oct 11, 2007

Hi,
I need to know whether a stored procedure returns only a single record or it can return more than one if the query if for example to return all records in a specific date range.
Thanks.

View 6 Replies View Related

Is This Call Inner Join As Well ?

Jul 18, 2006

hi, if i have query like following

select * from o_customer,o_address
where
o_customer.name = o_address.custname

is it same as

select * from o_customer
inner join o_address on o_customer.name = o_address.custname

if there are same, which one is prefer in term of performance ..

thank you for guidance

View 2 Replies View Related

ODBC Trusted_Connection Call To SQLGetInfo Returns DBO As User Name, Not NT User

Sep 26, 2004

Hello, all:
I am connecting to SQL Server 2000 with a trusted connection, and that is working fine. I then am issuing a SQLGetInfo call to find out the SQL_USER_NAME, which is always returning dbo instead of (my) Windows NT login name, which I expect to see. THe authentication is (apparently) confirmed by the SQL Server session monitor where it shows the right Windows NT user name logged in, over ODBC. With a standard SQL Login, untrusted, I definitely get the right user name back, not dbo.

So:

Login Type / user id /returned value from SQLGetInfo

Windows Authentication / Windows login / dbo
SQL Authentication / Windows login (UID) / UID

The problem is that I am trying to confirm that the userid entered in a dialog or passed on the command line to my application matches the actual connected user name inside SQL Server....

My questions:
Is there some pathology in Trusted_Connections that masks the Windows/NT login name and always returns UID 'dbo' from a call to SQLGetInfo? Is there a MSS (public) stored procedure that can robustly give back the logged in user name so I can bypass the SQLGetInfo call?

View 2 Replies View Related

Transact SQL :: Query That Returns Material (items) That Are Used In Event On Certain Day - RIGHT JOIN

Nov 29, 2015

I have a query that returns material(items) that are used in an event on a certain day.

SELECT C.categoryName, count(I.itemID) AS InMission
from items as I
RIGHT JOIN Categories AS C on I.categoryID = C.categoryID
INNER JOIN LinkMissionItem as LM on I.itemID = LM.itemID
INNER JOIN Missions as M on LM.missionID = M.MissionID
where '2015/12/19' BETWEEN M.freightLeave and M.freightReturn AND isReturned = 0
GROUP BY C.categoryName, C.categoryID
ORDER BY C.categoryID

There are a total of 20 categories and I would like all the categories listed in the result even though there are no items booked in a mission. At the moment, I can only get the categories that have items in that category booked in a mission. I hoped that the RIGHT JOIN on the categories table would do the trick but it doesn't.

View 4 Replies View Related

Call Function For Inner Join

Aug 10, 2007

I have a procedure which has query
like Query 1.

Query 1

Select Clinetid
from clinet
inner join {

select centerid from GetChildCenter(@Centerid)
union
select centerid from getParentCenter(@Centerid)
} as Center c

on c.Centerid = client.Centerid

Query 2

declare @Center table ( centerid int)
insert into @Center

select centerid from getchildCenter(@Centerid) union all select centerid from getparentcenter(@Centerid)

Select Clinetid
from clinet
inner join @Center c on c.Centerid = client.Centerid

I just want to know which one is better performance wise..
because there is millions of rows for table center which is used by function getChildCenter() and GetparentCenter()

View 1 Replies View Related

HOW To Select A Matrix (cross Join) With Empty Records To Retrieve The Same Amount Of Records For Each Cell

Nov 2, 2006

Hello

Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:

This is a simple matrix

test a

text b

text c

text d

text e

text f

text g

This is a matrix with all the same row-height.

test a

text b

.

text c

.
.

text d

text e

text f

text g

.

.

Thx you a lot

View 3 Replies View Related

IS NULL Returns Empty Records (using TEXT Type)

Mar 3, 2008

Hi all I am having some issues in selecting items from my database where the record is NOT NULL. I have the code below however although some fields do contain soem data in it, others are blank which I believe are empty spaces. How do I do a SELECT command which ignores empty spaces and NULLS?

Code Snippet

SELECT CustomSearch FROM OfficesTable WHERE CustomSearch IS NOT NULL
Thanks, Onam.

View 10 Replies View Related

Transact SQL :: How To UPDATE More Than 500 Records In One Call

Aug 13, 2015

I have following query which is created dynamically as -

UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1889147436' AND SEND_DATE = '1941-03-04';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1115509374' AND SEND_DATE = '1991-09-01';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1515579671' AND SEND_DATE = '1941-05-24';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1795509670' AND SEND_DATE = '1958-01-14';
UPDATE BUILDTABLE SET BUILD_ID = '984137' WHERE SET_NUMBER = '1915508672' AND SEND_DATE = '1961-09-07';

Here till " UPDATE BUILDTABLE SET BUILD_ID = '984137'" is the same clause for all queries, but "where" condition is different for all queries. I have to update more than 500 UPDATE statements(like above) in one call. Currently I am concatenating all the queries in string Builder which is time consuming.I want to increase performance of application.Any other class like BulkCopy ?

View 18 Replies View Related

How Can I Call MS Access Records From A SQL Store Procedure

Nov 30, 2004

How can I get MS ACCES database records from an sql Stored Procedure. Idea is I have a SQL stored procedure, which wants to calculate a few things using some data from an MS access DB. Is it possible or am I talking against any DB principles?

View 1 Replies View Related

Results Produce A Single Record Based Off Of Parameters. Want To Change It So It Returns Multiple Records.

Dec 20, 2007

I have a query that will return one record as its results if you provide two variables: @login and @record_date. This works great if you only want one result. However, now what I want to do is not provide those variables and get the result set back for each login and record_date combination. The hitch is that there are several other variables that are built off of the two that are supplied. Here is the query:

DECLARE @login char(20), /*This sets the rep for the query.*/
@record_date datetime, /*This is the date that we want to run this for.*/
@RWPY decimal(18,2), /*This is the required wins per year.*/
@OCPW decimal(18,2), /*This is the opportunities closed per week.*/
@OACW decimal(18,2), /*This is opportunities advanced to close per week.*/
@TOC decimal(18,2), /*This is the total number of opportunities in close.*/
@OANW decimal(18,2), /*This is opportunities advanced to negotiate per week.*/
@TON decimal(18,2), /*This is the total number of opportunities in negotiate.*/
@OADW decimal(18,2), /*This is the opportunities advanced to demonstrate per week*/
@TOD decimal(18,2), /*This is the total number of opportunities in demonstrate.*/
@OAIW decimal(18,2), /*This is the opportunities advanced to interview per week.*/
@TOI decimal(18,2), /*This is the total number of opportunities in interview.*/
@OCW decimal(18,2), /*This is the opportunities created per week.*/
@TOA decimal(18,2) /*This is the total number of opportunities in approach.*/

SET @login = 'GREP'
SET @record_date = '12/18/2007'
SET @RWPY = (SELECT ((SELECT annual_quota FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)/(SELECT target_deal FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)))
SET @OCPW = (SELECT @RWPY/weeks FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OACW = (SELECT @OCPW/cls_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOC = (SELECT @OACW*(cls_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OANW = (SELECT @OACW/neg_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TON = (SELECT @OANW*(neg_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OADW = (SELECT @OANW/dem_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOD = (SELECT @OADW*(dem_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OAIW = (SELECT @OADW/int_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOI = (SELECT @OAIW*(int_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OCW = (SELECT @OAIW/app_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOA = (SELECT @OCW*(app_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)

SELECT loginname,
CAST(@TOA AS decimal(18,1)) AS [Opps in Approach],
app_time AS [Approach Average Time],
app_perc_adv AS [Approach Perc Adv],
CAST(@TOI AS decimal(18,1)) AS [Opps in Interview],
int_time AS [Interview Average Time],
int_perc_adv AS [Interview Perc Adv],
CAST(@TOD AS decimal(18,1)) AS [Opps in Demonstrate],
dem_time AS [Demonstrate Average Time],
dem_perc_adv AS [Demonstrate Perc Adv],
CAST(@TON AS decimal(18,1)) AS [Opps in Negotiate],
neg_time AS [Negotiate Average Time],
neg_perc_adv AS [Negotiate Perc Adv],
CAST(@TOC AS decimal(18,1)) AS [Opps In Close],
cls_time AS [Close Average Time],
cls_perc_adv AS [Close Perc Adv]
FROM #pipelinehist
WHERE loginname = @login AND record_date = @record_date

Here is some sample data to use with this. With this sample data what I want to get back is a total of 30 records in the result set each with its data specific to the login and record_date of that returned record.

CREATE TABLE #pipelinehist (
glusftboid int IDENTITY(1,1) NOT NULL,
record_date datetime NOT NULL,
loginname char(20) NOT NULL,
app_new float NOT NULL,
app_time float NOT NULL,
app_perc_adv float NOT NULL,
int_time float NOT NULL,
int_perc_adv float NOT NULL,
dem_time float NOT NULL,
dem_perc_adv float NOT NULL,
neg_time float NOT NULL,
neg_perc_adv float NOT NULL,
cls_time float NOT NULL,
cls_perc_adv float NOT NULL,
target_deal money NOT NULL,
annual_quota money NOT NULL,
weeks int NOT NULL
) ON [PRIMARY]

INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'AREP', 56.8, 26.9, 0.57, 29.5, 0.47, 20, 0.67, 80.7, 0.53, 2.1, 0.97, 2194.93, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'BREP', 33.2, 0.5, 0.9, 7.7, 0.77, 8, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'CREP', 210.2, 0.3, 0.87, 6.6, 0.5, 13.7, 0.4, 16.3, 0.43, 1.5, 0.91, 461.25, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'DREP', 47.6, 5, 0.53, 33.3, 0.6, 57.5, 0.53, 50, 0.7, 1.5, 1, 2045.7, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'EREP', 75.3, 110.9, 0.47, 36, 0.5, 17.4, 0.87, 20.3, 0.6, 7.2, 0.83, 2021.74, 775000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'FREP', 17.2, 23.3, 0.73, 6.8, 0.8, 6.3, 0.93, 29.7, 0.67, 15.5, 0.83, 2218.95, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'GREP', 105.4, 67, 0.2, 32.9, 0.43, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'HREP', 116.4, 118.5, 0.33, 30.9, 0.77, 46.3, 0.77, 46.3, 0.6, 0.9, 0.97, 1735.13, 1150000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'IREP', 143.3, 9, 0.77, 96, 0.17, 21.6, 0.77, 39.9, 0.43, 0.9, 0.93, 1385.43, 400000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 67.6, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'KREP', 107.6, 38.2, 0.23, 47.5, 0.47, 21.3, 0.77, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'LREP', 18.6, 8.3, 0.87, 23.2, 0.57, 2.6, 0.87, 12.2, 0.67, 1, 1, 1229.02, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'MREP', 4, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.4, 1, 1091.22, 350000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'NREP', 54, 21.6, 0.57, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'OREP', 37.6, 24.4, 0.57, 50.1, 0.43, 6.7, 0.87, 15.6, 0.73, 0.9, 0.97, 1163.48, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'AREP', 57.2, 32.5, 0.6, 29.5, 0.47, 20, 0.67, 85.6, 0.5, 2.1, 0.97, 2194.93, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'BREP', 33.9, 0.5, 0.93, 7.8, 0.73, 8.3, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'CREP', 152.1, 0, 0.87, 4.3, 0.67, 9.7, 0.47, 15.7, 0.47, 1.8, 0.85, 396.43, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'DREP', 80.5, 9.8, 0.5, 40.7, 0.57, 68.3, 0.43, 64.2, 0.57, 1.5, 1, 2045.7, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'EREP', 61, 92.1, 0.5, 31, 0.53, 16.9, 0.83, 17.7, 0.6, 7.3, 0.83, 2318.04, 775000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'FREP', 19.4, 21.1, 0.7, 5.3, 0.77, 2.2, 0.93, 33.3, 0.7, 9.7, 0.87, 1937.17, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'GREP', 81.7, 40.5, 0.3, 33, 0.37, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'HREP', 128.6, 115.7, 0.3, 30.9, 0.77, 46.3, 0.77, 48.8, 0.6, 0.9, 0.97, 1728.29, 1150000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'IREP', 100.9, 3.4, 0.77, 86.2, 0.27, 18, 0.8, 54.7, 0.37, 0.9, 0.93, 1385.43, 400000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 63.5, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'KREP', 285.2, 36.5, 0.1, 46, 0.43, 24.2, 0.73, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'LREP', 17.6, 7.3, 0.9, 21.5, 0.57, 1.7, 0.87, 12.2, 0.67, 1, 1, 1250.54, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'MREP', 26.7, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.3, 1, 979.7, 350000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'NREP', 61.6, 20.8, 0.5, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'OREP', 31.6, 16.9, 0.63, 50.1, 0.43, 7.2, 0.87, 19.5, 0.7, 0.9, 0.97, 1303.48, 330000, 50)

View 3 Replies View Related

Insert Multiple Records Using One Stored Procedure Call. SQL SERVER 7

Mar 19, 2008

Hi I have asp.net page with approx 28 dropdowns. I need to insert these records using one stored procedure call. How can I do this while not sacrificing performance?

Thanks, Gary

View 9 Replies View Related

Find Phonecall Records Based On User At The Time Of Call

Oct 8, 2006

i have a table of phonecall records detailing amongst other things the time and duration of the call. there is a relationship linking this table to a table of handsets and from there to a table of users. this users table lists the start date the user had the phone. as ever sample data might make this easier to show:

PhoneUsers table

PhoneUserID Name StartDate PhoneID

1 john 1/08/2006 1

2 bob 20/08/2006 1

3 fred 2/08/2006 2

etc

Phones table:

PhoneID

1

2

etc

PhoneCalls table:

PhoneCallID PhoneID CallDate Duration

1 1 10/08/2006 25

2 1 23/082006 20

3 2 23/08/2006 20

i want the following result set:

user calldate duration

john 10/08/2006 25

bob 23/08/2006 20

fred 23/08/2006 20

essentially i am trying to link the phonecalls to the user of the phone at the time of the call. i make the following assumptions regarding the data:

the user is responsible for all calls for a given handset from the 'startdate' until the next 'startdate' for a different user, if such a record exists, if not then for all calls after that date

thanks

View 1 Replies View Related

SQL Server VARCHAR(MAX) Column Returns Error While Inserting Records Into Table(ODBC Driver: SQL Native Client)

Aug 15, 2007

I created very simple table with 3 columns and one is varchar(max) datatype

When i insert records thru VC++ ADO code i am getting this error

Exception Description Multiple-step OLE DB operation generated errors. Check e
ach OLE DB status value, if available. No work was done. and Error Number:: -2147217887

ODBC Driver: SQL Native Client

SQL server 2005

Table

CREATE TABLE [dbo].[RAVI_TEMP](

[ID] [int] NULL,

[Name] [varchar](max) NULL,

[CITY] [varchar](50) NULL

)

VC++ code

#include "stdafx.h"
#include <string>
#include <strstream>
#include <iomanip>

int main(int argc, char* argv[])
{
try
{
HRESULT hr = CoInitialize(NULL);
_RecordsetPtr pExtRst = NULL;
_bstr_t bstrtDSN, bstrtSQL;
bstrtDSN = L"DSN=espinfo;UID=opsuser;PWD=opsuser;";
bstrtSQL = L"SELECT * FROM RAVI_TEMP";

_variant_t vartValueID,vartValueNAME,vartValueCITY;
_bstr_t bstrtValueID,bstrtValueNAME,bstrtValueCITY;

pExtRst.CreateInstance(__uuidof(Recordset));
hr = pExtRst->Open(bstrtSQL, bstrtDSN, adOpenDynamic, adLockOptimistic, adCmdText);

hr = pExtRst->AddNew();

bstrtValueID = L"1";
vartValueID = bstrtValueID.copy();

bstrtValueNAME = L"RAVIBABUBANDARU";
vartValueNAME = bstrtValueNAME.copy();

bstrtValueCITY = L"Santa Clara";
vartValueCITY = bstrtValueCITY.copy();

pExtRst->GetFields()->GetItem(L"ID")->Value = vartValueID;
pExtRst->GetFields()->GetItem(L"NAME")->Value = vartValueNAME;
pExtRst->GetFields()->GetItem(L"CITY")->Value = vartValueCITY;
pExtRst->Update();
pExtRst->Close();

}
catch(_com_error e)
{
printf("Exception Description %s and Error Number:: %d",(LPTSTR)e.Description(),e.Error());
return e.Error();
}
return 0;
CoUninitialize();
}

if i use regular SQL ODBC driver, no error but its truncating the data

Adv Thanks for your help

View 1 Replies View Related

Successive Records With Self Join

Sep 22, 2014

We are using a presence checker to register when our employees show up at work, when they leave and whenever they change their working section.

The raw data has this format:

TABLE
-----

TIMESTAMP USERID SECTION INCIDENCE
------------------------------------------------------
2014-06-27 14:52:40.000 77675669 7 0
2014-06-27 15:08:33.000 77675669 6 8
2014-06-27 15:30:58.000 77675669 7 12
2014-06-27 18:52:00.000 77675669 11 15
2014-06-27 19:47:38.000 77675669 7 12
2014-06-27 23:30:18.000 77675669 7 0

In order to determine the exact time people had been working in the sections I have to match every record with the record of the successive timestamp I do:

SELECT TIMESTAMP as START, USERID, SECTION, INCIDENCE, MIN(TIME_END) as STOP
FROM (
SELECT *, TABLE2.TIMESTAMP as TIME_END
FROM TABLE
LEFT OUTER JOIN TABLE as TABLE2 on TABLE.USERID = TABLE2.USERID and
TABLE2.TIMESTAMP > Table.TIMESTAMP and

[Code] ....

How do I get the incidence of the successive timestamp in my query?

The key trick is that I first do a self join which increases the amount of records, including senseless matches from the first timestamp with the last one of that day. Afterwards I group by all columns of TABLE in order to get only the successive timestamp.

But once I include TABLE2.INCIDENCE in the GROUP BY clause, of course the query stops working correctly.

I can't use the TIMESTAMP isself as it is not singular (there are several terminals in the plant); and I prefer not to use Timestamp in combination with Userid, as the userid is actually not stored directly and has to be retrieved through a couple of joined tables. Any smarter way to include a second column once the successive timestamp has been determined.

View 2 Replies View Related

How To Show Records With JOIN?

Apr 5, 2007

Hi ,

I've got two tables.. the first table carried a ProductID, and amongst other things a TradePrice

The other tbl carries a ProductID, a IndivPrice and a CustomerID

The second tbl lists prices for products for indiv Customers.

My Query needs to bring back ALL the products from the first tbl...

It also needs to show the TradePrice for that product.

I need to join my query to the second tbl...

And finally, if the second tbl has a price for that product AND the customerID is the same as one I pass into the query.. show that price also..

So here's my first query:

SELECT dbo.Products.ProductID, ProductName, ProductTradePrice, IndivPrice, dbo.Trade_PriceLists.CustomerID AS PLCustomerID FROM dbo.Products LEFT OUTER JOIN dbo.Trade_PriceLists ON dbo.Products.ProductID = dbo.Trade_PriceLists.ProductID WHERE (ProductType = 'Trade' OR ProductType = 'Both') AND (Replace(Lower(ProductBrand),' ','') = 'brandname') AND (CustomerID IS NULL OR CustomerID = 'teste' OR CustomerID = '') ORDER BY TradeOrder

I thought that would work, but what happens is that, if that particular customer has no indiv prices set.. then it only shows the ones that have no records at all in that second tbl..

So unless there is a record for a particular product in that second tbl and it doesn't have a CustomerID assigned to (which would never happen as that tbl is only every for indiv customer prices) then it doesn't show.

Examples:

First Tbl

ProductID Name TradePrice

1 Jumper �1.00

2 Jeans �3.00

3 Shoes �5.00

4 Hat �2.00

Second Tbl

ProductID CustomerID IndivPrice

1 teste �0.50

2 othercustomer �2.50

3 teste �4.50

What I want in the results is:

ProductID ProductName TradePrice IndivPrice CustomerID (PLCustomerID)

1 Jumper �1.00 �0.50 teste

2 Jeans �3.00

3 Shoes �5.00 �4.50 teste

4 Hat �2.00

See? - The 2nd product should not get an indiv price as although it's in that second tbl, the customerID assigned to it is different. The 4th product should not get an indiv price as it's not in that second tbl at all.

however, with my query above I'd only get Products 1and 3... and if I did a query on a customer with no indiv prices I'd only get product 4 as it's not in the indiv at all...

HELP!!!!!

View 11 Replies View Related

Inner Join On Reverse Records

Jun 8, 2006

I have some data that looks like this

Col1 Col2 SumCol

XXX,YYY, 5

XXX,ZZZ,6

ZZZ,XXX,7

YYY,XXX,2

I want to do a inner join on that data so I get this

XXX,YYY, 7

XXX,ZZZ,13

Right now I'm using a CTE set to do that, is there a better way??

View 6 Replies View Related