Incorporating record selection into an SQL Command 1

[mangledbabyducks]

I am working in Crystal Reports 10 on an Interbase 7 database thru an EasySofy ODBC connection. This is my first attempt in trying to use an SQL command in a report. I've got all my record selection criteria into my SQL command except for one piece.

I am trying to include the following record selection from Crystal Report 10:

((Uppercase ({?ReportType}) = "C" AND {PROFILE_CUST.INTERFACEID(1)} = Uppercase ({?RequiredID}))
OR
(Uppercase ({?ReportType}) = "P" AND
ToText ({INVOICE.CLIENT_LINKNO}, 0, "") = Uppercase
({?RequiredID}))
OR
(Uppercase ({?ReportType}) = "A" AND true))

into this SQL Command:

SELECT BOOKING.TOTALFARE, INVOICE.INVOICETYPE_LINKCODE, INVOICE.ISSUEDATE, TRAVELCATEGORY.TRAVELCATEGORY, TRAVELCATEGORY.TRAVELCATEGORYNO, INVOICE.INVOICENO, INVOICE.INVOICENUMBER, PROFILE_VEND.INTERFACEID, PROFILE_CUST.NAME, BOOKING.RETURNDATE, BOOKING.DEPARTDATE, SUBMITTO.SUBMITTOCODE, TRAVELTYPE.TRAVELTYPENO, INVOICE.CLIENT_LINKNO, PROFILE_CUST.INTERFACEID, BOOKING.VENDORPAYSTATUS_LINKCODE, BRANCH.BRANCHNO, BRANCH.NAME, INVOICE.BRANCH_LINKNO, PROFILE_VEND.NAME
FROM INVOICE INVOICE
LEFT OUTER JOIN BOOKING BOOKING ON INVOICE.INVOICENO=BOOKING.INVOICE_LINKNO
LEFT OUTER JOIN BRANCH BRANCH ON INVOICE.BRANCH_LINKNO=BRANCH.BRANCHNO
LEFT OUTER JOIN PROFILE PROFILE_VEND ON BOOKING.VENDOR_LINKNO=PROFILE_VEND.PROFILENO
LEFT OUTER JOIN TRAVELTYPE TRAVELTYPE ON BOOKING.TRAVELTYPE_LINKNO=TRAVELTYPE.TRAVELTYPENO
LEFT OUTER JOIN SUBMITTO SUBMITTO ON BOOKING.SUBMITTO_LINKCODE=SUBMITTO.SUBMITTOCODE
LEFT OUTER JOIN TRAVELCATEGORY TRAVELCATEGORY ON TRAVELTYPE.TRAVELCATEGORY_LINKNO=TRAVELCATEGORY.TRAVELCATEGORYNO
LEFT OUTER JOIN PROFILE PROFILE_CUST ON PROFILE_CUST.PROFILENO=INVOICE.CLIENT_LINKNO
WHERE TRAVELCATEGORY.TRAVELCATEGORYNO IN (1,2,3)
AND ({?Branch} = 1000 OR {?Branch} = INVOICE.BRANCH_LINKNO)
AND
BOOKING.VENDORPAYSTATUS_LINKCODE<>'V' AND
(INVOICE.INVOICETYPE_LINKCODE='R' OR INVOICE.INVOICETYPE_LINKCODE='S') AND
INVOICE.ISSUEDATE BETWEEN {?Period1 Start} and {?Period1 End}
ORDER BY TRAVELCATEGORY.TRAVELCATEGORY

Thanks for all your previous answers and and the answer to this question (I hope)!!

MBD

 

[lbass]

Try using 'ucase' instead of uppercase', and using single quotes. Also, for a string parameter defined within the command, I think you have to enclose the parameter in single quotes.

Also, it looks like you have {?RequiredID} set up as a string. I don't understand how what is apparently a number ({INVOICE.CLIENT_LINKNO}) could ever match a parameter that requires uppercase, as there is no uppercase for numbers. If {?RequiredID} can appear with digits or letters, then it would make sense, although uppercase wouldn't be required for that part of the formula. Anyway, try:

(Ucase('{?ReportType}') = 'C' AND
{PROFILE_CUST.INTERFACEID(1)} = Ucase('{?RequiredID}'))
OR
(Ucase ({?ReportType}) = 'P' AND
{fn Convert({INVOICE.CLIENT_LINKNO},SQL_VARCHAR)} = Ucase('{?RequiredID}'))
OR
(Ucase ('{?ReportType}') = 'A')

-LB

 

[synapsevampire]

Go to Database->Show SQL Query in Crystal and you should see what is currently being passed to the database in the approriate syntax, which you can copy and paste.

No idea what LB means by the single quotes, it appears that you used single quotes in your query. If you're moving the filtering criteria from Crystal to an add command query, then you need to know the Interbase SQL, and this is NOT an Interbase SQL forum.

Also check to make sure that your database is case sensitive, many have settings for this and you may not require it.

-k

 

[mangledbabyducks]

Thanks LB

Added the following and it worked great:

((Upper ('{?ReportType}') = 'C' AND PROFILE_CUST.INTERFACEID = Upper ('{?RequiredID}'))
OR
(Upper ('{?ReportType}') = 'P' AND Cast ({INVOICE.CLIENT_LINKNO} as VARCHAR (32)) = Upper ('{?RequiredID}'))
OR
(Upper ('{?ReportType}') = 'A'))

and, as you can see SV, I converted the SQL to make it "InterBase compatible".

MBD

 

[synapsevampire]

I'd suggest trying an Interbase forum for such things, as this is a SQL qustion, not Crystal.

As my previous post mentioned, when you're using the record selection formula, it *should* generate the proper SQL within the Database->Show SQL Query, so when you're converting future Crystal syntax to your SQL syntax, use that cheat.

-k

 

[lbass]

SV-

This was only partly an Interbase question. The other aspect of the problem related to the use of command parameters in the command query.

-LB

 

[synapsevampire]

Ahhh, so I see, LB.

Didn't think that was a problem area, guess it can be.

-k