×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

谢谢指教...

本文发表在 rolia.net 枫下论坛不过俺的限制条件太多了,只有Select的权限,俺的电脑也不能直接连到Server上,否则在俺电脑的Oracle Database里建个DB Link,运行几个SQL就搞定了。

最后,俺写了个复杂的SQL,把每个B做成一张表(B数量有限),再将它们连起来,运行起来也还不算太慢,300+记录用了20+秒,一天运行几次还可以接受,但是结果一步到位,不用再处理了。

SELECT frt.responsibility_name "Responsibility",
sob.profile_option_value "GL Set of Books Name",
mrc_sob.profile_option_value "MRC: Reporting Set Of Books",
mo_ou.profile_option_value "MO: Operating Unit",
mo_dou.profile_option_value "MO: Default Operating Unit",
hr_user_type.profile_option_value "HR:User Type",
project_update.profile_option_value "PA: Cross Project -- Update",
project_view.profile_option_value "PA: Cross Project -- View"
FROM apps.fnd_responsibility_tl frt,
(SELECT level_value responsibility_id, profile_option_value
FROM apps.fnd_profile_option_values
WHERE level_id = 10003 AND profile_option_id = 1202) sob,
(SELECT fpov.level_value responsibility_id,
gsob.NAME profile_option_value
FROM apps.fnd_profile_option_values fpov,
apps.gl_sets_of_books gsob
WHERE fpov.level_id = 10003
AND fpov.level_value = 57202
AND fpov.profile_option_id = 2351
AND fpov.profile_option_value = gsob.set_of_books_id) mrc_sob,
(SELECT fpov.level_value responsibility_id,
hou.NAME profile_option_value
FROM apps.fnd_profile_option_values fpov,
apps.hr_organization_units_v hou
WHERE fpov.level_id = 10003
AND fpov.profile_option_id = 1991
AND fpov.profile_option_value = hou.organization_id) mo_ou,
(SELECT fpov.level_value responsibility_id,
hou.NAME profile_option_value
FROM apps.fnd_profile_option_values fpov,
apps.hr_organization_units_v hou
WHERE fpov.level_id = 10003
AND fpov.profile_option_id = 5799
AND fpov.profile_option_value = hou.organization_id) mo_dou,
(SELECT level_value responsibility_id,
DECODE (profile_option_value,
'PER', 'HR User',
'INT', 'HR with Payroll User',
'PAY', 'Payroll User',
''
) profile_option_value
FROM apps.fnd_profile_option_values
WHERE level_id = 10003 AND profile_option_id = 1207) hr_user_type,
(SELECT fpov.level_value responsibility_id,
DECODE (fpov.profile_option_value,
'Y', 'Yes',
'N', 'No',
''
) profile_option_value
FROM apps.fnd_profile_option_values fpov
WHERE fpov.level_id = 10003 AND fpov.profile_option_id = 5) project_update,
(SELECT fpov.level_value responsibility_id,
DECODE (fpov.profile_option_value,
'Y', 'Yes',
'N', 'No',
''
) profile_option_value
FROM apps.fnd_profile_option_values fpov
WHERE fpov.level_id = 10003 AND fpov.profile_option_id = 4983) project_view
WHERE frt.LANGUAGE = 'US'
AND frt.creation_date >= '01-NOV-2006'
AND frt.responsibility_id = sob.responsibility_id(+)
AND frt.responsibility_id = mrc_sob.responsibility_id(+)
AND frt.responsibility_id = hr_user_type.responsibility_id(+)
AND frt.responsibility_id = mo_ou.responsibility_id(+)
AND frt.responsibility_id = mo_dou.responsibility_id(+)
AND frt.responsibility_id = project_update.responsibility_id(+)
AND frt.responsibility_id = project_view.responsibility_id(+)
ORDER BY frt.responsibility_name更多精彩文章及讨论,请光临枫下论坛 rolia.net
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / 请教个SQL或Excel的问题...
    Oracle database,用户只有Select的权限,一个表有3列,分别是A,B,C,全部是字符型,所有的行唯一(可以想象成A+B+C=Primary Key),结果是一个表,A是行,B是列,C是表里的值。举例来说,原先的表里有一行,3个列的数值分别是A1, B1, C1,在结果表中,A1行B1列的值是C1

    要求用Toad执行SQL or PL/SQL,结果拷或存到Excel。

    俺能想到的可能的办法是(优先级有高到低):
    1.写个SQL,直接在Toad里运行
    2.写个PL/SQL,直接在Toad里运行。问题是,1. 俺不确定PL/SQL是否能直接在Toad里执行,并将结果保存到Excel。2.俺也不会PL/SQL,要是哪位大侠抽空写个模版出来,俺照猫画虎一下,心中定然万分感谢。
    3.把原先的表先存到Excel,再有个简单的办法处理一下。PivotTable俺试过了,不成,C不是数值,不能用Min or Max etc. 用Macro or VBA,也不必了,这个俺会,还是懒得写,好长时间不用了,忘的差不多了。

    谢谢先
    • 为什么不用VBA?这是很好的解决办法啊。先在TOAD中SELECT * FROM...然后SAVE AS delimited text to clipboard, 到 EXCEL 里PASTE, 再运行宏把数据填充到另一个sheet
      • 俺琢磨着应该还有更好的办法,这个表要经常更新,哪位高人要是能写个SQL或PL/SQL,一下子出结果,俺也跟着学点儿新东西不是的。
        • PL/SQL办法是有的,得写个函数,等会有空了我做一下
          • 来了
            本文发表在 rolia.net 枫下论坛CREATE TABLE test(A NUMBER, B NUMBER, C NUMBER);

            INSERT INTO test VALUES (2,1,2);
            INSERT INTO test VALUES (2,4,5);
            INSERT INTO test VALUES (4,3,8);
            INSERT INTO test VALUES (4,2,6);
            INSERT INTO test VALUES (7,1,1);
            INSERT INTO test VALUES (7,2,2);
            INSERT INTO test VALUES (7,3,3);
            INSERT INTO test VALUES (7,4,4);

            COMMIT;

            CREATE OR REPLACE TYPE t_row AS OBJECT (
            r_num NUMBER
            ,text VARCHAR2(2000)
            )
            /


            CREATE OR REPLACE TYPE t_matrix AS TABLE OF t_row
            /


            CREATE OR REPLACE FUNCTION f_matrix RETURN t_matrix
            AS
            lv_data t_matrix := t_matrix();
            lv_text VARCHAR2(2000);
            lv_lastrow NUMBER;
            lv_lastcol NUMBER;
            BEGIN
            lv_lastrow := 0;
            lv_lastcol := 1;
            lv_text := NULL;
            FOR lv_rec IN (SELECT * FROM test ORDER BY a,b)
            LOOP
            IF lv_lastrow <> lv_rec.a THEN

            FOR i IN GREATEST(1,lv_lastrow) .. lv_rec.a - 1
            LOOP
            lv_data.EXTEND;
            lv_data(i) := t_row(i,NULL);
            END LOOP;

            IF lv_lastrow>0 THEN
            lv_data(lv_lastrow).text := lv_text;
            END IF;

            lv_lastrow := lv_rec.a;
            lv_text := NULL;
            lv_lastcol := 1;
            END IF;

            lv_text := lv_text||SUBSTR(RPAD('*',lv_rec.b - lv_lastcol+1,CHR(9)),2)||lv_rec.c;
            lv_lastcol := lv_rec.b;
            END LOOP;

            IF lv_lastrow>0 THEN
            lv_data.EXTEND;
            lv_data(lv_lastrow) := t_row(lv_lastrow,lv_text);
            END IF;

            RETURN lv_data;
            END f_matrix;
            /

            最后,到TOAD里面执行这么一个查询:
            SELECT text FROM TABLE(CAST(f_matrix AS t_matrix)) order by r_num;

            结果集是一个包含了TAB分隔符的字串。选择 SAVE AS -> DELIMITED TEXT 到 CLIPBOARD, 然后到一个空白的 EXCEL SHEET 里面粘贴。更多精彩文章及讨论,请光临枫下论坛 rolia.net
            • 谢谢指教...
              本文发表在 rolia.net 枫下论坛不过俺的限制条件太多了,只有Select的权限,俺的电脑也不能直接连到Server上,否则在俺电脑的Oracle Database里建个DB Link,运行几个SQL就搞定了。

              最后,俺写了个复杂的SQL,把每个B做成一张表(B数量有限),再将它们连起来,运行起来也还不算太慢,300+记录用了20+秒,一天运行几次还可以接受,但是结果一步到位,不用再处理了。

              SELECT frt.responsibility_name "Responsibility",
              sob.profile_option_value "GL Set of Books Name",
              mrc_sob.profile_option_value "MRC: Reporting Set Of Books",
              mo_ou.profile_option_value "MO: Operating Unit",
              mo_dou.profile_option_value "MO: Default Operating Unit",
              hr_user_type.profile_option_value "HR:User Type",
              project_update.profile_option_value "PA: Cross Project -- Update",
              project_view.profile_option_value "PA: Cross Project -- View"
              FROM apps.fnd_responsibility_tl frt,
              (SELECT level_value responsibility_id, profile_option_value
              FROM apps.fnd_profile_option_values
              WHERE level_id = 10003 AND profile_option_id = 1202) sob,
              (SELECT fpov.level_value responsibility_id,
              gsob.NAME profile_option_value
              FROM apps.fnd_profile_option_values fpov,
              apps.gl_sets_of_books gsob
              WHERE fpov.level_id = 10003
              AND fpov.level_value = 57202
              AND fpov.profile_option_id = 2351
              AND fpov.profile_option_value = gsob.set_of_books_id) mrc_sob,
              (SELECT fpov.level_value responsibility_id,
              hou.NAME profile_option_value
              FROM apps.fnd_profile_option_values fpov,
              apps.hr_organization_units_v hou
              WHERE fpov.level_id = 10003
              AND fpov.profile_option_id = 1991
              AND fpov.profile_option_value = hou.organization_id) mo_ou,
              (SELECT fpov.level_value responsibility_id,
              hou.NAME profile_option_value
              FROM apps.fnd_profile_option_values fpov,
              apps.hr_organization_units_v hou
              WHERE fpov.level_id = 10003
              AND fpov.profile_option_id = 5799
              AND fpov.profile_option_value = hou.organization_id) mo_dou,
              (SELECT level_value responsibility_id,
              DECODE (profile_option_value,
              'PER', 'HR User',
              'INT', 'HR with Payroll User',
              'PAY', 'Payroll User',
              ''
              ) profile_option_value
              FROM apps.fnd_profile_option_values
              WHERE level_id = 10003 AND profile_option_id = 1207) hr_user_type,
              (SELECT fpov.level_value responsibility_id,
              DECODE (fpov.profile_option_value,
              'Y', 'Yes',
              'N', 'No',
              ''
              ) profile_option_value
              FROM apps.fnd_profile_option_values fpov
              WHERE fpov.level_id = 10003 AND fpov.profile_option_id = 5) project_update,
              (SELECT fpov.level_value responsibility_id,
              DECODE (fpov.profile_option_value,
              'Y', 'Yes',
              'N', 'No',
              ''
              ) profile_option_value
              FROM apps.fnd_profile_option_values fpov
              WHERE fpov.level_id = 10003 AND fpov.profile_option_id = 4983) project_view
              WHERE frt.LANGUAGE = 'US'
              AND frt.creation_date >= '01-NOV-2006'
              AND frt.responsibility_id = sob.responsibility_id(+)
              AND frt.responsibility_id = mrc_sob.responsibility_id(+)
              AND frt.responsibility_id = hr_user_type.responsibility_id(+)
              AND frt.responsibility_id = mo_ou.responsibility_id(+)
              AND frt.responsibility_id = mo_dou.responsibility_id(+)
              AND frt.responsibility_id = project_update.responsibility_id(+)
              AND frt.responsibility_id = project_view.responsibility_id(+)
              ORDER BY frt.responsibility_name更多精彩文章及讨论,请光临枫下论坛 rolia.net
              • 运行TOAD的机器不是你装本地数据库的那台?你的实际需求和你的问题差距很大,首先C的值是要经过复杂的变换得来的;B的数量既然那么有限,就没必要挖空心思做那么通用的程序。
                • 俺是通过Remote Desktop and Internet连到客户的Virtual Machine.Virtual Machine上装了TOAD并和Server在一个局域网内,运行完SQL,结果存到剪贴板,再贴到本地Excel。一上来,俺只是举个例子,没指望做通用的程序。很多东西也是逐渐发现的,如每个C都需要不同的转换。