Command Object in Crystal Reports 3

[Alundil]

Help....hehe

CR version: 9 Pro
DB: Oracle 8i

Currently stuck trying pull data displaying preference cards for a particular doctor and all of the materials referenced by a particular preference card.
Tables/fields in use: Person.PersonID,Person.Full_Name,Card.Card_ID,Card.SurgeonID.

I've not even begun to try and get the materials yet as I have hit the road block called poor db design.

I should be able to join the tables in Crystal based on the Person.Person_ID field and the Card.Surgeon_ID field. Unfortunately, the Person_ID field is a string value and the Surgeon_ID field is a number value. This is a totally vendor maintained db so creating a view or otherwise modifying the database is not really an option.

Is there something that I can do to pull/link those tables/fields in Crystal?

Thank you,

Andy

 

[vidru]

Using a Command, you can try using the Oracle To_Number function to convert the string to a number in the JOIN condition. Something like this:

SELECT P.PersonID, P.Full_Name, C.Card_ID, C.SurgeonID
FROM Person P
INNER JOIN Card C ON C.Surgeon_ID = To_Number(P.PersonID)

-dave

 

[Alundil]

OK, V. I tried adding that to a Command object in a blank report -- simply edited the names to match my data sources like so:

//
SELECT "Person"."Person_ID","Person"."Full_Name","Card"."Card_ID","Card"."SurgeonID"
  FROM "Person"
  INNER JOIN "Card" ON "Card"."Surgeon_ID" = To_Number("Person"."Person_ID")
//

I received the following error though when I tried to click OK

'Query Engine Error: 'ORA-00933: SQL Command not properly ended'

followed by

Not Supported.
Details: Fail to execute SQL statement. OCI Call: OCIStmtExecute


Question: do I need to declare the db name in the From section like: FROM "SISI"."PERSON" ? Also, when creating the command object itself, there is a section to the right in the menu box for 'Parameter List' which is blank, do I need to do anything with that? If so, what?

Thanks

Andy

 

[Alundil]

I also tried adding the WHERE statment back in like so:

//
SELECT "Person"."Person_ID","Person"."Full_Name","Card"."Card_ID","Card"."SurgeonID"
  FROM "SIS"."Person"
  WHERE "CARD"."SURGEON_ID" = "PERSON"."PERSON_ID" and "CARD"."SURGEON_ID" > 0;
  INNER JOIN "SISI"."Card" ON "Card"."Surgeon_ID" = To_Number("Person"."Person_ID")
//

this time I received an error stating:
'Query Engine Error: 'ORA-00911: invalid character'

followed by:
Details: Fail to execute SQL statement. OCI Call: OCIStmtExecute

 

[vidru]

It's been a loooong time since I've used Oracle, but the syntax I gave you should be pretty close. Does Oracle require a semicolon at the end of the statement? With your WHERE clause, the statement would look like:

SELECT P.PERSON_ID, P.FULL_NAME, C.CARD_ID, C.SURGEON_ID
  FROM SIS.Person P
  INNER JOIN SISI.Card C ON C.Surgeon_ID = TO_NUMBER(P.Person_ID)
  WHERE C.SURGEON_ID > 0;

I don't see why it wouldn't work, but like I said, been a while since I've used Oracle.

-dave

 

[synapsevampire]

Hopefully finding another vendor is an option if the y don't preserve data types across foreign keys...

Oracle 8 may not allow for that syntax, try:

SELECT P.PERSON_ID, P.FULL_NAME, C.CARD_ID, C.SURGEON_ID
FROM SIS C,Person P
where
C.Surgeon_ID = cast(P.Person_ID as numeric)
and
C.SURGEON_ID > 0

Consider downloading TOAD, a freebie sql development tool for Oracle which sports a gui for building sql, once you get the desired results, you can copy/paste the sql into Crystal.

-k

 

[lbass]

It's not as efficient, but you could also place one of the tables in a subreport, create a formula that changes the string to a value, and then link the subreport to the main report using the formula and corresponding field. This method becomes more complex (requiring the use of shared variables) if you need to perform calculations that use both subreport and main report data.

-LB

 

[Alundil]

Vidru, SV, LB

Thank you all so far for the help. I've not had much success with the syntax/code examples so far. I am curious, when I look at the SQL statement for other queries that I have run against our Oracle db, the table and field names have always been completely written out. In the examples that you have posted though, you have abbreviated them. This might be a silly question brought on by over thinking things and burning brain cells, but you ARE just abbreviating for the sake of brevity and not out of any acceptable code "shorthand" right? (lord I am tired hehe)

Anywho, as for your suggestion LB, can you elucidate your idea a bit more? I am intrigued; as it would appear to be the least complex solution (since I do not intend to perform calculations between the main/subreport).

Thanks as always,

Andy

 

[lbass]

The "abbreviations" are actually alias names for the tables.

I'm a little unclear on your overall report design, so I'm not sure what your main report table should be, but let's assume that your main report uses the Card table, and that you have a group on {Card.SurgeonID}. In the subreport, you would add the Person table and then create a formula {@PersonId}:

tonumber({Person.PersonID})

Place this formula and the fields you want displayed in the detail section, and then link the subreport to the main report. In the linking screen, choose {Card.SurgeonID} to move to the right as the main report linking field, and then in the bottom right, use the dropdown list to select {@PersonID} as the subreport linking field.

Place the subreport in an inserted group header_b or a group footer of the Surgeon group. You can use the section expert to format the group header (if that's where you have placed the subreport) to "Underlay following sections" if you want the subreport to align with detail fields from the main report.

-LB

 

[synapsevampire]

Probably too many cooks here, but after you work with whichever turns you on and accomplish your requirement, do as I suggested and go get toad and you'll have a great resource at your disposal going forward.

As you may have guessed, I'm opposed to subreports because they are slow and increase maintenance while diminishing reusability, and promote taking the easy way out

-k

 

[Alundil]

OK SV - I downloaded TOAD like you mentioned (free version). I am a bit confused by the GUI of that applcation though.

Are any of the SQL snippets here something that I could paste into TOAD?

 

[madelca100]

I'm not a SQL expert, but I've played around with getting Access SQL to work in a Crystal command.


Any error message you get
"Select not found", "Invalid Character", etc.
won't really give you a clue as to what the problem is.

If you can put the code into a file and run it from Oracle SQLPlus you might get better (but probably not much better) hints as to what is wrong with the code.

Oracle doesn't want a semicolon at the end of the statement. It also wants single quotes not double.

Here is a sample of what runs in Access and the same (pretty much) code in a Crystal command (oracle sql)

Notice that SHELL_FUNDS has been changed to SHELL.FUNDS.
Also notice that the joins are done in the FROM clause in Access. (perhaps for a legitimate reason.)

It's things like this that will make one looney(er).

Access:
SELECT
SHELL_FUNDS.TRANS_DATE,
SHELL_FUNDS_TRANS_SPLIT.TRANS_TYPE_CODE,
SHELL_FUNDS_TRANS_SPLIT.AMOUNT,
SHELL_FUNDS.VOID_FLAG,
SHELL_FUNDS.COLLECTION_FLAG,
SHELL_CLAIM.CLAIM_NUMBER
FROM
(SHELL_CLAIM INNER JOIN SHELL_FUNDS ON SHELL_CLAIM.CLAIM_ID = SHELL_FUNDS.CLAIM_ID) INNER JOIN SHELL_FUNDS_TRANS_SPLIT ON SHELL_FUNDS.TRANS_ID = SHELL_FUNDS_TRANS_SPLIT.TRANS_ID
WHERE
(((SHELL_FUNDS.TRANS_DATE)>="20050101" And (SHELL_FUNDS.TRANS_DATE)<="20051231") AND ((SHELL_FUNDS.VOID_FLAG)=0) AND ((SHELL_FUNDS.COLLECTION_FLAG)=-1));


Command:
SELECT
SHELL.FUNDS.TRANS_DATE,
SHELL.FUNDS_TRANS_SPLIT.TRANS_TYPE_CODE, SHELL.FUNDS_TRANS_SPLIT.AMOUNT,
SHELL.FUNDS.VOID_FLAG,
SHELL.FUNDS.COLLECTION_FLAG,
SHELL.CLAIM.CLAIM_NUMBER
FROM
SHELL.CLAIM,
SHELL.FUNDS,
SHELL.FUNDS_TRANS_SPLIT
WHERE
SHELL.CLAIM.CLAIM_ID = SHELL.FUNDS.CLAIM_ID AND
SHELL.FUNDS.TRANS_ID = SHELL.FUNDS_TRANS_SPLIT.TRANS_ID AND
(((SHELL.FUNDS.TRANS_DATE)>='20050101' And (SHELL.FUNDS.TRANS_DATE)<='20051231') AND
((SHELL.FUNDS.VOID_FLAG)=0) AND
((SHELL.FUNDS.COLLECTION_FLAG)=-1))

Hope this helps.
mike

 

[Alundil]

Everyone - thanks for the suggestions. At this point I have completely frustrated myself trying to find a workaround for the developer's sloppiness.

I've documented what I am trying to do, and the specifics of the problem that I am experiencing with the db design and have forwarded that to the vendor/developer. I've got so many other projects not getting attention due to this (hehe). I am sure that we have all been there more times than we care to count due to issues like this.

Ah well.

I hope to hear from them within the next 1 - 2 weeks (not holding my breath though)

Andy



-- Andy