join/from/where/ combine queries

[Coldfusionstudent]

Hello I have this query in my cf program

First query
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,
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 ((TAPLC INNER JOIN TCASE ON TAPLC.APLC_ID = TCASE.APLC_ID) INNER JOIN TLOC ON TAPLC.APLC_ID = TLOC.APLC_ID) ON TLA_PROP.PROP_ID = TLOC.LOC_ID
WHERE TCASE.CASE_NBR Like '%HPOZ%' and TCASE.CASE_NBR = '#form.caseNbr#'


'#form.caseNbr#' is the input field on my input box.

I am a beginner and I am not used to joins and joins in the from class

Second query
In toad I use this query
Select
tcase.case_id
,Tcase.case_NBR
,tcntct.CNTCT_ID as ownerInformation
,tcntct.CO_BUSN_NM, tcntct.FRST_NM, tcntct. LST_NM
,tcntct_owner.cntct_id as repInformation
,tcntct_owner.CO_BUSN_NM
, tcntct_owner. FRST_NM
, tcntct_owner.LST_NM
,Tref_plan_area.plan_area_desc
,tcase_req.case_req_id
,tcase_req.case_req_typ_cd
,tcase_req.sys_user_id
,tla_prop.pin
,tla_prop.cncl_dist_nbr
,tla_prop.PLAN_AREA_NBR
,tla_prop.str_nbr
,tla_prop.STR_FRAC_NBR
,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.ZIP_CD
,tla_prop.ASSR_PRCL_NBR
,TLA_PROP.ZONE_REG_CD
from
tloc, tla_prop , tcase, tref_plan_area, tcase_req,tcntct_aplc, tcntct, tcntct_aplc tcntct_aplc_owner, tcntct tcntct_owner /*tcntct_tla_prop,tcntct*/

where
tla_prop.prop_id = tloc.loc_id
and
tla_prop.plan_area_nbr = tref_plan_area .plan_area_NBR
and
tloc.aplc_id = tcase.aplc_id
and
tcase.case_id = tcase_req .case_id
and
tcase_req.CASE_req_typ_CD like '%HPOZ%'
/*

and

tla_prop.prop_id = tcntct_tla_prop.PROP_ID(+)

and

tcntct_tla_prop.CNTCT_ID = tcntct.CNTCT_ID(+)

*/

and

tcase.aplc_id = tcntct_aplc.APLC_ID(+)
and

tcntct_aplc.cntct_id = tcntct.cntct_id(+)
and

(tcntct_aplc.CNTCT_TYP_CD = 'A' or tcntct_aplc .cntct_typ_cd is null)
and

tcase.aplc_id = tcntct_aplc_owner .aplc_id(+)
and

tcntct_aplc_owner.cntct_id = tcntct_owner.cntct_id (+)
and

(tcntct_aplc_owner.cntct_typ_cd = 'R' or tcntct_aplc_owner.cntct_typ_cd is null)
/*

group by

tcase.case_id,

Tcase.case_NBR,

Tref_plan_area.plan_area_desc

,tcase_req.case_req_id

,tcase_req.case_req_typ_cd

,tcase_req.sys_user_id

,tla_prop.pin

,tla_prop.cncl_dist_nbr

,tla_prop.PLAN_AREA_NBR

,tla_prop.str_nbr

,tla_prop.STR_FRAC_NBR

,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.ZIP_CD

,tla_prop.ASSR_PRCL_NBR,

tcntct.CO_BUSN_NM
, tcntct.FRST_NM
, tcntct.LST_NM,

tcntct_owner.CO_BUSN_NM
, tcntct_owner.FRST_NM
, tcntct_owner.LST_NM

*/

order by

to get
Tref_plan_area.plan_area_desc or simply get plan_area_desc. This is done through a join tla_prop.plan_area_nbr = tref_plan_area .plan_area_NBR .

I am still using the first query ( the very first query above) but there are joins in the from clause.
My question here is I am still trying to get plan_area_desc and want to incorporate some syntax from the second query to the first query.

What I did that was a mistake was ( error)

Added the table from and put this tla_prop.plan_area_nbr = tref_plan_area .plan_area_NBR .
In the where clause. It did not work. Can any one help me on how I can still get the tref_plan_area .plan_area_NBR .

What do I add to the first query
Thanks

[paross1]

Did you try adding something like this to your first query?

INNER JOIN tref_plan_area ON tla_prop.plan_area_nbr = tref_plan_area.plan_area_NBR

plus the desired fields from this table in your SELECT?

Phil

[Coldfusionstudent]

to get the plan_area_nbr
i need to join
tla_prop.plan_area_nbr = tref_plan_area .plan_area_NBR

but to get the
tla_prop.plan_area_nbr
i need to join
tla_prop.prop_id = tloc.loc_id

but to get the
tloc.loc_id
i need to join
tloc.aplc_id = tcase.aplc_id


like the joins below in the 2nd query


where
tla_prop.prop_id = tloc.loc_id
and
tla_prop.plan_area_nbr = tref_plan_area .plan_area_NBR
and
tloc.aplc_id = tcase.aplc_id
and
tcase.case_id = tcase_req .case_id

again ultimately, i need the Tref_plan_area.plan_area_desc

is this all i need to join in the from clause in the first query to get Tref_plan_area.plan_area_desc


INNER JOIN tref_plan_area ON tla_prop.plan_area_nbr = tref_plan_area.plan_area_NBR
as you indicated

