Query not Working. Need Help!

Query not Working. Need Help!

Post by ME » Sat, 16 Aug 2003 10:38:32



Below is my procedure & query result. I'm getting some bizarre
results. For example, when I try to do a count on access rates i get
0. Even though there is 4 access rates returned from the join and
inserted in the temp table. It works fine in the query analyzer, but
returns 0 when I try to return the recordset in asp. Please I need
guideance. Thanks.

-----------------------------------------------------------------------------
Here is the procedure:
-----------------------------------------------------------------------------

PROCEDURE dbo.ViewComp_PP_CN





AS
BEGIN
SET NOCOUNT ON

CREATE TABLE #TempCompEst
(
        CompAutoID int IDENTITY,
        Comp_Category_ID int Default 0,
        Tier_Level int Default 0,
        Tier_Desc nvarchar(50) Default 'nada',
        Contract_Length int Default 0,
        Tier_Payout money Default 0,
        Deact_Amount int Default 0,
        Access_Rate money Default 0,
        Comp_Month datetime
)

INSERT INTO #TempCompEst(Comp_Category_ID, Tier_Level, Tier_Desc,
Contract_Length, Tier_Payout, Deact_Amount, Access_Rate, Comp_Month)

SELECT tblCompTiers.Comp_Category_ID, tblCompTiers.Tier_Level,
       tblCompTiers.Tier_Description,
tblCompTiers.Tier_Contract_Length,
       tblCompTiers.Tier_Payout, tblDeacts.Deact_Amount,
       tblTransactions.Access_Rate, tblTransactions.Comp_Month

FROM tblComptiers
LEFT JOIN tblTransactions ON tblCompTiers.Comp_Category_ID =
tblTransactions.Sales_Type_ID
AND tblCompTiers.Tier_Contract_Length =
tblTransactions.Contract_Length


AND tblTransactions.Access_Rate BETWEEN tblCompTiers.Tier_Min AND
tblCompTiers.Tier_Max

LEFT JOIN tblDeacts ON tblCompTiers.Tier_ID = tblDeacts.Tier_ID



--GROUP BY tblCompTiers.Comp_Category_ID, tblCompTiers.Tier_Level,
--       tblCompTiers.Tier_Description, tblCompTiers.Tier_Contract_Length,
--       tblCompTiers.Tier_Payout, tblDeacts.Deact_Amount,
--       tblTransactions.Access_Rate, tblTransactions.Contract_Length,
--       tblTransactions.Comp_Month

--ORDER BY tblCompTiers.Tier_Level ASC,
--       tblCompTiers.Tier_Description DESC,
--       tblTransactions.Contract_Length ASC

SELECT * FROM #TempCompEst

SET NOCOUNT OFF

END

-----------------------------------------------------------------------------
Here is the result in the query analyzer:
-----------------------------------------------------------------------------

1       1       1       [FREEUP]        1       1.0000  5       NULL    NULL
2       1       1       [FREEUP]        2       .0000   NULL    NULL    NULL
3       1       1       $0 - $34.99     1       1.0000  NULL    29.9900 2003-08-01 00:00:00.000
4       1       1       $0 - $34.99     1       1.0000  NULL    34.9900 2003-08-01 00:00:00.000
5       1       1       $0 - $34.99     2       1.5000  NULL    34.9900 2003-08-01 00:00:00.000
6       1       2       $35 - $44.99    1       4.0000  NULL    NULL    NULL
7       1       2       $35 - $44.99    2       6.0000  NULL    39.9900 2003-08-01 00:00:00.000
8       1       2       $35 - $44.99    2       6.0000  NULL    39.9900 2003-08-01 00:00:00.000
9       1       3       $45 - $54.99    1       8.0000  NULL    NULL    NULL
10      1       3       $45 - $54.99    2       12.0000 NULL    NULL    NULL
11      1       4       $55+    1       12.0000 8       NULL    NULL
12      1       4       $55+    2       18.0000 NULL    NULL    NULL

 
 
 

1. Query works in Query Analyzer, but not as SP - Please Help

This is driving me nuts. I have tried everything and can't find what is
wrong.

The batch of queries below work perfectly from within the SQL 7.0 Query
Analyzer, but when I put them into a stored proceedure, no result set is
returned. I did some experementing and found that the problem must be in the
first select  - it returns a record in the Query Analyzer, but not as a
stored proceedure.



CREATE TABLE #Temp1 (ClientNo smallint, TableNo smallint, DateTableLastUsed
datetime)
INSERT INTO #Temp1

SELECT ClientTableDetail.ClientNo, ClientTableDetail.TableNo,
ClientTableDetail.DateTableLastUsed
FROM ClientTableDetail

SELECT TableDesc.TableNo, #Temp1.DateTableLastUsed, TableDesc.TableName,
TableDesc.TableDescription
FROM #Temp1
RIGHT JOIN TableDesc ON #Temp1.TableNo=TableDesc.TableNo
ORDER BY #Temp1.DateTableLastUsed DESC, TableDesc.TableNo

return

Any ideas anybody? Any comments or suggestions will be greatly appreciated.

Stephen Barash

Hewlett Packard Mxico

2. transfer from as400 to SQL SERVER???

3. Help, Query occasionally does not work !!

4. MCDBA Discussion List...

5. Plz Help with OUTER JOIN Query-Not Working

6. How to get started?

7. Help with Query (works in Access, not in SQL 7)

8. US-NC-Application Developer (Visual Basic, Oracle, Sybase, SQL Server) DB Basics, Inc. #94

9. rookie needs help: like operator is not working...

10. Insert that works in query does not work as sp

11. Check constraint does not work, need help

12. Query not working on SQL7 but worked on SQL6.5

13. SELECT statement not working - why?? (Need Help)