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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply