Need change my store procedure

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need change my store procedure

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'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources