4 Replies Latest reply on Aug 22, 2019 2:26 PM by G.Y

    select trans id row base

    G.Y

      below coding return the following result

      select

      (SELECT NVL(MAX(TRANS_ID),0)+1 FROM INV.ITEMS_STOCK_C) as trans_id,

      a.item_code, a.item_name

       

      from inv.all_items_all_qty a

      where a.item_code not in (select distinct item_code from inv.items_stock_c);

       

      result as per the above coding...

       

      trans_iditem_codeitem_name
      6125
      item 1
      6
      55739
      item 2
      66985
      item 3
      6203
      item 4
      6189
      item 5

       

       

       

      but i want the following result in trans_id column should come next value in each row.

       

      trans_id
      item_codeitem_name
      6

      125

      item 1
      755739item 2
      86985item 3
      9203item 4
      10189item 5
        • 1. Re: select trans id row base
          mathguy

          By what rule or rules?  This seems like a pretty odd requirement (very likely, the wrong solution to a problem; even the problem itself, which you did not state, is probably wrong to begin with).

          • 2. Re: select trans id row base
            Frank Kulash

            Hi,

             

            G.Y wrote:

             

            below coding return the following result

            select

            (SELECT NVL(MAX(TRANS_ID),0)+1 FROM INV.ITEMS_STOCK_C) as trans_id,

            a.item_code, a.item_name

             

            from inv.all_items_all_qty a

            where a.item_code not in (select distinct item_code from inv.items_stock_c);

             

            result as per the above coding...

             

            trans_id
            item_code
            item_name
            6125
            item 1
            6
            55739
            item 2
            66985
            item 3
            6203
            item 4
            6189
            item 5

             

             

             

            but i want the following result in trans_id column should come next value in each row.

             

            trans_id
            item_code
            item_name
            6

            125

            item 1
            755739item 2
            86985item 3
            9203item 4
            10189item 5

            Let me make surte I understand.

            You have a scalar sub-query that returns a number (5 in this case).

            Right now your-re displaying 5 + 1 = 6 in the trans_id column on every row.

            You want to change that to display 5 + N on each row, where N=1, 2, 3, … incrementing by 1 on each row.

            Is that it?

             

            If so, you can use ROWNUM or the analytic ROW_NUMBER function, like this:

            SELECT    (

                          SELECT  NVL (MAX (trans_id), 0)

                                      + ROW_NUMEBR ()

                                            OVER (ORDER BY  item_name)

                          FROM    inv.items_stock_c

                      )    as trans_id,

                      item_code,

                      item_name

            FROM      inv.all_items_all_qty

            WHERE     item_code  NOT IN (

                                            SELECT  item_code  -- No need for DISTINCT

                                            FROM    inv.items_stock_c

                                            WHERE   item_code  IS NOT NULL  -- If needed

                                        )

            ORDER BY  item_name;

            If you'd care to post CREATE TABLE and INSERT statements for the sample data, then I could test this.

            See the Forum FAQ Re: 2. How do I ask a question on the forums?

             

            I'm assuming you want the rows numbered in order by item_name.  If you want them in some other order, substitute the appropriate epression in the analytic ORDER BY clause.  You'll probably want the same expression in the query ORDER BY clause, but the two are independent, and you can use different expressions if you want, or you can omit the query ORDER BY clause altogether.  (An analytic ORDER BY clause is required with ROW_NUMBER.)

            • 3. Re: select trans id row base
              G.Y

              (SELECT  NVL (MAX (trans_id),0) FROM    inv.items_stock_c ) + rownum as trans_id,

               

              by doing this my problem is solved.

              • 4. Re: select trans id row base
                G.Y

                dear sir thanks for your good suggestions.