June 11, 2014 at 3:04 am
pk2dpvp (6/11/2014)
11,5311,535,75,7
1,671,67
100,57132
13,111,97
09,81
I was expecting to see two columns of float numbers. You may get some clues from this:
CREATE TABLE #Temp (Float1 FLOAT(1), Float24 FLOAT(24), Float53 FLOAT(53))
INSERT INTO #Temp (Float1, Float24, Float53) SELECT pi(), pi(), pi()
SELECT * FROM #Temp
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
June 11, 2014 at 3:09 am
I see...
but is that my problem?
and if it is, how may I fix it?
June 11, 2014 at 3:20 am
Select one of the tables in Object Explorer in SSMS.
Right-click on it, from the popup menu select "Script Table as", then "Create to" then "New Query Editor Window".
Do the same with the other table.
Post both scripts here.
*Please* use the IF code shortcut for SQL code.
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
June 11, 2014 at 3:23 am
USE [Hofstede]
GO
/****** Object: Table [dbo].[sparepartsupdate] Script Date: 11-6-2014 11:22:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[sparepartsupdate](
[id] [int] IDENTITY(1,1) NOT NULL,
[PartNrFabrikant] [nvarchar](35) NULL,
[omschrijving] [nvarchar](50) NULL,
[groep] [int] NULL,
[verkoopprijs] [float] NULL,
[toegevoegd] [datetime] NULL,
[gewijzigd] [datetime] NULL,
[serienummer] [bit] NULL,
[endoflife] [bit] NULL,
[eol_date] [datetime] NULL,
[nchar](20) NULL,
[Eenheid] [int] NULL,
[BTW] [int] NULL,
[Voorraad] [bit] NULL,
[VoorraadAantal] [int] NULL,
[Afbeelding] [nvarchar](50) NULL,
[Merk] [nchar](10) NULL,
[EAN] [nvarchar](25) NULL,
[Front] [bit] NULL,
[FrontGroep] [int] NULL,
[Frontcolor] [int] NULL,
[Fronttekst] [nchar](20) NULL,
[Frontimage] [nvarchar](50) NULL,
[Kleurcode1] [int] NULL,
[Kleur1] [nvarchar](50) NULL,
[Kleur2] [nvarchar](50) NULL,
[Kleurcode2] [int] NULL,
[Model] [nvarchar](50) NULL,
[Framecode] [nchar](10) NULL,
[Framehoogte] [int] NULL,
[Remsysteem] [nchar](10) NULL,
[CollectieJaar] [int] NULL,
[BarcodeType] [nvarchar](10) NULL,
[BestelNummer] [nvarchar](50) NULL,
[ArtikelGroep] [int] NULL,
[VervangendArtikel] [varchar](50) NULL,
[Leverancier] [nchar](25) NULL,
[Verwijderd] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [Hofstede]
GO
/****** Object: Table [dbo].[artikelen] Script Date: 11-6-2014 11:21:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[artikelen](
[id] [int] IDENTITY(1,1) NOT NULL,
[PartNrFabrikant] [nvarchar](35) NULL,
[omschrijving] [nvarchar](50) NULL,
[groep] [int] NULL,
[verkoopprijs] [float] NULL,
[toegevoegd] [datetime] NULL,
[gewijzigd] [datetime] NULL,
[serienummer] [bit] NULL,
[endoflife] [bit] NULL,
[eol_date] [datetime] NULL,
[nchar](20) NULL,
[Eenheid] [int] NULL,
[BTW] [int] NULL,
[Voorraad] [bit] NULL,
[VoorraadAantal] [int] NULL,
[Afbeelding] [nvarchar](50) NULL,
[Merk] [nchar](10) NULL,
[EAN] [nvarchar](25) NULL,
[Front] [bit] NULL,
[FrontGroep] [int] NULL,
[Frontcolor] [int] NULL,
[Fronttekst] [nchar](20) NULL,
[Frontimage] [nvarchar](50) NULL,
[Kleurcode1] [int] NULL,
[Kleur1] [nvarchar](50) NULL,
[Kleur2] [nvarchar](50) NULL,
[Kleurcode2] [int] NULL,
[Model] [nvarchar](50) NULL,
[Framecode] [nchar](10) NULL,
[Framehoogte] [int] NULL,
[Remsysteem] [nchar](10) NULL,
[CollectieJaar] [int] NULL,
[BarcodeType] [nvarchar](10) NULL,
[BestelNummer] [nvarchar](50) NULL,
[ArtikelGroep] [int] NULL,
[VervangendArtikel] [varchar](50) NULL,
[Leverancier] [nchar](25) NULL,
[Verwijderd] [datetime] NULL,
CONSTRAINT [PK_artikelen] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
June 11, 2014 at 3:51 am
Select one of the tables in SSMS.
Right-click on column [verkoopprijs] to raise the property sheet. Record the values for [Length] and [Numeric Precision].
Do the same with the other table, post the results here.
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
June 11, 2014 at 3:56 am
-Artikelen -Verkoopprijs
Length: 8
Numeric Precision: 53
-SparePartsUpdate -Verkoopprijs
Length: 8
Numeric Precision: 53
June 11, 2014 at 4:29 am
Thanks.
Here's an article which explains what's happening and offers a workaround.
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
June 11, 2014 at 4:48 am
so according to what you are saying, the problem in my script is the float point
I've tried to run my script without any float's (Just the description, which is nvarchar) but it didn't manage anything different from what it did WITH the float
I tried the workaround but either that was not successfull..
June 11, 2014 at 5:01 am
pk2dpvp (6/11/2014)
so according to what you are saying, the problem in my script is the float pointI've tried to run my script without any float's (Just the description, which is nvarchar) but it didn't manage anything different from what it did WITH the float
I tried the workaround but either that was not successfull..
Interesting...
Run these three queries, one at a time, in SSMS - and post back your findings:
-- Query 1
SELECT
a.omschrijving, sp.omschrijving,
--a.verkoopprijs, sp.verkoopprijs,
a.gewijzigd, getDate()
FROM artikelen a
INNER JOIN Hofstede.dbo.sparepartsupdate sp
ON a.PartNrFabrikant = sp.PartNrFabrikant
WHERE A.omschrijving <> sp.omschrijving
--OR A.verkoopprijs <> sp.verkoopprijs
-- Query 2
UPDATE a SET
omschrijving = sp.omschrijving,
--verkoopprijs = sp.verkoopprijs,
gewijzigd = getDate()
FROM artikelen a
INNER JOIN Hofstede.dbo.sparepartsupdate sp
ON a.PartNrFabrikant = sp.PartNrFabrikant
WHERE a.omschrijving <> sp.omschrijving
--OR a.verkoopprijs <> sp.verkoopprijs
-- Query 3
SELECT
a.omschrijving, sp.omschrijving,
--a.verkoopprijs, sp.verkoopprijs,
a.gewijzigd, getDate()
FROM artikelen a
INNER JOIN Hofstede.dbo.sparepartsupdate sp
ON a.PartNrFabrikant = sp.PartNrFabrikant
WHERE A.omschrijving <> sp.omschrijving
--OR A.verkoopprijs <> sp.verkoopprijs
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
June 11, 2014 at 5:08 am
gone.
June 11, 2014 at 5:44 am
pk2dpvp (6/11/2014)
...(1960 row(s) affected)
(1960 row(s) affected)
(1960 row(s) affected)
it returned utter chaos :p
Not quite. Your results are exactly consistent with running this:
-- Query 1
SELECT
a.omschrijving, sp.omschrijving,
--a.verkoopprijs, sp.verkoopprijs,
a.gewijzigd, getDate()
FROM artikelen a
INNER JOIN Hofstede.dbo.sparepartsupdate sp
ON a.PartNrFabrikant = sp.PartNrFabrikant
WHERE A.omschrijving <> sp.omschrijving
--OR A.verkoopprijs <> sp.verkoopprijs
-- Query 2
UPDATE a SET
--omschrijving = sp.omschrijving,
--verkoopprijs = sp.verkoopprijs,
gewijzigd = getDate()
FROM artikelen a
INNER JOIN Hofstede.dbo.sparepartsupdate sp
ON a.PartNrFabrikant = sp.PartNrFabrikant
WHERE a.omschrijving <> sp.omschrijving
--OR a.verkoopprijs <> sp.verkoopprijs
-- Query 3
SELECT
sp.omschrijving, a.omschrijving,
--a.verkoopprijs, sp.verkoopprijs,
a.gewijzigd, getDate()
FROM artikelen a
INNER JOIN Hofstede.dbo.sparepartsupdate sp
ON a.PartNrFabrikant = sp.PartNrFabrikant
WHERE A.omschrijving <> sp.omschrijving
--OR A.verkoopprijs <> sp.verkoopprijs
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
June 11, 2014 at 5:47 am
yes, but what can I do with this information?
June 11, 2014 at 6:41 am
The two large data blocks which you posted earlier are slowing down access to this thread - can I ask you to shorten each block to say ten rows please? Thanks.
I'm not sure where to take this from here. The three queries I posted earlier, if copied and pasted without any changes into an SSMS window and executed, shouldn't generate the output you posted. Did you make any changes before running them?
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
June 11, 2014 at 7:59 am
well,
I fixed the problem
though I do not know why it got fixed...
what I did:
I changed ever A to SP and every SP to A (Turned around the script so it'd run the other way)
It made 1029 changes and afterwardsly no more then 0 unless I changed something
So I changed it back again (Every SP to A and A to SP) and it just ran perfectly
It still runs perfectly 😀
I have no idea how that fixed it, But I'm glad it is fixed after all :p
For now I guess this thread can be seen as [SOLVED]
Thanks for the help, though! 😀 It is appreciated big time ^^
June 11, 2014 at 8:09 am
pk2dpvp (6/11/2014)
well,I fixed the problem
though I do not know why it got fixed...
what I did:
I changed ever A to SP and every SP to A (Turned around the script so it'd run the other way)
It made 1029 changes and afterwardsly no more then 0 unless I changed something
So I changed it back again (Every SP to A and A to SP) and it just ran perfectly
It still runs perfectly 😀
I have no idea how that fixed it, But I'm glad it is fixed after all :p
For now I guess this thread can be seen as [SOLVED]
Thanks for the help, though! 😀 It is appreciated big time ^^
You had the update the wrong way around somewhere :hehe:
Two lessons you can take away with you today.
Firstly, if a poster offers code for you to run in order to test something, always run it exactly as it is, unless they specifically state otherwise.
Secondly, post the exact code you would run to demonstrate the problem. Any tweaking to make it look simpler could remove vital clues.
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 - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply