Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Alternative Ways of Joining Tables 1

Status
Not open for further replies.

dannyb29

Technical User
Nov 10, 2010
90
GB
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

 
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.
 
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'
 
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'
 
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'
 
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'
 
Dohh..School boy error!
Thank you so much!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top