thanks
again

[Coldfusionstudent]

I have added to this query below. But I got this error shown below. Can you help me with my query ??

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 ((TAPLC INNER JOIN TCASE ON TAPLC.APLC_ID = TCASE.APLC_ID) INNER JOIN TLOC ON TAPLC.APLC_ID = TLOC.APLC_ID) ON TLA_PROP.PROP_ID = TLOC.LOC_ID INNER JOIN tref_plan_area ON tla_prop.plan_area_nbr = tref_plan_area.plan_area_NBR


WHERE TCASE.CASE_NBR Like '%HPOZ%' and TCASE.CASE_NBR = '#form.caseNbr#'


In the select clause I have added
Tref_plan_area.plan_area_desc

And in the


From clause I have added
INNER JOIN TLOC ON TAPLC.APLC_ID = TLOC.APLC_ID) ON TLA_PROP.PROP_ID = TLOC.LOC_ID

I got this error


Error Executing Database Query.
ORA-00923: FROM keyword not found where expected

The error occurred in --------.cfm: line 10
8 : <!----TLA_PROP INNER JOIN ((TAPLC INNER JOIN TCASE ON TAPLC.APLC_ID = TCASE.APLC_ID) INNER JOIN TLOC ON TAPLC.APLC_ID = TLOC.APLC_ID) ON TLA_PROP.PROP_ID = TLOC.LOC_ID----->9 : 10 : WHERE TCASE.CASE_NBR Like '%HPOZ%' and TCASE.CASE_NBR = '#form.caseNbr#'11 : </cfquery>12 :

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 ((TAPLC INNER JOIN TCASE ON TAPLC.APLC_ID = TCASE.APLC_ID) INNER JOIN TLOC ON TAPLC.APLC_ID = TLOC.APLC_ID) ON TLA_PROP.PROP_ID = TLOC.LOC_ID INNER JOIN tref_plan_area ON tla_prop.plan_area_nbr = tref_plan_area.plan_area_NBR WHERE TCASE.CASE_NBR Like '%HPOZ%' and TCASE.CASE_NBR = 'DIR-2004-4269-HPOZ-CCMP'
DATASOURCE
VENDORERRORCODE 923
SQLSTATE 42000

[Coldfusionstudent]

I have added to this query below. But I got this error shown below. Can you help me with my query ??

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 ((TAPLC INNER JOIN TCASE ON TAPLC.APLC_ID = TCASE.APLC_ID) INNER JOIN TLOC ON TAPLC.APLC_ID = TLOC.APLC_ID) ON TLA_PROP.PROP_ID = TLOC.LOC_ID INNER JOIN tref_plan_area ON tla_prop.plan_area_nbr = tref_plan_area.plan_area_NBR


WHERE TCASE.CASE_NBR Like '%HPOZ%' and TCASE.CASE_NBR = '#form.caseNbr#'


In the select clause I have added
Tref_plan_area.plan_area_desc

And in the


From clause I have added
INNER JOIN TLOC ON TAPLC.APLC_ID = TLOC.APLC_ID) ON TLA_PROP.PROP_ID = TLOC.LOC_ID

I got this error


Error Executing Database Query.
ORA-00923: FROM keyword not found where expected

The error occurred in --------.cfm: line 10
8 : <!----TLA_PROP INNER JOIN ((TAPLC INNER JOIN TCASE ON TAPLC.APLC_ID = TCASE.APLC_ID) INNER JOIN TLOC ON TAPLC.APLC_ID = TLOC.APLC_ID) ON TLA_PROP.PROP_ID = TLOC.LOC_ID----->9 : 10 : WHERE TCASE.CASE_NBR Like '%HPOZ%' and TCASE.CASE_NBR = '#form.caseNbr#'11 : </cfquery>12 :

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 ((TAPLC INNER JOIN TCASE ON TAPLC.APLC_ID = TCASE.APLC_ID) INNER JOIN TLOC ON TAPLC.APLC_ID = TLOC.APLC_ID) ON TLA_PROP.PROP_ID = TLOC.LOC_ID INNER JOIN tref_plan_area ON tla_prop.plan_area_nbr = tref_plan_area.plan_area_NBR WHERE TCASE.CASE_NBR Like '%HPOZ%' and TCASE.CASE_NBR = 'DIR-2004-4269-HPOZ-CCMP'
DATASOURCE
VENDORERRORCODE 923
SQLSTATE 42000

Please try the following:

[paross1]

Does this work, by any chance?

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 ((TAPLC INNER JOIN TCASE ON TAPLC.APLC_ID = TCASE.APLC_ID)
INNER JOIN TLOC ON TAPLC.APLC_ID = TLOC.APLC_ID) ON TLA_PROP.PROP_ID = TLOC.LOC_ID
WHERE TCASE.CASE_NBR Like '%HPOZ%'
AND TCASE.CASE_NBR = 'DIR-2004-4269-HPOZ-CCMP'


Phil

[paross1 · Accepted solution]

...or perhaps this?

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 '%HPOZ%'
AND TCASE.CASE_NBR = 'DIR-2004-4269-HPOZ-CCMP'

Phil

[Coldfusionstudent]

thanks
it works great!!!!

just dont know if i really needed the parenthesis on the inner join part.

anyway thanks
you can reply again if you want to anser the ( parenthesis on the inner join part.
)

thanks
again