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


ADVERTISEMENT

SQL CLR Udf Returns 0 Instead Of Expected Value

Dec 12, 2007

My first foray into the SQL CLR world is a simple function to return the size of a specified file.
I created the function in VS2005, where it works as expected.
Running the function in SSMS, however, returns a value of zero, regardless of the file it is pointed at.

Here's the class member code:


Public Shared Function GetFileSize(ByVal strTargetFolder As String, ByVal strTargetFile As String) As Long

' Returns the size of the specified file.

' Parameters: strTargetFolder = path to target file, strTargetFile = target file name.

Dim lngFileSize As Long

Dim objFileInfo As FileInfo

' Confirm file exists.

If Not File.Exists(strTargetFolder & "" & strTargetFile) Then

Return -1

End If

Try

objFileInfo = My.Computer.FileSystem.GetFileInfo(strTargetFolder & "" & strTargetFile)

lngFileSize = objFileInfo.Length

Catch

' TODO: add error handling; system folders cause error during processed.

End Try

Return lngFileSize

End Function

In SSMS (sp2), here's my assembly steps (this is my local dev machine; I have admin rights):


sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

-- For file system access, set the database TRUSTWORTHY property.

ALTER DATABASE dba_use

SET TRUSTWORTHY ON

CREATE ASSEMBLY GetFolderInfo

FROM 'C:ProjectsGetFolderInfoGetFolderInfoinDebugGetFolderInfo.dll'

WITH PERMISSION_SET = UNSAFE


Here's the udf declaration:


CREATE FUNCTION dbo.udfGetFileSize( @strTargetFolder nvarchar(200), @strTargetFile nvarchar(50) )

RETURNS bigint

AS EXTERNAL NAME

GetFolderInfo.[GetFolderInfo.clsFolderInfo].GetFileSize


And the function call - note the target file is on a remote server. Actual file name differs slightly:


SELECT dbo.udfGetFileSize('\SomeServerName$MSSQL2000MSSQLData', 'SomeDBName_Data.MDF')


This always returns zero with no error displayed. Running Profiler was little help and there's not much in the Event Log.
The function returns correct values in VS2005.
The assembly is created with UNSAFE because using EXTERNAL_ACCESS resulted in a security error that prevented the assembly from being created, let alone running. Security is, I suspect, at the root of this issue as well, but I'm not sure what or where to look to verify this.

Any assistance is greatly appreciated.

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

SQL Stored Procedure Not Returning Expected Records.

Oct 24, 2006

I am running a SP using this code:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[SelectQueryAddressPostCodeSearchOnly]

(

@postcode nvarchar(50)

)

AS

SET NOCOUNT ON;

SELECT

Cust_Address.Cust_Address_ID,

Cust_Address.Cust_Address_1,

Cust_Address.Cust_Address_2,

Cust_Address.Cust_Address_Post_Code,

Cust_Address.Cust_Post_Town,

Post_Town.Post_Town_ID,

Post_Town.Post_Town_Name,

Post_Town.Post_Town_Priority

FROM Cust_Address

INNER JOIN Post_Town ON Cust_Address.Cust_Post_Town = Post_Town.Post_Town_ID

WHERE Cust_Address.Cust_Post_Town LIKE @postcode



The value for @postcode being passed for testing is "%SA%". I would expect this to return all records which have "SA" in the Post Code field. In the test database I am using there are several (Mainly SA43 0EZ). However the SP in fact returns no matching records at all.



Am I not understanding something here, or is there something to do with space in post code field that is causing a problem ? I have other SPs that are behaving just fine.



Cheers Matt



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

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

SQL Call INNER JOIN Returns Too Many Records

Jan 25, 2006

I have my SQL call:
SELECT     CallLog.CallID, Journal.HEATSeqFROM         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           HEATSeq00000164     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           HEATSeq00000164     983295209 00000231     985280248
Can this be done directly in the SQL call?

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

SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related

SQL Server 2008 :: Error - Value Does Not Fall Within Expected Range In Delete Query?

May 28, 2015

I have a execute sql task which insert data into a table, but before that I need to make sure no data is available for particular date. we have a table where we are defining date for which date we want to data, and we are fetching that particular date using ssis variable 'Date'. today I was trying to load data dor '2015-05-10'. But it gave me error like '[Execute SQL Task] Error: "Value does not fall within the expected range.'. Below are the my execute sql task query:

delete from STG_Shipped_Invoiced
where Transaction_Date=?
INSERT INTO STG_Shipped_Invoiced (div_Code, inv_inv_id, tot_Net_Amt,
trans_date, trans_type, Created_date, Transaction_Date)
select inv.DIV_CODE as Div_Code, inv.INV_ID as inv_inv_id, inv.TOT_NET_AMT as Tot_Net_Amt,

[Code] ....

Here I have defined a variable 'Date', where we are passing the date. Am i doing anything wrong with the delete query?

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

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

Query Returns No Row

Feb 23, 1999

When I was using a simple query using select statement with where clauses, I can get the results. When
I use AND to specify more conditions. It returns no row even though I get the result when query seperately.
What should be the possible cause of this. I am using SQL Server 6.5. Thank you

View 2 Replies View Related

Query Returns Different Values?

Dec 10, 2012

I have written sql query

select INVOICE.InvoiceTypeCode, INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,
INVOICETYPEMASTER.InvoiceTypeName, INVOICEITEMS.ItemQuantity as SumQuantity,
INVOICE.BasicValue ,INVOICE.BasicValue * INVOICE.ExchangeRate +

[Code].....

I am getting different amount 984000.0000 and quantity 9.

View 1 Replies View Related

Query Returns Inaccurate Count

Dec 13, 2014

Why does this return 64.00000 Hours Worked when if you do the calculation yourself you see that it should only be 16?

Code:
Create Table #One
(
BadgeNum int,
NameOnFile varchar(1000),
hoursworked int

[code]....

View 3 Replies View Related

I Want A Query That Returns Only The Column Names

Dec 16, 2007

I want a query that returns only the column names

View 4 Replies View Related

SubQuery Returns More Than One Row - Update Query

Sep 21, 2006

Hello,

I'm trying to update column based upon the results of a subquery. I'm getting the error that my Subquery returns more than one result. I've tried adding the EXISTS or IN keywords and cannot get the syntax right. I can't find any examples of how to write this with an Update query.

Here's my query:

UPDATE temp_UpdateRemainingHours
SET UsedHours =

(SELECT dbo.vw_SumEnteredHours_byCHARGE_CD.SumEnteredHours, dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD
FROM dbo.vw_SumEnteredHours_byCHARGE_CD INNER JOIN
dbo.temp_UpdateRemainingHours ON
dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD = dbo.temp_UpdateRemainingHours.CHARGE_CD)

FROM dbo.vw_SumEnteredHours_byCHARGE_CD, temp_UpdateRemainingHours

WHERE
dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD = dbo.temp_UpdateRemainingHours.CHARGE_CD


;




View 5 Replies View Related

Transact SQL :: Same Query Returns Different Result?

Jul 13, 2015

I am facing a problem that following query give me different result whenever I execute the query, it returns me same number of records but not all the rows are same everytime it is executed.

Select[Field1]
From
(
SelectRow_number() Over(Partition By [Field2], [Field3] Order By [Field2], [Field3], [Field4] Desc) ID, *
From[dbo].[Table1]
) A
WhereID > 1
OrderBy [Field1]

Those highlighted in yellow colours are duplicate records, I need to remove one of them.

View 8 Replies View Related

Transact SQL :: Query Returns 0 For Calculation

Aug 6, 2015

This is my syntax, and if I print the value that is stored in each variable @goodtries = 120 @badtries = 25 but when I run the syntax below it gives me 0.00

Declare @goodtries as int, @badtries as int
select @goodtries = convert(decimal(18,4),count(userID))
from table1
WHERE logintype IN ('Valid', 'Success')
select @badtries = convert(decimal(18,4),count(userID))

[code].....

View 7 Replies View Related

A Simple Query That Returns The Most Current Address

Jul 24, 2007

Hi,I have an Address table which contains more than one addresses for aparticular member. I want to write a query that would only displaymost current address. All addresses have a unique ID (addID).Example:memberID addID address1-------- ------ --------------------------------------------------295 69 13 Auster St295 70 465 Lorre Ct295 71 P.O. Box 321722 171 10 Hannaford Rd722 172 Dubai, United Arab Emirates


Quote:

View 1 Replies View Related

Parameterized Query Returns One Row With Null Values.

Jul 28, 2006

I am hoping someone could help me understand why this is happening and perhaps a solution.
I am using ASP.NET 2.0 with a SQL 2005 database.
In code behind, I am performing a query using a parameter as below:
sql = "SELECT field_name FROM myTable WHERE (field_name = @P1)"
objCommand.Parameters.Add(New SqlParameter("@P1", TextBox1.Text))
The parameter is obtained from TextBox1 which has valid input. However, the value is not in the table. The query should not return ANY results. However, I am getting one single row back with null values for each field requested in the query.
The SQL user account for this query has select, insert, and update permissions on the table. The query is simple, no joins, and the table has no null values in any fields. If I perform the exact same query using an account with select only permission on the table, I get what I was expecting, no records. Then if I go back to the previous user account with more permissioins, and I change the query to pass the paramter this way:
sql = String.Format("SELECT field_name FROM myTable WHERE (field_name = {0})", TextBox1.Text)
I also get NO records retuned using the same criteria.
What is going on here? I would prefer to use the parameterized query method with the account having elevated permissions. Is there some command object setting that can prevent the null row from returning?
Thanks!

View 7 Replies View Related

Query That Returns Record That Arn't Duplicated In Another Table

Jul 20, 2005

Hi.I have a table (websitehits) which holds statistics about websites.This table has a date field (datecounted). What I need is to create aquery which returns a list of dates between two date ranges (say ayear ago from today and a year from now) which only shows dates thathaven't been used in the websitehits table for a given website.For example if my table contains something like:Website Datecounted HitsSite101/01/046000Site101/02/046500Site101/03/046250Site201/03/041000Site201/04/041200Site201/05/041500So if query for ‘site1' then I'd get a list of all dates between30/11/03 to 30/11/05 with the exception of the dates 01/01/04,01/02/04 and 01/03/04.So far I've tried to do this using another table named calendar whichcontains a very long list of dates and to use this to produce the list– but I'm not getting very far.By the way I'm using sql server, an I need this query to generate alist for an asp page - so I need to pass the website name as aparameter so I guess I need to make this query as a stored procedure.Does anyone know how this can be done?

View 1 Replies View Related

Sql Query To Update A Column When The Subquery Returns More Than One Row

Oct 6, 2006

Hi People,

I am having a table which has some 10 cols, only one column had all Nulls. DB-SQL2K5

I am now writing a query like

Update Test1

set Id =

(Select t2.Id from

Test2 t2, Test1 t1

where

t2.Name = t1.Name)

as likely this query is faling as the sub query is retuning more than a row. What is the best method to achive my requirement?

Thanks

View 7 Replies View Related

Aggregate Task When Query Returns Empty.

Jan 13, 2008



To create a strike file for a text file output, I piped the output of a query throught a mulitcast. One output is the actual flat file, the other output is the strike file. The next task for the strike output is an aggregate count and sum functions. If the original query returns no records, the count shows zero, but the sum shows NULL. Further down the process I have to pad out the values with zero's, but that NULL is causing problems. How do I convert the Null to either an empty string, or a value of zero?
Thanks

View 1 Replies View Related

Data Mining Query Returns ADOMDDataReader

May 22, 2006

Hello,

I created a Market Basket Data Mining Model with Association Rules, which I want to query and show in a Report. Everything works fine, when I preview the result in the Reporting Services Data tab I see some sort of table which I can expand and then see the related products.

Unfortunately this result seems to be an ADOMD Datareader which I cannot place in a table, matrix or textfield.

If anyone knows how I can make this Informationen available in my Report please let me know.

Thanks in advance

View 6 Replies View Related

SUM Returns Incorrect Value In SQL Server Mobile Query ... Bug??

Feb 6, 2007

Hi all,

I am a newcomer to Microsoft Database Technology and have appeared to come across an issue with the SUM function in SQL Server Mobile Edition.

I am running Visual Studio 2005 and have created 2 tables:

Orders and OrderLines which are set up in master detail fashion.

The SQL Statement I create in the Query Builder is as follows:


SELECT     Orders.OrderNo, Orders.OrderDate, Orders.Priority, Orders.Address, SUM(OrderLines.Quantity * OrderLines.QualityReference) AS Total
FROM         Orders, OrderLines
WHERE     Orders.OrderNo = OrderLines.OrderNo
GROUP BY Orders.OrderNo, Orders.OrderDate, Orders.Priority, Orders.Address

Now, the SUM returns a total for all records in the OrderLines table, and not just the records whose OrderNo is the same as Orders.OrderNo

Can someone out there please clarify whether its an issue with my code or a bug with SQL Server Mobile???

Here are a couple of screenshots which will demonstrate what I mean.

Here is the contents of the Orders and OrderLines tables.  Each order has only 1 line item in it.

 http://public.fotki.com/AussieNoobie/sqlerrors/vs2005sqlceordersdata.html

When performing the summation over the OrderLines table, it produces the SUM of the all records in OrderLines and not according to the GROUP BY clause. See the following screen shot.

http://public.fotki.com/AussieNoobie/sqlerrors/vs2005sqlceerror1.html

I hope this explains it better.

Anyone have any ideas??? 

Thanks in advance

 

View 13 Replies View Related

SqlDataSource Returns Fewer Results Than Original Sql Query

Aug 3, 2007

I have a query that I created in SqlServer and then merely copied the code to a sqldatasource. They are both connected to the same db, however the sqldatasource query only returns 6 records and when run in SqlServer, it returns 52 rows which is the correct number.
 Any ideas on what might be causing this? There are no filters on the sqldatasource. It only has that one query. Do they differ in the way they execute sql code?
 Thanks for any help!

View 1 Replies View Related

SQL 2012 :: Query Returns 13864 On A Varchar Columns

Mar 24, 2015

We have a customer that is running SQL2012 and we are seeing a weird result on a query when we run it on their db. It is based off of a table that has about 30 columns but in this case we only care about 2 of them.

[Number] [varchar](15) NOT NULL
[Person_ID] [varchar](12) NULL

Here is the query we are doing:
Select Number,Person_ID From TableName where LP='ABC123'

The result I get back is the following:
Number:1
Person_ID:13864

The Person_ID should be a result of another table that created that Person_ID but it doesn't exist in that table. So we do not know where that 13864 is coming from. When we open that record through our application it shows Nothing for the Person_ID in that field.

When we do this query on our copy we get back
Number:1
Person_ID:

Which is exactly what we should see as the result.

Could there be a sql server setting that is set on their server that could possibly be given us back 13864 for a NULL value?

View 2 Replies View Related

Need Efficient Query To Partition Records By Type And Pull Top N Records From DB

Jan 18, 2008

I have a query similar to the following. The intent of this query is to retrieve the top 6 records meeting the specified criteria (LOGTYPENAME = 'Process Status Start' OR LOGTYPENAME = 'Process Status End' ) based on most recent dates. Please keep in mind that I expect to return up to 6 records for each unique LogProcessName. This could be thousands of different LogProcessNames with up to 6 records for each.

1) The table I am executing against currently is very large in size and thus takes a long time to execute against. It would seem there must be a more efficient query to get the results I am looking for?
2) CTE doesn't work on SQL 2000. I need a query that does.
3) I cannot modify the database itself in the process.


;WITH cte AS (
SELECT [LogProcessName], [LogBody], [LogDate], [LogGUID], row_number()
OVER(PARTITION BY [LogProcessName]
ORDER BY [LogDate] DESC)
AS RN
FROM [LOGTABLE]
WHERE [LogTypeGUID] IN (
SELECT LogTypeGUID
FROM LOGTYPE
WHERE LogTypeName = 'Process Status Start'
OR LogTypeName = 'Process Status End' ) )
SELECT *
FROM cte
WHERE RN = 1 OR RN = 2 OR RN = 3 OR RN = 4 OR RN = 5 OR RN = 6
ORDER BY [LogProcessName] DESC, [LogDate] DESC

Does anybody else have any idea that would yield the results that I am looking for and take into account items 1-3 above?

Thanks in advance.

View 4 Replies View Related

Nested SELECT Query That Also Returns COUNT From Related Table

Mar 4, 2005

OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.

Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.

CatID | Name | Description | No. Products

0001 | Cars | Blah blah blah | 5

etc etc

At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!

However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.

Many thanks!

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







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