4 Replies Latest reply on Sep 11, 2006 9:01 AM by Coldfusionstudent

    join/from/where/ combine queries2

    Coldfusionstudent Level 1
      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
      ------------------------------------------------------------------------------------------ --------------

      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'
        • 1. Re:  join/from/where/ combine queries2
          draves
          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')
          • 2. Re:  join/from/where/ combine queries2
            Coldfusionstudent Level 1
            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
            • 3. Re:  join/from/where/ combine queries2
              Coldfusionstudent Level 1

              apologies

              this is the error

              Error Executing Database Query.
              ORA-00911: invalid character

              The error occurred in D:\export\htdocs\CTS_RPTSTEST\HPOZFORMs4.2\act_search.cfm: line 38

              Called from D:h.cfm: 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 :



              --------------------------------------------------------------------------------

              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
              SQLSTATE 42000

              Resources:
              • 4. Re:  join/from/where/ combine queries2
                Coldfusionstudent Level 1
                thanks
                never mind that error it was just the in and the sapce between the field namethanks


                but the main prob is the inner join and the question below thanks


                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

                thnaks