Posted By

mariolassnig on 06/30/11


Tagged

sql Oracle


Versions (?)

test snippet 2


 / Published in: SQL
 

  1. SELECT /*+ use_hash(l u0 u1 u2 u3 u4 u5 m0 m1 m2 m3) */ l.value AS acclocation
  2. , u0.value AS accdatatype
  3. , u1.value AS accprodstep
  4. , u2.value AS accproject
  5. , u3.value AS accrunnumber
  6. , u4.value AS accstreamname
  7. , u5.value AS accversion
  8. , COUNT(DISTINCT l.duid) AS duids, SUM(m0.value) AS nbfiles, SUM(m1.value) AS rnbfiles, SUM(m2.value) AS length, SUM(m3.value) AS rlength, TO_CHAR (systimestamp, 'YYYY-MM-DD HH24:MI:SS') AS tstamp
  9. FROM t_kvs_partlist l
  10. , t_kvs_partlist u0
  11. , t_kvs_partlist u1
  12. , t_kvs_partlist u2
  13. , t_kvs_partlist u3
  14. , t_kvs_partlist u4
  15. , t_kvs_partlist u5
  16. , t_kvs_partlist m0, t_kvs_partlist m1, t_kvs_partlist m2, t_kvs_partlist m3
  17. WHERE l.KEY = 'location'
  18. AND u0.KEY = 'datatype' AND u0.duid = l.duid
  19. AND u1.KEY = 'prodstep' AND u1.duid = l.duid
  20. AND u2.KEY = 'project' AND u2.duid = l.duid
  21. AND u3.KEY = 'runnumber' AND u3.duid = l.duid
  22. AND u4.KEY = 'streamname' AND u4.duid = l.duid
  23. AND u5.KEY = 'version' AND u5.duid = l.duid
  24. AND m0.KEY = 'nbfiles' AND m0.duid = l.duid AND m1.KEY = 'rnbfiles' AND m1.pid = 'location:'||l.value AND m1.duid = l.duid AND m2.KEY = 'length' AND m2.duid = l.duid AND m3.KEY = 'rlength' AND m1.pid = 'location:'||l.value AND m3.duid = l.duid
  25. GROUP BY l.value
  26. , u0.value
  27. , u1.value
  28. , u2.value
  29. , u3.value
  30. , u4.value
  31. , u5.value
  32. ORDER BY l.value
  33. , u0.value
  34. , u1.value
  35. , u2.value
  36. , u3.value
  37. , u4.value
  38. , u5.value

Report this snippet  

You need to login to post a comment.