Loading ...
Sorry, an error occurred while loading the content.

Re: [SQLQueriesNoCode] SQL update help

Expand Messages
  • peterim5659@yahoo.com
    Wild card should be % instead of ? for MSSQL. Sent from my Verizon Wireless BlackBerry ... From: John Warner Date: Fri, 16 Oct 2009
    Message 1 of 22 , Oct 16, 2009
    • 0 Attachment
      Wild card should be % instead of ? for MSSQL.
      Sent from my Verizon Wireless BlackBerry

      -----Original Message-----
      From: "John Warner" <john@...>
      Date: Fri, 16 Oct 2009 13:20:19
      To: <SQLQueriesNoCode@yahoogroups.com>
      Subject: RE: [SQLQueriesNoCode] SQL update help

      UPDATE Table T
      SET T.Description = 'Tool A'
      WHERE Item LIKE '1234?';

      John Warner

      > -----Original Message-----
      > From: SQLQueriesNoCode@yahoogroups.com
      > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of J P
      > Sent: Friday, October 16, 2009 12:10 PM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: [SQLQueriesNoCode] SQL update help
      >
      > Hello,
      >
      > I have a table with item and description like below
      >
      > Item Description
      > 1234a Tool A
      > 1234b Null
      > 1234c Null
      > 2345a Item B
      > 2345b Null
      > 2345c Null
      >
      > How do I intra-table update the description (where null) to be the same
      as the
      > prefix item where description is not null, as below?
      >
      > 1234a Tool A
      > 1234b Tool A
      > 1234c Tool A
      > 2345a Item B
      > 2345b Item B
      > 2345c Item B
      >
      > Thanks for any help, and yes i've created a backup copy of the table, so
      any
      > ideas will be fine to test with.
      >
      >
      >
      >
      > [Non-text portions of this message have been removed]
      >
      >
      >
      > ------------------------------------
      >
      > Yahoo! Groups Links
      >
      >
      >




      [Non-text portions of this message have been removed]
    • J P
      no i fixed it by creating a table with unique item numbers and descriptions and then running an update to set the description to the unique description on a
      Message 2 of 22 , Oct 16, 2009
      • 0 Attachment
        no i fixed it by creating a table with unique item numbers and descriptions and then running an update to set the description to the unique description on a substring match

        Temp table
        substring(item,1,4) as item, max(description) group by substring(item,1,4)

        Update table a set description = (select description from temp table where substring(item,1,4)=item) where description is null




        ________________________________
        From: John Warner <john@...>
        To: SQLQueriesNoCode@yahoogroups.com
        Sent: Fri, October 16, 2009 1:41:33 PM
        Subject: RE: [SQLQueriesNoCode] SQL update help


        Is the pattern of Item numbers consistent, A always has a the valid
        description and A is always followed by b and c but not d e f ... ?

        John Warner

        > -----Original Message-----
        > From: SQLQueriesNoCode@ yahoogroups. com
        > [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of J P
        > Sent: Friday, October 16, 2009 1:56 PM
        > To: SQLQueriesNoCode@ yahoogroups. com
        > Subject: Re: [SQLQueriesNoCode] SQL update help
        >
        > I have 19000 different items, so that would work if I modified the code
        19000
        > times.
        >
        > SQL Server 2005 is my version if that helps.
        >
        >
        >
        >
        >
        >
        > ____________ _________ _________ __
        > From: John Warner <john@jwarner. com>
        > To: SQLQueriesNoCode@ yahoogroups. com
        > Sent: Fri, October 16, 2009 12:20:19 PM
        > Subject: RE: [SQLQueriesNoCode] SQL update help
        >
        >
        > UPDATE Table T
        > SET T.Description = 'Tool A'
        > WHERE Item LIKE '1234?';
        >
        > John Warner
        >
        > > -----Original Message-----
        > > From: SQLQueriesNoCode@ yahoogroups. com
        > > [mailto:SQLQueriesN oCode@ yahoogroups. com] On Behalf Of J P
        > > Sent: Friday, October 16, 2009 12:10 PM
        > > To: SQLQueriesNoCode@ yahoogroups. com
        > > Subject: [SQLQueriesNoCode] SQL update help
        > >
        > > Hello,
        > >
        > > I have a table with item and description like below
        > >
        > > Item Description
        > > 1234a Tool A
        > > 1234b Null
        > > 1234c Null
        > > 2345a Item B
        > > 2345b Null
        > > 2345c Null
        > >
        > > How do I intra-table update the description (where null) to be the
        same
        > as the
        > > prefix item where description is not null, as below?
        > >
        > > 1234a Tool A
        > > 1234b Tool A
        > > 1234c Tool A
        > > 2345a Item B
        > > 2345b Item B
        > > 2345c Item B
        > >
        > > Thanks for any help, and yes i've created a backup copy of the table,
        so
        > any
        > > ideas will be fine to test with.
        > >
        > >
        > >
        > >
        > > [Non-text portions of this message have been removed]
        > >
        > >
        > >
        > > ------------ --------- --------- ------
        > >
        > > Yahoo! Groups Links
        > >
        > >
        > >
        >
        >
        >
        >
        >
        >
        >
        > [Non-text portions of this message have been removed]
        >
        >
        >
        > ------------ --------- --------- ------
        >
        > Yahoo! Groups Links
        >
        >
        >







        [Non-text portions of this message have been removed]
      • MELVYN LOPEZ
        Actually, you can use a self-join instead of a temporary table: update Table t set t.description=t2.description from Table t, Table t2 where
        Message 3 of 22 , Oct 17, 2009
        • 0 Attachment
          Actually, you can use a self-join instead of a temporary table:

          update Table t
          set t.description=t2.description
          from Table t, Table t2
          where substring(t.item,1,4) =substring(t2.item,1,4)

          MELVYN

          http://www.blurb.com/my/book/detail/722634


          --- On Fri, 10/16/09, J P <pulverizers73@...> wrote:

          From: J P <pulverizers73@...>
          Subject: Re: [SQLQueriesNoCode] SQL update help
          To: SQLQueriesNoCode@yahoogroups.com
          Date: Friday, October 16, 2009, 3:20 PM






           





          no i fixed it by creating a table with unique item numbers and descriptions and then running an update to set the description to the unique description on a substring match



          Temp table

          substring(item, 1,4) as item, max(description) group by substring(item, 1,4)



          Update table a set description = (select description from temp table where substring(item, 1,4)=item) where description is null



          ____________ _________ _________ __

          From: John Warner <john@jwarner. com>

          To: SQLQueriesNoCode@ yahoogroups. com

          Sent: Fri, October 16, 2009 1:41:33 PM

          Subject: RE: [SQLQueriesNoCode] SQL update help



          Is the pattern of Item numbers consistent, A always has a the valid

          description and A is always followed by b and c but not d e f ... ?



          John Warner



          > -----Original Message-----

          > From: SQLQueriesNoCode@ yahoogroups. com

          > [mailto:SQLQueriesN oCode@ yahoogroups. com] On Behalf Of J P

          > Sent: Friday, October 16, 2009 1:56 PM

          > To: SQLQueriesNoCode@ yahoogroups. com

          > Subject: Re: [SQLQueriesNoCode] SQL update help

          >

          > I have 19000 different items, so that would work if I modified the code

          19000

          > times.

          >

          > SQL Server 2005 is my version if that helps.

          >

          >

          >

          >

          >

          >

          > ____________ _________ _________ __

          > From: John Warner <john@jwarner. com>

          > To: SQLQueriesNoCode@ yahoogroups. com

          > Sent: Fri, October 16, 2009 12:20:19 PM

          > Subject: RE: [SQLQueriesNoCode] SQL update help

          >

          >

          > UPDATE Table T

          > SET T.Description = 'Tool A'

          > WHERE Item LIKE '1234?';

          >

          > John Warner

          >

          > > -----Original Message-----

          > > From: SQLQueriesNoCode@ yahoogroups. com

          > > [mailto:SQLQueriesN oCode@ yahoogroups. com] On Behalf Of J P

          > > Sent: Friday, October 16, 2009 12:10 PM

          > > To: SQLQueriesNoCode@ yahoogroups. com

          > > Subject: [SQLQueriesNoCode] SQL update help

          > >

          > > Hello,

          > >

          > > I have a table with item and description like below

          > >

          > > Item Description

          > > 1234a Tool A

          > > 1234b Null

          > > 1234c Null

          > > 2345a Item B

          > > 2345b Null

          > > 2345c Null

          > >

          > > How do I intra-table update the description (where null) to be the

          same

          > as the

          > > prefix item where description is not null, as below?

          > >

          > > 1234a Tool A

          > > 1234b Tool A

          > > 1234c Tool A

          > > 2345a Item B

          > > 2345b Item B

          > > 2345c Item B

          > >

          > > Thanks for any help, and yes i've created a backup copy of the table,

          so

          > any

          > > ideas will be fine to test with.

          > >

          > >

          > >

          > >

          > > [Non-text portions of this message have been removed]

          > >

          > >

          > >

          > > ------------ --------- --------- ------

          > >

          > > Yahoo! Groups Links

          > >

          > >

          > >

          >

          >

          >

          >

          >

          >

          >

          > [Non-text portions of this message have been removed]

          >

          >

          >

          > ------------ --------- --------- ------

          >

          > Yahoo! Groups Links

          >

          >

          >



          [Non-text portions of this message have been removed]































          [Non-text portions of this message have been removed]
        • MELVYN LOPEZ
          REMATCH:  I left out the null requirement...sorry. Here it is again: update Table t set t.description= t2.description from Table t, Table t2 where
          Message 4 of 22 , Oct 17, 2009
          • 0 Attachment
            REMATCH:  I left out the null requirement...sorry. Here it is again:

            update Table t

            set t.description= t2.description

            from Table t, Table t2

            where substring(t. item,1,4) =substring(t2. item,1,4)
            and substring(t. item,1,4)  is null


            MELVYN

            http://www.blurb.com/my/book/detail/722634


            --- On Sat, 10/17/09, MELVYN LOPEZ <melvynpatrick@...> wrote:

            From: MELVYN LOPEZ <melvynpatrick@...>
            Subject: Re: [SQLQueriesNoCode] SQL update help
            To: SQLQueriesNoCode@yahoogroups.com
            Date: Saturday, October 17, 2009, 7:41 AM






             





            Actually, you can use a self-join instead of a temporary table:



            update Table t

            set t.description= t2.description

            from Table t, Table t2

            where substring(t. item,1,4) =substring(t2. item,1,4)



            MELVYN



            http://www.blurb com/my/book/ detail/722634



            --- On Fri, 10/16/09, J P <pulverizers73@ yahoo.com> wrote:



            From: J P <pulverizers73@ yahoo.com>

            Subject: Re: [SQLQueriesNoCode] SQL update help

            To: SQLQueriesNoCode@ yahoogroups. com

            Date: Friday, October 16, 2009, 3:20 PM



             



            no i fixed it by creating a table with unique item numbers and descriptions and then running an update to set the description to the unique description on a substring match



            Temp table



            substring(item, 1,4) as item, max(description) group by substring(item, 1,4)



            Update table a set description = (select description from temp table where substring(item, 1,4)=item) where description is null



            ____________ _________ _________ __



            From: John Warner <john@jwarner. com>



            To: SQLQueriesNoCode@ yahoogroups. com



            Sent: Fri, October 16, 2009 1:41:33 PM



            Subject: RE: [SQLQueriesNoCode] SQL update help



            Is the pattern of Item numbers consistent, A always has a the valid



            description and A is always followed by b and c but not d e f ... ?



            John Warner



            > -----Original Message-----



            > From: SQLQueriesNoCode@ yahoogroups. com



            > [mailto:SQLQueriesN oCode@ yahoogroups. com] On Behalf Of J P



            > Sent: Friday, October 16, 2009 1:56 PM



            > To: SQLQueriesNoCode@ yahoogroups. com



            > Subject: Re: [SQLQueriesNoCode] SQL update help



            >



            > I have 19000 different items, so that would work if I modified the code



            19000



            > times.



            >



            > SQL Server 2005 is my version if that helps.



            >



            >



            >



            >



            >



            >



            > ____________ _________ _________ __



            > From: John Warner <john@jwarner. com>



            > To: SQLQueriesNoCode@ yahoogroups. com



            > Sent: Fri, October 16, 2009 12:20:19 PM



            > Subject: RE: [SQLQueriesNoCode] SQL update help



            >



            >



            > UPDATE Table T



            > SET T.Description = 'Tool A'



            > WHERE Item LIKE '1234?';



            >



            > John Warner



            >



            > > -----Original Message-----



            > > From: SQLQueriesNoCode@ yahoogroups. com



            > > [mailto:SQLQueriesN oCode@ yahoogroups. com] On Behalf Of J P



            > > Sent: Friday, October 16, 2009 12:10 PM



            > > To: SQLQueriesNoCode@ yahoogroups. com



            > > Subject: [SQLQueriesNoCode] SQL update help



            > >



            > > Hello,



            > >



            > > I have a table with item and description like below



            > >



            > > Item Description



            > > 1234a Tool A



            > > 1234b Null



            > > 1234c Null



            > > 2345a Item B



            > > 2345b Null



            > > 2345c Null



            > >



            > > How do I intra-table update the description (where null) to be the



            same



            > as the



            > > prefix item where description is not null, as below?



            > >



            > > 1234a Tool A



            > > 1234b Tool A



            > > 1234c Tool A



            > > 2345a Item B



            > > 2345b Item B



            > > 2345c Item B



            > >



            > > Thanks for any help, and yes i've created a backup copy of the table,



            so



            > any



            > > ideas will be fine to test with.



            > >



            > >



            > >



            > >



            > > [Non-text portions of this message have been removed]



            > >



            > >



            > >



            > > ------------ --------- --------- ------



            > >



            > > Yahoo! Groups Links



            > >



            > >



            > >



            >



            >



            >



            >



            >



            >



            >



            > [Non-text portions of this message have been removed]



            >



            >



            >



            > ------------ --------- --------- ------



            >



            > Yahoo! Groups Links



            >



            >



            >



            [Non-text portions of this message have been removed]























            [Non-text portions of this message have been removed]































            [Non-text portions of this message have been removed]
          • MELVYN LOPEZ
            REMATCH2:  I left out the non-null requirement for instance t2. ..sorry. Here it is again: update Table t set t.description= t2.description from Table t,
            Message 5 of 22 , Oct 17, 2009
            • 0 Attachment
              REMATCH2:  I left out the non-null requirement for instance t2. ..sorry. Here it is again:

              update Table t
              set t.description= t2.description
              from Table t, Table t2
              where substring(t.item,1,4) =substring(t2.item,1,4)
              and substring(t.item,1,4)  is null
              and substring(t2.item,1,4)  is not null

              MELVYN

              http://www.blurb.com/my/book/detail/722634


              --- On Sat, 10/17/09, MELVYN LOPEZ <melvynpatrick@...> wrote:

              From: MELVYN LOPEZ <melvynpatrick@...>
              Subject: Re: [SQLQueriesNoCode] SQL update help
              To: SQLQueriesNoCode@yahoogroups.com
              Date: Saturday, October 17, 2009, 7:43 AM






               





              REMATCH:  I left out the null requirement. ..sorry. Here it is again:



              update Table t



              set t.description= t2.description



              from Table t, Table t2



              where substring(t. item,1,4) =substring(t2. item,1,4)

              and substring(t. item,1,4)  is null



              MELVYN



              http://www.blurb com/my/book/ detail/722634



              --- On Sat, 10/17/09, MELVYN LOPEZ <melvynpatrick@ yahoo.com> wrote:



              From: MELVYN LOPEZ <melvynpatrick@ yahoo.com>

              Subject: Re: [SQLQueriesNoCode] SQL update help

              To: SQLQueriesNoCode@ yahoogroups. com

              Date: Saturday, October 17, 2009, 7:41 AM



               



              Actually, you can use a self-join instead of a temporary table:



              update Table t



              set t.description= t2.description



              from Table t, Table t2



              where substring(t. item,1,4) =substring(t2. item,1,4)



              MELVYN



              http://www.blurb com/my/book/ detail/722634



              --- On Fri, 10/16/09, J P <pulverizers73@ yahoo.com> wrote:



              From: J P <pulverizers73@ yahoo.com>



              Subject: Re: [SQLQueriesNoCode] SQL update help



              To: SQLQueriesNoCode@ yahoogroups. com



              Date: Friday, October 16, 2009, 3:20 PM



               



              no i fixed it by creating a table with unique item numbers and descriptions and then running an update to set the description to the unique description on a substring match



              Temp table



              substring(item, 1,4) as item, max(description) group by substring(item, 1,4)



              Update table a set description = (select description from temp table where substring(item, 1,4)=item) where description is null



              ____________ _________ _________ __



              From: John Warner <john@jwarner. com>



              To: SQLQueriesNoCode@ yahoogroups. com



              Sent: Fri, October 16, 2009 1:41:33 PM



              Subject: RE: [SQLQueriesNoCode] SQL update help



              Is the pattern of Item numbers consistent, A always has a the valid



              description and A is always followed by b and c but not d e f ... ?



              John Warner



              > -----Original Message-----



              > From: SQLQueriesNoCode@ yahoogroups. com



              > [mailto:SQLQueriesN oCode@ yahoogroups. com] On Behalf Of J P



              > Sent: Friday, October 16, 2009 1:56 PM



              > To: SQLQueriesNoCode@ yahoogroups. com



              > Subject: Re: [SQLQueriesNoCode] SQL update help



              >



              > I have 19000 different items, so that would work if I modified the code



              19000



              > times.



              >



              > SQL Server 2005 is my version if that helps.



              >



              >



              >



              >



              >



              >



              > ____________ _________ _________ __



              > From: John Warner <john@jwarner. com>



              > To: SQLQueriesNoCode@ yahoogroups. com



              > Sent: Fri, October 16, 2009 12:20:19 PM



              > Subject: RE: [SQLQueriesNoCode] SQL update help



              >



              >



              > UPDATE Table T



              > SET T.Description = 'Tool A'



              > WHERE Item LIKE '1234?';



              >



              > John Warner



              >



              > > -----Original Message-----



              > > From: SQLQueriesNoCode@ yahoogroups. com



              > > [mailto:SQLQueriesN oCode@ yahoogroups. com] On Behalf Of J P



              > > Sent: Friday, October 16, 2009 12:10 PM



              > > To: SQLQueriesNoCode@ yahoogroups. com



              > > Subject: [SQLQueriesNoCode] SQL update help



              > >



              > > Hello,



              > >



              > > I have a table with item and description like below



              > >



              > > Item Description



              > > 1234a Tool A



              > > 1234b Null



              > > 1234c Null



              > > 2345a Item B



              > > 2345b Null



              > > 2345c Null



              > >



              > > How do I intra-table update the description (where null) to be the



              same



              > as the



              > > prefix item where description is not null, as below?



              > >



              > > 1234a Tool A



              > > 1234b Tool A



              > > 1234c Tool A



              > > 2345a Item B



              > > 2345b Item B



              > > 2345c Item B



              > >



              > > Thanks for any help, and yes i've created a backup copy of the table,



              so



              > any



              > > ideas will be fine to test with.



              > >



              > >



              > >



              > >



              > > [Non-text portions of this message have been removed]



              > >



              > >



              > >



              > > ------------ --------- --------- ------



              > >



              > > Yahoo! Groups Links



              > >



              > >



              > >



              >



              >



              >



              >



              >



              >



              >



              > [Non-text portions of this message have been removed]



              >



              >



              >



              > ------------ --------- --------- ------



              >



              > Yahoo! Groups Links



              >



              >



              >



              [Non-text portions of this message have been removed]































              [Non-text portions of this message have been removed]























              [Non-text portions of this message have been removed]































              [Non-text portions of this message have been removed]
            • MELVYN LOPEZ
              BTW, it is always good practice to test your update query with a select of which rows are going to be affected by the update... here is what i do: I highlight
              Message 6 of 22 , Oct 17, 2009
              • 0 Attachment
                BTW, it is always good practice to test your update query with a select of which rows are going to be affected by the update... here is what i do:

                I highlight the select statement with the from and where clause and run it...
                Now I know whether the results are what I wanted.... if so, I run the update.

                update Table t
                set t.description= t2.description
                ---select t.description, t2.description
                from Table t, Table t2
                where substring(t.item,1,4) =substring(t2.item,1,4)
                and substring(t.item,1,4)  is null
                and substring(t2.item,1,4)  is not null

                MELVYN

                http://www.blurb.com/my/book/detail/722634


                --- On Sat, 10/17/09, MELVYN LOPEZ <melvynpatrick@...> wrote:

                From: MELVYN LOPEZ <melvynpatrick@...>
                Subject: Re: [SQLQueriesNoCode] SQL update help - REMATCH2
                To: SQLQueriesNoCode@yahoogroups.com
                Date: Saturday, October 17, 2009, 7:50 AM






                 







                REMATCH2:  I left out the non-null requirement for instance t2. ..sorry. Here it is again:



                update Table t

                set t.description= t2.description

                from Table t, Table t2

                where substring(t. item,1,4) =substring(t2. item,1,4)

                and substring(t. item,1,4)  is null

                and substring(t2. item,1,4)  is not null



                MELVYN



                http://www.blurb com/my/book/ detail/722634



                --- On Sat, 10/17/09, MELVYN LOPEZ <melvynpatrick@ yahoo.com> wrote:



                From: MELVYN LOPEZ <melvynpatrick@ yahoo.com>

                Subject: Re: [SQLQueriesNoCode] SQL update help

                To: SQLQueriesNoCode@ yahoogroups. com

                Date: Saturday, October 17, 2009, 7:43 AM



                 



                REMATCH:  I left out the null requirement. ..sorry. Here it is again:



                update Table t



                set t.description= t2.description



                from Table t, Table t2



                where substring(t. item,1,4) =substring(t2. item,1,4)



                and substring(t. item,1,4)  is null



                MELVYN



                http://www.blurb com/my/book/ detail/722634



                --- On Sat, 10/17/09, MELVYN LOPEZ <melvynpatrick@ yahoo.com> wrote:



                From: MELVYN LOPEZ <melvynpatrick@ yahoo.com>



                Subject: Re: [SQLQueriesNoCode] SQL update help



                To: SQLQueriesNoCode@ yahoogroups. com



                Date: Saturday, October 17, 2009, 7:41 AM



                 



                Actually, you can use a self-join instead of a temporary table:



                update Table t



                set t.description= t2.description



                from Table t, Table t2



                where substring(t. item,1,4) =substring(t2. item,1,4)



                MELVYN



                http://www.blurb com/my/book/ detail/722634



                --- On Fri, 10/16/09, J P <pulverizers73@ yahoo.com> wrote:



                From: J P <pulverizers73@ yahoo.com>



                Subject: Re: [SQLQueriesNoCode] SQL update help



                To: SQLQueriesNoCode@ yahoogroups. com



                Date: Friday, October 16, 2009, 3:20 PM



                 



                no i fixed it by creating a table with unique item numbers and descriptions and then running an update to set the description to the unique description on a substring match



                Temp table



                substring(item, 1,4) as item, max(description) group by substring(item, 1,4)



                Update table a set description = (select description from temp table where substring(item, 1,4)=item) where description is null



                ____________ _________ _________ __



                From: John Warner <john@jwarner. com>



                To: SQLQueriesNoCode@ yahoogroups. com



                Sent: Fri, October 16, 2009 1:41:33 PM



                Subject: RE: [SQLQueriesNoCode] SQL update help



                Is the pattern of Item numbers consistent, A always has a the valid



                description and A is always followed by b and c but not d e f ... ?



                John Warner



                > -----Original Message-----



                > From: SQLQueriesNoCode@ yahoogroups. com



                > [mailto:SQLQueriesN oCode@ yahoogroups. com] On Behalf Of J P



                > Sent: Friday, October 16, 2009 1:56 PM



                > To: SQLQueriesNoCode@ yahoogroups. com



                > Subject: Re: [SQLQueriesNoCode] SQL update help



                >



                > I have 19000 different items, so that would work if I modified the code



                19000



                > times.



                >



                > SQL Server 2005 is my version if that helps.



                >



                >



                >



                >



                >



                >



                > ____________ _________ _________ __



                > From: John Warner <john@jwarner. com>



                > To: SQLQueriesNoCode@ yahoogroups. com



                > Sent: Fri, October 16, 2009 12:20:19 PM



                > Subject: RE: [SQLQueriesNoCode] SQL update help



                >



                >



                > UPDATE Table T



                > SET T.Description = 'Tool A'



                > WHERE Item LIKE '1234?';



                >



                > John Warner



                >



                > > -----Original Message-----



                > > From: SQLQueriesNoCode@ yahoogroups. com



                > > [mailto:SQLQueriesN oCode@ yahoogroups. com] On Behalf Of J P



                > > Sent: Friday, October 16, 2009 12:10 PM



                > > To: SQLQueriesNoCode@ yahoogroups. com



                > > Subject: [SQLQueriesNoCode] SQL update help



                > >



                > > Hello,



                > >



                > > I have a table with item and description like below



                > >



                > > Item Description



                > > 1234a Tool A



                > > 1234b Null



                > > 1234c Null



                > > 2345a Item B



                > > 2345b Null



                > > 2345c Null



                > >



                > > How do I intra-table update the description (where null) to be the



                same



                > as the



                > > prefix item where description is not null, as below?



                > >



                > > 1234a Tool A



                > > 1234b Tool A



                > > 1234c Tool A



                > > 2345a Item B



                > > 2345b Item B



                > > 2345c Item B



                > >



                > > Thanks for any help, and yes i've created a backup copy of the table,



                so



                > any



                > > ideas will be fine to test with.



                > >



                > >



                > >



                > >



                > > [Non-text portions of this message have been removed]



                > >



                > >



                > >



                > > ------------ --------- --------- ------



                > >



                > > Yahoo! Groups Links



                > >



                > >



                > >



                >



                >



                >



                >



                >



                >



                >



                > [Non-text portions of this message have been removed]



                >



                >



                >



                > ------------ --------- --------- ------



                >



                > Yahoo! Groups Links



                >



                >



                >



                [Non-text portions of this message have been removed]































                [Non-text portions of this message have been removed]































                [Non-text portions of this message have been removed]























                [Non-text portions of this message have been removed]































                [Non-text portions of this message have been removed]
              • MELVYN LOPEZ
                THE FINAL UPDATE STATEMENT... see, that this resolves your problem... Sometimes, it takes multiple tries.... update Table t set t.description= t2.description
                Message 7 of 22 , Oct 17, 2009
                • 0 Attachment
                  THE FINAL UPDATE STATEMENT... see, that this resolves your problem...
                  Sometimes, it takes multiple tries....

                  update Table t
                  set t.description= t2.description
                  from Table t, Table t2
                  where substring(t.item,1,4) =substring(t2.item,1,4)
                  and  t.description  is null
                  and  t2.description is not null


                  MELVYN


                  --- On Sat, 10/17/09, MELVYN LOPEZ <melvynpatrick@...> wrote:

                  From: MELVYN LOPEZ <melvynpatrick@...>
                  Subject: Re: [SQLQueriesNoCode] SQL update help - GOOD ADVICE
                  To: SQLQueriesNoCode@yahoogroups.com
                  Date: Saturday, October 17, 2009, 7:54 AM






                   





                  BTW, it is always good practice to test your update query with a select of which rows are going to be affected by the update... here is what i do:



                  I highlight the select statement with the from and where clause and run it...

                  Now I know whether the results are what I wanted.... if so, I run the update.



                  update Table t

                  set t.description= t2.description

                  ---select t.description, t2.description

                  from Table t, Table t2

                  where substring(t. item,1,4) =substring(t2. item,1,4)

                  and substring(t. item,1,4)  is null

                  and substring(t2. item,1,4)  is not null



                  MELVYN



                  http://www.blurb com/my/book/ detail/722634



                  --- On Sat, 10/17/09, MELVYN LOPEZ <melvynpatrick@ yahoo.com> wrote:



                  From: MELVYN LOPEZ <melvynpatrick@ yahoo.com>

                  Subject: Re: [SQLQueriesNoCode] SQL update help - REMATCH2

                  To: SQLQueriesNoCode@ yahoogroups. com

                  Date: Saturday, October 17, 2009, 7:50 AM



                   



                  REMATCH2:  I left out the non-null requirement for instance t2. ..sorry. Here it is again:



                  update Table t



                  set t.description= t2.description



                  from Table t, Table t2



                  where substring(t. item,1,4) =substring(t2. item,1,4)



                  and substring(t. item,1,4)  is null



                  and substring(t2. item,1,4)  is not null



                  MELVYN



                  http://www.blurb com/my/book/ detail/722634



                  --- On Sat, 10/17/09, MELVYN LOPEZ <melvynpatrick@ yahoo.com> wrote:



                  From: MELVYN LOPEZ <melvynpatrick@ yahoo.com>



                  Subject: Re: [SQLQueriesNoCode] SQL update help



                  To: SQLQueriesNoCode@ yahoogroups. com



                  Date: Saturday, October 17, 2009, 7:43 AM



                   



                  REMATCH:  I left out the null requirement. ..sorry. Here it is again:



                  update Table t



                  set t.description= t2.description



                  from Table t, Table t2



                  where substring(t. item,1,4) =substring(t2. item,1,4)



                  and substring(t. item,1,4)  is null



                  MELVYN



                  http://www.blurb com/my/book/ detail/722634



                  --- On Sat, 10/17/09, MELVYN LOPEZ <melvynpatrick@ yahoo.com> wrote:



                  From: MELVYN LOPEZ <melvynpatrick@ yahoo.com>



                  Subject: Re: [SQLQueriesNoCode] SQL update help



                  To: SQLQueriesNoCode@ yahoogroups. com



                  Date: Saturday, October 17, 2009, 7:41 AM



                   



                  Actually, you can use a self-join instead of a temporary table:



                  update Table t



                  set t.description= t2.description



                  from Table t, Table t2



                  where substring(t. item,1,4) =substring(t2. item,1,4)



                  MELVYN



                  http://www.blurb com/my/book/ detail/722634



                  --- On Fri, 10/16/09, J P <pulverizers73@ yahoo.com> wrote:



                  From: J P <pulverizers73@ yahoo.com>



                  Subject: Re: [SQLQueriesNoCode] SQL update help



                  To: SQLQueriesNoCode@ yahoogroups. com



                  Date: Friday, October 16, 2009, 3:20 PM



                   



                  no i fixed it by creating a table with unique item numbers and descriptions and then running an update to set the description to the unique description on a substring match



                  Temp table



                  substring(item, 1,4) as item, max(description) group by substring(item, 1,4)



                  Update table a set description = (select description from temp table where substring(item, 1,4)=item) where description is null



                  ____________ _________ _________ __



                  From: John Warner <john@jwarner. com>



                  To: SQLQueriesNoCode@ yahoogroups. com



                  Sent: Fri, October 16, 2009 1:41:33 PM



                  Subject: RE: [SQLQueriesNoCode] SQL update help



                  Is the pattern of Item numbers consistent, A always has a the valid



                  description and A is always followed by b and c but not d e f ... ?



                  John Warner



                  > -----Original Message-----



                  > From: SQLQueriesNoCode@ yahoogroups. com



                  > [mailto:SQLQueriesN oCode@ yahoogroups. com] On Behalf Of J P



                  > Sent: Friday, October 16, 2009 1:56 PM



                  > To: SQLQueriesNoCode@ yahoogroups. com



                  > Subject: Re: [SQLQueriesNoCode] SQL update help



                  >



                  > I have 19000 different items, so that would work if I modified the code



                  19000



                  > times.



                  >



                  > SQL Server 2005 is my version if that helps.



                  >



                  >



                  >



                  >



                  >



                  >



                  > ____________ _________ _________ __



                  > From: John Warner <john@jwarner. com>



                  > To: SQLQueriesNoCode@ yahoogroups. com



                  > Sent: Fri, October 16, 2009 12:20:19 PM



                  > Subject: RE: [SQLQueriesNoCode] SQL update help



                  >



                  >



                  > UPDATE Table T



                  > SET T.Description = 'Tool A'



                  > WHERE Item LIKE '1234?';



                  >



                  > John Warner



                  >



                  > > -----Original Message-----



                  > > From: SQLQueriesNoCode@ yahoogroups. com



                  > > [mailto:SQLQueriesN oCode@ yahoogroups. com] On Behalf Of J P



                  > > Sent: Friday, October 16, 2009 12:10 PM



                  > > To: SQLQueriesNoCode@ yahoogroups. com



                  > > Subject: [SQLQueriesNoCode] SQL update help



                  > >



                  > > Hello,



                  > >



                  > > I have a table with item and description like below



                  > >



                  > > Item Description



                  > > 1234a Tool A



                  > > 1234b Null



                  > > 1234c Null



                  > > 2345a Item B



                  > > 2345b Null



                  > > 2345c Null



                  > >



                  > > How do I intra-table update the description (where null) to be the



                  same



                  > as the



                  > > prefix item where description is not null, as below?



                  > >



                  > > 1234a Tool A



                  > > 1234b Tool A



                  > > 1234c Tool A



                  > > 2345a Item B



                  > > 2345b Item B



                  > > 2345c Item B



                  > >



                  > > Thanks for any help, and yes i've created a backup copy of the table,



                  so



                  > any



                  > > ideas will be fine to test with.



                  > >



                  > >



                  > >



                  > >



                  > > [Non-text portions of this message have been removed]



                  > >



                  > >



                  > >



                  > > ------------ --------- --------- ------



                  > >



                  > > Yahoo! Groups Links



                  > >



                  > >



                  > >



                  >



                  >



                  >



                  >



                  >



                  >



                  >



                  > [Non-text portions of this message have been removed]



                  >



                  >



                  >



                  > ------------ --------- --------- ------



                  >



                  > Yahoo! Groups Links



                  >



                  >



                  >



                  [Non-text portions of this message have been removed]































                  [Non-text portions of this message have been removed]































                  [Non-text portions of this message have been removed]































                  [Non-text portions of this message have been removed]























                  [Non-text portions of this message have been removed]































                  [Non-text portions of this message have been removed]
                • John Warner
                  Actually it should have been the _ underscore. I only wanted to match a single character. The % character matches zero or more characters. But you are
                  Message 8 of 22 , Oct 17, 2009
                  • 0 Attachment
                    Actually it should have been the "_" underscore. I only wanted to match a
                    single character. The '%' character matches zero or more characters. But
                    you are right, the question mark was wrong.

                    John Warner


                    > -----Original Message-----
                    > From: SQLQueriesNoCode@yahoogroups.com
                    > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of
                    > peterim5659@...
                    > Sent: Friday, October 16, 2009 1:49 PM
                    > To: SQLQueriesNoCode@yahoogroups.com
                    > Subject: Re: [SQLQueriesNoCode] SQL update help
                    >
                    > Wild card should be % instead of ? for MSSQL.
                    > Sent from my Verizon Wireless BlackBerry
                    >
                    > -----Original Message-----
                    > From: "John Warner" <john@...>
                    > Date: Fri, 16 Oct 2009 13:20:19
                    > To: <SQLQueriesNoCode@yahoogroups.com>
                    > Subject: RE: [SQLQueriesNoCode] SQL update help
                    >
                    > UPDATE Table T
                    > SET T.Description = 'Tool A'
                    > WHERE Item LIKE '1234?';
                    >
                    > John Warner
                    >
                    > > -----Original Message-----
                    > > From: SQLQueriesNoCode@yahoogroups.com
                    > > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of J P
                    > > Sent: Friday, October 16, 2009 12:10 PM
                    > > To: SQLQueriesNoCode@yahoogroups.com
                    > > Subject: [SQLQueriesNoCode] SQL update help
                    > >
                    > > Hello,
                    > >
                    > > I have a table with item and description like below
                    > >
                    > > Item Description
                    > > 1234a Tool A
                    > > 1234b Null
                    > > 1234c Null
                    > > 2345a Item B
                    > > 2345b Null
                    > > 2345c Null
                    > >
                    > > How do I intra-table update the description (where null) to be the
                    same
                    > as the
                    > > prefix item where description is not null, as below?
                    > >
                    > > 1234a Tool A
                    > > 1234b Tool A
                    > > 1234c Tool A
                    > > 2345a Item B
                    > > 2345b Item B
                    > > 2345c Item B
                    > >
                    > > Thanks for any help, and yes i've created a backup copy of the table,
                    so
                    > any
                    > > ideas will be fine to test with.
                    > >
                    > >
                    > >
                    > >
                    > > [Non-text portions of this message have been removed]
                    > >
                    > >
                    > >
                    > > ------------------------------------
                    > >
                    > > Yahoo! Groups Links
                    > >
                    > >
                    > >
                    >
                    >
                    >
                    >
                    > [Non-text portions of this message have been removed]
                    >
                    >
                    >
                    > ------------------------------------
                    >
                    > Yahoo! Groups Links
                    >
                    >
                    >
                  • Muhammad Rafizeldi
                    my query become slow because i used non postgresql standard function dateadd() is mssql function, i didn t realize it before to solve my problem, i use +/-
                    Message 9 of 22 , Nov 9, 2009
                    • 0 Attachment
                      my query become slow because i used non postgresql standard function
                      dateadd() is mssql function, i didn't realize it before
                      to solve my problem, i use +/- operators and interval
                      dateadd('year', 1, '2008/1/1') ==> date '2008/1/1' + interval '1 year'
                      between dateadd('day', 1, dateadd('year', 1, '2008/1/1')) and
                      dateadd('year', 2, '2008/1/1')
                      ==> between date '2008/1/1' + interval '1 year' + interval '1 day' and
                      date '2008/1/1' + interval '2 year'

                      On Wed, Oct 14, 2009 at 8:38 AM, Aal-Rafizeldi <rafizeldi@...> wrote:

                      >
                      >
                      > Thank you Sir, for remind me, i totally forgot about it
                      > this my query
                      > http://docs.google.com/View?id=df9tz3kc_49hk4vmgd6
                      > <http://docs.google.com/View?id=df9tz3kc_49hk4vmgd6>
                      >
                      > this is my query plan
                      > http://docs.google.com/View?id=df9tz3kc_47hkpcjwfr
                      >
                      > this is the related schema from my query
                      > http://docs.google.com/View?id=df9tz3kc_46g8pcsqdn
                      >
                      > any help/suggestion is really appreciated...
                      >
                      >
                      > --- In SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogroups.com>,
                      > "John Warner" <john@...> wrote:
                      > >
                      > > Without knowing the schema you ask a lot of us.
                      > >
                      > > John Warner
                      > >
                      > >
                      > > > -----Original Message-----
                      > > > From: SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogroups.com>
                      > > > [mailto:SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogroups.com>]
                      > On Behalf Of Aal-Rafizeldi
                      > > > Sent: Tuesday, October 13, 2009 5:49 AM
                      > > > To: SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogroups.com>
                      > > > Subject: [SQLQueriesNoCode] Help-Optimize Query
                      > > >
                      > > > Dear all
                      > > > I am really new to complex sql query
                      > > > I need help to find another possibilities for this query
                      > > > there's so many subquery to skala_rental table
                      > > > Ho to decrease it? since skala_rental is the biggest table, more
                      > than 2
                      > > > million rows
                      > > > Is there any better query for this one?
                      > > >
                      > > > this is the query plan result queryplan.txt
                      > > > <http://f1.grp.yahoofs.com/v1/kEHUStgtfgm_jmD6DOY2DK-
                      > > > gZztvKuqq3gKSs7Gr5B\
                      > > > yQJpJ2HI7ZZB8VGFSAqXKYl4WJ4qPecUVmYeA2uum9jN46km6RSlo/queryp
                      > > > lan-mri>
                      > > >
                      > > > select
                      > > > cf.cf_application_id as Id490_0_,
                      > > > '' as CfApplic2_490_0_,
                      > > > cf.cf_drawdown_number as Drawdown3_490_0_,
                      > > > '' as OldDrawd4_490_0_,
                      > > > p.full_name as Personal5_490_0_,
                      > > > c.name as Corporat6_490_0_,
                      > > > coalesce( (select name
                      > > > from lookup
                      > > > where lookup_id = pe.employment_status_id), 'NON GROUP'
                      > > > ) as Group490_0_,
                      > > >
                      > > > --principle +1 tahun, 2009/1/1
                      > > > coalesce( (select sum(principle)
                      > > > from skala_rental
                      > > > where cf_application_id = cf.cf_application_id
                      > > > and (payment_date is null or payment_date >
                      > '2008/1/1')
                      > > > and counter <> 0
                      > > > and due_date <= dateadd('year', 1, '2008/1/1')), 0
                      > > > ) as Principle8_490_0_,
                      > > > --interest +1 tahun, 2009/1/1
                      > > > coalesce( (select sum(interest)
                      > > > from skala_rental
                      > > > where cf_application_id = cf.cf_application_id
                      > > > and (payment_date is null or payment_date >
                      > '2008/1/1')
                      > > > and counter <> 0
                      > > > and due_date <= dateadd('year', 1, '2008/1/1')), 0
                      > > > ) as Interest12_490_0_,
                      > > >
                      > > > --principle (2009/1/1 + 1hari) - 2010/1/1
                      > > > coalesce( (select sum(principle)
                      > > > from skala_rental
                      > > > where cf_application_id = cf.cf_application_id
                      > > > and due_date between dateadd('day', 1, dateadd('year',
                      > 1,
                      > > > '2008/1/1'))
                      > > > and dateadd('year', 2, '2008/1/1')), 0
                      > > > ) as Principle9_490_0_,
                      > > > --interest (2009/1/1 + 1hari) - 2010/1/1
                      > > > coalesce( (select sum(interest)
                      > > > from skala_rental
                      > > > where cf_application_id = cf.cf_application_id
                      > > > and due_date between dateadd('day', 1, dateadd('year',
                      > 1,
                      > > > '2008/1/1'))
                      > > > and dateadd('year', 2, '2008/1/1')), 0
                      > > > ) as Interest13_490_0_,
                      > > >
                      > > > --principle (2010/1/1 + 1hari) - 2011/1/1
                      > > > coalesce( (select sum(principle)
                      > > > from skala_rental
                      > > > where cf_application_id = cf.cf_application_id
                      > > > and due_date between dateadd('day', 1, dateadd('year',
                      > 2,
                      > > > '2008/1/1'))
                      > > > and dateadd('year', 3, '2008/1/1')), 0
                      > > > ) as Principle10_490_0_,
                      > > > --interest (2010/1/1 + 1hari) - 2011/1/1
                      > > > coalesce( (select sum(interest)
                      > > > from skala_rental
                      > > > where cf_application_id = cf.cf_application_id
                      > > > and due_date between dateadd('day', 1, dateadd('year',
                      > 2,
                      > > > '2008/1/1'))
                      > > > and dateadd('year', 3, '2008/1/1')), 0
                      > > > ) as Interest14_490_0_,
                      > > >
                      > > > --principle > (2008 + 3)
                      > > > coalesce( (select sum(principle)
                      > > > from skala_rental
                      > > > where cf_application_id = cf.cf_application_id
                      > > > and due_date > dateadd('year', 3, '2008/1/1')), 0
                      > > > ) as Principle11_490_0_,
                      > > > --interest > (2008 + 3)
                      > > > coalesce( (select sum(interest)
                      > > > from skala_rental
                      > > > where cf_application_id = cf.cf_application_id
                      > > > and due_date > dateadd('year', 3, '2008/1/1')), 0
                      > > > ) as Interest15_490_0_,
                      > > >
                      > > > coalesce( (sr.unearned + sr.interest), 0) as Unearne16_490_0_,
                      > > > coalesce(sr.outstanding_balance, 0) as OsGross490_0_,
                      > > > (select number_of_digit
                      > > > from organization_setup
                      > > > where organization_id = cf.organization_id
                      > > > ) as NumberO18_490_0_
                      > > >
                      > > > from cf_application cf
                      > > > left join personal_main p on p.personal_id = cf.personal_id
                      > > > left join corporate_main c on c.corporate_id = cf.corporate_id
                      > > > left join personal_employment pe on pe.personal_id =
                      > p.personal_id
                      > > > join skala_rental sr on sr.cf_application_id =
                      > cf.cf_application_id
                      > > >
                      > > > where 1=1
                      > > > and cf.drawdown_date <= '2008/1/1'
                      > > > and (cf.terminate_date is null or cf.terminate_date > '2008/1/1')
                      > > > and sr.counter = (select min(counter)
                      > > > from skala_rental
                      > > > where cf_application_id = cf.cf_application_id
                      > > > and (payment_date is null or payment_date >
                      > > > '2008/1/1')
                      > > > and counter <> 0)
                      > > > and cf.branch_id = 2
                      > > > order by cf.cf_drawdown_number
                      > > >
                      > > > any idea?
                      > > > thank's very much
                      > > >
                      > > >
                      > > >
                      > > > [Non-text portions of this message have been removed]
                      > > >
                      > > >
                      > > >
                      > > > ------------------------------------
                      > > >
                      > > > Yahoo! Groups Links
                      > > >
                      > > >
                      > > >
                      > >
                      >
                      > [Non-text portions of this message have been removed]
                      >
                      >
                      >

                      PR: wait... <javascript:{}> I: wait... <javascript:{}> L:
                      wait...<javascript:{}> LD:
                      wait... <javascript:{}> I: wait... <javascript:{}>wait... <javascript:{}> Rank:
                      wait... <javascript:{}> Traffic: wait... <javascript:{}> Price:
                      wait...<javascript:{}> C:
                      wait... <javascript:{}>


                      [Non-text portions of this message have been removed]
                    • John Warner
                      http://developer.postgresql.org/pgdocs/postgres/functions-datetime.html John Warner ... and
                      Message 10 of 22 , Nov 10, 2009
                      • 0 Attachment
                        http://developer.postgresql.org/pgdocs/postgres/functions-datetime.html

                        John Warner


                        > -----Original Message-----
                        > From: SQLQueriesNoCode@yahoogroups.com
                        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Muhammad
                        > Rafizeldi
                        > Sent: Monday, November 09, 2009 9:41 PM
                        > To: SQLQueriesNoCode@yahoogroups.com
                        > Subject: Re: [SQLQueriesNoCode] Re: Help-Optimize Query
                        >
                        > my query become slow because i used non postgresql standard function
                        > dateadd() is mssql function, i didn't realize it before
                        > to solve my problem, i use +/- operators and interval
                        > dateadd('year', 1, '2008/1/1') ==> date '2008/1/1' + interval '1 year'
                        > between dateadd('day', 1, dateadd('year', 1, '2008/1/1')) and
                        > dateadd('year', 2, '2008/1/1')
                        > ==> between date '2008/1/1' + interval '1 year' + interval '1 day'
                        and
                        > date '2008/1/1' + interval '2 year'
                        >
                        > On Wed, Oct 14, 2009 at 8:38 AM, Aal-Rafizeldi <rafizeldi@...>
                        wrote:
                        >
                        > >
                        > >
                        > > Thank you Sir, for remind me, i totally forgot about it
                        > > this my query
                        > > http://docs.google.com/View?id=df9tz3kc_49hk4vmgd6
                        > > <http://docs.google.com/View?id=df9tz3kc_49hk4vmgd6>
                        > >
                        > > this is my query plan
                        > > http://docs.google.com/View?id=df9tz3kc_47hkpcjwfr
                        > >
                        > > this is the related schema from my query
                        > > http://docs.google.com/View?id=df9tz3kc_46g8pcsqdn
                        > >
                        > > any help/suggestion is really appreciated...
                        > >
                        > >
                        > > --- In
                        > SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogrou
                        > ps.com>,
                        > > "John Warner" <john@...> wrote:
                        > > >
                        > > > Without knowing the schema you ask a lot of us.
                        > > >
                        > > > John Warner
                        > > >
                        > > >
                        > > > > -----Original Message-----
                        > > > > From:
                        > SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogrou
                        > ps.com>
                        > > > >
                        > [mailto:SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yah
                        > oogroups.com>]
                        > > On Behalf Of Aal-Rafizeldi
                        > > > > Sent: Tuesday, October 13, 2009 5:49 AM
                        > > > > To:
                        > SQLQueriesNoCode@yahoogroups.com<SQLQueriesNoCode%40yahoogrou
                        > ps.com>
                        > > > > Subject: [SQLQueriesNoCode] Help-Optimize Query
                        > > > >
                        > > > > Dear all
                        > > > > I am really new to complex sql query
                        > > > > I need help to find another possibilities for this query
                        > > > > there's so many subquery to skala_rental table
                        > > > > Ho to decrease it? since skala_rental is the biggest table, more
                        > > than 2
                        > > > > million rows
                        > > > > Is there any better query for this one?
                        > > > >
                        > > > > this is the query plan result queryplan.txt
                        > > > > <http://f1.grp.yahoofs.com/v1/kEHUStgtfgm_jmD6DOY2DK-
                        > > > > gZztvKuqq3gKSs7Gr5B\
                        > > > >
                        > yQJpJ2HI7ZZB8VGFSAqXKYl4WJ4qPecUVmYeA2uum9jN46km6RSlo/queryp
                        > > > > lan-mri>
                        > > > >
                        > > > > select
                        > > > > cf.cf_application_id as Id490_0_,
                        > > > > '' as CfApplic2_490_0_,
                        > > > > cf.cf_drawdown_number as Drawdown3_490_0_,
                        > > > > '' as OldDrawd4_490_0_,
                        > > > > p.full_name as Personal5_490_0_,
                        > > > > c.name as Corporat6_490_0_,
                        > > > > coalesce( (select name
                        > > > > from lookup
                        > > > > where lookup_id = pe.employment_status_id), 'NON GROUP'
                        > > > > ) as Group490_0_,
                        > > > >
                        > > > > --principle +1 tahun, 2009/1/1
                        > > > > coalesce( (select sum(principle)
                        > > > > from skala_rental
                        > > > > where cf_application_id = cf.cf_application_id
                        > > > > and (payment_date is null or payment_date >
                        > > '2008/1/1')
                        > > > > and counter <> 0
                        > > > > and due_date <= dateadd('year', 1, '2008/1/1')), 0
                        > > > > ) as Principle8_490_0_,
                        > > > > --interest +1 tahun, 2009/1/1
                        > > > > coalesce( (select sum(interest)
                        > > > > from skala_rental
                        > > > > where cf_application_id = cf.cf_application_id
                        > > > > and (payment_date is null or payment_date >
                        > > '2008/1/1')
                        > > > > and counter <> 0
                        > > > > and due_date <= dateadd('year', 1, '2008/1/1')), 0
                        > > > > ) as Interest12_490_0_,
                        > > > >
                        > > > > --principle (2009/1/1 + 1hari) - 2010/1/1
                        > > > > coalesce( (select sum(principle)
                        > > > > from skala_rental
                        > > > > where cf_application_id = cf.cf_application_id
                        > > > > and due_date between dateadd('day', 1, dateadd('year',
                        > > 1,
                        > > > > '2008/1/1'))
                        > > > > and dateadd('year', 2, '2008/1/1')), 0
                        > > > > ) as Principle9_490_0_,
                        > > > > --interest (2009/1/1 + 1hari) - 2010/1/1
                        > > > > coalesce( (select sum(interest)
                        > > > > from skala_rental
                        > > > > where cf_application_id = cf.cf_application_id
                        > > > > and due_date between dateadd('day', 1, dateadd('year',
                        > > 1,
                        > > > > '2008/1/1'))
                        > > > > and dateadd('year', 2, '2008/1/1')), 0
                        > > > > ) as Interest13_490_0_,
                        > > > >
                        > > > > --principle (2010/1/1 + 1hari) - 2011/1/1
                        > > > > coalesce( (select sum(principle)
                        > > > > from skala_rental
                        > > > > where cf_application_id = cf.cf_application_id
                        > > > > and due_date between dateadd('day', 1, dateadd('year',
                        > > 2,
                        > > > > '2008/1/1'))
                        > > > > and dateadd('year', 3, '2008/1/1')), 0
                        > > > > ) as Principle10_490_0_,
                        > > > > --interest (2010/1/1 + 1hari) - 2011/1/1
                        > > > > coalesce( (select sum(interest)
                        > > > > from skala_rental
                        > > > > where cf_application_id = cf.cf_application_id
                        > > > > and due_date between dateadd('day', 1, dateadd('year',
                        > > 2,
                        > > > > '2008/1/1'))
                        > > > > and dateadd('year', 3, '2008/1/1')), 0
                        > > > > ) as Interest14_490_0_,
                        > > > >
                        > > > > --principle > (2008 + 3)
                        > > > > coalesce( (select sum(principle)
                        > > > > from skala_rental
                        > > > > where cf_application_id = cf.cf_application_id
                        > > > > and due_date > dateadd('year', 3, '2008/1/1')), 0
                        > > > > ) as Principle11_490_0_,
                        > > > > --interest > (2008 + 3)
                        > > > > coalesce( (select sum(interest)
                        > > > > from skala_rental
                        > > > > where cf_application_id = cf.cf_application_id
                        > > > > and due_date > dateadd('year', 3, '2008/1/1')), 0
                        > > > > ) as Interest15_490_0_,
                        > > > >
                        > > > > coalesce( (sr.unearned + sr.interest), 0) as Unearne16_490_0_,
                        > > > > coalesce(sr.outstanding_balance, 0) as OsGross490_0_,
                        > > > > (select number_of_digit
                        > > > > from organization_setup
                        > > > > where organization_id = cf.organization_id
                        > > > > ) as NumberO18_490_0_
                        > > > >
                        > > > > from cf_application cf
                        > > > > left join personal_main p on p.personal_id = cf.personal_id
                        > > > > left join corporate_main c on c.corporate_id = cf.corporate_id
                        > > > > left join personal_employment pe on pe.personal_id =
                        > > p.personal_id
                        > > > > join skala_rental sr on sr.cf_application_id =
                        > > cf.cf_application_id
                        > > > >
                        > > > > where 1=1
                        > > > > and cf.drawdown_date <= '2008/1/1'
                        > > > > and (cf.terminate_date is null or cf.terminate_date > '2008/1/1')
                        > > > > and sr.counter = (select min(counter)
                        > > > > from skala_rental
                        > > > > where cf_application_id = cf.cf_application_id
                        > > > > and (payment_date is null or payment_date >
                        > > > > '2008/1/1')
                        > > > > and counter <> 0)
                        > > > > and cf.branch_id = 2
                        > > > > order by cf.cf_drawdown_number
                        > > > >
                        > > > > any idea?
                        > > > > thank's very much
                        > > > >
                        > > > >
                        > > > >
                        > > > > [Non-text portions of this message have been removed]
                        > > > >
                        > > > >
                        > > > >
                        > > > > ------------------------------------
                        > > > >
                        > > > > Yahoo! Groups Links
                        > > > >
                        > > > >
                        > > > >
                        > > >
                        > >
                        > > [Non-text portions of this message have been removed]
                        > >
                        > >
                        > >
                        >
                        > PR: wait... <javascript:{}> I: wait... <javascript:{}> L:
                        > wait...<javascript:{}> LD:
                        > wait... <javascript:{}> I: wait... <javascript:{}>wait...
                        <javascript:{}> Rank:
                        > wait... <javascript:{}> Traffic: wait... <javascript:{}> Price:
                        > wait...<javascript:{}> C:
                        > wait... <javascript:{}>
                        >
                        >
                        > [Non-text portions of this message have been removed]
                        >
                        >
                        >
                        > ------------------------------------
                        >
                        > Yahoo! Groups Links
                        >
                        >
                        >
                      Your message has been successfully submitted and would be delivered to recipients shortly.