April 9, 2012 at 11:04 am
For some reason these two statements are not returning the same data.. first is NULL and the second returns the correct data set.. I must be missing something simple...
declare @model varchar
set @model = 'SVLL_P1_GS'
SELECT MODELCODE,
MATERIALCODE,
SUM(SETUPTIMEHOURS) AS HRS,
count(*) as total_count,
(select count(*)
FROM NDCSQLASPSQDP01.ASCCDB.DBO.MOM_GSCS_SETUP_CLEANING_DETAIL as b
where (FZFLAG ='1' OR FIXEDLOTNUMBER <> 'X')
and a.modelcode = b.modelcode
and a.MATERIALCODE = b.MATERIALCODE) as count_frozen,
(select count(*)
FROM NDCSQLASPSQDP01.ASCCDB.DBO.MOM_GSCS_SETUP_CLEANING_DETAIL as c
where (FZFLAG ='0' AND FIXEDLOTNUMBER = 'X')
and a.modelcode = c.modelcode
and a.MATERIALCODE = c.MATERIALCODE) as count_unfrozen,
(select sum(SETUPTIMEHOURS)
FROM NDCSQLASPSQDP01.ASCCDB.DBO.MOM_GSCS_SETUP_CLEANING_DETAIL as d
where (FZFLAG ='1' OR FIXEDLOTNUMBER <> 'X')
and a.modelcode = d.modelcode
and a.MATERIALCODE = d.MATERIALCODE) as HRS_Frozen,
(select sum(SETUPTIMEHOURS)
FROM NDCSQLASPSQDP01.ASCCDB.DBO.MOM_GSCS_SETUP_CLEANING_DETAIL as e
where (FZFLAG ='0' AND FIXEDLOTNUMBER = 'X')
and a.modelcode = e.modelcode
and a.MATERIALCODE= e.MATERIALCODE) as HRS_unfrozen
FROM NDCSQLASPSQDP01.ASCCDB.DBO.MOM_GSCS_SETUP_CLEANING_DETAIL as a
where MODELCODE LIKE @Model
group by modelcode, MATERIALCODE
The second is
-----------------
SELECT MODELCODE,
MATERIALCODE,
SUM(SETUPTIMEHOURS) AS HRS,
count(*) as total_count,
(select count(*)
FROM NDCSQLASPSQDP01.ASCCDB.DBO.MOM_GSCS_SETUP_CLEANING_DETAIL as b
where (FZFLAG ='1' OR FIXEDLOTNUMBER <> 'X')
and a.modelcode = b.modelcode
and a.MATERIALCODE = b.MATERIALCODE) as count_frozen,
(select count(*)
FROM NDCSQLASPSQDP01.ASCCDB.DBO.MOM_GSCS_SETUP_CLEANING_DETAIL as c
where (FZFLAG ='0' AND FIXEDLOTNUMBER = 'X')
and a.modelcode = c.modelcode
and a.MATERIALCODE = c.MATERIALCODE) as count_unfrozen,
(select sum(SETUPTIMEHOURS)
FROM NDCSQLASPSQDP01.ASCCDB.DBO.MOM_GSCS_SETUP_CLEANING_DETAIL as d
where (FZFLAG ='1' OR FIXEDLOTNUMBER <> 'X')
and a.modelcode = d.modelcode
and a.MATERIALCODE = d.MATERIALCODE) as HRS_Frozen,
(select sum(SETUPTIMEHOURS)
FROM NDCSQLASPSQDP01.ASCCDB.DBO.MOM_GSCS_SETUP_CLEANING_DETAIL as e
where (FZFLAG ='0' AND FIXEDLOTNUMBER = 'X')
and a.modelcode = e.modelcode
and a.MATERIALCODE= e.MATERIALCODE) as HRS_unfrozen
FROM NDCSQLASPSQDP01.ASCCDB.DBO.MOM_GSCS_SETUP_CLEANING_DETAIL as a
where MODELCODE LIKE 'SVLL_P1_GS'
group by modelcode, MATERIALCODE
April 9, 2012 at 11:10 am
dwilliscp (4/9/2012)
For some reason these two statements are not returning the same data.. first is NULL and the second returns the correct data set.. I must be missing something simple...declare @model varchar
set @model = 'SVLL_P1_GS'
Yup, something very simple...
You declared a varchar with no length specified. Do you know what the default length for a varchar is if you don't specify it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2012 at 12:18 pm
Let me guess.. given the result.. zero? Yep... knew it was simple, thanks.
April 9, 2012 at 12:26 pm
Close...
DECLARE @Var VARCHAR means DECLARE @Var VARCHAR(1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy