Trying to create a different unique key to query onWhat is the difference between varchar and nvarchar?How can foreign key constraints be temporarily disabled using T-SQL?Inserting multiple rows in a single SQL query?Difference between JOIN and INNER JOINHow do I create a unique constraint that also allows nulls?Does a foreign key automatically create an index?Select varchar max in Codeigniter returns unknown charactersImproving the Performance of SQL QueryIF Condition Perform Query, Else Perform Other Query Base on Count to ExecuteHow to pivot two of the columns in my SQL Query
Customs and immigration on a USA-UK-Sweden flight itinerary
How soon after takeoff can you recline your airplane seat?
Single method for different parameterized mysql command
What does 5d4 x 10 gp mean?
Indentation of First Paragraphs
Is it possible to pray to Hashem for a specific person as your prospective spouse?
How would one prevent political gerrymandering?
Discworld quote about an "old couple" who having said everything to each other, can finally go about living their lives
Why would Dementors torture a Death Eater if they are loyal to Voldemort?
Does friction always oppose motion?
Where to connect the fuse and why?
Convert array type variable to string type with space delimiter
Journal standards vs. personal standards
Should Catholics in a state of grace call themselves sinners?
Why do movie directors use brown tint on Mexico cities?
Do electrons really perform instantaneous quantum leaps?
Tikz, loop not appearing
Can dual citizens open crypto exchange accounts where U.S. citizens are prohibited?
Listen to my Story...Let us find the Unique Invisible Pan Digital Pair
Would skyscrapers tip over if people fell sideways?
Why were the first airplanes "backwards"?
Avoiding repetition when using the "snprintf idiom" to write text
"I am [the / an] owner of a bookstore"?
Is this house-rule removing the increased effect of cantrips at higher character levels balanced?
Trying to create a different unique key to query on
What is the difference between varchar and nvarchar?How can foreign key constraints be temporarily disabled using T-SQL?Inserting multiple rows in a single SQL query?Difference between JOIN and INNER JOINHow do I create a unique constraint that also allows nulls?Does a foreign key automatically create an index?Select varchar max in Codeigniter returns unknown charactersImproving the Performance of SQL QueryIF Condition Perform Query, Else Perform Other Query Base on Count to ExecuteHow to pivot two of the columns in my SQL Query
0
I have a table that has two fields that are a unique key, but the keys in this table I don't need because I need two other fields that are not included in this table (Jda_sourcing). I created a query that I think would give me what I need, but I'm not sure. I ran and there are no errors, but wondering if this is the way go.
This is a long query, so I won't include everything. I want the composite key to be BrandNum and site vendor. These columns are not in the Jda_sourcing Table. The Item is what is in common with both tables. I'm not sure if this is the way to go, so that is why I'm posting this question.
SELECT COUNT(*),
item,
source,
dest
FROM [dq].[jda_sourcing] AS js
WHERE [item] IN (SELECT [item]
FROM [dq].[jda_udt_item_local]
WHERE
--EXISTS
[u_brand] IN (SELECT TOP 100
itm.u_brand AS BrandNum
FROM (SELECT lnk.material,
loc.plant,
loc.storagelocation,
lnk.purchasingstatus,
lnk.sellingstatus,
lnk.sitevendor,
CASE WHEN ssc.material IS NOT NULL THEN 'SSC' ELSE 'DIRECT' END AS source
FROM scm.sap_marc AS lnk
JOIN (SELECT DISTINCT
loc AS plant,
RIGHT(location_id, 4) AS storagelocation,
loc_type
FROM dq.static_location
WHERE loc IS NOT NULL
AND loc_status = 'A'
AND loc_type = 'FWD') AS loc ON loc.plant = lnk.plant
LEFT JOIN scm.sap_mard AS ssc ON ssc.plant = loc.plant
AND ssc.material = lnk.material
AND ssc.sscvalidfrom <= CONVERT(varchar(8), GETDATE(), 112)
AND ssc.sscvalidto >= CONVERT(varchar(8), GETDATE(), 112)
LEFT JOIN scm.sap_zdt_mm_source AS src ON src.plant = lnk.plant
AND src.material = lnk.material
AND (lnk.sitevendor = src.vendor
OR ssc.material IS NOT NULL
OR LEN(src.vendor) = 4)
AND src.validfromdate <= CONVERT(varchar(8), GETDATE(), 112)
AND src.validtodate >= CONVERT(varchar(8), GETDATE(), 112)
WHERE lnk.purchasingstatus IN ('P', 'U', 'W')
AND src.material IS NULL) AS msrc
LEFT JOIN dq.jda_udt_item_local AS itm ON itm.item = msrc.material
LEFT JOIN scm.sap_mara AS ssn ON ssn.material = msrc.material
AND ssn.supercedeeffdate <= GETDATE()
LEFT JOIN (SELECT item,
dest,
tier1,
gs_source,
supplier,
otc_status,
u_inco_term
FROM [flmir-jdasqldv1].scdq.dq.jda_sku_sourcing AS SKUSRC
JOIN [flmir-jdasqldv1].scdq.dq.jda_udt_loc AS LOC ON LOC.loc = SKUSRC.tier1
WHERE tier1 <> ''
--and otc_status in ('AA','AH','IN','IF','IH')
) AS lss ON lss.item = msrc.material
AND lss.dest = CAST(msrc.plant AS varchar) + '-' + CAST(msrc.storagelocation AS varchar)
LEFT JOIN scm.ci_corpitem AS ci ON ci.item = msrc.material
--left join [dq].[jda_sourcing]js on js.[item]=msrc.material
--and js.[dest]= cast(msrc.plant as varchar) + '-' + cast(msrc.storagelocation as varchar)
LEFT JOIN temp.lg_sourcing_xref AS lgs ON lgs.item = msrc.material
AND lgs.plant = CAST(msrc.plant AS varchar)
AND lgs.sloc = CAST(msrc.storagelocation AS varchar)
WHERE ISNULL(itm.u_prod_class, 0) NOT IN (40, 85, 86, 94, 96, 99)
AND ISNULL(itm.u_brand, 0) <> '9999'))
AND LEN(source) = 13
GROUP BY item,
source,
dest
UNION ALL
SELECT COUNT(*),
item,
source,
dest
FROM [dq].[jda_sourcing] AS js
WHERE [item] IN (SELECT [material]
FROM [scm].[sap_marc]
WHERE
--EXISTS
[sitevendor] IN (SELECT TOP 100
msrc.sitevendor AS SiteVendor
FROM (SELECT lnk.material,
loc.plant,
loc.storagelocation,
lnk.purchasingstatus,
lnk.sellingstatus,
lnk.sitevendor,
CASE WHEN ssc.material IS NOT NULL THEN 'SSC' ELSE 'DIRECT' END AS source
FROM scm.sap_marc AS lnk
JOIN (SELECT DISTINCT
loc AS plant,
RIGHT(location_id, 4) AS storagelocation,
loc_type
FROM dq.static_location
WHERE loc IS NOT NULL
AND loc_status = 'A'
AND loc_type = 'FWD') AS loc ON loc.plant = lnk.plant
LEFT JOIN scm.sap_mard AS ssc ON ssc.plant = loc.plant
AND ssc.material = lnk.material
AND ssc.sscvalidfrom <= CONVERT(varchar(8), GETDATE(), 112)
AND ssc.sscvalidto >= CONVERT(varchar(8), GETDATE(), 112)
LEFT JOIN scm.sap_zdt_mm_source AS src ON src.plant = lnk.plant
AND src.material = lnk.material
AND (lnk.sitevendor = src.vendor
OR ssc.material IS NOT NULL
OR LEN(src.vendor) = 4)
AND src.validfromdate <= CONVERT(varchar(8), GETDATE(), 112)
AND src.validtodate >= CONVERT(varchar(8), GETDATE(), 112)
WHERE lnk.purchasingstatus IN ('P', 'U', 'W')
AND src.material IS NULL) AS msrc
LEFT JOIN dq.jda_udt_item_local AS itm ON itm.item = msrc.material
LEFT JOIN scm.sap_mara AS ssn ON ssn.material = msrc.material
AND ssn.supercedeeffdate <= GETDATE()
LEFT JOIN (SELECT item,
dest,
tier1,
gs_source,
supplier,
otc_status,
u_inco_term
FROM [flmir-jdasqldv1].scdq.dq.jda_sku_sourcing AS SKUSRC
JOIN [flmir-jdasqldv1].scdq.dq.jda_udt_loc AS LOC ON LOC.loc = SKUSRC.tier1
WHERE tier1 <> ''
--and otc_status in ('AA','AH','IN','IF','IH')
) AS lss ON lss.item = msrc.material
AND lss.dest = CAST(msrc.plant AS varchar) + '-' + CAST(msrc.storagelocation AS varchar)
LEFT JOIN scm.ci_corpitem AS ci ON ci.item = msrc.material
--left join [dq].[jda_sourcing]js on js.[item]=msrc.material
--and js.[dest]= cast(msrc.plant as varchar) + '-' + cast(msrc.storagelocation as varchar)
LEFT JOIN temp.lg_sourcing_xref AS lgs ON lgs.item = msrc.material
AND lgs.plant = CAST(msrc.plant AS varchar)
AND lgs.sloc = CAST(msrc.storagelocation AS varchar)
WHERE ISNULL(itm.u_prod_class, 0) NOT IN (40, 85, 86, 94, 96, 99)
AND ISNULL(itm.u_brand, 0) <> '9999'))
AND LEN(source) = 13
GROUP BY item,
source,
dest;
sql-server edited Mar 25 at 16:01
Larnu
27.9k6 gold badges20 silver badges33 bronze badges
asked Mar 25 at 15:35
RFCRFC
134 bronze badges
|
show 4 more comments
0
I have a table that has two fields that are a unique key, but the keys in this table I don't need because I need two other fields that are not included in this table (Jda_sourcing). I created a query that I think would give me what I need, but I'm not sure. I ran and there are no errors, but wondering if this is the way go.
This is a long query, so I won't include everything. I want the composite key to be BrandNum and site vendor. These columns are not in the Jda_sourcing Table. The Item is what is in common with both tables. I'm not sure if this is the way to go, so that is why I'm posting this question.
SELECT COUNT(*),
item,
source,
dest
FROM [dq].[jda_sourcing] AS js
WHERE [item] IN (SELECT [item]
FROM [dq].[jda_udt_item_local]
WHERE
--EXISTS
[u_brand] IN (SELECT TOP 100
itm.u_brand AS BrandNum
FROM (SELECT lnk.material,
loc.plant,
loc.storagelocation,
lnk.purchasingstatus,
lnk.sellingstatus,
lnk.sitevendor,
CASE WHEN ssc.material IS NOT NULL THEN 'SSC' ELSE 'DIRECT' END AS source
FROM scm.sap_marc AS lnk
JOIN (SELECT DISTINCT
loc AS plant,
RIGHT(location_id, 4) AS storagelocation,
loc_type
FROM dq.static_location
WHERE loc IS NOT NULL
AND loc_status = 'A'
AND loc_type = 'FWD') AS loc ON loc.plant = lnk.plant
LEFT JOIN scm.sap_mard AS ssc ON ssc.plant = loc.plant
AND ssc.material = lnk.material
AND ssc.sscvalidfrom <= CONVERT(varchar(8), GETDATE(), 112)
AND ssc.sscvalidto >= CONVERT(varchar(8), GETDATE(), 112)
LEFT JOIN scm.sap_zdt_mm_source AS src ON src.plant = lnk.plant
AND src.material = lnk.material
AND (lnk.sitevendor = src.vendor
OR ssc.material IS NOT NULL
OR LEN(src.vendor) = 4)
AND src.validfromdate <= CONVERT(varchar(8), GETDATE(), 112)
AND src.validtodate >= CONVERT(varchar(8), GETDATE(), 112)
WHERE lnk.purchasingstatus IN ('P', 'U', 'W')
AND src.material IS NULL) AS msrc
LEFT JOIN dq.jda_udt_item_local AS itm ON itm.item = msrc.material
LEFT JOIN scm.sap_mara AS ssn ON ssn.material = msrc.material
AND ssn.supercedeeffdate <= GETDATE()
LEFT JOIN (SELECT item,
dest,
tier1,
gs_source,
supplier,
otc_status,
u_inco_term
FROM [flmir-jdasqldv1].scdq.dq.jda_sku_sourcing AS SKUSRC
JOIN [flmir-jdasqldv1].scdq.dq.jda_udt_loc AS LOC ON LOC.loc = SKUSRC.tier1
WHERE tier1 <> ''
--and otc_status in ('AA','AH','IN','IF','IH')
) AS lss ON lss.item = msrc.material
AND lss.dest = CAST(msrc.plant AS varchar) + '-' + CAST(msrc.storagelocation AS varchar)
LEFT JOIN scm.ci_corpitem AS ci ON ci.item = msrc.material
--left join [dq].[jda_sourcing]js on js.[item]=msrc.material
--and js.[dest]= cast(msrc.plant as varchar) + '-' + cast(msrc.storagelocation as varchar)
LEFT JOIN temp.lg_sourcing_xref AS lgs ON lgs.item = msrc.material
AND lgs.plant = CAST(msrc.plant AS varchar)
AND lgs.sloc = CAST(msrc.storagelocation AS varchar)
WHERE ISNULL(itm.u_prod_class, 0) NOT IN (40, 85, 86, 94, 96, 99)
AND ISNULL(itm.u_brand, 0) <> '9999'))
AND LEN(source) = 13
GROUP BY item,
source,
dest
UNION ALL
SELECT COUNT(*),
item,
source,
dest
FROM [dq].[jda_sourcing] AS js
WHERE [item] IN (SELECT [material]
FROM [scm].[sap_marc]
WHERE
--EXISTS
[sitevendor] IN (SELECT TOP 100
msrc.sitevendor AS SiteVendor
FROM (SELECT lnk.material,
loc.plant,
loc.storagelocation,
lnk.purchasingstatus,
lnk.sellingstatus,
lnk.sitevendor,
CASE WHEN ssc.material IS NOT NULL THEN 'SSC' ELSE 'DIRECT' END AS source
FROM scm.sap_marc AS lnk
JOIN (SELECT DISTINCT
loc AS plant,
RIGHT(location_id, 4) AS storagelocation,
loc_type
FROM dq.static_location
WHERE loc IS NOT NULL
AND loc_status = 'A'
AND loc_type = 'FWD') AS loc ON loc.plant = lnk.plant
LEFT JOIN scm.sap_mard AS ssc ON ssc.plant = loc.plant
AND ssc.material = lnk.material
AND ssc.sscvalidfrom <= CONVERT(varchar(8), GETDATE(), 112)
AND ssc.sscvalidto >= CONVERT(varchar(8), GETDATE(), 112)
LEFT JOIN scm.sap_zdt_mm_source AS src ON src.plant = lnk.plant
AND src.material = lnk.material
AND (lnk.sitevendor = src.vendor
OR ssc.material IS NOT NULL
OR LEN(src.vendor) = 4)
AND src.validfromdate <= CONVERT(varchar(8), GETDATE(), 112)
AND src.validtodate >= CONVERT(varchar(8), GETDATE(), 112)
WHERE lnk.purchasingstatus IN ('P', 'U', 'W')
AND src.material IS NULL) AS msrc
LEFT JOIN dq.jda_udt_item_local AS itm ON itm.item = msrc.material
LEFT JOIN scm.sap_mara AS ssn ON ssn.material = msrc.material
AND ssn.supercedeeffdate <= GETDATE()
LEFT JOIN (SELECT item,
dest,
tier1,
gs_source,
supplier,
otc_status,
u_inco_term
FROM [flmir-jdasqldv1].scdq.dq.jda_sku_sourcing AS SKUSRC
JOIN [flmir-jdasqldv1].scdq.dq.jda_udt_loc AS LOC ON LOC.loc = SKUSRC.tier1
WHERE tier1 <> ''
--and otc_status in ('AA','AH','IN','IF','IH')
) AS lss ON lss.item = msrc.material
AND lss.dest = CAST(msrc.plant AS varchar) + '-' + CAST(msrc.storagelocation AS varchar)
LEFT JOIN scm.ci_corpitem AS ci ON ci.item = msrc.material
--left join [dq].[jda_sourcing]js on js.[item]=msrc.material
--and js.[dest]= cast(msrc.plant as varchar) + '-' + cast(msrc.storagelocation as varchar)
LEFT JOIN temp.lg_sourcing_xref AS lgs ON lgs.item = msrc.material
AND lgs.plant = CAST(msrc.plant AS varchar)
AND lgs.sloc = CAST(msrc.storagelocation AS varchar)
WHERE ISNULL(itm.u_prod_class, 0) NOT IN (40, 85, 86, 94, 96, 99)
AND ISNULL(itm.u_brand, 0) <> '9999'))
AND LEN(source) = 13
GROUP BY item,
source,
dest;
edited Mar 25 at 16:01
Larnu
27.9k6 gold badges20 silver badges33 bronze badges
asked Mar 25 at 15:35
RFCRFC
134 bronze badges
1
That query is incomplete. You haveFROM (SELECT )Where's the rest of that statement (and the alias on the subquery)? Then you have anANDbut no priorWHERE.
– Larnu
Mar 25 at 15:39
That's not really a lot to go on, without knowing the structures of all those objects in your query.
– Richard Hansell
Mar 25 at 15:40
1
But what is the question? You posted a partial bit of sql but you don't actually ask anything other than a vague explanation of the requirements.
– Sean Lange
Mar 25 at 15:40
There is also 3 sets of parenthesis that aren't closed. That many nested subqueries though are going to be likely a performance killer.
– Larnu
Mar 25 at 15:40
i will post the whole query. I'll edit it.
– RFC
Mar 25 at 15:44
|
show 4 more comments
0
0
0
I have a table that has two fields that are a unique key, but the keys in this table I don't need because I need two other fields that are not included in this table (Jda_sourcing). I created a query that I think would give me what I need, but I'm not sure. I ran and there are no errors, but wondering if this is the way go.
This is a long query, so I won't include everything. I want the composite key to be BrandNum and site vendor. These columns are not in the Jda_sourcing Table. The Item is what is in common with both tables. I'm not sure if this is the way to go, so that is why I'm posting this question.
SELECT COUNT(*),
item,
source,
dest
FROM [dq].[jda_sourcing] AS js
WHERE [item] IN (SELECT [item]
FROM [dq].[jda_udt_item_local]
WHERE
--EXISTS
[u_brand] IN (SELECT TOP 100
itm.u_brand AS BrandNum
FROM (SELECT lnk.material,
loc.plant,
loc.storagelocation,
lnk.purchasingstatus,
lnk.sellingstatus,
lnk.sitevendor,
CASE WHEN ssc.material IS NOT NULL THEN 'SSC' ELSE 'DIRECT' END AS source
FROM scm.sap_marc AS lnk
JOIN (SELECT DISTINCT
loc AS plant,
RIGHT(location_id, 4) AS storagelocation,
loc_type
FROM dq.static_location
WHERE loc IS NOT NULL
AND loc_status = 'A'
AND loc_type = 'FWD') AS loc ON loc.plant = lnk.plant
LEFT JOIN scm.sap_mard AS ssc ON ssc.plant = loc.plant
AND ssc.material = lnk.material
AND ssc.sscvalidfrom <= CONVERT(varchar(8), GETDATE(), 112)
AND ssc.sscvalidto >= CONVERT(varchar(8), GETDATE(), 112)
LEFT JOIN scm.sap_zdt_mm_source AS src ON src.plant = lnk.plant
AND src.material = lnk.material
AND (lnk.sitevendor = src.vendor
OR ssc.material IS NOT NULL
OR LEN(src.vendor) = 4)
AND src.validfromdate <= CONVERT(varchar(8), GETDATE(), 112)
AND src.validtodate >= CONVERT(varchar(8), GETDATE(), 112)
WHERE lnk.purchasingstatus IN ('P', 'U', 'W')
AND src.material IS NULL) AS msrc
LEFT JOIN dq.jda_udt_item_local AS itm ON itm.item = msrc.material
LEFT JOIN scm.sap_mara AS ssn ON ssn.material = msrc.material
AND ssn.supercedeeffdate <= GETDATE()
LEFT JOIN (SELECT item,
dest,
tier1,
gs_source,
supplier,
otc_status,
u_inco_term
FROM [flmir-jdasqldv1].scdq.dq.jda_sku_sourcing AS SKUSRC
JOIN [flmir-jdasqldv1].scdq.dq.jda_udt_loc AS LOC ON LOC.loc = SKUSRC.tier1
WHERE tier1 <> ''
--and otc_status in ('AA','AH','IN','IF','IH')
) AS lss ON lss.item = msrc.material
AND lss.dest = CAST(msrc.plant AS varchar) + '-' + CAST(msrc.storagelocation AS varchar)
LEFT JOIN scm.ci_corpitem AS ci ON ci.item = msrc.material
--left join [dq].[jda_sourcing]js on js.[item]=msrc.material
--and js.[dest]= cast(msrc.plant as varchar) + '-' + cast(msrc.storagelocation as varchar)
LEFT JOIN temp.lg_sourcing_xref AS lgs ON lgs.item = msrc.material
AND lgs.plant = CAST(msrc.plant AS varchar)
AND lgs.sloc = CAST(msrc.storagelocation AS varchar)
WHERE ISNULL(itm.u_prod_class, 0) NOT IN (40, 85, 86, 94, 96, 99)
AND ISNULL(itm.u_brand, 0) <> '9999'))
AND LEN(source) = 13
GROUP BY item,
source,
dest
UNION ALL
SELECT COUNT(*),
item,
source,
dest
FROM [dq].[jda_sourcing] AS js
WHERE [item] IN (SELECT [material]
FROM [scm].[sap_marc]
WHERE
--EXISTS
[sitevendor] IN (SELECT TOP 100
msrc.sitevendor AS SiteVendor
FROM (SELECT lnk.material,
loc.plant,
loc.storagelocation,
lnk.purchasingstatus,
lnk.sellingstatus,
lnk.sitevendor,
CASE WHEN ssc.material IS NOT NULL THEN 'SSC' ELSE 'DIRECT' END AS source
FROM scm.sap_marc AS lnk
JOIN (SELECT DISTINCT
loc AS plant,
RIGHT(location_id, 4) AS storagelocation,
loc_type
FROM dq.static_location
WHERE loc IS NOT NULL
AND loc_status = 'A'
AND loc_type = 'FWD') AS loc ON loc.plant = lnk.plant
LEFT JOIN scm.sap_mard AS ssc ON ssc.plant = loc.plant
AND ssc.material = lnk.material
AND ssc.sscvalidfrom <= CONVERT(varchar(8), GETDATE(), 112)
AND ssc.sscvalidto >= CONVERT(varchar(8), GETDATE(), 112)
LEFT JOIN scm.sap_zdt_mm_source AS src ON src.plant = lnk.plant
AND src.material = lnk.material
AND (lnk.sitevendor = src.vendor
OR ssc.material IS NOT NULL
OR LEN(src.vendor) = 4)
AND src.validfromdate <= CONVERT(varchar(8), GETDATE(), 112)
AND src.validtodate >= CONVERT(varchar(8), GETDATE(), 112)
WHERE lnk.purchasingstatus IN ('P', 'U', 'W')
AND src.material IS NULL) AS msrc
LEFT JOIN dq.jda_udt_item_local AS itm ON itm.item = msrc.material
LEFT JOIN scm.sap_mara AS ssn ON ssn.material = msrc.material
AND ssn.supercedeeffdate <= GETDATE()
LEFT JOIN (SELECT item,
dest,
tier1,
gs_source,
supplier,
otc_status,
u_inco_term
FROM [flmir-jdasqldv1].scdq.dq.jda_sku_sourcing AS SKUSRC
JOIN [flmir-jdasqldv1].scdq.dq.jda_udt_loc AS LOC ON LOC.loc = SKUSRC.tier1
WHERE tier1 <> ''
--and otc_status in ('AA','AH','IN','IF','IH')
) AS lss ON lss.item = msrc.material
AND lss.dest = CAST(msrc.plant AS varchar) + '-' + CAST(msrc.storagelocation AS varchar)
LEFT JOIN scm.ci_corpitem AS ci ON ci.item = msrc.material
--left join [dq].[jda_sourcing]js on js.[item]=msrc.material
--and js.[dest]= cast(msrc.plant as varchar) + '-' + cast(msrc.storagelocation as varchar)
LEFT JOIN temp.lg_sourcing_xref AS lgs ON lgs.item = msrc.material
AND lgs.plant = CAST(msrc.plant AS varchar)
AND lgs.sloc = CAST(msrc.storagelocation AS varchar)
WHERE ISNULL(itm.u_prod_class, 0) NOT IN (40, 85, 86, 94, 96, 99)
AND ISNULL(itm.u_brand, 0) <> '9999'))
AND LEN(source) = 13
GROUP BY item,
source,
dest;
edited Mar 25 at 16:01
Larnu
27.9k6 gold badges20 silver badges33 bronze badges
asked Mar 25 at 15:35
RFCRFC
134 bronze badges
I have a table that has two fields that are a unique key, but the keys in this table I don't need because I need two other fields that are not included in this table (Jda_sourcing). I created a query that I think would give me what I need, but I'm not sure. I ran and there are no errors, but wondering if this is the way go.
This is a long query, so I won't include everything. I want the composite key to be BrandNum and site vendor. These columns are not in the Jda_sourcing Table. The Item is what is in common with both tables. I'm not sure if this is the way to go, so that is why I'm posting this question.
SELECT COUNT(*),
item,
source,
dest
FROM [dq].[jda_sourcing] AS js
WHERE [item] IN (SELECT [item]
FROM [dq].[jda_udt_item_local]
WHERE
--EXISTS
[u_brand] IN (SELECT TOP 100
itm.u_brand AS BrandNum
FROM (SELECT lnk.material,
loc.plant,
loc.storagelocation,
lnk.purchasingstatus,
lnk.sellingstatus,
lnk.sitevendor,
CASE WHEN ssc.material IS NOT NULL THEN 'SSC' ELSE 'DIRECT' END AS source
FROM scm.sap_marc AS lnk
JOIN (SELECT DISTINCT
loc AS plant,
RIGHT(location_id, 4) AS storagelocation,
loc_type
FROM dq.static_location
WHERE loc IS NOT NULL
AND loc_status = 'A'
AND loc_type = 'FWD') AS loc ON loc.plant = lnk.plant
LEFT JOIN scm.sap_mard AS ssc ON ssc.plant = loc.plant
AND ssc.material = lnk.material
AND ssc.sscvalidfrom <= CONVERT(varchar(8), GETDATE(), 112)
AND ssc.sscvalidto >= CONVERT(varchar(8), GETDATE(), 112)
LEFT JOIN scm.sap_zdt_mm_source AS src ON src.plant = lnk.plant
AND src.material = lnk.material
AND (lnk.sitevendor = src.vendor
OR ssc.material IS NOT NULL
OR LEN(src.vendor) = 4)
AND src.validfromdate <= CONVERT(varchar(8), GETDATE(), 112)
AND src.validtodate >= CONVERT(varchar(8), GETDATE(), 112)
WHERE lnk.purchasingstatus IN ('P', 'U', 'W')
AND src.material IS NULL) AS msrc
LEFT JOIN dq.jda_udt_item_local AS itm ON itm.item = msrc.material
LEFT JOIN scm.sap_mara AS ssn ON ssn.material = msrc.material
AND ssn.supercedeeffdate <= GETDATE()
LEFT JOIN (SELECT item,
dest,
tier1,
gs_source,
supplier,
otc_status,
u_inco_term
FROM [flmir-jdasqldv1].scdq.dq.jda_sku_sourcing AS SKUSRC
JOIN [flmir-jdasqldv1].scdq.dq.jda_udt_loc AS LOC ON LOC.loc = SKUSRC.tier1
WHERE tier1 <> ''
--and otc_status in ('AA','AH','IN','IF','IH')
) AS lss ON lss.item = msrc.material
AND lss.dest = CAST(msrc.plant AS varchar) + '-' + CAST(msrc.storagelocation AS varchar)
LEFT JOIN scm.ci_corpitem AS ci ON ci.item = msrc.material
--left join [dq].[jda_sourcing]js on js.[item]=msrc.material
--and js.[dest]= cast(msrc.plant as varchar) + '-' + cast(msrc.storagelocation as varchar)
LEFT JOIN temp.lg_sourcing_xref AS lgs ON lgs.item = msrc.material
AND lgs.plant = CAST(msrc.plant AS varchar)
AND lgs.sloc = CAST(msrc.storagelocation AS varchar)
WHERE ISNULL(itm.u_prod_class, 0) NOT IN (40, 85, 86, 94, 96, 99)
AND ISNULL(itm.u_brand, 0) <> '9999'))
AND LEN(source) = 13
GROUP BY item,
source,
dest
UNION ALL
SELECT COUNT(*),
item,
source,
dest
FROM [dq].[jda_sourcing] AS js
WHERE [item] IN (SELECT [material]
FROM [scm].[sap_marc]
WHERE
--EXISTS
[sitevendor] IN (SELECT TOP 100
msrc.sitevendor AS SiteVendor
FROM (SELECT lnk.material,
loc.plant,
loc.storagelocation,
lnk.purchasingstatus,
lnk.sellingstatus,
lnk.sitevendor,
CASE WHEN ssc.material IS NOT NULL THEN 'SSC' ELSE 'DIRECT' END AS source
FROM scm.sap_marc AS lnk
JOIN (SELECT DISTINCT
loc AS plant,
RIGHT(location_id, 4) AS storagelocation,
loc_type
FROM dq.static_location
WHERE loc IS NOT NULL
AND loc_status = 'A'
AND loc_type = 'FWD') AS loc ON loc.plant = lnk.plant
LEFT JOIN scm.sap_mard AS ssc ON ssc.plant = loc.plant
AND ssc.material = lnk.material
AND ssc.sscvalidfrom <= CONVERT(varchar(8), GETDATE(), 112)
AND ssc.sscvalidto >= CONVERT(varchar(8), GETDATE(), 112)
LEFT JOIN scm.sap_zdt_mm_source AS src ON src.plant = lnk.plant
AND src.material = lnk.material
AND (lnk.sitevendor = src.vendor
OR ssc.material IS NOT NULL
OR LEN(src.vendor) = 4)
AND src.validfromdate <= CONVERT(varchar(8), GETDATE(), 112)
AND src.validtodate >= CONVERT(varchar(8), GETDATE(), 112)
WHERE lnk.purchasingstatus IN ('P', 'U', 'W')
AND src.material IS NULL) AS msrc
LEFT JOIN dq.jda_udt_item_local AS itm ON itm.item = msrc.material
LEFT JOIN scm.sap_mara AS ssn ON ssn.material = msrc.material
AND ssn.supercedeeffdate <= GETDATE()
LEFT JOIN (SELECT item,
dest,
tier1,
gs_source,
supplier,
otc_status,
u_inco_term
FROM [flmir-jdasqldv1].scdq.dq.jda_sku_sourcing AS SKUSRC
JOIN [flmir-jdasqldv1].scdq.dq.jda_udt_loc AS LOC ON LOC.loc = SKUSRC.tier1
WHERE tier1 <> ''
--and otc_status in ('AA','AH','IN','IF','IH')
) AS lss ON lss.item = msrc.material
AND lss.dest = CAST(msrc.plant AS varchar) + '-' + CAST(msrc.storagelocation AS varchar)
LEFT JOIN scm.ci_corpitem AS ci ON ci.item = msrc.material
--left join [dq].[jda_sourcing]js on js.[item]=msrc.material
--and js.[dest]= cast(msrc.plant as varchar) + '-' + cast(msrc.storagelocation as varchar)
LEFT JOIN temp.lg_sourcing_xref AS lgs ON lgs.item = msrc.material
AND lgs.plant = CAST(msrc.plant AS varchar)
AND lgs.sloc = CAST(msrc.storagelocation AS varchar)
WHERE ISNULL(itm.u_prod_class, 0) NOT IN (40, 85, 86, 94, 96, 99)
AND ISNULL(itm.u_brand, 0) <> '9999'))
AND LEN(source) = 13
GROUP BY item,
source,
dest;
edited Mar 25 at 16:01
Larnu
27.9k6 gold badges20 silver badges33 bronze badges
asked Mar 25 at 15:35
RFCRFC
134 bronze badges
edited Mar 25 at 16:01
Larnu
27.9k6 gold badges20 silver badges33 bronze badges
asked Mar 25 at 15:35
RFCRFC
134 bronze badges
edited Mar 25 at 16:01
Larnu
27.9k6 gold badges20 silver badges33 bronze badges
edited Mar 25 at 16:01
Larnu
27.9k6 gold badges20 silver badges33 bronze badges
edited Mar 25 at 16:01
Larnu
27.9k6 gold badges20 silver badges33 bronze badges
27.9k6 gold badges20 silver badges33 bronze badges
asked Mar 25 at 15:35
RFCRFC
134 bronze badges
asked Mar 25 at 15:35
RFCRFC
134 bronze badges
asked Mar 25 at 15:35
RFCRFC
134 bronze badges
134 bronze badges
1
That query is incomplete. You haveFROM (SELECT )Where's the rest of that statement (and the alias on the subquery)? Then you have anANDbut no priorWHERE.
– Larnu
Mar 25 at 15:39
That's not really a lot to go on, without knowing the structures of all those objects in your query.
– Richard Hansell
Mar 25 at 15:40
1
But what is the question? You posted a partial bit of sql but you don't actually ask anything other than a vague explanation of the requirements.
– Sean Lange
Mar 25 at 15:40
There is also 3 sets of parenthesis that aren't closed. That many nested subqueries though are going to be likely a performance killer.
– Larnu
Mar 25 at 15:40
i will post the whole query. I'll edit it.
– RFC
Mar 25 at 15:44
|
show 4 more comments
1
That query is incomplete. You haveFROM (SELECT )Where's the rest of that statement (and the alias on the subquery)? Then you have anANDbut no priorWHERE.
– Larnu
Mar 25 at 15:39
That's not really a lot to go on, without knowing the structures of all those objects in your query.
– Richard Hansell
Mar 25 at 15:40
1
But what is the question? You posted a partial bit of sql but you don't actually ask anything other than a vague explanation of the requirements.
– Sean Lange
Mar 25 at 15:40
There is also 3 sets of parenthesis that aren't closed. That many nested subqueries though are going to be likely a performance killer.
– Larnu
Mar 25 at 15:40
i will post the whole query. I'll edit it.
– RFC
Mar 25 at 15:44
1
1
That query is incomplete. You have
FROM (SELECT ) Where's the rest of that statement (and the alias on the subquery)? Then you have an AND but no prior WHERE.– Larnu
Mar 25 at 15:39
That query is incomplete. You have
FROM (SELECT ) Where's the rest of that statement (and the alias on the subquery)? Then you have an AND but no prior WHERE.– Larnu
Mar 25 at 15:39
That's not really a lot to go on, without knowing the structures of all those objects in your query.
– Richard Hansell
Mar 25 at 15:40
That's not really a lot to go on, without knowing the structures of all those objects in your query.
– Richard Hansell
Mar 25 at 15:40
1
1
But what is the question? You posted a partial bit of sql but you don't actually ask anything other than a vague explanation of the requirements.
– Sean Lange
Mar 25 at 15:40
But what is the question? You posted a partial bit of sql but you don't actually ask anything other than a vague explanation of the requirements.
– Sean Lange
Mar 25 at 15:40
There is also 3 sets of parenthesis that aren't closed. That many nested subqueries though are going to be likely a performance killer.
– Larnu
Mar 25 at 15:40
There is also 3 sets of parenthesis that aren't closed. That many nested subqueries though are going to be likely a performance killer.
– Larnu
Mar 25 at 15:40
i will post the whole query. I'll edit it.
– RFC
Mar 25 at 15:44
i will post the whole query. I'll edit it.
– RFC
Mar 25 at 15:44
|
show 4 more comments
1 Answer
1
active
oldest
votes
0
With a little refactoring your code can be clearer -- like this:
Maybe this will also help to make your question clearer since you can reference the sub-queries by name?
WITH lss AS
(
SELECT item,
dest,
tier1,
gs_source,
supplier,
otc_status,
u_inco_term
FROM [flmir-jdasqldv1].scdq.dq.jda_sku_sourcing AS SKUSRC
JOIN [flmir-jdasqldv1].scdq.dq.jda_udt_loc AS LOC ON LOC.loc = SKUSRC.tier1
WHERE tier1 <> ''
), loc as
(
SELECT DISTINCT
loc AS plant,
RIGHT(location_id, 4) AS storagelocation,
loc_type
FROM dq.static_location
WHERE loc IS NOT NULL AND loc_status = 'A' AND loc_type = 'FWD'
), msrc AS
(
SELECT lnk.material,
loc.plant,
loc.storagelocation,
lnk.purchasingstatus,
lnk.sellingstatus,
lnk.sitevendor,
CASE WHEN ssc.material IS NOT NULL THEN 'SSC' ELSE 'DIRECT' END AS source
FROM scm.sap_marc AS lnk
JOIN loc ON loc.plant = lnk.plant
LEFT JOIN scm.sap_mard AS ssc ON ssc.plant = loc.plant
AND ssc.material = lnk.material
AND ssc.sscvalidfrom <= CONVERT(varchar(8), GETDATE(), 112)
AND ssc.sscvalidto >= CONVERT(varchar(8), GETDATE(), 112)
LEFT JOIN scm.sap_zdt_mm_source AS src ON src.plant = lnk.plant
AND src.material = lnk.material
AND (lnk.sitevendor = src.vendor
OR ssc.material IS NOT NULL
OR LEN(src.vendor) = 4)
AND src.validfromdate <= CONVERT(varchar(8), GETDATE(), 112)
AND src.validtodate >= CONVERT(varchar(8), GETDATE(), 112)
WHERE lnk.purchasingstatus IN ('P', 'U', 'W') AND src.material IS NULL
), t100brand AS
(
SELECT TOP 100
itm.u_brand AS BrandNum
FROM msrc
LEFT JOIN dq.jda_udt_item_local AS itm ON itm.item = msrc.material
LEFT JOIN scm.sap_mara AS ssn ON ssn.material = msrc.material
AND ssn.supercedeeffdate <= GETDATE()
LEFT JOIN lss ON lss.item = msrc.material AND lss.dest = CAST(msrc.plant AS varchar) + '-' + CAST(msrc.storagelocation AS varchar)
LEFT JOIN scm.ci_corpitem AS ci ON ci.item = msrc.material
--left join [dq].[jda_sourcing]js on js.[item]=msrc.material
--and js.[dest]= cast(msrc.plant as varchar) + '-' + cast(msrc.storagelocation as varchar)
LEFT JOIN temp.lg_sourcing_xref AS lgs ON lgs.item = msrc.material
AND lgs.plant = CAST(msrc.plant AS varchar)
AND lgs.sloc = CAST(msrc.storagelocation AS varchar)
WHERE ISNULL(itm.u_prod_class, 0) NOT IN (40, 85, 86, 94, 96, 99)
AND ISNULL(itm.u_brand, 0) <> '9999')
), t100branditem AS
(
SELECT [item]
FROM [dq].[jda_udt_item_local]
JOIN t100brand t100 on u_brand = t100.u_brand)
), t100vender AS
(
SELECT TOP 100
msrc.sitevendor AS SiteVendor
FROM msrc
LEFT JOIN dq.jda_udt_item_local AS itm ON itm.item = msrc.material
LEFT JOIN scm.sap_mara AS ssn ON ssn.material = msrc.material
AND ssn.supercedeeffdate <= GETDATE()
LEFT JOIN lss ON lss.item = msrc.material
AND lss.dest = CAST(msrc.plant AS varchar) + '-' + CAST(msrc.storagelocation AS varchar)
LEFT JOIN scm.ci_corpitem AS ci ON ci.item = msrc.material
--left join [dq].[jda_sourcing]js on js.[item]=msrc.material
--and js.[dest]= cast(msrc.plant as varchar) + '-' + cast(msrc.storagelocation as varchar)
LEFT JOIN temp.lg_sourcing_xref AS lgs ON lgs.item = msrc.material
AND lgs.plant = CAST(msrc.plant AS varchar)
AND lgs.sloc = CAST(msrc.storagelocation AS varchar)
WHERE ISNULL(itm.u_prod_class, 0) NOT IN (40, 85, 86, 94, 96, 99)
AND ISNULL(itm.u_brand, 0) <> '9999')
), t100materialvendor as
(
SELECT [material]
FROM [scm].[sap_marc]
join t100vender v on sitevendor = v.sitevendor
where LEN(source) = 13
)
SELECT COUNT(*),
item,
source,
dest
FROM [dq].[jda_sourcing] AS js
join t100branditem bi ON bi.item = js.item
where LEN(source) = 13
GROUP BY item, source, dest
UNION ALL
SELECT COUNT(*),
item,
source,
dest
FROM [dq].[jda_sourcing] AS js
join t100materialvendor mv ON mv.material = js.item
GROUP BY item, source, dest;
answered Mar 25 at 16:25
HoganHogan
56.4k10 gold badges68 silver badges104 bronze badges
thank you. I'm reviewing it and I get an error because of invalid column name u_brand. I think it because none of the tables inside msrc has u-brand as a column. I checked all 3 tables, scm.sap_marc, scm.sap_mard and scm.sap_zdt_mm_source.
– RFC
Mar 25 at 18:26
@RFC what table was it coming from in your original script?
– Hogan
Mar 26 at 15:58
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55341370%2ftrying-to-create-a-different-unique-key-to-query-on%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
With a little refactoring your code can be clearer -- like this:
Maybe this will also help to make your question clearer since you can reference the sub-queries by name?
WITH lss AS
(
SELECT item,
dest,
tier1,
gs_source,
supplier,
otc_status,
u_inco_term
FROM [flmir-jdasqldv1].scdq.dq.jda_sku_sourcing AS SKUSRC
JOIN [flmir-jdasqldv1].scdq.dq.jda_udt_loc AS LOC ON LOC.loc = SKUSRC.tier1
WHERE tier1 <> ''
), loc as
(
SELECT DISTINCT
loc AS plant,
RIGHT(location_id, 4) AS storagelocation,
loc_type
FROM dq.static_location
WHERE loc IS NOT NULL AND loc_status = 'A' AND loc_type = 'FWD'
), msrc AS
(
SELECT lnk.material,
loc.plant,
loc.storagelocation,
lnk.purchasingstatus,
lnk.sellingstatus,
lnk.sitevendor,
CASE WHEN ssc.material IS NOT NULL THEN 'SSC' ELSE 'DIRECT' END AS source
FROM scm.sap_marc AS lnk
JOIN loc ON loc.plant = lnk.plant
LEFT JOIN scm.sap_mard AS ssc ON ssc.plant = loc.plant
AND ssc.material = lnk.material
AND ssc.sscvalidfrom <= CONVERT(varchar(8), GETDATE(), 112)
AND ssc.sscvalidto >= CONVERT(varchar(8), GETDATE(), 112)
LEFT JOIN scm.sap_zdt_mm_source AS src ON src.plant = lnk.plant
AND src.material = lnk.material
AND (lnk.sitevendor = src.vendor
OR ssc.material IS NOT NULL
OR LEN(src.vendor) = 4)
AND src.validfromdate <= CONVERT(varchar(8), GETDATE(), 112)
AND src.validtodate >= CONVERT(varchar(8), GETDATE(), 112)
WHERE lnk.purchasingstatus IN ('P', 'U', 'W') AND src.material IS NULL
), t100brand AS
(
SELECT TOP 100
itm.u_brand AS BrandNum
FROM msrc
LEFT JOIN dq.jda_udt_item_local AS itm ON itm.item = msrc.material
LEFT JOIN scm.sap_mara AS ssn ON ssn.material = msrc.material
AND ssn.supercedeeffdate <= GETDATE()
LEFT JOIN lss ON lss.item = msrc.material AND lss.dest = CAST(msrc.plant AS varchar) + '-' + CAST(msrc.storagelocation AS varchar)
LEFT JOIN scm.ci_corpitem AS ci ON ci.item = msrc.material
--left join [dq].[jda_sourcing]js on js.[item]=msrc.material
--and js.[dest]= cast(msrc.plant as varchar) + '-' + cast(msrc.storagelocation as varchar)
LEFT JOIN temp.lg_sourcing_xref AS lgs ON lgs.item = msrc.material
AND lgs.plant = CAST(msrc.plant AS varchar)
AND lgs.sloc = CAST(msrc.storagelocation AS varchar)
WHERE ISNULL(itm.u_prod_class, 0) NOT IN (40, 85, 86, 94, 96, 99)
AND ISNULL(itm.u_brand, 0) <> '9999')
), t100branditem AS
(
SELECT [item]
FROM [dq].[jda_udt_item_local]
JOIN t100brand t100 on u_brand = t100.u_brand)
), t100vender AS
(
SELECT TOP 100
msrc.sitevendor AS SiteVendor
FROM msrc
LEFT JOIN dq.jda_udt_item_local AS itm ON itm.item = msrc.material
LEFT JOIN scm.sap_mara AS ssn ON ssn.material = msrc.material
AND ssn.supercedeeffdate <= GETDATE()
LEFT JOIN lss ON lss.item = msrc.material
AND lss.dest = CAST(msrc.plant AS varchar) + '-' + CAST(msrc.storagelocation AS varchar)
LEFT JOIN scm.ci_corpitem AS ci ON ci.item = msrc.material
--left join [dq].[jda_sourcing]js on js.[item]=msrc.material
--and js.[dest]= cast(msrc.plant as varchar) + '-' + cast(msrc.storagelocation as varchar)
LEFT JOIN temp.lg_sourcing_xref AS lgs ON lgs.item = msrc.material
AND lgs.plant = CAST(msrc.plant AS varchar)
AND lgs.sloc = CAST(msrc.storagelocation AS varchar)
WHERE ISNULL(itm.u_prod_class, 0) NOT IN (40, 85, 86, 94, 96, 99)
AND ISNULL(itm.u_brand, 0) <> '9999')
), t100materialvendor as
(
SELECT [material]
FROM [scm].[sap_marc]
join t100vender v on sitevendor = v.sitevendor
where LEN(source) = 13
)
SELECT COUNT(*),
item,
source,
dest
FROM [dq].[jda_sourcing] AS js
join t100branditem bi ON bi.item = js.item
where LEN(source) = 13
GROUP BY item, source, dest
UNION ALL
SELECT COUNT(*),
item,
source,
dest
FROM [dq].[jda_sourcing] AS js
join t100materialvendor mv ON mv.material = js.item
GROUP BY item, source, dest;
answered Mar 25 at 16:25
HoganHogan
56.4k10 gold badges68 silver badges104 bronze badges
thank you. I'm reviewing it and I get an error because of invalid column name u_brand. I think it because none of the tables inside msrc has u-brand as a column. I checked all 3 tables, scm.sap_marc, scm.sap_mard and scm.sap_zdt_mm_source.
– RFC
Mar 25 at 18:26
@RFC what table was it coming from in your original script?
– Hogan
Mar 26 at 15:58
add a comment |
0
With a little refactoring your code can be clearer -- like this:
Maybe this will also help to make your question clearer since you can reference the sub-queries by name?
WITH lss AS
(
SELECT item,
dest,
tier1,
gs_source,
supplier,
otc_status,
u_inco_term
FROM [flmir-jdasqldv1].scdq.dq.jda_sku_sourcing AS SKUSRC
JOIN [flmir-jdasqldv1].scdq.dq.jda_udt_loc AS LOC ON LOC.loc = SKUSRC.tier1
WHERE tier1 <> ''
), loc as
(
SELECT DISTINCT
loc AS plant,
RIGHT(location_id, 4) AS storagelocation,
loc_type
FROM dq.static_location
WHERE loc IS NOT NULL AND loc_status = 'A' AND loc_type = 'FWD'
), msrc AS
(
SELECT lnk.material,
loc.plant,
loc.storagelocation,
lnk.purchasingstatus,
lnk.sellingstatus,
lnk.sitevendor,
CASE WHEN ssc.material IS NOT NULL THEN 'SSC' ELSE 'DIRECT' END AS source
FROM scm.sap_marc AS lnk
JOIN loc ON loc.plant = lnk.plant
LEFT JOIN scm.sap_mard AS ssc ON ssc.plant = loc.plant
AND ssc.material = lnk.material
AND ssc.sscvalidfrom <= CONVERT(varchar(8), GETDATE(), 112)
AND ssc.sscvalidto >= CONVERT(varchar(8), GETDATE(), 112)
LEFT JOIN scm.sap_zdt_mm_source AS src ON src.plant = lnk.plant
AND src.material = lnk.material
AND (lnk.sitevendor = src.vendor
OR ssc.material IS NOT NULL
OR LEN(src.vendor) = 4)
AND src.validfromdate <= CONVERT(varchar(8), GETDATE(), 112)
AND src.validtodate >= CONVERT(varchar(8), GETDATE(), 112)
WHERE lnk.purchasingstatus IN ('P', 'U', 'W') AND src.material IS NULL
), t100brand AS
(
SELECT TOP 100
itm.u_brand AS BrandNum
FROM msrc
LEFT JOIN dq.jda_udt_item_local AS itm ON itm.item = msrc.material
LEFT JOIN scm.sap_mara AS ssn ON ssn.material = msrc.material
AND ssn.supercedeeffdate <= GETDATE()
LEFT JOIN lss ON lss.item = msrc.material AND lss.dest = CAST(msrc.plant AS varchar) + '-' + CAST(msrc.storagelocation AS varchar)
LEFT JOIN scm.ci_corpitem AS ci ON ci.item = msrc.material
--left join [dq].[jda_sourcing]js on js.[item]=msrc.material
--and js.[dest]= cast(msrc.plant as varchar) + '-' + cast(msrc.storagelocation as varchar)
LEFT JOIN temp.lg_sourcing_xref AS lgs ON lgs.item = msrc.material
AND lgs.plant = CAST(msrc.plant AS varchar)
AND lgs.sloc = CAST(msrc.storagelocation AS varchar)
WHERE ISNULL(itm.u_prod_class, 0) NOT IN (40, 85, 86, 94, 96, 99)
AND ISNULL(itm.u_brand, 0) <> '9999')
), t100branditem AS
(
SELECT [item]
FROM [dq].[jda_udt_item_local]
JOIN t100brand t100 on u_brand = t100.u_brand)
), t100vender AS
(
SELECT TOP 100
msrc.sitevendor AS SiteVendor
FROM msrc
LEFT JOIN dq.jda_udt_item_local AS itm ON itm.item = msrc.material
LEFT JOIN scm.sap_mara AS ssn ON ssn.material = msrc.material
AND ssn.supercedeeffdate <= GETDATE()
LEFT JOIN lss ON lss.item = msrc.material
AND lss.dest = CAST(msrc.plant AS varchar) + '-' + CAST(msrc.storagelocation AS varchar)
LEFT JOIN scm.ci_corpitem AS ci ON ci.item = msrc.material
--left join [dq].[jda_sourcing]js on js.[item]=msrc.material
--and js.[dest]= cast(msrc.plant as varchar) + '-' + cast(msrc.storagelocation as varchar)
LEFT JOIN temp.lg_sourcing_xref AS lgs ON lgs.item = msrc.material
AND lgs.plant = CAST(msrc.plant AS varchar)
AND lgs.sloc = CAST(msrc.storagelocation AS varchar)
WHERE ISNULL(itm.u_prod_class, 0) NOT IN (40, 85, 86, 94, 96, 99)
AND ISNULL(itm.u_brand, 0) <> '9999')
), t100materialvendor as
(
SELECT [material]
FROM [scm].[sap_marc]
join t100vender v on sitevendor = v.sitevendor
where LEN(source) = 13
)
SELECT COUNT(*),
item,
source,
dest
FROM [dq].[jda_sourcing] AS js
join t100branditem bi ON bi.item = js.item
where LEN(source) = 13
GROUP BY item, source, dest
UNION ALL
SELECT COUNT(*),
item,
source,
dest
FROM [dq].[jda_sourcing] AS js
join t100materialvendor mv ON mv.material = js.item
GROUP BY item, source, dest;
answered Mar 25 at 16:25
HoganHogan
56.4k10 gold badges68 silver badges104 bronze badges
thank you. I'm reviewing it and I get an error because of invalid column name u_brand. I think it because none of the tables inside msrc has u-brand as a column. I checked all 3 tables, scm.sap_marc, scm.sap_mard and scm.sap_zdt_mm_source.
– RFC
Mar 25 at 18:26
@RFC what table was it coming from in your original script?
– Hogan
Mar 26 at 15:58
add a comment |
0
0
0
With a little refactoring your code can be clearer -- like this:
Maybe this will also help to make your question clearer since you can reference the sub-queries by name?
WITH lss AS
(
SELECT item,
dest,
tier1,
gs_source,
supplier,
otc_status,
u_inco_term
FROM [flmir-jdasqldv1].scdq.dq.jda_sku_sourcing AS SKUSRC
JOIN [flmir-jdasqldv1].scdq.dq.jda_udt_loc AS LOC ON LOC.loc = SKUSRC.tier1
WHERE tier1 <> ''
), loc as
(
SELECT DISTINCT
loc AS plant,
RIGHT(location_id, 4) AS storagelocation,
loc_type
FROM dq.static_location
WHERE loc IS NOT NULL AND loc_status = 'A' AND loc_type = 'FWD'
), msrc AS
(
SELECT lnk.material,
loc.plant,
loc.storagelocation,
lnk.purchasingstatus,
lnk.sellingstatus,
lnk.sitevendor,
CASE WHEN ssc.material IS NOT NULL THEN 'SSC' ELSE 'DIRECT' END AS source
FROM scm.sap_marc AS lnk
JOIN loc ON loc.plant = lnk.plant
LEFT JOIN scm.sap_mard AS ssc ON ssc.plant = loc.plant
AND ssc.material = lnk.material
AND ssc.sscvalidfrom <= CONVERT(varchar(8), GETDATE(), 112)
AND ssc.sscvalidto >= CONVERT(varchar(8), GETDATE(), 112)
LEFT JOIN scm.sap_zdt_mm_source AS src ON src.plant = lnk.plant
AND src.material = lnk.material
AND (lnk.sitevendor = src.vendor
OR ssc.material IS NOT NULL
OR LEN(src.vendor) = 4)
AND src.validfromdate <= CONVERT(varchar(8), GETDATE(), 112)
AND src.validtodate >= CONVERT(varchar(8), GETDATE(), 112)
WHERE lnk.purchasingstatus IN ('P', 'U', 'W') AND src.material IS NULL
), t100brand AS
(
SELECT TOP 100
itm.u_brand AS BrandNum
FROM msrc
LEFT JOIN dq.jda_udt_item_local AS itm ON itm.item = msrc.material
LEFT JOIN scm.sap_mara AS ssn ON ssn.material = msrc.material
AND ssn.supercedeeffdate <= GETDATE()
LEFT JOIN lss ON lss.item = msrc.material AND lss.dest = CAST(msrc.plant AS varchar) + '-' + CAST(msrc.storagelocation AS varchar)
LEFT JOIN scm.ci_corpitem AS ci ON ci.item = msrc.material
--left join [dq].[jda_sourcing]js on js.[item]=msrc.material
--and js.[dest]= cast(msrc.plant as varchar) + '-' + cast(msrc.storagelocation as varchar)
LEFT JOIN temp.lg_sourcing_xref AS lgs ON lgs.item = msrc.material
AND lgs.plant = CAST(msrc.plant AS varchar)
AND lgs.sloc = CAST(msrc.storagelocation AS varchar)
WHERE ISNULL(itm.u_prod_class, 0) NOT IN (40, 85, 86, 94, 96, 99)
AND ISNULL(itm.u_brand, 0) <> '9999')
), t100branditem AS
(
SELECT [item]
FROM [dq].[jda_udt_item_local]
JOIN t100brand t100 on u_brand = t100.u_brand)
), t100vender AS
(
SELECT TOP 100
msrc.sitevendor AS SiteVendor
FROM msrc
LEFT JOIN dq.jda_udt_item_local AS itm ON itm.item = msrc.material
LEFT JOIN scm.sap_mara AS ssn ON ssn.material = msrc.material
AND ssn.supercedeeffdate <= GETDATE()
LEFT JOIN lss ON lss.item = msrc.material
AND lss.dest = CAST(msrc.plant AS varchar) + '-' + CAST(msrc.storagelocation AS varchar)
LEFT JOIN scm.ci_corpitem AS ci ON ci.item = msrc.material
--left join [dq].[jda_sourcing]js on js.[item]=msrc.material
--and js.[dest]= cast(msrc.plant as varchar) + '-' + cast(msrc.storagelocation as varchar)
LEFT JOIN temp.lg_sourcing_xref AS lgs ON lgs.item = msrc.material
AND lgs.plant = CAST(msrc.plant AS varchar)
AND lgs.sloc = CAST(msrc.storagelocation AS varchar)
WHERE ISNULL(itm.u_prod_class, 0) NOT IN (40, 85, 86, 94, 96, 99)
AND ISNULL(itm.u_brand, 0) <> '9999')
), t100materialvendor as
(
SELECT [material]
FROM [scm].[sap_marc]
join t100vender v on sitevendor = v.sitevendor
where LEN(source) = 13
)
SELECT COUNT(*),
item,
source,
dest
FROM [dq].[jda_sourcing] AS js
join t100branditem bi ON bi.item = js.item
where LEN(source) = 13
GROUP BY item, source, dest
UNION ALL
SELECT COUNT(*),
item,
source,
dest
FROM [dq].[jda_sourcing] AS js
join t100materialvendor mv ON mv.material = js.item
GROUP BY item, source, dest;
answered Mar 25 at 16:25
HoganHogan
56.4k10 gold badges68 silver badges104 bronze badges
With a little refactoring your code can be clearer -- like this:
Maybe this will also help to make your question clearer since you can reference the sub-queries by name?
WITH lss AS
(
SELECT item,
dest,
tier1,
gs_source,
supplier,
otc_status,
u_inco_term
FROM [flmir-jdasqldv1].scdq.dq.jda_sku_sourcing AS SKUSRC
JOIN [flmir-jdasqldv1].scdq.dq.jda_udt_loc AS LOC ON LOC.loc = SKUSRC.tier1
WHERE tier1 <> ''
), loc as
(
SELECT DISTINCT
loc AS plant,
RIGHT(location_id, 4) AS storagelocation,
loc_type
FROM dq.static_location
WHERE loc IS NOT NULL AND loc_status = 'A' AND loc_type = 'FWD'
), msrc AS
(
SELECT lnk.material,
loc.plant,
loc.storagelocation,
lnk.purchasingstatus,
lnk.sellingstatus,
lnk.sitevendor,
CASE WHEN ssc.material IS NOT NULL THEN 'SSC' ELSE 'DIRECT' END AS source
FROM scm.sap_marc AS lnk
JOIN loc ON loc.plant = lnk.plant
LEFT JOIN scm.sap_mard AS ssc ON ssc.plant = loc.plant
AND ssc.material = lnk.material
AND ssc.sscvalidfrom <= CONVERT(varchar(8), GETDATE(), 112)
AND ssc.sscvalidto >= CONVERT(varchar(8), GETDATE(), 112)
LEFT JOIN scm.sap_zdt_mm_source AS src ON src.plant = lnk.plant
AND src.material = lnk.material
AND (lnk.sitevendor = src.vendor
OR ssc.material IS NOT NULL
OR LEN(src.vendor) = 4)
AND src.validfromdate <= CONVERT(varchar(8), GETDATE(), 112)
AND src.validtodate >= CONVERT(varchar(8), GETDATE(), 112)
WHERE lnk.purchasingstatus IN ('P', 'U', 'W') AND src.material IS NULL
), t100brand AS
(
SELECT TOP 100
itm.u_brand AS BrandNum
FROM msrc
LEFT JOIN dq.jda_udt_item_local AS itm ON itm.item = msrc.material
LEFT JOIN scm.sap_mara AS ssn ON ssn.material = msrc.material
AND ssn.supercedeeffdate <= GETDATE()
LEFT JOIN lss ON lss.item = msrc.material AND lss.dest = CAST(msrc.plant AS varchar) + '-' + CAST(msrc.storagelocation AS varchar)
LEFT JOIN scm.ci_corpitem AS ci ON ci.item = msrc.material
--left join [dq].[jda_sourcing]js on js.[item]=msrc.material
--and js.[dest]= cast(msrc.plant as varchar) + '-' + cast(msrc.storagelocation as varchar)
LEFT JOIN temp.lg_sourcing_xref AS lgs ON lgs.item = msrc.material
AND lgs.plant = CAST(msrc.plant AS varchar)
AND lgs.sloc = CAST(msrc.storagelocation AS varchar)
WHERE ISNULL(itm.u_prod_class, 0) NOT IN (40, 85, 86, 94, 96, 99)
AND ISNULL(itm.u_brand, 0) <> '9999')
), t100branditem AS
(
SELECT [item]
FROM [dq].[jda_udt_item_local]
JOIN t100brand t100 on u_brand = t100.u_brand)
), t100vender AS
(
SELECT TOP 100
msrc.sitevendor AS SiteVendor
FROM msrc
LEFT JOIN dq.jda_udt_item_local AS itm ON itm.item = msrc.material
LEFT JOIN scm.sap_mara AS ssn ON ssn.material = msrc.material
AND ssn.supercedeeffdate <= GETDATE()
LEFT JOIN lss ON lss.item = msrc.material
AND lss.dest = CAST(msrc.plant AS varchar) + '-' + CAST(msrc.storagelocation AS varchar)
LEFT JOIN scm.ci_corpitem AS ci ON ci.item = msrc.material
--left join [dq].[jda_sourcing]js on js.[item]=msrc.material
--and js.[dest]= cast(msrc.plant as varchar) + '-' + cast(msrc.storagelocation as varchar)
LEFT JOIN temp.lg_sourcing_xref AS lgs ON lgs.item = msrc.material
AND lgs.plant = CAST(msrc.plant AS varchar)
AND lgs.sloc = CAST(msrc.storagelocation AS varchar)
WHERE ISNULL(itm.u_prod_class, 0) NOT IN (40, 85, 86, 94, 96, 99)
AND ISNULL(itm.u_brand, 0) <> '9999')
), t100materialvendor as
(
SELECT [material]
FROM [scm].[sap_marc]
join t100vender v on sitevendor = v.sitevendor
where LEN(source) = 13
)
SELECT COUNT(*),
item,
source,
dest
FROM [dq].[jda_sourcing] AS js
join t100branditem bi ON bi.item = js.item
where LEN(source) = 13
GROUP BY item, source, dest
UNION ALL
SELECT COUNT(*),
item,
source,
dest
FROM [dq].[jda_sourcing] AS js
join t100materialvendor mv ON mv.material = js.item
GROUP BY item, source, dest;
answered Mar 25 at 16:25
HoganHogan
56.4k10 gold badges68 silver badges104 bronze badges
answered Mar 25 at 16:25
HoganHogan
56.4k10 gold badges68 silver badges104 bronze badges
answered Mar 25 at 16:25
HoganHogan
56.4k10 gold badges68 silver badges104 bronze badges
answered Mar 25 at 16:25
HoganHogan
56.4k10 gold badges68 silver badges104 bronze badges
56.4k10 gold badges68 silver badges104 bronze badges
thank you. I'm reviewing it and I get an error because of invalid column name u_brand. I think it because none of the tables inside msrc has u-brand as a column. I checked all 3 tables, scm.sap_marc, scm.sap_mard and scm.sap_zdt_mm_source.
– RFC
Mar 25 at 18:26
@RFC what table was it coming from in your original script?
– Hogan
Mar 26 at 15:58
add a comment |
thank you. I'm reviewing it and I get an error because of invalid column name u_brand. I think it because none of the tables inside msrc has u-brand as a column. I checked all 3 tables, scm.sap_marc, scm.sap_mard and scm.sap_zdt_mm_source.
– RFC
Mar 25 at 18:26
@RFC what table was it coming from in your original script?
– Hogan
Mar 26 at 15:58
thank you. I'm reviewing it and I get an error because of invalid column name u_brand. I think it because none of the tables inside msrc has u-brand as a column. I checked all 3 tables, scm.sap_marc, scm.sap_mard and scm.sap_zdt_mm_source.
– RFC
Mar 25 at 18:26
thank you. I'm reviewing it and I get an error because of invalid column name u_brand. I think it because none of the tables inside msrc has u-brand as a column. I checked all 3 tables, scm.sap_marc, scm.sap_mard and scm.sap_zdt_mm_source.
– RFC
Mar 25 at 18:26
@RFC what table was it coming from in your original script?
– Hogan
Mar 26 at 15:58
@RFC what table was it coming from in your original script?
– Hogan
Mar 26 at 15:58
add a comment |
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55341370%2ftrying-to-create-a-different-unique-key-to-query-on%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
That query is incomplete. You have
FROM (SELECT )Where's the rest of that statement (and the alias on the subquery)? Then you have anANDbut no priorWHERE.– Larnu
Mar 25 at 15:39
That's not really a lot to go on, without knowing the structures of all those objects in your query.
– Richard Hansell
Mar 25 at 15:40
1
But what is the question? You posted a partial bit of sql but you don't actually ask anything other than a vague explanation of the requirements.
– Sean Lange
Mar 25 at 15:40
There is also 3 sets of parenthesis that aren't closed. That many nested subqueries though are going to be likely a performance killer.
– Larnu
Mar 25 at 15:40
i will post the whole query. I'll edit it.
– RFC
Mar 25 at 15:44