December 5, 2008 at 3:15 am
SQl 2005
When I create the following View:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.vwImportAllNewClientIDs
AS
SELECT dbo.Bargains.ClientID, dbo.Bargains.Client, dbo.Bargains.CountryCode
FROM dbo.tblClients RIGHT OUTER JOIN
dbo.Bargains ON dbo.tblClients.ClientID COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Bargains.ClientID
WHERE (dbo.tblClients.ClientID IS NULL)
GROUP BY dbo.Bargains.ClientID, dbo.Bargains.Client, dbo.Bargains.CountryCode
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
If I now drill in the View using the Object Explorer, i can see the following Data Types have been set:
ClientID (varchar(20),null)
Client (varchar(255),null)
CountryCode (varchar(255),null)
Problem
How do you change the ClientID Data Type to (varchar(10),null)?
Thanks
December 5, 2008 at 3:25 am
Like this:
[font="Courier New"]SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.vwImportAllNewClientIDs
AS
SELECT CAST(b.ClientID AS VARCHAR (10)) AS ClientID, b.Client, b.CountryCode
FROM dbo.tblClients c
RIGHT OUTER JOIN dbo.Bargains b ON c.ClientID COLLATE SQL_Latin1_General_CP1_CI_AS = b.ClientID
WHERE c.ClientID IS NULL
GROUP BY b.ClientID, b.Client, b.CountryCode
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-- ALTERNATIVE
-- SELECT CAST(b.ClientID AS VARCHAR (10)) AS ClientID, b.Client, b.CountryCode
-- FROM dbo.Bargains b
-- WHERE NOT EXISTS (SELECT 1 FROM dbo.tblClients c WHERE c.ClientID COLLATE SQL_Latin1_General_CP1_CI_AS = b.ClientID)
[/font]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 5, 2008 at 3:41 am
Chris, thats fantastic, thank.
BUT....:angry:
Now when I run my SP which calls this View I get the following error:
(Error from Job History)
Message
Executed as user: UK\SQLAdmin. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:34:24 Error: 2008-12-05 10:34:30.06 Code: 0x00000000 Source: ProcImportAllFiles Description: Violation of PRIMARY KEY constraint 'PK_tblClients'. Cannot insert duplicate key in object 'dbo.tblClients'. End Error Error: 2008-12-05 10:34:30.06 Code: 0xC002F210 Source: ProcImportAllFiles Execute SQL Task Description: Executing the query "exec procImportAllFiles" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 10:34:24 Finished: 10:34:30 Elapsed: 6.015 seconds. The package execution failed. The step failed.
But when I run the follow SELECT against the 2 tables i get no returned results:
select c.clientid , v.clientid , c.client_long_Name
from dbo.vwImportAllNewClientIDs v
join dbo.tblclients c
on c.clientid COLLATE SQL_Latin1_General_CP1_CI_AS = v.clientid
SP Line with Error:
INSERT INTO dbo.tblClients
(Client_Short_Name,Client_Long_Name,CountryCode,ClientID)
SELECT dbo.vwImportAllNewClientIDs.Client, dbo.vwImportAllNewClientIDs.Client,
dbo.vwImportAllNewClientIDs.CountryCode, dbo.vwImportAllNewClientIDs.ClientID
FROM dbo.vwImportAllNewClientIDs
BUT, when i execute the Job or SSIS it works fine.... the correct
tables/rows are populated....?
December 5, 2008 at 3:54 am
You're not accounting for the trimming of clientid when you extract from the bargains table and insert into clients table. But why all the fuss with views and DTS/SSIS whatever, how about this?
INSERT INTO dbo.tblClients
(Client_Short_Name, Client_Long_Name, CountryCode, ClientID)
SELECT b.Client, b.Client, b.CountryCode, CAST(b.ClientID AS VARCHAR (10)) AS ClientID
FROM dbo.Bargains b
WHERE NOT EXISTS (SELECT 1 FROM dbo.tblClients c WHERE c.ClientID COLLATE SQL_Latin1_General_CP1_CI_AS = CAST(b.ClientID AS VARCHAR (10)))
Is there any other code you're running at the same time as part of the process?
Is the view wImportAllNewClientIDs being used for anything else other than this process?
Are you sure that the collation casting is necessary?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 5, 2008 at 4:04 am
Same error with this INSERT:
Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK_tblClients'.
Cannot insert duplicate key in object 'dbo.tblClients'.
The statement has been terminated.
Can I email you the:
- SP
- View
- Tables (Modify statements)
December 5, 2008 at 4:14 am
pri.amin (12/5/2008)
Same error with this INSERT:Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK_tblClients'.
Cannot insert duplicate key in object 'dbo.tblClients'.
The statement has been terminated.
Can I email you the:
- SP
- View
- Tables (Modify statements)
Assuming that both the view and the sp are specific to this process, which is extracting new clients from the bargains table and putting them into the clients table, then you're not going to need much code.
It's better to post here than to post by PM, others may benefit from this thread if all of it is here to be seen. Can you please post the table structures? Just tblClients and Bargains. Thanks.
D'oh, I missed the GROUP BY, sorry.
INSERT INTO dbo.tblClients
(Client_Short_Name, Client_Long_Name, CountryCode, ClientID)
SELECT b.Client, b.Client, b.CountryCode, CAST(b.ClientID AS VARCHAR (10)) AS ClientID
FROM dbo.Bargains b
WHERE NOT EXISTS (SELECT 1 FROM dbo.tblClients c WHERE c.ClientID COLLATE SQL_Latin1_General_CP1_CI_AS = CAST(b.ClientID AS VARCHAR (10)))
GROUP BY b.Client, b.CountryCode, CAST(b.ClientID AS VARCHAR (10))
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 5, 2008 at 4:32 am
Stil the same error with the GROUP BY...
Assuming that both the view and the sp are specific to this process, which is extracting new clients from the bargains table and putting them into the clients table, then you're not going to need much code.
It's better to post here than to post by PM, others may benefit from this thread if all of it is here to be seen. Can you please post the table structures? Just tblClients and Bargains. Thanks.
Bargains
USE [DealbookV2]
GO
/****** Object: Table [dbo].[Bargains] Script Date: 12/05/2008 11:28:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Bargains](
[Client] [varchar](255) NULL,
[BoughtSold] [varchar](255) NULL,
[RIC] [varchar](100) NULL,
[Trade_Date] [smalldatetime] NULL,
[Price] [varchar](16) NULL,
[Counterparty_Code] [varchar](255) NULL,
[Dealer] [varchar](255) NULL,
[Commission] [nvarchar](53) NULL,
[Consideration] [nvarchar](53) NULL,
[Quantity] [nvarchar](50) NULL,
[Currency] [varchar](255) NULL,
[SEDOL] [varchar](255) NULL,
[Participant_ID] [varchar](255) NULL,
[FIDESSA_Ref] [varchar](255) NULL,
[FX_Rate] [nvarchar](50) NULL,
[Local_Commission] [nvarchar](53) NULL,
[Bgn_Cond1] [varchar](255) NULL,
[Bgn_Cond2] [varchar](255) NULL,
[Bgn_Cond3] [varchar](255) NULL,
[Bgn_Cond4] [varchar](255) NULL,
[Dealing_Capacity] [varchar](255) NULL,
[Riskless_Principal] [varchar](255) NULL,
[CountryCode] [varchar](50) NULL,
[StockLongName] [varchar](255) NULL,
[StockShortName] [varchar](100) NULL,
[NumOfSharesInIssue] [nvarchar](50) NULL,
[MidPrice] [nvarchar](50) NULL,
[IndustrialClassification] [varchar](255) NULL,
[MarketSector] [varchar](255) NULL,
[MarketCap] [varchar](255) NULL,
[ISIN] [varchar](255) NULL,
[ClientID] [varchar](20) NULL,
[DealsID] [bigint] IDENTITY(1,1) NOT NULL,
[msrepl_tran_version] [uniqueidentifier] NOT NULL CONSTRAINT [DF__Bargains__msrepl__36F11965] DEFAULT (newid()),
CONSTRAINT [PK_Bargains] PRIMARY KEY CLUSTERED
(
[DealsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =
ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
tblClients
USE [DealbookV2]
GO
/****** Object: Table [dbo].[tblClients] Script Date: 12/05/2008 11:30:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblClients](
[Client_Short_Name] [nvarchar](255) NOT NULL,
[Client_Long_Name] [nvarchar](255) NULL,
[UK] [bit] NOT NULL CONSTRAINT [DF_tblClients_UK] DEFAULT ((0)),
[ClientID] [nvarchar](10) NOT NULL,
[Category] [nvarchar](25) NULL,
[CountryCode] [nvarchar](50) NULL,
[McLaganCode] [nvarchar](50) NULL,
[SLogixAccountID] [nvarchar](30) NULL,
[msrepl_tran_version] [uniqueidentifier] NOT NULL CONSTRAINT [DF__tblClient__msrep__21F5FC7F] DEFAULT (newid()),
[US] [bit] NOT NULL CONSTRAINT [DF_tblClients_US] DEFAULT ((0)),
[Client_Code] [nvarchar](50) NULL,
[UnbundlingDesc] [nvarchar](200) NULL,
[SalesTrader] [nvarchar](100) NULL,
CONSTRAINT [PK_tblClients] PRIMARY KEY CLUSTERED
(
[ClientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients',
@level2type=N'COLUMN',@level2name=N'Client_Short_Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients',
@level2type=N'COLUMN',@level2name=N'Client_Short_Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients',
@level2type=N'COLUMN',@level2name=N'Client_Short_Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients',
@level2type=N'COLUMN',@level2name=N'Client_Long_Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'Client_Long_Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'Client_Long_Name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'UK'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'UK'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'UK'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'ClientID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'ClientID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients',
@level2type=N'COLUMN',@level2name=N'ClientID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'Category'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'Category'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=3015 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'Category'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'CountryCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'CountryCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'CountryCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'McLaganCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients',
@level2type=N'COLUMN',@level2name=N'McLaganCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'McLaganCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'SLogixAccountID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'SLogixAccountID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'SLogixAccountID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'msrepl_tran_version'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'msrepl_tran_version'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients',
@level2type=N'COLUMN',@level2name=N'msrepl_tran_version'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'US'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients',
@level2type=N'COLUMN',@level2name=N'US'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'US'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnHidden', @value=False , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients',
@level2type=N'COLUMN',@level2name=N'Client_Code'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnOrder', @value=0 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'Client_Code'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_ColumnWidth', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients', @level2type=N'COLUMN',@level2name=N'Client_Code'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DefaultView', @value=0x02 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Filter', @value=NULL , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_OrderBy', @value=N'tblClients.Client_Short_Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_OrderByOn', @value=True , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Orientation', @value=0x00 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_TableMaxRecords', @value=10000 ,
@level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblClients'
December 5, 2008 at 4:51 am
Ah, it's a NVARCHAR!
Please run these in QA (or SMS) and post the counts from each statement:
SELECT COUNT(*)
FROM dbo.Bargains b
GROUP BY b.Client, b.CountryCode, CAST(b.ClientID AS NVARCHAR (10))
SELECT COUNT(*)
FROM dbo.Bargains b
WHERE NOT EXISTS (SELECT 1 FROM dbo.tblClients c WHERE c.ClientID COLLATE SQL_Latin1_General_CP1_CI_AS = CAST(b.ClientID AS NVARCHAR (10)))
GROUP BY b.Client, b.CountryCode, CAST(b.ClientID AS NVARCHAR (10))
SELECT COUNT(*)
FROM dbo.Bargains b
WHERE NOT EXISTS (SELECT 1 FROM dbo.tblClients c WHERE c.ClientID COLLATE SQL_Latin1_General_CP1_CI_AS = CAST(b.ClientID AS VARCHAR (10)))
GROUP BY b.Client, b.CountryCode, CAST(b.ClientID AS VARCHAR (10))
SELECT COUNT(*)
FROM dbo.Bargains b
WHERE NOT EXISTS (SELECT 1 FROM dbo.tblClients c WHERE c.ClientID = CAST(b.ClientID AS NVARCHAR (10)))
GROUP BY b.Client, b.CountryCode, CAST(b.ClientID AS NVARCHAR (10))
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 5, 2008 at 4:59 am
No results returned for any of the statements!
:crying:
December 5, 2008 at 5:02 am
Try this:
SELECT COUNT(*) FROM dbo.Bargains
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 5, 2008 at 5:04 am
Zero....
Is this going to drive you mad as it has done me! :crazy:
Thanks for your time, keep the suggestions coming....lol
December 5, 2008 at 5:08 am
A Little Help Please (12/5/2008)
Zero....Is this going to drive you mad as it has done me! :crazy:
Thanks for your time, keep the suggestions coming....lol
You have no data in your bargains table. How would you like to proceed?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 5, 2008 at 5:58 am
Ok i did a stupid one... I forgot to run my SSIS package to populate the tables before I ran the statements.
I have now attched the results in a spreadsheet as they are over 100 rows.
Each column is for each statement, in order as above
December 5, 2008 at 6:05 am
A Little Help Please (12/5/2008)
Ok i did a stupid one... I forgot to run my SSIS package to populate the tables before I ran the statements.I have now attched the results in a spreadsheet as they are over 100 rows.
Each column is for each statement, in order as above
Don't need 100 rows, just 4 numbers:
SELECT COUNT(*)
FROM dbo.Bargains b
GROUP BY b.Client, b.CountryCode, CAST(b.ClientID AS NVARCHAR (10))
-- COUNT(*) = ?
SELECT COUNT(*)
FROM dbo.Bargains b
WHERE NOT EXISTS (SELECT 1 FROM dbo.tblClients c WHERE c.ClientID COLLATE SQL_Latin1_General_CP1_CI_AS = CAST(b.ClientID AS NVARCHAR (10)))
GROUP BY b.Client, b.CountryCode, CAST(b.ClientID AS NVARCHAR (10))
-- COUNT(*) = ?
SELECT COUNT(*)
FROM dbo.Bargains b
WHERE NOT EXISTS (SELECT 1 FROM dbo.tblClients c WHERE c.ClientID COLLATE SQL_Latin1_General_CP1_CI_AS = CAST(b.ClientID AS VARCHAR (10)))
GROUP BY b.Client, b.CountryCode, CAST(b.ClientID AS VARCHAR (10))
-- COUNT(*) = ?
SELECT COUNT(*)
FROM dbo.Bargains b
WHERE NOT EXISTS (SELECT 1 FROM dbo.tblClients c WHERE c.ClientID = CAST(b.ClientID AS NVARCHAR (10)))
GROUP BY b.Client, b.CountryCode, CAST(b.ClientID AS NVARCHAR (10))
-- COUNT(*) = ?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 5, 2008 at 6:06 am
A Little Help Please (12/5/2008)
Ok i did a stupid one... I forgot to run my SSIS package to populate the tables before I ran the statements.I have now attched the results in a spreadsheet as they are over 100 rows.
Each column is for each statement, in order as above
Don't need 100 rows, just 4 numbers:
SELECT COUNT(*)
FROM dbo.Bargains b
GROUP BY b.Client, b.CountryCode, CAST(b.ClientID AS NVARCHAR (10))
-- COUNT(*) = ?
SELECT COUNT(*)
FROM dbo.Bargains b
WHERE NOT EXISTS (SELECT 1 FROM dbo.tblClients c WHERE c.ClientID COLLATE SQL_Latin1_General_CP1_CI_AS = CAST(b.ClientID AS NVARCHAR (10)))
GROUP BY b.Client, b.CountryCode, CAST(b.ClientID AS NVARCHAR (10))
-- COUNT(*) = ?
SELECT COUNT(*)
FROM dbo.Bargains b
WHERE NOT EXISTS (SELECT 1 FROM dbo.tblClients c WHERE c.ClientID COLLATE SQL_Latin1_General_CP1_CI_AS = CAST(b.ClientID AS VARCHAR (10)))
GROUP BY b.Client, b.CountryCode, CAST(b.ClientID AS VARCHAR (10))
-- COUNT(*) = ?
SELECT COUNT(*)
FROM dbo.Bargains b
WHERE NOT EXISTS (SELECT 1 FROM dbo.tblClients c WHERE c.ClientID = CAST(b.ClientID AS NVARCHAR (10)))
GROUP BY b.Client, b.CountryCode, CAST(b.ClientID AS NVARCHAR (10))
-- COUNT(*) = ?
Paste the above into SMS or QA, run each statement separately, record the count, then post back.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply