problem in union
-
hi to all how to union two below query thanks in advance
SELECT
dailyAbsences.*,'غیبت' as [RowType], '0' as [RowTypeCode]
INTO
#fullPersonnelDetails2
FROM
#personnelWorkingPeriodRange personnelWorkingPeriodRange
INNER JOIN dbo.tkp_vwDailyAbsences As dailyAbsences
ON dailyAbsences.PersonnelBaseID = personnelWorkingPeriodRange.PersonnelBaseID
And dailyAbsences.StartDate >= personnelWorkingPeriodRange.StartDate
And dailyAbsences.EndDate <= DateAdd(day, 1, personnelWorkingPeriodRange.EndDate)
LEFT JOIN tkp_CalculationQueue As calculationQueue
ON dailyAbsences.PersonnelBaseID = calculationQueue.PersonnelBaseID
And calculationQueue.StartDate <= personnelWorkingPeriodRange.EndDate
ORDER BY
dailyAbsences.PersonnelBaseID,
dailyAbsences.StartDate
UNION
SELECT
'اضافه کاری' as [RowType], '1' as [RowTypeCode]
FROM
dbo.tkp_DailyStatistics dailystatistics
WHERE
dailystatistics.ActualExtraWork > dailystatistics.ExtraWork AND
dailystatistics.PersonnelBaseID = @PersonnelBaseID AND
dailystatistics.WorkingPeriodID = @WorkingPeriodID AND
dailystatistics.YearWorkingPeriodID = @WorkingPeriodYearIF EXISTS ( SELECT * FROM tempdb..SYSOBJECTS WHERE Name = '#PersonnelWorkingPeriodRange' and xType = 'u')
DROP TABLE #PersonnelWorkingPeriodRange -
hi to all how to union two below query thanks in advance
SELECT
dailyAbsences.*,'غیبت' as [RowType], '0' as [RowTypeCode]
INTO
#fullPersonnelDetails2
FROM
#personnelWorkingPeriodRange personnelWorkingPeriodRange
INNER JOIN dbo.tkp_vwDailyAbsences As dailyAbsences
ON dailyAbsences.PersonnelBaseID = personnelWorkingPeriodRange.PersonnelBaseID
And dailyAbsences.StartDate >= personnelWorkingPeriodRange.StartDate
And dailyAbsences.EndDate <= DateAdd(day, 1, personnelWorkingPeriodRange.EndDate)
LEFT JOIN tkp_CalculationQueue As calculationQueue
ON dailyAbsences.PersonnelBaseID = calculationQueue.PersonnelBaseID
And calculationQueue.StartDate <= personnelWorkingPeriodRange.EndDate
ORDER BY
dailyAbsences.PersonnelBaseID,
dailyAbsences.StartDate
UNION
SELECT
'اضافه کاری' as [RowType], '1' as [RowTypeCode]
FROM
dbo.tkp_DailyStatistics dailystatistics
WHERE
dailystatistics.ActualExtraWork > dailystatistics.ExtraWork AND
dailystatistics.PersonnelBaseID = @PersonnelBaseID AND
dailystatistics.WorkingPeriodID = @WorkingPeriodID AND
dailystatistics.YearWorkingPeriodID = @WorkingPeriodYearIF EXISTS ( SELECT * FROM tempdb..SYSOBJECTS WHERE Name = '#PersonnelWorkingPeriodRange' and xType = 'u')
DROP TABLE #PersonnelWorkingPeriodRangeYour 2 select statements do not have the same number of columns - union requires the 2 queries to match exactly. dailyabscence.* is not in your 2nd query.
Never underestimate the power of human stupidity RAH
-
Your 2 select statements do not have the same number of columns - union requires the 2 queries to match exactly. dailyabscence.* is not in your 2nd query.
Never underestimate the power of human stupidity RAH
Also using * is not advised. Technically okay but it is better to spell out the columns.
-
Also using * is not advised. Technically okay but it is better to spell out the columns.
I think using .* is the least of his problem if he does not understand the structuring of a union query. He obviously has not even glanced at the BOL doco for union so expecting him to understand a more subtle issue of using * is probably asking a little too much.
Never underestimate the power of human stupidity RAH
-
I think using .* is the least of his problem if he does not understand the structuring of a union query. He obviously has not even glanced at the BOL doco for union so expecting him to understand a more subtle issue of using * is probably asking a little too much.
Never underestimate the power of human stupidity RAH
I agree. I was just pointing out one of many things to look at.