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]
    • 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 2 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 3 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 4 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 5 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 6 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 7 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 8 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 9 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.