Substituting generated SQL with manual written SQL

[NBVC]

I have a complex Crystal Report that I created using the SQL created automatically through the various manipulations I did in the report.

I need to remove one item from the defaulted "Select" statement. The item is used in a formula and not directly in the report. Is there a way to remove it from the generated Select statement.

If not, is there a way to easily replace the generated SQL with an added Command object SQL without having to re-do the report from scratch?

 

[Madawc]

If it's in a formula you should be able to remove it. If it has a use in the report in its original form, then turn it into two formulas.

But if you need your own SQL, this is known as a Stored Procedure not hard.
[ul]
[li]Within DataBase Expert, choose the database[/li]
[li]Choose 'Add Command'. Open the worksheet[/li]
[li]Paste the code from 'Query'. This includes comments etc.[/li]
[li]The command can also be edited.[/li]
[li]Once added, the fields are just like any other table.[/li]
[/ul]

You can link to datasets but this is inefficient, better to collect all of the data in the SQL.

PS. This is based on Crystal 2008. It helps to give your Crystal version - 8, 8.5, 9, 10, 11, 2008 or whatever. Methods sometimes change between versions, and higher versions have extra options.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[NBVC]

Hi Madawc,

Thanks for the reply.

I have a formula call fla_unit_price written as:

if isnull({PART_UNITS_CONV.CONVERSION_FACTOR}) or {PART_UNITS_CONV.FROM_UM}={PURC_ORDER_LINE.PURCHASE_UM}then
  {PURC_ORDER_LINE.UNIT_PRICE}
else
   {PURC_ORDER_LINE.UNIT_PRICE}*{PURC_ORDER_LINE.USER_ORDER_QTY}/{RECEIVER_LINE.RECEIVED_QTY}

It is the field PART_UNITS_CONV.CONVERSION_FACTOR that is in this formula that is causing issues with my formula results.

My auto-general SQL looks like this:

SELECT DISTINCT "RECEIVER"."PURC_ORDER_ID", "RECEIVER"."RECEIVED_DATE", "PURC_ORDER_LINE"."PART_ID", "PART"."DESCRIPTION", "PART"."STOCK_UM", "VENDOR"."NAME", "RECEIVER_LINE"."RECEIVED_QTY", "PURC_ORDER_LINE"."UNIT_PRICE", "PURCHASE_ORDER"."CURRENCY_ID", "PURC_ORDER_LINE"."USER_ORDER_QTY", "PURC_ORDER_LINE"."USER_10", "PURCHASE_ORDER"."BUYER", "PURC_ORDER_LINE"."LINE_NO", "VENDOR"."ID", "PURCHASE_ORDER"."ID", "RECEIVER"."ID", "PURC_ORDER_LINE"."GL_EXPENSE_ACCT_ID", "PURC_ORDER_LINE"."PRODUCT_CODE", "PURC_ORDER_LINE"."SERVICE_ID", "PART"."PRODUCT_CODE", "RECEIVER"."USER_ID", "PURC_ORDER_LINE"."MFG_NAME", "PART_UNITS_CONV"."CONVERSION_FACTOR", "RECEIVER"."CREATE_DATE", "PURC_ORDER_LINE"."PURCHASE_UM", "PART_UNITS_CONV"."FROM_UM"
 FROM   "SYSADM"."RECEIVER_LINE" "RECEIVER_LINE", "SYSADM"."PURC_ORDER_LINE" "PURC_ORDER_LINE", "SYSADM"."RECEIVER" "RECEIVER", "SYSADM"."PART" "PART", "SYSADM"."PURCHASE_ORDER" "PURCHASE_ORDER", "SYSADM"."VENDOR" "VENDOR", "SYSADM"."PART_UNITS_CONV" "PART_UNITS_CONV"
 WHERE  (("RECEIVER_LINE"."PURC_ORDER_ID"="PURC_ORDER_LINE"."PURC_ORDER_ID") AND ("RECEIVER_LINE"."PURC_ORDER_LINE_NO"="PURC_ORDER_LINE"."LINE_NO")) AND (("RECEIVER_LINE"."RECEIVER_ID"="RECEIVER"."ID") AND ("RECEIVER_LINE"."PURC_ORDER_ID"="RECEIVER"."PURC_ORDER_ID")) AND ("PURC_ORDER_LINE"."PART_ID"="PART"."ID" (+)) AND ("PURC_ORDER_LINE"."PURC_ORDER_ID"="PURCHASE_ORDER"."ID") AND ("PURCHASE_ORDER"."VENDOR_ID"="VENDOR"."ID") AND (("PART"."ID"="PART_UNITS_CONV"."PART_ID" (+)) AND ("PART"."STOCK_UM"="PART_UNITS_CONV"."FROM_UM" (+))) AND ("PART_UNITS_CONV"."CONVERSION_FACTOR" IS  NULL  OR "PART_UNITS_CONV"."CONVERSION_FACTOR"<>1) AND ("RECEIVER"."CREATE_DATE">={ts '2013-05-10 00:00:00'} AND "RECEIVER"."CREATE_DATE"<{ts '2013-05-11 00:00:00'})

I want to remove "CONVERSION_FACTOR" from this, so that I don't get duplicates.

If I copy the code and paste it in the "Add command" feature, how do I remove the old auto generated code?


Btw I am on version 9. Sorry for not specifying.

 

[Madawc]

Once added, code can be edited.

You also need to remove the datasets and their record selection.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP