I have a problem passing Where clau

[week]

I have a problem passing Where clause that is going to be used as part of the query for the Crystal Report. I had to add a new table to the Crystal report. If you look at the link, it has the new table (TB_INVOICES) linked to one of the pre-existing table (TB_COMPONENTS.) But the Show SQL from Crystal says the following (TB_INVOICES doesn't show)

SELECT
"TT_SPEC_LIST"."TTSL_CLASS_DESC", "TB_COMPONENTS"."COMP_REF_NO", "TB_PHOTOS"."PHOT_STOCK_ID", "TB_PHOTOS"."PHOT_CREDIT", "TB_PHOTOS"."PHOT_DESC", "TB_PHOTOS"."PHOT_BW_FLAG", "TB_COMPONENT_ATTRIBUTES"."COMA_DESC", "TB_SUBMISSIONS"."SBMS_VENDOR_SUB_CODE",
(SELECT "TB_COMPONENT_ATTRIBUTES"."COMA_DESC"
FROM "TB_COMPONENT_ATTRIBUTES"
WHERE "TB_COMPONENT_ATTRIBUTES"."COMA_CODE" = "TB_COMPONENTS"."COMP_ATTRIBUTE_3_FK&quot
FROM
"HMBDBA"."TB_COMPONENTS" "TB_COMPONENTS", "HMBDBA"."TB_COMPONENT_ATTRIBUTES" "TB_COMPONENT_ATTRIBUTES", "HMBDBA"."TB_PHOTOS" "TB_PHOTOS", "HMBDBA"."TT_SPEC_LIST" "TT_SPEC_LIST", "HMBDBA"."TB_SUBMISSIONS" "TB_SUBMISSIONS"
WHERE
"TB_COMPONENTS"."COMP_ATTRIBUTE_3_FK" = "TB_COMPONENT_ATTRIBUTES"."COMA_CODE" (+) AND "TB_COMPONENTS"."COMP_PHOTO_FK" = "TB_PHOTOS"."PHOT_CODE" AND "TB_COMPONENTS"."COMP_CODE" = "TT_SPEC_LIST"."TTSL_SPEC_CODE" AND "TB_PHOTOS"."PHOT_SUBMISSION_FK" = "TB_SUBMISSIONS"."SBMS_CODE" (+) AND UPPER(TTSL_SCST_ABBR) = 'APPROVED' AND PHOT_STOCK_TYPE_FK (+) = 150
ORDER BY
COMP_REF_NO ASC

With this query though, if I pass additional Where clause looks like below, it will attatch to the end of existing where clause and it works fine. Brings the correct amount of data.
and {TT_SPEC_LIST.TTSL_LOGON_ID} = 'xxxx_xxxxx'
AND {TB_PHOTOS.PHOT_PERSONNEL_FK} = 81306

But If I pass different Query like
AND {TT_SPEC_LIST.TTSL_CLASS_DESC} NOT IN ('New', '2ndU', '2ndU-Alt')

Or
AND {TB_INVOICES.INVS_CODE} IS NOT NULL

it errors out with -2147191854 "The remaining text does not appear to be part of the formula."

Does anybody have any clue, why my where clause wouldn't work? Of course, If I run the query through SQL analyzer or Interdev, it works fine..

Thanks for your help in advance!

 

[prasadalone]

hey if u r using formula try to end each line with ";"
it will solve the problem..

send me reply at sawantprasad2001@yahoo.com if u able to solve the problem

thanks

prasad

 

[week]

I couldn't really give a good try in putting ";" since the report had some other problems. Instead of messing with all the problems, I came up with some other work around logic and it works fine now.

Sorry, I couldn't tell you if your suggestion worked or not...thanks for the input though.