If you are going to use OR conditions with AND conditions you
need to use parenthesis:
WHERE (TCASE.CASE_NBR Like '%CWC%' or TCASE.CASE _NBR Like
'%CWNC%')
AND (TCASE.CASE_NBR = 'DIR-2004-4269-CWC'
Or TCASE.CASE_NBR = 'DIR-2004-4269-CWNC')
But if you are really hardcoding the case number values you
are wasting time with a LIKE comparison so you could just have:
WHERE TCASE.CASE_NBR = 'DIR-2004-4269-CWC'
Or TCASE.CASE_NBR = 'DIR-2004-4269-CWNC'
You might get a more efficient response if not atleast more
readable code with:
WHERE TCASE.CASE_NBR in
('DIR-2004-4269-CWC','DIR-2004-4269-CWNC')
thanks
hmm
there is an error
Error Executing Database Query.
ORA-00911: invalid character
The error occurred in D:.cfm: line 38
Called from line 38
36 : taplc.aplc_id
37 : FROM TLA_PROP INNER JOIN tref_plan_area ON
tla_prop.plan_area_nbr = tref_plan_area.plan_area_NBR INNER JOIN
TLOC ON TLA_PROP.PROP_ID = TLOC.LOC_ID INNER JOIN TAPLC ON
TLOC.APLC_ID = TAPLC.APLC_ID INNER JOIN TCASE ON TAPLC.APLC_ID =
TCASE.APLC_ID
38 : WHERE TCASE.CASE_NBR Like '%CWC%' or TCASE.CASE _NBR
Like '%CWNC%')and TCASE.CASE_NBR = '#form.caseNbr#'
39 : </cfquery>
40 :
--------------------------------------------------------------------------------
also,
what about the inner join question and question (3) below:
SQL SELECT TLA_PROP.PIN, TLA_PROP.ASSR_PRCL_NBR,
TCASE.CASE_NBR, TLA_PROP.STR_NBR, TLA_PROP.STR_NBR_RNG_END,
TLA_PROP.STR_FRAC_NBR, Tref_plan_area.plan_area_desc,
TLA_PROP.STR_FRAC_NBR_RNG_END, TLA_PROP.STR_DIR_CD,
TLA_PROP.STR_NM, TLA_PROP.STR_SFX_CD, TLA_PROP.STR_SFX_DIR_CD,
TLA_PROP.STR_UNIT_TYP_CD, TLA_PROP.UNIT_NBR,
TLA_PROP.UNIT_NBR_RNG_END, TLA_PROP.ZIP_CD, TLA_PROP.ZIP_CD_SFX,
TLA_PROP.CNCL_DIST_NBR, TLA_PROP.PLAN_AREA_NBR,
TLA_PROP.ZONE_REG_CD, TAPLC.PROJ_DESC_TXT, TCASE.CASE_ID,
TCASE.CASE_NBR, taplc.aplc_id FROM TLA_PROP INNER JOIN
tref_plan_area ON tla_prop.plan_area_nbr =
tref_plan_area.plan_area_NBR INNER JOIN TLOC ON TLA_PROP.PROP_ID =
TLOC.LOC_ID INNER JOIN TAPLC ON TLOC.APLC_ID = TAPLC.APLC_ID INNER
JOIN TCASE ON TAPLC.APLC_ID = TCASE.APLC_ID WHERE TCASE.CASE_NBR
Like '%CWC%' or TCASE.CASE _NBR Like '%CWNC%')and TCASE.CASE_NBR =
'DIR-2004-4242-CWNC'
DATASOURCE
VENDORERRORCODE 911
SQLSTATE 42000
Resources
Hello ,
I need help again on the inner join/and consitions in the
where clause
I have these 2 tables tcase and tcase_req where there common
field Is the case_id.
tcase is the parent table and the tcase_req is the child
table.
(1)I wanted to add this to the from clause using the inner
join table . what do I do and where do I put it
(2)I then need to put a condition in the where clause to
replace
WHERE TCASE.CASE_NBR Like '%HPOZ%'
AND TCASE.CASE_NBR = 'DIR-2004-4269-HPOZ-CCMP'
by
WHERE TCASE.CASE_NBR Like '%CWC%' or TCASE.CASE _NBR Like
'%CWNC%'
AND TCASE.CASE_NBR = 'DIR-2004-4269-CWC'
Or TCASE.CASE_NBR = 'DIR-2004-4269-CWNC'
is this efficient??
(3)To a tcase_req
Suffix_id are equal to = cwc and cwnc ( in the tcase_req
table)(Suffix_id is the field that cintains the suffix cwc and cwnc
for the tcase_req)
Where do I add all this to?
Here is the current query,below: THANKS
1 Correct answer
4
Replies
AdChoices