troubleshooting Question

Crystal reports - SQL Command add field

Avatar of kelsanit
kelsanit asked on
Crystal ReportsMySQL Server
4 Comments2 Solutions432 ViewsLast Modified:
have the following SQL Comman where the SmsnMaster.SalesRepName does not appear as a field name in Crystal. When I try and add to the select statement it tells me it could not be bound.



WITH Orders (TakenBy, InvoiceDate, PlacedBy,District,OrderNumber,OutsideSalesRep,SalesRepName)
AS
(
    SELECT "OrderHeaderMaster"."TakenBy",
           "OrderHeaderMaster"."InvoiceDate",
           "OrderHeaderMaster"."PlacedBy",
           "SmsnMaster"."District",
           "OrderHeaderMaster"."OrderNumber",
           "CustomerMaster"."OutsideSalesRep",
           "SmsnMaster"."SalesRepName"
    FROM   "ksdb_eff"."dbo"."OrderHeaderMaster" "OrderHeaderMaster"
           INNER JOIN (
                    "ksdb_eff"."dbo"."SmsnMaster" "SmsnMaster" INNER JOIN
                    "ksdb_eff"."dbo"."CustomerMaster" "CustomerMaster" ON
                    "SmsnMaster"."SalesRepNumber" = "CustomerMaster"."OutsideSalesRep"
                )
                ON  "OrderHeaderMaster"."CustomerNumber" = "CustomerMaster"."CustomerNumber"
    WHERE  NOT (
               "SmsnMaster"."District" = 'krystal'
               OR "SmsnMaster"."District" = 'no manager'
           )
           AND "OrderHeaderMaster"."InvoiceDate" >= {ts '2011-08-02 00:00:00'}
           AND "SmsnMaster"."SalesRepName" <> 'available'
)

SELECT tm.OutsideSalesRep,tm.Orders AS TMOrders,onln.Orders AS OnlineOrders, convert(decimal(10,4),isnull(onln.Orders,0))/convert(decimal(10,4),tm.Orders)  AS BIZcPr


FROM (
                  SELECT o.OutsideSalesRep,COUNT(*) as Orders
                  FROM Orders o
                  GROUP BY  o.OutsideSalesRep
                  ) tm
      LEFT JOIN (
                                          SELECT oo.OutsideSalesRep, COUNT(*) AS Orders
                                          FROM Orders oo
                                          WHERE TakenBy = 'WWW'
                                          GROUP BY oo.OutsideSalesRep

) onln ON tm.OutsideSalesRep = onln.OutsideSalesRep;

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros