May 7, 2008 at 6:26 am
Dear All,
I want to split the SELECT Clause output.
Here is the senario.
Insert into Log
SELECT getdate(),sp,Null,convert(varchar,isnull(count(#R1.sP),0))+' calculation done.'
FROM #Pfm,#R1
Where #Pfm.sp *= #R1.sp
and #Pfm.Flag ='S'
group by #Pfm.sp
having isnull(count(#R1.sp),0) = 0
Union All
SELECT getdate(),sp,null,'Record Not found'
FROM #Pfm,#R1
Where #Pfm.sp *= #R1.sp
and #Pfm.Flag ='S'
group by #Pfm.sp
having isnull(count(#R1.sp),0) = 0
Output:
11/May/2008 101 Null 0 calculation done
11/May/2008 101 Null 0 No Records found
Basically 2 rows will be inserted into Log table. It is mandatory.
Both FROM Clause staments are identical.There is no difference found in the FROM Clause.
If i do like this,
SELECT getdate()sp,Null,convert(varchar,isnull(count(#R1.sP),0))+' calculation done','No Record Found'
FROM #Pfm,#R1
Where #Pfm.sp *= #R1.sp
and #Pfm.Flag ='S'
group by #Pfm.sp
having isnull(count(#R1.sp),0) = 0
I got a single output like
11/May/2008 101 Null 0 calculation done No Record Found
Then a single row will be inserted into the Log table.But it is not correct.
My requirement is:
1) is it posible to split the above SELECT statement output as two part like what i have mentioned in the previous output ?
2) I don't want to repeat the same FROM Clause.Because I want to avoid the Join Cost.
Inputs are urgently needed!
karthik
May 7, 2008 at 7:17 am
Wait a minute... Your title is Senior Software Engineer and this is the zillionth question you have?
N 56°04'39.16"
E 12°55'05.25"
May 7, 2008 at 7:24 am
Try this first to see if it produces the wanted result, THEN we can talk about optimization.
INSERT[Log]
SELECTGETDATE(),
#pfm.sp,
NULL,
CAST(COUNT(#r1.sp) AS VARCHAR(12)) + ' calculation done.'
FROM#pfm
LEFT JOIN#r1 ON #r1.sp = #pfm.sp
WHERE#pfm.Flag = 'S'
GROUP BY#pfm.sp
UNION ALL
SELECTGETDATE(),
#pfm.sp,
NULL,
'Record Not Found'
FROM#pfm
LEFT JOIN#r1 ON #r1.sp = #pfm.sp
WHERE#pfm.Flag = 'S'
GROUP BY#pfm.sp
HAVINGCOUNT(#R1.sp) = 0
N 56°04'39.16"
E 12°55'05.25"
May 7, 2008 at 7:37 am
No. It is now giving identical result.
karthik
May 7, 2008 at 7:59 am
Thank you. You admit they produce the same result.
Now we can move forward.
N 56°04'39.16"
E 12°55'05.25"
May 7, 2008 at 8:03 am
Here is something that only uses the "JOIN" once.
INSERT[Log]
SELECTy.theDateTime,
y.theSP,
y.theNULL,
CASE
WHEN x.theIdentifier = 0 THEN 'Record Not Found'
ELSE CAST(x.theCount AS VARCHAR(12)) + ' calculation done.'
END AS theMessage
FROM(
SELECTGETDATE() AS theDateTime,
#pfm.sp AS theSP,
NULL AS theNULL,
COUNT(#r1.sp) AS theCount
FROM#pfm
LEFT JOIN#r1 ON #r1.sp = #pfm.sp
WHERE#pfm.Flag = 'S'
GROUP BY#pfm.sp
) AS y
CROSS JOIN(
SELECT0 AS theIdentifier
UNION ALL
SELECT1
) AS x
N 56°04'39.16"
E 12°55'05.25"
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply