Levi Martin
asked on
Query errors with "Error converting varchar to numeric."
I have a query (looking be formatted better in the very near future) but for now the query errors on the SWAP Rate dataset (located at the bottom of the code snippet in the last section). The first two sections generate fine but when the third is UNIONed  the error "Error converting varchar to numeric' is thrown even though all txtActivityNumber columns are varchar(20) formatted - - out of ideas to try and would love some expert assiatnce to lead me in the right direction.
Thanks Experts!!!
Thanks Experts!!!
--(1) Scheduled Activities & (12) COV/POV Count & (13) TNOS (Tech Not On Site) Count:
-------------------------------------------------------------------------------------
SELECT DISTINCT sa.txtActivityNumber AS ActivityNumber,
'Scheduled_Activities' AS DataType,
(CASE WHEN dbo.tblBranches.intCompanyBranchID = 30 THEN 'Bluegrass'
WHEN dbo.tblBranches.intCompanyBranchID = 42 THEN 'Michigan'
WHEN dbo.tblBranches.intCompanyBranchID = 31 THEN 'Northeast'
WHEN dbo.tblBranches.intCompanyBranchID = 29 THEN 'Southwest' END) AS Company,
dbo.tblBranches.txtBranchName AS Branch,
HATUsers.intUserID AS HATID,
HATUsers.txtUserFirstName AS HATFirstName,
HATUsers.txtUserLastName AS HATLastName,
WTLUsers.intUserID AS WTLID,
WTLUsers.txtUserFirstName AS WTLFirstName,
WTLUsers.txtUserLastName AS WTLLastName,
dbo.tblUsers.intUserID AS TechID,
dbo.tblUsers.txtUserFirstName AS TechFirstName,
dbo.tblUsers.txtUserLastName AS TechLastName,
CONVERT(CHAR(10), sa.dteActivityDueRT, 101) AS Date,
(CASE WHEN dbo.tblUsers.txtDONLENNumber IS NOT NULL THEN 'COV' ELSE 'POV' END) AS COV_POV,
(CASE WHEN dbo.tblUsers.txtDONLENNumber IS NOT NULL THEN 1 ELSE 0 END) AS COVOccuranceCount,
(CASE WHEN dbo.tblUsers.txtDONLENNumber IS NULL THEN 1 ELSE 0 END) AS POVOccuranceCount,
(CASE WHEN dbo.tblTicketDTV.bitTechOnSite = 0 THEN 1 ELSE 0 END) AS TNOS_Count,
1 AS ScheduledActivitiesCount,
'' AS CompletedActivitiesCount,
'' AS ProductivityPoints,
dbo.tblTechEfficiency.decEfficiencyRate AS TechEfficiencyRate,
(CASE WHEN sa.txtRepeatServiceFlag='Y' THEN 'Yes' ELSE 'No' END) AS RepeatServiceFlag,
(CASE WHEN sa.txtRepeatServiceFlag='Y' THEN 1 ELSE 0 END) AS RepeatServiceOccuranceCount,
(CASE WHEN dbo.tblActivitySurvey.intSurveyAnswer5 = 5 THEN 1 ELSE 0 END) AS Oustanding,
(CASE WHEN dbo.tblActivitySurvey.intSurveyAnswer5 = 4 THEN 1 ELSE 0 END) AS VerySatisfied,
(CASE WHEN dbo.tblActivitySurvey.intSurveyAnswer5 = 3 THEN 1 ELSE 0 END) AS Acceptable,
(CASE WHEN dbo.tblActivitySurvey.intSurveyAnswer5 = 2 THEN 1 ELSE 0 END) AS NotSoGood,
(CASE WHEN dbo.tblActivitySurvey.intSurveyAnswer5 = 1 THEN 1 ELSE 0 END) AS Poor,
'' AS SWAPRateCount
FROM dbo.tblSiebelMgnt
RIGHT OUTER JOIN dbo.tblTicketDTV
RIGHT OUTER JOIN (SELECT * FROM dbo.tblSiebelActivity1000
UNION
SELECT * FROM dbo.tblSiebelActivity1200
UNION
SELECT * FROM dbo.tblSiebelActivity1400
UNION
SELECT * FROM dbo.tblSiebelActivity1800) AS sa ON dbo.tblTicketDTV.txtJobNumber = sa.txtActivityNumber
LEFT OUTER JOIN dbo.tblUsers AS HATUsers
INNER JOIN dbo.tblUsers AS WTLUsers
INNER JOIN dbo.tblUsers ON WTLUsers.intUserID = dbo.tblUsers.intSupNumber ON HATUsers.intUserID = dbo.tblUsers.intMgrNumber
LEFT OUTER JOIN dbo.tblBranches ON dbo.tblUsers.intHomeBranchID = dbo.tblBranches.intBranchID
LEFT OUTER JOIN dbo.tblTechEfficiency ON dbo.tblUsers.intUserID = dbo.tblTechEfficiency.intTechID ON CAST(SUBSTRING(sa.txtTechUserID, 6, LEN(sa.txtTechUserID)) AS BIGINT) = dbo.tblUsers.intUserID ON dbo.tblSiebelMgnt.intSiebelMgnt = sa.txtServiceRegion
LEFT OUTER JOIN dbo.tblActivitySurvey ON sa.intSiebelActivityID = dbo.tblActivitySurvey.intSiebelActivityID
WHERE ((CASE WHEN dbo.tblBranches.intCompanyBranchID = 30 THEN 'Bluegrass'
WHEN dbo.tblBranches.intCompanyBranchID = 42 THEN 'Michigan'
WHEN dbo.tblBranches.intCompanyBranchID = 31 THEN 'Northeast'
WHEN dbo.tblBranches.intCompanyBranchID = 29 THEN 'Southwest' END) IS NOT NULL) AND
(dbo.tblBranches.txtBranchName IS NOT NULL) AND
(dbo.tblUsers.intUserID IS NOT NULL) AND
(sa.dteActivityDueRT BETWEEN '11/15/2007' AND '11/16/2007')
-----------------------------------------------------------------------------------------------------------------------------------------------------
UNION
-----------------------------------------------------------------------------------------------------------------------------------------------------
--(2) Completed Activities & (4) Productivity Points & (12) COV/POV Count & (13) TNOS (Tech Not On Site) Count:
---------------------------------------------------------------------------------------------------------------
SELECT DISTINCT sc.txtActivityNumber AS ActivityNumber,
'Completed_Activities' AS DataType,
(CASE WHEN dbo.tblBranches.intCompanyBranchID = 30 THEN 'Bluegrass'
WHEN dbo.tblBranches.intCompanyBranchID = 42 THEN 'Michigan'
WHEN dbo.tblBranches.intCompanyBranchID = 31 THEN 'Northeast'
WHEN dbo.tblBranches.intCompanyBranchID = 29 THEN 'Southwest' END) AS Company,
dbo.tblBranches.txtBranchName AS Branch,
HATUsers.intUserID AS HATID,
HATUsers.txtUserFirstName AS HATFirstName,
HATUsers.txtUserLastName AS HATLastName,
WTLUsers.intUserID AS WTLID,
WTLUsers.txtUserFirstName AS WTLFirstName,
WTLUsers.txtUserLastName AS WTLLastName,
dbo.tblUsers.intUserID AS TechID,
dbo.tblUsers.txtUserFirstName AS TechFirstName,
dbo.tblUsers.txtUserLastName AS TechLastName,
CONVERT(CHAR(10), sc.dteActualEndDate, 101) AS Date,
'' AS COV_POV,
'' AS COVOccuranceCount,
'' AS POVOccuranceCount,
'' AS TNOS_Count,
'' AS ScheduledActivitiesCount,
1 AS CompletedActivitiesCount,
sc.intProductivityPoints AS ProductivityPoints,
dbo.tblTechEfficiency.decEfficiencyRate AS TechEfficiencyRate,
'' AS RepeatServiceFlag,
'' AS RepeatServiceOccuranceCount,
'' AS Oustanding,
'' AS VerySatisfied,
'' AS Acceptable,
'' AS NotSoGood,
'' AS Poor,
'' AS SWAPRateCount
FROM dbo.tblSiebelMgnt
RIGHT OUTER JOIN dbo.tblTicketDTV
RIGHT OUTER JOIN (SELECT * FROM dbo.tblSiebelComplete1000
UNION
SELECT * FROM dbo.tblSiebelComplete1200
UNION
SELECT * FROM dbo.tblSiebelComplete1400
UNION
SELECT * FROM dbo.tblSiebelComplete1800) AS sc ON dbo.tblTicketDTV.txtJobNumber = sc.txtActivityNumber
LEFT OUTER JOIN dbo.tblUsers AS HATUsers
INNER JOIN dbo.tblUsers AS WTLUsers
INNER JOIN dbo.tblUsers ON WTLUsers.intUserID = dbo.tblUsers.intSupNumber ON HATUsers.intUserID = dbo.tblUsers.intMgrNumber
LEFT OUTER JOIN dbo.tblBranches ON dbo.tblUsers.intHomeBranchID = dbo.tblBranches.intBranchID
LEFT OUTER JOIN dbo.tblTechEfficiency ON dbo.tblUsers.intUserID = dbo.tblTechEfficiency.intTechID ON sc.intTechID = dbo.tblUsers.intUserID ON dbo.tblSiebelMgnt.intSiebelMgnt = sc.txtServiceRegion
WHERE ((CASE WHEN dbo.tblBranches.intCompanyBranchID = 30 THEN 'Bluegrass'
WHEN dbo.tblBranches.intCompanyBranchID = 42 THEN 'Michigan'
WHEN dbo.tblBranches.intCompanyBranchID = 31 THEN 'Northeast'
WHEN dbo.tblBranches.intCompanyBranchID = 29 THEN 'Southwest' END) IS NOT NULL) AND
(dbo.tblBranches.txtBranchName IS NOT NULL) AND
(dbo.tblUsers.intUserID IS NOT NULL) AND
(sc.dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007')
-----------------------------------------------------------------------------------------------------------------------------------------------------
UNION
-----------------------------------------------------------------------------------------------------------------------------------------------------
--(10 & 11) SWAP Rate:
----------------------
SELECT DISTINCT scd.txtActivityNumber AS ActivityNumber,
'SWAP_Rate' AS DataType,
'' AS Company,
'' AS Branch,
'' AS HATID,
'' AS HATFirstName,
'' AS HATLastName,
'' AS WTLID,
'' AS WTLFirstName,
'' AS WTLLastName,
'' AS TechID,
'' AS TechFirstName,
'' AS TechLastName,
'' AS Date,
'' AS COV_POV,
'' AS COVOccuranceCount,
'' AS POVOccuranceCount,
'' AS TNOS_Count,
'' AS ScheduledActivitiesCount,
'' AS CompletedActivitiesCount,
'' AS ProductivityPoints,
'' AS TechEfficiencyRate,
'' AS RepeatServiceFlag,
'' AS RepeatServiceOccuranceCount,
'' AS Oustanding,
'' AS VerySatisfied,
'' AS Acceptable,
'' AS NotSoGood,
'' AS Poor,
1 AS SWAPRateCount
FROM (SELECT * FROM dbo.tblSiebelCompleteDetail1000
UNION
SELECT * FROM dbo.tblSiebelCompleteDetail1200
UNION
SELECT * FROM dbo.tblSiebelCompleteDetail1400
UNION
SELECT * FROM dbo.tblSiebelCompleteDetail1800) AS scd
WHERE --(scd.txtActionRequired = 'SWAP Relocate') AND
(scd.txtTechActionTaken IN ('Call Center Removed', 'Call Center Replaced')) AND
(scd.txtActivityNumber IN ((SELECT txtActivityNumber FROM dbo.tblSiebelComplete1000 WHERE (txtOrderSubType = 'Service') AND (dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007')
UNION
SELECT txtActivityNumber FROM dbo.tblSiebelComplete1200 WHERE (txtOrderSubType = 'Service') AND (dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007')
UNION
SELECT txtActivityNumber FROM dbo.tblSiebelComplete1400 WHERE (txtOrderSubType = 'Service') AND (dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007')
UNION
SELECT txtActivityNumber FROM dbo.tblSiebelComplete1800 WHERE (txtOrderSubType = 'Service') AND (dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007'))))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Checking:
If you run from line 1 to line 123 as one process, this works, correct?
If you run line 124 down as one process, does this work?
If you run from line 1 to line 123 as one process, this works, correct?
If you run line 124 down as one process, does this work?
ASKER
Yes, 1-123 works and 124-End works - - I receive the error when the all is combined to run on the txtActivityNumber field.
point1:
UNION is explicitly DISTINCT across all UNIONed sets. so there is no need for DISTINCT within the top/bottom SQL
UNION is explicitly DISTINCT across all UNIONed sets. so there is no need for DISTINCT within the top/bottom SQL
can you please do this:
--(1) Scheduled Activities & (12) COV/POV Count & (13) TNOS (Tech Not On Site) Count:
-------------------------------------------------------------------------------------
SELECT top 0 sa.txtActivityNumber AS ActivityNumber,
'Scheduled_Activities' AS DataType,
(CASE WHEN dbo.tblBranches.intCompanyBranchID = 30 THEN 'Bluegrass'
WHEN dbo.tblBranches.intCompanyBranchID = 42 THEN 'Michigan'
WHEN dbo.tblBranches.intCompanyBranchID = 31 THEN 'Northeast'
WHEN dbo.tblBranches.intCompanyBranchID = 29 THEN 'Southwest' END) AS Company,
dbo.tblBranches.txtBranchName AS Branch,
HATUsers.intUserID AS HATID,
HATUsers.txtUserFirstName AS HATFirstName,
HATUsers.txtUserLastName AS HATLastName,
WTLUsers.intUserID AS WTLID,
WTLUsers.txtUserFirstName AS WTLFirstName,
WTLUsers.txtUserLastName AS WTLLastName,
dbo.tblUsers.intUserID AS TechID,
dbo.tblUsers.txtUserFirstName AS TechFirstName,
dbo.tblUsers.txtUserLastName AS TechLastName,
CONVERT(CHAR(10), sa.dteActivityDueRT, 101) AS Date,
(CASE WHEN dbo.tblUsers.txtDONLENNumber IS NOT NULL THEN 'COV' ELSE 'POV' END) AS COV_POV,
(CASE WHEN dbo.tblUsers.txtDONLENNumber IS NOT NULL THEN 1 ELSE 0 END) AS COVOccuranceCount,
(CASE WHEN dbo.tblUsers.txtDONLENNumber IS NULL THEN 1 ELSE 0 END) AS POVOccuranceCount,
(CASE WHEN dbo.tblTicketDTV.bitTechOnSite = 0 THEN 1 ELSE 0 END) AS TNOS_Count,
1 AS ScheduledActivitiesCount,
'' AS CompletedActivitiesCount,
'' AS ProductivityPoints,
dbo.tblTechEfficiency.decEfficiencyRate AS TechEfficiencyRate,
(CASE WHEN sa.txtRepeatServiceFlag='Y' THEN 'Yes' ELSE 'No' END) AS RepeatServiceFlag,
(CASE WHEN sa.txtRepeatServiceFlag='Y' THEN 1 ELSE 0 END) AS RepeatServiceOccuranceCount,
(CASE WHEN dbo.tblActivitySurvey.intSurveyAnswer5 = 5 THEN 1 ELSE 0 END) AS Oustanding,
(CASE WHEN dbo.tblActivitySurvey.intSurveyAnswer5 = 4 THEN 1 ELSE 0 END) AS VerySatisfied,
(CASE WHEN dbo.tblActivitySurvey.intSurveyAnswer5 = 3 THEN 1 ELSE 0 END) AS Acceptable,
(CASE WHEN dbo.tblActivitySurvey.intSurveyAnswer5 = 2 THEN 1 ELSE 0 END) AS NotSoGood,
(CASE WHEN dbo.tblActivitySurvey.intSurveyAnswer5 = 1 THEN 1 ELSE 0 END) AS Poor,
'' AS SWAPRateCount
into tmp1__
FROM dbo.tblSiebelMgnt
RIGHT OUTER JOIN dbo.tblTicketDTV
RIGHT OUTER JOIN (SELECT * FROM dbo.tblSiebelActivity1000
UNION
SELECT * FROM dbo.tblSiebelActivity1200
UNION
SELECT * FROM dbo.tblSiebelActivity1400
UNION
SELECT * FROM dbo.tblSiebelActivity1800) AS sa ON dbo.tblTicketDTV.txtJobNumber = sa.txtActivityNumber
LEFT OUTER JOIN dbo.tblUsers AS HATUsers
INNER JOIN dbo.tblUsers AS WTLUsers
INNER JOIN dbo.tblUsers ON WTLUsers.intUserID = dbo.tblUsers.intSupNumber ON HATUsers.intUserID = dbo.tblUsers.intMgrNumber
LEFT OUTER JOIN dbo.tblBranches ON dbo.tblUsers.intHomeBranchID = dbo.tblBranches.intBranchID
LEFT OUTER JOIN dbo.tblTechEfficiency ON dbo.tblUsers.intUserID = dbo.tblTechEfficiency.intTechID ON CAST(SUBSTRING(sa.txtTechUserID, 6, LEN(sa.txtTechUserID)) AS BIGINT) = dbo.tblUsers.intUserID ON dbo.tblSiebelMgnt.intSiebelMgnt = sa.txtServiceRegion
LEFT OUTER JOIN dbo.tblActivitySurvey ON sa.intSiebelActivityID = dbo.tblActivitySurvey.intSiebelActivityID
WHERE ((CASE WHEN dbo.tblBranches.intCompanyBranchID = 30 THEN 'Bluegrass'
WHEN dbo.tblBranches.intCompanyBranchID = 42 THEN 'Michigan'
WHEN dbo.tblBranches.intCompanyBranchID = 31 THEN 'Northeast'
WHEN dbo.tblBranches.intCompanyBranchID = 29 THEN 'Southwest' END) IS NOT NULL) AND
(dbo.tblBranches.txtBranchName IS NOT NULL) AND
(dbo.tblUsers.intUserID IS NOT NULL) AND
(sa.dteActivityDueRT BETWEEN '11/15/2007' AND '11/16/2007')
-----------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------
--(2) Completed Activities & (4) Productivity Points & (12) COV/POV Count & (13) TNOS (Tech Not On Site) Count:
---------------------------------------------------------------------------------------------------------------
SELECT top 0 sc.txtActivityNumber AS ActivityNumber,
'Completed_Activities' AS DataType,
(CASE WHEN dbo.tblBranches.intCompanyBranchID = 30 THEN 'Bluegrass'
WHEN dbo.tblBranches.intCompanyBranchID = 42 THEN 'Michigan'
WHEN dbo.tblBranches.intCompanyBranchID = 31 THEN 'Northeast'
WHEN dbo.tblBranches.intCompanyBranchID = 29 THEN 'Southwest' END) AS Company,
dbo.tblBranches.txtBranchName AS Branch,
HATUsers.intUserID AS HATID,
HATUsers.txtUserFirstName AS HATFirstName,
HATUsers.txtUserLastName AS HATLastName,
WTLUsers.intUserID AS WTLID,
WTLUsers.txtUserFirstName AS WTLFirstName,
WTLUsers.txtUserLastName AS WTLLastName,
dbo.tblUsers.intUserID AS TechID,
dbo.tblUsers.txtUserFirstName AS TechFirstName,
dbo.tblUsers.txtUserLastName AS TechLastName,
CONVERT(CHAR(10), sc.dteActualEndDate, 101) AS Date,
'' AS COV_POV,
'' AS COVOccuranceCount,
'' AS POVOccuranceCount,
'' AS TNOS_Count,
'' AS ScheduledActivitiesCount,
1 AS CompletedActivitiesCount,
sc.intProductivityPoints AS ProductivityPoints,
dbo.tblTechEfficiency.decEfficiencyRate AS TechEfficiencyRate,
'' AS RepeatServiceFlag,
'' AS RepeatServiceOccuranceCount,
'' AS Oustanding,
'' AS VerySatisfied,
'' AS Acceptable,
'' AS NotSoGood,
'' AS Poor,
'' AS SWAPRateCount
into tmp2__
FROM dbo.tblSiebelMgnt
RIGHT OUTER JOIN dbo.tblTicketDTV
RIGHT OUTER JOIN (SELECT * FROM dbo.tblSiebelComplete1000
UNION
SELECT * FROM dbo.tblSiebelComplete1200
UNION
SELECT * FROM dbo.tblSiebelComplete1400
UNION
SELECT * FROM dbo.tblSiebelComplete1800) AS sc ON dbo.tblTicketDTV.txtJobNumber = sc.txtActivityNumber
LEFT OUTER JOIN dbo.tblUsers AS HATUsers
INNER JOIN dbo.tblUsers AS WTLUsers
INNER JOIN dbo.tblUsers ON WTLUsers.intUserID = dbo.tblUsers.intSupNumber ON HATUsers.intUserID = dbo.tblUsers.intMgrNumber
LEFT OUTER JOIN dbo.tblBranches ON dbo.tblUsers.intHomeBranchID = dbo.tblBranches.intBranchID
LEFT OUTER JOIN dbo.tblTechEfficiency ON dbo.tblUsers.intUserID = dbo.tblTechEfficiency.intTechID ON sc.intTechID = dbo.tblUsers.intUserID ON dbo.tblSiebelMgnt.intSiebelMgnt = sc.txtServiceRegion
WHERE ((CASE WHEN dbo.tblBranches.intCompanyBranchID = 30 THEN 'Bluegrass'
WHEN dbo.tblBranches.intCompanyBranchID = 42 THEN 'Michigan'
WHEN dbo.tblBranches.intCompanyBranchID = 31 THEN 'Northeast'
WHEN dbo.tblBranches.intCompanyBranchID = 29 THEN 'Southwest' END) IS NOT NULL) AND
(dbo.tblBranches.txtBranchName IS NOT NULL) AND
(dbo.tblUsers.intUserID IS NOT NULL) AND
(sc.dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007')
-----------------------------------------------------------------------------------------------------------------------------------------------------
UNION
-----------------------------------------------------------------------------------------------------------------------------------------------------
--(10 & 11) SWAP Rate:
----------------------
SELECT DISTINCT scd.txtActivityNumber AS ActivityNumber,
'SWAP_Rate' AS DataType,
'' AS Company,
'' AS Branch,
'' AS HATID,
'' AS HATFirstName,
'' AS HATLastName,
'' AS WTLID,
'' AS WTLFirstName,
'' AS WTLLastName,
'' AS TechID,
'' AS TechFirstName,
'' AS TechLastName,
'' AS Date,
'' AS COV_POV,
'' AS COVOccuranceCount,
'' AS POVOccuranceCount,
'' AS TNOS_Count,
'' AS ScheduledActivitiesCount,
'' AS CompletedActivitiesCount,
'' AS ProductivityPoints,
'' AS TechEfficiencyRate,
'' AS RepeatServiceFlag,
'' AS RepeatServiceOccuranceCount,
'' AS Oustanding,
'' AS VerySatisfied,
'' AS Acceptable,
'' AS NotSoGood,
'' AS Poor,
1 AS SWAPRateCount
FROM (SELECT * FROM dbo.tblSiebelCompleteDetail1000
UNION
SELECT * FROM dbo.tblSiebelCompleteDetail1200
UNION
SELECT * FROM dbo.tblSiebelCompleteDetail1400
UNION
SELECT * FROM dbo.tblSiebelCompleteDetail1800) AS scd
WHERE --(scd.txtActionRequired = 'SWAP Relocate') AND
(scd.txtTechActionTaken IN ('Call Center Removed', 'Call Center Replaced')) AND
(scd.txtActivityNumber IN ((SELECT txtActivityNumber FROM dbo.tblSiebelComplete1000 WHERE (txtOrderSubType = 'Service') AND (dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007')
UNION
SELECT txtActivityNumber FROM dbo.tblSiebelComplete1200 WHERE (txtOrderSubType = 'Service') AND (dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007')
UNION
SELECT txtActivityNumber FROM dbo.tblSiebelComplete1400 WHERE (txtOrderSubType = 'Service') AND (dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007')
UNION
SELECT txtActivityNumber FROM dbo.tblSiebelComplete1800 WHERE (txtOrderSubType = 'Service') AND (dteActualEndDate BETWEEN '11/15/2007' AND '11/16/2007'))))
select a.name, b.name, xtype, length, xprec, xscale
from syscolumns a, syscolumns b
where a.id = object_id('tmp1')
and b.id = object_id('tmp2')
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks so much to the experts who helped me understand this now simple and basic concept -- very much appreciated I hope that I too can pass on the knowledge. Many thanks experts!!!
ASKER
Tried your suggestions and found the following:
(1) Could not restructure the code for SWAP Rate as I have Complete and CompleteDetail tables although putting everything into the FROM statement was a great idea that I haven't attempted before.
(2) Could not get the 0 or NULL to work for ''ID (HAT, WTL, Tech) as I still receive a error on txtActivityNumber for could not convert varchar to numeric - - I was originally uising the following code snippet for the SWAP Rate and receive the same error still.
Thanks for you help and continuing suggestion greatly!!!
Open in new window