Alternative Ways of Joining Tables 1

[dannyb29]

Hi All,

I am using Crystal Reports 2008 and I was wondering whether there is a function that enables you to join tables using raw SQl. As far as I can see, I can only use the Database Expert which is quite limiting in some ways.

Can anyone shed any light?

Dannyb

 

[hilfy]

Instead of using tables in your report, you could use a Command, where you'll write the full SQL select statement to provide the data for your report. Commands are not available for all databases - in particular they're not available for non-server-based databases like Paradox and FoxPro. When you open your connection in the Database Explorer you will see an option to "Add Command" if this is available for your database type.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[dannyb29]

Thank you very much for that. I have tried writing a piece of code and it is throwing up errors. Am I missing something?

Here is my code.

SELECT
FROM [OCMPhase2_Report].[dbo].[AVBTransactionLog]
FULL OUTER JOIN [OCMPhase2_Report].[dbo].[AVBTransactionLogDetails] ON [OCMPhase2_Report].[dbo].[AVBTransactionLogDetails].TxnId = [OCMPhase2_Report].[dbo].[AVBTransactionLog].TxnId
LEFT OUTER JOIN [OCMPhase2_Report].[dbo].[Products] ON [OCMPhase2_Report].[dbo].[AVBTransactionLogDetails].ProductCode = [OCMPhase2_Report].[dbo].[Products].ProductCode
AND [OCMPhase2_Report].[dbo].[Products].[ValidFromDatetime] <= [OCMPhase2_Report].[dbo].[AVBTransactionLog].[ActionDateTime]
AND [OCMPhase2_Report].[dbo].[Products].[ValidToDatetime] >= [OCMPhase2_Report].[dbo].[AVBTransactionLog].[ActionDateTime]
WHERE AVBUserId = '2698'
and ActionDateTime > '2011-12-10 00:00:00'
and ActionDateTime < '2011-12-10 23:59:59'

 

[kray4660]

What are you selecting? You need as least one field.

 

[dannyb29]

Sorry I forgot to add the first part, but it is still throwing up errors.

SELECT
" AVBTransactionLog"."ActionDateTime",
" AVBTransactionLog"."RecordType",
" AVBTransactionLog".."SuccessFlag",
" AVBTransactionLog".."OriginatingTxnId",
" AVBTransactionLog"."AVBUserId",
" AVBTransactionLog"."AVBMachineCode",
" AVBTransactionLog"."PVN",
" AVBTransactionLog"."PVN2",
" AVBTransactionLog"."RetailServiceID",
" AVBTransactionLog"."SalesValue",
" AVBTransactionLog"."ServiceCentreId",
" AVBTransactionLog"."HeadCode",
" AVBTransactionLog"."Description",
" AVBTransactionLog"."RetailPrice",
" AVBTransactionLog"."CostPrice",

FROM "AVBTransactionLog"
FULL OUTER JOIN "AVBTransactionLogDetails" ON

"AVBTransactionLogDetails"."TxnId" = "AVBTransactionLog"."TxnId"
LEFT OUTER JOIN "Products" ON "AVBTransactionLogDetails"."ProductCode" = "Products"."ProductCode"

AND "Products"."ValidFromDatetime" <="AVBTransactionLog"."ActionDateTime"
AND "Products"."ValidToDatetime" >= "AVBTransactionLog"."ActionDateTime"
"AVBTransactionLog"."AVBUserId" = '2698'
and " AVBTransactionLog"."ActionDateTime" > '2011-12-10 00:00:00'
and " AVBTransactionLog"."ActionDateTime" < '2011-12-10 23:59:59'

 

[dannyb29]

Sorry, its this set of code:


SELECT
"AVBTransactionLog"."ActionDateTime",
"AVBTransactionLog"."RecordType",
"AVBTransactionLog"."SuccessFlag",
"AVBTransactionLog"."OriginatingTxnId",
"AVBTransactionLog"."AVBUserId",
"AVBTransactionLog"."AVBMachineCode",
"AVBTransactionLog"."PVN",
"AVBTransactionLog"."PVN2",
"AVBTransactionLog"."RetailServiceID",
"AVBTransactionLog"."SalesValue",
"AVBTransactionLog"."ServiceCentreId",
"AVBTransactionLog"."HeadCode",
"AVBTransactionLog"."Description",
"AVBTransactionLog"."RetailPrice",
"AVBTransactionLog"."CostPrice"

FROM "AVBTransactionLog"
FULL OUTER JOIN "AVBTransactionLogDetails" ON

"AVBTransactionLogDetails"."TxnId" = "AVBTransactionLog"."TxnId"
LEFT OUTER JOIN "Products" ON "AVBTransactionLogDetails"."ProductCode" = "Products"."ProductCode"

AND "Products"."ValidFromDatetime" <="AVBTransactionLog"."ActionDateTime"
AND "Products"."ValidToDatetime" >= "AVBTransactionLog"."ActionDateTime"
"AVBTransactionLog"."AVBUserId" = '2698'
and "AVBTransactionLog"."ActionDateTime" > '2011-12-10 00:00:00'
and "AVBTransactionLog"."ActionDateTime" < '2011-12-10 23:59:59'

 

[kray4660]

What kind of errors are you getting?

I noticed that you do not have a AND or an OR between
"Products"."ValidToDatetime" >=AVBTransactionLog"."ActionDateTime" ???
"AVBTransactionLog"."AVBUserId" = '2698'

 

[dannyb29]

Dohh..School boy error!
Thank you so much!