Report Design
 Crystal Reports Forum : Crystal Reports for Visual Studio 2005 and Newer : Report Design
Message Icon Topic: Incorrect syntax near the keyword 'WITH' Post Reply Post New Topic
Author Message
cchuang_ca
Newbie
Newbie


Joined: 13 Apr 2009
Online Status: Offline
Posts: 2
Quote cchuang_ca Replybullet Topic: Incorrect syntax near the keyword 'WITH'
    Posted: 13 Apr 2009 at 11:21am
I'm using VS2008 with CR Basic. I tried to add the following SQL as command under Database Expert. However, it complains about the WITH clause "Incorrect syntax near the keyword 'WITH'". I tried with the exactly same SQL using other SQL generator, it works perfectly fine. Anyone knows what the problem is?

WITH

SegmentChargeAmountByChargeTypeAndChargeCode(PassengerId, SegmentID, ChargeAmount, CurrencyCode, ChargeType, ChargeCode) as
(
    SELECT
          PJC.PassengerId,
          PJC.SegmentID,
          PJC.ChargeAmount, PJC.CurrencyCode,           
          PJC.ChargeType, ChargeCode
    FROM PassengerJourneyCharge PJC WITH(NOLOCK)
),

-- Returns promotional discount
PromotionalDiscount(PassengerID, SegmentID, ChargeAmount) AS
(
    SELECT
          PassengerID,
          SegmentID,
          SUM(ChargeAmount) AS ChargeAmount
    FROM SegmentChargeAmountByChargeTypeAndChargeCode
    WHERE ChargeType = 7
    GROUP BY PassengerID, SegmentID
)

SELECT IsNull(PromotionalDiscount.ChargeAmount,0) AS PromotionalDiscount
FROM Booking b (nolock)
JOIN BookingPassenger bp (nolock)--
      ON bp.BookingId = b.bookingId
JOIN BookingPassengerVersion bpv (nolock)--
      ON bp.PassengerID = bpv.PassengerID
JOIN PassengerJourneySegment pjs (nolock)--
      ON pjs.PassengerID = bp.PassengerID
JOIN dbo.Organization o (nolock)
      ON o.OrganizationCode = b.SourceOrganizationCode
LEFT JOIN PassengerJourneyCharge pjc (nolock)
      ON pjc.Passengerid = bp.Passengerid
      AND pjc.SegmentID  = pjs.SegmentID
LEFT JOIN PromotionalDiscount
      ON PromotionalDiscount.PassengerID = bp.PassengerID
      AND PromotionalDiscount.SegmentID = pjs.SegmentID
WHERE pjc.ChargeType = 0 
pjs.SegmentSTD >= '01/15/09'
AND pjs.SegmentSTD <= '01/17/09'

IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 14 Apr 2009 at 6:35am
Crystal is not always the smartest with SQL.  I haven't used this command, so I don't really know what it does, but if it works in SQL, write a stored proc and use that instead...in the end you will have way more control over your report, and you don't have to worry about Crystal complaining about syntax, because ultimately, Crystal doesn't care about the where the record set comes from, just that it has a consistent structure.
IP IP Logged
cchuang_ca
Newbie
Newbie


Joined: 13 Apr 2009
Online Status: Offline
Posts: 2
Quote cchuang_ca Replybullet Posted: 15 Apr 2009 at 7:47am
Thanks lockwelle for the reply!

I'm currently facing the fundamental design problem. All my company's reports were written in SQL by 3rd party. What I did with CR was to use the SQL as a base and implement them using linking tables with PULL method. However, I found out I don't know how to implements some data fields using CR's formula. It seems I have 3 options from the research I did on the Internet

1. Use PUSH method where I can use the SQL file directly.
But it's a lot more complicated than PULL method and I need to re-do everything I've done and have made it work
2. Use stored procedures as you suggested.
I never used stored procedures and seems it has to be done on the database side. Do I need to have permission to add new stored proc? b/c the database is completely created by 3rd party
3. subquery?
It's nested SQL, so I'll probably have the same problem as option 1.

Hope I explained my situation clearly and please give me some advices!

Thanks a ton!!!
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 15 Apr 2009 at 8:08am
I am sure that you need permission to add stored proc, depends if the 3rd party will let you add.  I have worked with some that barely let you read ;)
 
Pushing isn't so bad.  The biggest plus, is that the report no longer cares about database connections, you just give it the data.  All of my reports run off of XML datasets, which is the push method, and I no longer need to worry about the DSN connection being changed, or the database being used is not the one specified, the app gets to take care of all of that.
 
By sub query, do you mean a subreport?  If so, I try to stay away from them as they multiply the hits to the database (though with a push, there is only 1 hit to the database)
 
HTH
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.