July 30, 2018 at 9:02 am
I have this code to create a temp table with 50,000 rows.
CREATE TABLE #TEMP1(Part varchar(90),Supplier varchar (10),Import varchar (10),Summary Varchar (15),HTS_8 Varchar(8),[Tariff_No] varchar(10),[Comm_Qty] Varchar(8),[Comm_qty_UoM] varchar (6),[Value_entered] int(20),[Supplier] Varchar (5),[Destination Varchar (5))
INSERT INTO #TEMP1 VALUES ('1F2Z17D957JAA_M300T','40911','40926','87081060','','','','','','','')
INSERT INTO #TEMP1 VALUES ('6L2Z15200AACP_BRJ0B','40911','40926','85122020','','','','','','','')
INSERT INTO #TEMP1 VALUES ('6C3Z25219A65BF_G977A','40922','40938','87082950','','','','','','','')
......
FROM
#TEMP1 T
INNER JOIN
[TSI].[ZATS_BROKER_FEED] Z
ON T.Part_Num = concat(Z.COMPOSITE_PART,'-',Supplier)
and
Z.CREATED_DATE = (Select max(Z.CREATED_DATE) from #TEMP1 T
Where Z.CREATED_DATE < T.Summary and
concat(Z.COMPOSITE_PART,'-',Supplier) = t.PART_Num)
Several Issues.
1. I get error: Msg 103, Level 15, State 4, Line 1
The identifier that starts with 'Destination Varchar (5))
INSERT INTO #TEMP1 VALUES ('1F2Z17D957JAA_M300T','40911','40926','87081060','','','','','','','')
INS' is too long. Maximum length is 128.
2. will that code return the max CREATED_DATE that is less the than the Summary Date in the Temp table?
My head hurts when i try to do this . . .thanks!
July 30, 2018 at 9:08 am
We don't have quite enough of your query to know exactly what's going on. However, I can see one potential problem in the Alias for your second instance of the #TEMP1 table as it occurs in your query fragment. You in both cases gave it the same alias, and that's a really bad idea, and can lead to significant confusion. At least change that 2nd table alias to T2 instead of just T, and adjust other join conditions appropriately, and see what you end up with, and then post back AT LEAST your entire query.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 30, 2018 at 9:11 am
thanks.
1. I fixed the TOO LONG error, i was missing a bracket.
i will review your suggestion about the alias, THANKS!
July 30, 2018 at 9:25 am
Ok, my SELECT vanished. I am back to this . . .
I have my temp table working, so here is my select
FROM
#TEMP1 T
INNER JOIN
[TSI].[ZATS_BROKER_FEED] Z
ON T.Part_Num = concat(Z.COMPOSITE_PART,'-',Supplier)
and
Z.CREATED_DATE = (Select max(Z.CREATED_DATE) from #TEMP1 Q
Where Z.CREATED_DATE < Q.Summary and
concat(Z.COMPOSITE_PART,'-',Supplier) = Q.PART_Num)
WHERE SUB_ORG = 'FORD'
I get two errors,
1, Incorrect syntax near FROM
2. incorrect syntax near WHERE
Grrrr, thoughts? thanks
July 30, 2018 at 9:43 am
Somewhat confusing, but I think this may return the rows you want:
SELECT T.*, Z.*
FROM
#TEMP1 T
INNER JOIN
[TSI].[ZATS_BROKER_FEED] Z
ON T.Part_Num LIKE Z.COMPOSITE_PART + '%' AND /*this is added in case it helps with efficiency*/
T.Part_Num = concat(Z.COMPOSITE_PART,'-',Supplier)
and
Z.CREATED_DATE = (Select max(Z.CREATED_DATE) from #TEMP1 Q
Where Z.CREATED_DATE < Q.Summary and
T.Part_num = Q.PART_Num)
WHERE Z.SUB_ORG = 'FORD' /* I guess SUB_ORG is in Z, since I don't see it listed in the definition for T above */
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 30, 2018 at 9:49 am
There are several issues with this:
1) You are returning the MAX() of a field from the main query. Since the subquery is evaluated for each row in the main query, there is exactly one distinct value for each evaluation of the subquery.
2) You are using a function on a field in your JOIN and WHERE clauses. This prevents any index on that field from being used. You should set up the TEMP table so that it has separate fields for the part and supplier so that they more closely match the design of ZATS_BROKER_FEED.
3) You are reading the #TEMP1 table twice when you only need to read it once.
I think what you are looking for is the following.
SELECT *
FROM
#TEMP1 T
CROSS APPLY
(
SELECT TOP(1) *
FROM [TSI].[ZATS_BROKER_FEED] Z
WHERE T.Part_Num = concat(Z.COMPOSITE_PART,'-',Supplier)
AND Z.CREATED_DATE < T.Summary
ORDER BY Z.CREATED_DATE DESC
) Z
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 30, 2018 at 3:24 pm
Temp table working correctly, now i am running this, but get zero results.
SELECT
.T.Part
,[COMPOSITE_PART]
,[ELIGIBILITY]
,[EFFECTIVE_FROM]
,[HTS_NUMBER]
,[CREATED_DATE]
,[HTS_DESCRIPTION]
,[PREF_PROGRAM_CODE]
,[COUNTRY_ORIGIN]
,[PTNR_SITE_ID]
FROM
#TEMP1 T
INNER JOIN
[TSI].[ZATS_BROKER_FEED] Z
ON T.Part_Num = Concat(z.COMPOSITE_PART,'_',z.PTNR_SITE_ID)
where
Z.CREATED_DATE = (Select max(Q.CREATED_DATE) from [TSI].[ZATS_BROKER_FEED] Q
Where Q.CREATED_DATE <= T.Summary) and Concat(q.COMPOSITE_PART,'_',Q.PTNR_SITE_ID) = T.PART_Num)
I thought this would work . . thoughts, again! thanks
July 30, 2018 at 3:34 pm
jeffshelix - Monday, July 30, 2018 3:24 PMTemp table working correctly, now i am running this, but get zero results.
SELECT
.T.Part
,[COMPOSITE_PART]
,[ELIGIBILITY]
,[EFFECTIVE_FROM]
,[HTS_NUMBER]
,[CREATED_DATE]
,[HTS_DESCRIPTION]
,[PREF_PROGRAM_CODE]
,[COUNTRY_ORIGIN]
,[PTNR_SITE_ID]
FROM
#TEMP1 T
INNER JOIN
[TSI].[ZATS_BROKER_FEED] Z
ON T.Part_Num = Concat(z.COMPOSITE_PART,'_',z.PTNR_SITE_ID)
where
Z.CREATED_DATE = (Select max(Q.CREATED_DATE) from [TSI].[ZATS_BROKER_FEED] Q
Where Q.CREATED_DATE <= T.Summary) and Concat(q.COMPOSITE_PART,'_',Q.PTNR_SITE_ID) = T.PART_Num)I thought this would work . . thoughts, again! thanks
We can't see your data, so we can't tell you why you're not getting any results. If you post data according to the specs in the first link in my signature, we might be able to help you.
Also, did you look at the CROSS APPLY method that I posted. I believe that will perform better than what you currently have, although there may be another option which will perform even better.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 30, 2018 at 3:51 pm
cross apply didnt work either, so my co worker went back to this method.
first few lines of temp table:
USE GTM_ODS
IF OBJECT_ID('TEMPDB..#TEMP1') IS NOT NULL DROP TABLE #TEMP1
CREATE TABLE #TEMP1(Part varchar(50),Import Date ,Summary Date,HTS8 Varchar (15),[Part_Num] Varchar(256),[Tariff_No] varchar(10),[Comm_Qty] Varchar(8),[Comm_qty_UoM] varchar (6),[Value_entered] varchar (20),TBD Varchar (5),[Destination] Varchar (5))
INSERT INTO #TEMP1 VALUES ('1F2Z17D957JAA_M300T','01/03/2012','01/18/2012','87081060','','','','','','','')
INSERT INTO #TEMP1 VALUES ('6L2Z15200AACP_BRJ0B','01/03/2012','01/18/2012','85122020','','','','','','','')
INSERT INTO #TEMP1 VALUES ('6C3Z25219A65BF_G977A','01/14/2012','01/30/2012','87082950','','','','','','','')
INSERT INTO #TEMP1 VALUES ('1M512450DC_K850E','01/27/2012','02/10/2012','87089375','','','','','','','')
INSERT INTO #TEMP1 VALUES ('6C3Z25219A65BF_G977A','02/14/2012','02/29/2012','87082950','','','','','','','')
INSERT INTO #TEMP1 VALUES ('389550S300_P761A','02/22/2012','03/07/2012','84099150','','','','','','','')
thanks
July 30, 2018 at 4:00 pm
I would expect something like
Select *
From #TEMP1 T1
Cross Apply (Select Max(Import) From #TEMP1 T2 Where T2.Import < T1.Summary And T2.Part = T1.Part) X(MaxImportDate)
To work, but I'm not quite sure what your expected results are from that data.
July 30, 2018 at 10:33 pm
jeffshelix - Monday, July 30, 2018 3:51 PMcross apply didnt work either, so my co worker went back to this method.first few lines of temp table:
USE GTM_ODSIF OBJECT_ID('TEMPDB..#TEMP1') IS NOT NULL DROP TABLE #TEMP1
CREATE TABLE #TEMP1(Part varchar(50),Import Date ,Summary Date,HTS8 Varchar (15),[Part_Num] Varchar(256),[Tariff_No] varchar(10),[Comm_Qty] Varchar(8),[Comm_qty_UoM] varchar (6),[Value_entered] varchar (20),TBD Varchar (5),[Destination] Varchar (5))
INSERT INTO #TEMP1 VALUES ('1F2Z17D957JAA_M300T','01/03/2012','01/18/2012','87081060','','','','','','','')
INSERT INTO #TEMP1 VALUES ('6L2Z15200AACP_BRJ0B','01/03/2012','01/18/2012','85122020','','','','','','','')
INSERT INTO #TEMP1 VALUES ('6C3Z25219A65BF_G977A','01/14/2012','01/30/2012','87082950','','','','','','','')
INSERT INTO #TEMP1 VALUES ('1M512450DC_K850E','01/27/2012','02/10/2012','87089375','','','','','','','')
INSERT INTO #TEMP1 VALUES ('6C3Z25219A65BF_G977A','02/14/2012','02/29/2012','87082950','','','','','','','')
INSERT INTO #TEMP1 VALUES ('389550S300_P761A','02/22/2012','03/07/2012','84099150','','','','','','','')thanks
So, what is the expected output of this sample data?
July 31, 2018 at 7:25 am
jeffshelix - Monday, July 30, 2018 3:51 PMcross apply didnt work either, so my co worker went back to this method.first few lines of temp table:
USE GTM_ODSIF OBJECT_ID('TEMPDB..#TEMP1') IS NOT NULL DROP TABLE #TEMP1
CREATE TABLE #TEMP1(Part varchar(50),Import Date ,Summary Date,HTS8 Varchar (15),[Part_Num] Varchar(256),[Tariff_No] varchar(10),[Comm_Qty] Varchar(8),[Comm_qty_UoM] varchar (6),[Value_entered] varchar (20),TBD Varchar (5),[Destination] Varchar (5))
INSERT INTO #TEMP1 VALUES ('1F2Z17D957JAA_M300T','01/03/2012','01/18/2012','87081060','','','','','','','')
INSERT INTO #TEMP1 VALUES ('6L2Z15200AACP_BRJ0B','01/03/2012','01/18/2012','85122020','','','','','','','')
INSERT INTO #TEMP1 VALUES ('6C3Z25219A65BF_G977A','01/14/2012','01/30/2012','87082950','','','','','','','')
INSERT INTO #TEMP1 VALUES ('1M512450DC_K850E','01/27/2012','02/10/2012','87089375','','','','','','','')
INSERT INTO #TEMP1 VALUES ('6C3Z25219A65BF_G977A','02/14/2012','02/29/2012','87082950','','','','','','','')
INSERT INTO #TEMP1 VALUES ('389550S300_P761A','02/22/2012','03/07/2012','84099150','','','','','','','')thanks
You have two tables in your query, but you only posted sample data for one.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply