Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Patyk
Yak Posting Veteran
74 Posts |
 Posted - 2015-04-30 : 14:53:20
|
I have the following procedure that I got help before. Now I just need to include all records where categ and type and flavor are not <> ' '
I am not sure which part of the code I should change. thanks
Begin
Insert into ProdType (StockCode, categ,flavor, type)
SELECT TOP (100) PERCENT StockCode, MAX(categ) AS categ, MAX(flavor) AS flavor, MAX(type) AS type
FROM
(SELECT RTRIM(KeyField) AS StockCode, RTRIM(AlphaValue) AS categ, RTRIM(AlphaValue) AS flavor, RTRIM(AlphaValue) AS type
FROM companyB.dbo.AdmFormData WHERE (0 = 1)
UNION ALL
SELECT RTRIM(KeyField) AS StockCode, RTRIM(AlphaValue) AS categ, NULL AS flavor, NULL AS type
FROM companyB.dbo.AdmFormData AS AdmFormData_3
WHERE (FieldName = 'categ')
UNION ALL
SELECT RTRIM(KeyField) AS StockCode, NULL AS categ, RTRIM(AlphaValue) AS flavor, NULL AS type
FROM companyB.dbo.AdmFormData AS AdmFormData_2
WHERE (FieldName = 'flavor')
UNION ALL
SELECT RTRIM(KeyField) AS StockCode, NULL AS categ, NULL AS flavor, RTRIM(AlphaValue) AS type
FROM companyB.dbo.AdmFormData AS AdmFormData_1
WHERE (FieldName = 'type')) AS a
GROUP BY StockCode
ORDER BY StockCode
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-04-30 : 14:55:45
|
SELECT StockCode, categ, flavor, type
FROM (your select query goes here) t
WHERE categ <> '' AND type <> '' AND flavor <> ''
Or you can add it to the individual SELECTs inside. You might need OR instead of AND, depending on what you want.
Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/ |
 |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2015-04-30 : 15:23:09
|
Shorter and faster:select rtrim(keyfield) as StockCode
,max(case when fieldname='categ' then alphavalue else null end) as categ
,max(case when fieldname='flavor' then alphavalue else null end) as flavor
,max(case when fieldname='type' then alphavalue else null end) as type
from companyB.dbo.AdmFormData
where fieldname in ('categ','flavor','type')
group by keyfield
having isnull(max(case when fieldname='categ' then alphavalue else null end),'')<>''
and isnull(max(case when fieldname='flavor' then alphavalue else null end),'')<>''
and isnull(max(case when fieldname='type' then alphavalue else null end),'')<>''
|
|
|
|
Patyk
Yak Posting Veteran
74 Posts |
Posted - 2015-04-30 : 16:07:23
|
Basically in my new Table I don't want to have any lines where categ or type or flavor are empty.
I have made this change but it's not helping.
(SELECT RTRIM(KeyField) AS StockCode, RTRIM(AlphaValue) AS categ, RTRIM(AlphaValue) AS flavor, RTRIM(AlphaValue) AS type
FROM companyB.dbo.AdmFormData WHERE (0 = 1) and ( AlphaValue) <> ' ') |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-04-30 : 16:24:23
|
How could it help?
WHERE (0 = 1)
is always false, so no rows would ever be selected. |
|
|
|
Patyk
Yak Posting Veteran
74 Posts |
Posted - 2015-04-30 : 19:33:31
|
create Procedure [dbo].[Insert_Prod_Type1]
as
truncate table BO_SKUBreakDownProdType
Begin
Insert into BO_SKUBreakDownProdType (StockCode, categ,flavor, type)
select rtrim(keyfield) as StockCode
,max(case when fieldname='categ' then alphavalue else null end) as categ
,max(case when fieldname='flavor' then alphavalue else null end) as flavor
,max(case when fieldname='type' then alphavalue else null end) as type
from companyB.dbo.AdmFormData
where fieldname in ('categ','flavor','type')
group by keyfield
having isnull(max(case when fieldname='categ' then alphavalue else null end),'')<>''
and isnull(max(case when fieldname='flavor' then alphavalue else null end),'')<>''
and isnull(max(case when fieldname='type' then alphavalue else null end),'')<>''
GROUP BY StockCode
ORDER BY StockCode
End
this looks like a shorter code but it's showing errors such
Msg 156, Level 15, State 1, Procedure Insert_Prod_Type1, Line 21
Incorrect syntax near the keyword 'GROUP'. |
|
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2015-04-30 : 19:44:40
|
quote:
Originally posted by Patyk
create Procedure [dbo].[Insert_Prod_Type1]
as
truncate table BO_SKUBreakDownProdType
Begin
Insert into BO_SKUBreakDownProdType (StockCode, categ,flavor, type)
select rtrim(keyfield) as StockCode
,max(case when fieldname='categ' then alphavalue else null end) as categ
,max(case when fieldname='flavor' then alphavalue else null end) as flavor
,max(case when fieldname='type' then alphavalue else null end) as type
from companyB.dbo.AdmFormData
where fieldname in ('categ','flavor','type')
group by keyfield
having isnull(max(case when fieldname='categ' then alphavalue else null end),'')<>''
and isnull(max(case when fieldname='flavor' then alphavalue else null end),'')<>''
and isnull(max(case when fieldname='type' then alphavalue else null end),'')<>''
GROUP BY StockCode
ORDER BY StockCode
End
this looks like a shorter code but it's showing errors such
Msg 156, Level 15, State 1, Procedure Insert_Prod_Type1, Line 21
Incorrect syntax near the keyword 'GROUP'.
|
|
|
|
|
|
|
|
|
Need change my store procedure