7 Replies Latest reply on Aug 30, 2006 8:32 AM by Coldfusionstudent

    join/from/where/ combine queries

    Coldfusionstudent Level 1
      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
        • 1. Re: join/from/where/ combine queries
          paross1 Level 2
          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
          • 2. Re: join/from/where/ combine queries
            Coldfusionstudent Level 1
            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
            • 3. join/from/where/ combine queries
              Coldfusionstudent Level 1
              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



              • 4. join/from/where/ combine queries
                Coldfusionstudent Level 1
                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:

                • 5. Re: join/from/where/ combine queries
                  paross1 Level 2
                  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
                  • 6. Re: join/from/where/ combine queries
                    paross1 Level 2
                    ...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
                    • 7. Re: join/from/where/ combine queries
                      Coldfusionstudent Level 1
                      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