SQL query help to generate data

Below the query I created to get certain itemnumbers, qty ordered and price and others from the database. The problem is that sometimes an order doesn开发者_JAVA百科't contain 20 itemsnumbers but only 2. Now my question is if it's possible to fill the spaces with other itemnumbers random from the DB. It doesn't need to be correct because it's just for testing.

So can anybody help?

select   
  t.*, 
  -- THE THREE SUMVAT VALUES BELOW ARE VERY IMPORTANT. THEY ARE ONLY CORRECT HOWEVER WHEN THERE ARE NO NULL VALUES INVOLVED IN THE MATH,
  -- I.E. WHEN THERE ARE 20 ITEMS/QTYS/PRICES INVOLVED WITH A CERTAIN ORDER_NO
  ((t.QTY1*t.PRICE1)+(t.QTY2*t.PRICE2)+(t.QTY3*t.PRICE3)+(t.QTY4*t.PRICE4)+(t.QTY5*t.PRICE5))           SUMVAT0,    -- example: 5123.45   <- lines 1-5: Q*P
  ((t.QTY6*t.PRICE6)+(t.QTY7*t.PRICE7)+(t.QTY8*t.PRICE8)+(t.QTY9*t.PRICE9)+(t.QTY10*t.PRICE10)+(t.QTY11*t.PRICE11)+(t.QTY12*t.PRICE12)+(t.QTY13*t.PRICE13)+(t.QTY14*t.PRICE14)+(t.QTY15*t.PRICE15)) 
                                                                                                        SUMVAT6,    -- example: 1234.56   <- lines 6-15: Q*P
  ((t.QTY16*t.PRICE16)+(t.QTY17*t.PRICE17)+(t.QTY18*t.PRICE18)+(t.QTY19*t.PRICE19)+(t.QTY20*t.PRICE20)) SUMVAT19    -- example: 4567.89   <- lines 16-20: Q*P
from (
  select 
    (to_char(p.vdate, 'YYYYMMDD') || to_char(sysdate, 'HH24MISS')) DT,
    (to_char(p.vdate, 'YYYY-MM-DD') ||'T' || to_char(sysdate, 'HH24:MI:') || '00') DATETIME,
    (to_char(orh.written_date, 'YYYY-MM-DD') ||'T00:00:00') DATETIME2,
    orh.supplier FAKE_GLN,
    y.*
  from (
    select 
      x.order_no ORDNO

      , max(decode(r,1 ,x.item,null)) FAKE_GTIN1
      , max(decode(r,2 ,x.item,null)) FAKE_GTIN2
      , max(decode(r,3 ,x.item,null)) FAKE_GTIN3
      , max(decode(r,4 ,x.item,null)) FAKE_GTIN4
      , max(decode(r,5 ,x.item,null)) FAKE_GTIN5
      , max(decode(r,6 ,x.item,null)) FAKE_GTIN6
      , max(decode(r,7 ,x.item,null)) FAKE_GTIN7
      , max(decode(r,8 ,x.item,null)) FAKE_GTIN8
      , max(decode(r,9 ,x.item,null)) FAKE_GTIN9
      , max(decode(r,10,x.item,null)) FAKE_GTIN10
      , max(decode(r,11,x.item,null)) FAKE_GTIN11
      , max(decode(r,12,x.item,null)) FAKE_GTIN12
      , max(decode(r,13,x.item,null)) FAKE_GTIN13
      , max(decode(r,14,x.item,null)) FAKE_GTIN14
      , max(decode(r,15,x.item,null)) FAKE_GTIN15
      , max(decode(r,16,x.item,null)) FAKE_GTIN16
      , max(decode(r,17,x.item,null)) FAKE_GTIN17
      , max(decode(r,18,x.item,null)) FAKE_GTIN18
      , max(decode(r,19,x.item,null)) FAKE_GTIN19
      , max(decode(r,20,x.item,null)) FAKE_GTIN20

      , max(decode(r,1 ,x.qty_ordered,null)) QTY1
      , max(decode(r,2 ,x.qty_ordered,null)) QTY2
      , max(decode(r,3 ,x.qty_ordered,null)) QTY3
      , max(decode(r,4 ,x.qty_ordered,null)) QTY4
      , max(decode(r,5 ,x.qty_ordered,null)) QTY5
      , max(decode(r,6 ,x.qty_ordered,null)) QTY6
      , max(decode(r,7 ,x.qty_ordered,null)) QTY7
      , max(decode(r,8 ,x.qty_ordered,null)) QTY8
      , max(decode(r,9 ,x.qty_ordered,null)) QTY9
      , max(decode(r,10,x.qty_ordered,null)) QTY10
      , max(decode(r,11,x.qty_ordered,null)) QTY11
      , max(decode(r,12,x.qty_ordered,null)) QTY12
      , max(decode(r,13,x.qty_ordered,null)) QTY13
      , max(decode(r,14,x.qty_ordered,null)) QTY14
      , max(decode(r,15,x.qty_ordered,null)) QTY15
      , max(decode(r,16,x.qty_ordered,null)) QTY16
      , max(decode(r,17,x.qty_ordered,null)) QTY17
      , max(decode(r,18,x.qty_ordered,null)) QTY18
      , max(decode(r,19,x.qty_ordered,null)) QTY19
      , max(decode(r,20,x.qty_ordered,null)) QTY20

      , max(decode(r,1 ,x.unit_cost,null)) PRICE1
      , max(decode(r,2 ,x.unit_cost,null)) PRICE2
      , max(decode(r,3 ,x.unit_cost,null)) PRICE3
      , max(decode(r,4 ,x.unit_cost,null)) PRICE4
      , max(decode(r,5 ,x.unit_cost,null)) PRICE5
      , max(decode(r,6 ,x.unit_cost,null)) PRICE6
      , max(decode(r,7 ,x.unit_cost,null)) PRICE7
      , max(decode(r,8 ,x.unit_cost,null)) PRICE8
      , max(decode(r,9 ,x.unit_cost,null)) PRICE9
      , max(decode(r,10,x.unit_cost,null)) PRICE10
      , max(decode(r,11,x.unit_cost,null)) PRICE11
      , max(decode(r,12,x.unit_cost,null)) PRICE12
      , max(decode(r,13,x.unit_cost,null)) PRICE13
      , max(decode(r,14,x.unit_cost,null)) PRICE14
      , max(decode(r,15,x.unit_cost,null)) PRICE15
      , max(decode(r,16,x.unit_cost,null)) PRICE16
      , max(decode(r,17,x.unit_cost,null)) PRICE17
      , max(decode(r,18,x.unit_cost,null)) PRICE18
      , max(decode(r,19,x.unit_cost,null)) PRICE19
      , max(decode(r,20,x.unit_cost,null)) PRICE20
    from (
      select 
        rank() over (partition by oh.order_no order by ol.item asc) r,
        oh.supplier,
        oh.order_no,
        oh.written_date,
        ol.item,
        ol.qty_ordered,
        ol.unit_cost
      from 
        ordhead oh
        JOIN ordloc ol ON oh.order_no = ol.order_no
      where 
--        count(numrows) = 1500
        not unit_cost is null
--        and ol.order_no in (6181,6121)
    ) x 
    group by x.order_no
  ) y
  JOIN ordhead orh ON orh.order_no = y.ORDNO,
  period p
) t
;

Without being able to really test this, you might try something like this. Replace the inline view 'x' with this:

FROM (
  WITH q AS (
   SELECT LEVEL  r, TO_CHAR(TRUNC(dbms_random.value*1000,0)) item
                  , TRUNC(dbms_random.value*100,0) qty_ordered
                  , TRUNC(dbms_random.value*10,2) unit_cost
     FROM dual CONNECT BY LEVEL <= 20
  )
   SELECT COALESCE(x1.r, q.r) r, supplier, order_no, written_date
        , COALESCE(x1.item, q.item) item
        , COALESCE(x1.qty_ordered, q.qty_ordered) qty_ordered
        , COALESCE(x1.unit_cost, q.unit_cost) unit_cost
    FROM (SELECT ROW_NUMBER() OVER (PARTITION BY oh.order_no ORDER BY ol.item ASC) r
               , oh.supplier
               , oh.order_no
               , oh.written_date
               , ol.item
               , ol.qty_ordered
               , ol.unit_cost
            FROM ordhead oh JOIN ordloc ol ON oh.order_no = ol.order_no
           WHERE NOT unit_cost IS NULL) x1 RIGHT JOIN q ON x1.r = q.r
) x 
GROUP BY x.order_no

The WITH clause will give you a table with 20 rows of random data. Outer join that with your old 'x' data and you will be guaranteed 20 rows of data. You might not need to cast the item as a varchar2 depending on data. (N.B., I finally found a query that it makes sense to use a RIGHT JOIN with. See this SO question)

I'm not quite sure what you're trying to do with the GROUP BY and MAX stuff? In the future it would be helpful to condense your examples into something others can easily test, a minimal case that gets your point across.

I also incorporated @Kevin's good suggestion to use ROW_NUMBER instead of RANK.

very difficult to understand...

i think you might be ok if you put a 0 instead of null in the price values...

 , max(decode(r,18,x.unit_cost,0)) PRICE18

and

 , max(decode(r,20,x.qty_ordered,0)) QTY20

then at least the math should work.

Rank will not guarantee a sequential count of the items in the groups there, may be gaps when you have several rows with the same value.

for a decent explanation see:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2920665938600

I think you need to use row_number