Home » Developer & Programmer » Reports & Discoverer » Fix blank columns (Reports )
Fix blank columns [message #428599] Thu, 29 October 2009 02:00 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Hi ,

I am having report based on two queries one query having print direction condition as down and under that another query having print direction as across.But my problem in across condition i need to fix four blank colums as permanent that is if there is data it will print into it even if there is no data the blank fixed columns must come as it is.but now its comming to whatever data available.please check the attachment and advise.
Re: Fix blank columns [message #428603 is a reply to message #428599] Thu, 29 October 2009 02:19 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"Blank data" probably means NULL value in that column. If so, assuming that "fixed value" equals '***', you might use NVL function:
select id, name, NVL(address, '***') address
from some_table
where ...
Re: Fix blank columns [message #428615 is a reply to message #428603] Thu, 29 October 2009 02:40 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Dear Littlefoot,

I appreciate your fast reply.But my problem is i have two null columns to be added as allways .if there is one column of data comming in that case i need to add 3 blank columns, if its 2 then 2 blank columns need to be added.so four columns whether data there or not need to be fixed .
Re: Fix blank columns [message #428627 is a reply to message #428615] Thu, 29 October 2009 04:09 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I think I understand, but I can't imagine how is that supposed to look like printed on a paper. Could you draw it and help me visualize the problem?
Re: Fix blank columns [message #428939 is a reply to message #428627] Sat, 31 October 2009 00:34 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Yeahn its like there is one item which needs to undergo certain operation example one iron piece it may require 4 operations like cutting,welding,painting and blasting but the problem is there may be 2 operations there may be 3 operations depending on the input of user.

case 1
item a - cut
item a - weld

in this case i want to have it printed like below

item a - cut weld 'blank' 'blank' as seperate fields on report

case 2

item a - cut
item a - weld
item a - paint

in this case the output will be

item a - cut weld paint 'blank'

i hope u understood my problem.
Re: Fix blank columns [message #428958 is a reply to message #428627] Sat, 31 October 2009 02:24 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

i have written one proceedure as below , can we call this procedure in the report

CREATE OR REPLACE PROCEDURE AIC_OP_CD (
    p_SYS      IN       NUMBER,
    p_field6   OUT      VARCHAR2,
    p_field7   OUT      VARCHAR2,
    p_field8   OUT      VARCHAR2,
    p_field9   OUT      varchar2
 )
 IS
    CURSOR c1
    IS
       SELECT
               CSO_OPER_CODE , CSO_OP_STAGE_NO
 FROM OT_CUTTING_SHEET_OPERATION,ot_cutting_sheet_head,ot_cutting_sheet_detail
 where csh_sys_id = csd_csh_sys_id
 and  csd_sys_id = cso_csd_sys_id
 and csd_sys_id = P_SYS
 ORDER BY CSO_OP_STAGE_NO;
   cursor c2
   is
       SELECT COUNT(*) FROM
 OT_CUTTING_SHEET_OPERATION,ot_cutting_sheet_head,ot_cutting_sheet_detail
 where csh_sys_id = csd_csh_sys_id
 and  csd_sys_id = cso_csd_sys_id
 and csd_sys_id = P_SYS
 ORDER BY CSO_OP_STAGE_NO   ;
 M_OPER VARCHAR2(20);
 M_STG NUMBER;
 M_NO NUMBER;
 BEGIN
    OPEN c1;
    FETCH c1
     INTO M_OPER,M_STG;
    CLOSE c1;
    OPEN C2;
    FETCH C2 INTO M_NO;
    CLOSE C2;
  FOR I IN C2
   LOOP
    IF M_NO = 1 AND M_STG=1
    THEN
       p_field6 := M_OPER;
       p_field7 := 'X';
       P_FIELD8 := 'X';
       P_FIELD9 := 'X';
    ELSIF  M_NO = 2 AND M_STG=1
    THEN
       P_field6 := M_OPER;
       p_field7 := 'X';
       p_field8 := 'X';
       P_FIELD9 := 'X';
   ELSIF  M_NO = 2 AND M_STG=2
    THEN
       P_field6 := 'X';
       p_field7 := M_OPER;
       p_field8 := 'X';
       P_FIELD9 := 'X';
   ELSIF  M_NO = 3 AND M_STG=1
    THEN
       P_field6 := M_OPER;
       p_field7 := 'X';
       p_field8 := 'X';
       P_FIELD9 := 'X';
   ELSIF  M_NO = 3 AND M_STG=2
    THEN
       P_field6 := 'X';
       p_field7 := M_OPER;
       p_field8 := 'X';
       P_FIELD9 := 'X';
    ELSE
       P_field6 := 'X';
       p_field7 := 'X';
       p_field8 := M_OPER;
       P_FIELD9 := 'X';
    END IF;
 END LOOP;
  END;

[EDITED by LF: fixed [code] tags]

[Updated on: Sat, 31 October 2009 10:48] by Moderator

Report message to a moderator

Re: Fix blank columns [message #428960 is a reply to message #428958] Sat, 31 October 2009 03:01 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

yes,

You can write this code in after parameter form trigger in report builder to insert the data in a global temporary table.
then you can query from this temporary table.


regards
Halim
Re: Fix blank columns [message #428961 is a reply to message #428960] Sat, 31 October 2009 03:04 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

but can you tell me how with step by step process.please i am not well versed in reports.my simple problem is as below

there is one item which needs to undergo certain operation example one iron piece it may require 4 operations like cutting,welding,painting and blasting but the problem is there may be 2 operations there may be 3 operations depending on the input of user.

case 1
item a - cut
item a - weld

in this case i want to have it printed like below

item a - cut weld 'blank' 'blank' as seperate fields on report

case 2

item a - cut
item a - weld
item a - paint

in this case the output will be

item a - cut weld paint 'blank'

i hope u understood my problem.May be you have different solution
Re: Fix blank columns [message #428969 is a reply to message #428961] Sat, 31 October 2009 04:06 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

input a test case, like tables and insert statements.

As i understood your requierment, you need to create a matrix report.


Regards
Halim
Re: Fix blank columns [message #428982 is a reply to message #428627] Sat, 31 October 2009 08:40 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

dear littlefoot atlast i decided to write function like below but i am getting errors

CREATE OR REPLACE FUNCTION ORION2007.getoper1 (p_sys  in  

ot_cutting_sheet_detail.csd_sys_id%TYPE)
   RETURN VARCHAR2
 IS
   l_text  VARCHAR2(32767) := NULL;
    CURSOR c1
     IS
        SELECT
              cso_csd_sys_id, CSO_OP_STAGE_NO
  FROM OT_CUTTING_SHEET_OPERATION,ot_cutting_sheet_head,ot_cutting_sheet_detail
  where csh_sys_id = csd_csh_sys_id
  and  csd_sys_id = cso_csd_sys_id
  and csd_sys_id = P_SYS
  ORDER BY CSO_OP_STAGE_NO;
  cursor c2
    is
        SELECT COUNT(*) FROM
  OT_CUTTING_SHEET_OPERATION,ot_cutting_sheet_head,ot_cutting_sheet_detail
  where csh_sys_id = csd_csh_sys_id
  and  csd_sys_id = cso_csd_sys_id
  and csd_sys_id = P_SYS
  ORDER BY CSO_OP_STAGE_NO   ;
  cursor c3 (p_stg number) is
    select   CSO_oper_code
  FROM OT_CUTTING_SHEET_OPERATION,ot_cutting_sheet_head,ot_cutting_sheet_detail
  where csh_sys_id = csd_csh_sys_id
  and  csd_sys_id = cso_csd_sys_id
  and csd_sys_id = P_SYS
  and cso_op_stage_no = p_stg
  ORDER BY CSO_OP_STAGE_NO;
 p_field6 VARCHAR2(20);
 p_field7 varchar2(20);
 p_field8 varchar2(20);
 p_field9 varchar2(20);
  m_sys number;
  m_oper varchar2(20);
  M_STG NUMBER;
  M_NO NUMBER;
  BEGIN
     OPEN c1;
     FETCH c1
      INTO M_sys,M_STG;
     CLOSE c1;
     OPEN C2;
     FETCH C2 INTO M_NO;
     CLOSE C2;
     open c3(m_stg);
     fetch c3 into m_oper;
     close c3;
   FOR j IN C2
    LOOP
     IF M_NO = 1
       THEN
    for i in c1
      loop
      for k in c3(i.cso_op_stage_no)
       loop
        p_field6 := M_OPER;
        p_field7 := 'X';
        P_FIELD8 := 'X';
        P_FIELD9 := 'X';
    l_text := (p_field6||''||p_field7||''||p_field8||''||p_field9);
         end loop;
      end loop;
     ELSIF  M_NO = 2
     THEN
     for i in c1
      loop
      for k in c3(i.cso_op_stage_no)
       loop
       if i.cso_op_stage_no = 1
        then
        P_field6 := M_OPER;
        p_field7 := 'X';
        p_field8 := 'X';
        P_FIELD9 := 'X';
        else
        P_field6 := 'X';
        p_field7 := M_OPER;
        p_field8 := 'X';
        P_FIELD9 := 'X';
      l_text := (p_field6||''||p_field7||''||p_field8||''||p_field9);
    END IF;
        END LOOP;
       END LOOP;
   END IF;
  RETURN  l_text;
   END;


error message is
87/7 PLS-00103: Encountered the symbol ";" when expecting one of the following: loop

[EDITED by LF: fixed [code] tags]

[Updated on: Sat, 31 October 2009 10:49] by Moderator

Report message to a moderator

Re: Fix blank columns [message #428988 is a reply to message #428982] Sat, 31 October 2009 10:54 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There are syntax errors you have to fix. As the code is badly formatted (in spite of [code] tags (by the way, check OraFAQ Forum Guide to learn how to properly use them)) and Formatter refuses to format it (because of errors), please, double check the code and post code that compiles. Make sure to properly format it so that is easier to read.
Re: Fix blank columns [message #429002 is a reply to message #428982] Sat, 31 October 2009 22:44 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

Input scrift of these three tables and some insert statements.

OT_CUTTING_SHEET_OPERATION,ot_cutting_sheet_head,ot_cutting_sheet_detail

and a sample output format


Regards
Halim
Re: Fix blank columns [message #429004 is a reply to message #428982] Sat, 31 October 2009 23:03 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
Do not cross/multi-post

http://www.orafaq.com/forum/m/428983/136107/#msg_428983
Re: Fix blank columns [message #429012 is a reply to message #429002] Sun, 01 November 2009 00:43 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks Mr.Halim , well the table structure is as below.

there are two table i will just give the key fields

ot_cutting_sheet_head - this is header table
pk- csh_sys_id
,csh_dt
ot_cutting_sheet_detail - this is detail which has one position
fk - csd_csh_sys_id,
csd_sys_id,csd_pos
ot_cutting_sheet_operation - this is the operation table with oper_codes
fk-cso_csd_sys_id,
cso_sys_id,
cso_oper_code


header has csh_sys_id which is primary key
detail has positions that is item to be manufactured with link to header using forien key csd_csh_sys_id linked to csh_sys_id
operation table contains details of one position that is one position may have one or more operation codes link is cso_csd_sys_id of operation is linked to csd_sys_id of detail .

Now the output that i want is like below

csh_dt - csh_position - csd_oper_codes
1/10/2009 - 101 - cut - weld - paint - blast
1/10/2009 - 102 - cut - weld - x - x
1/10/2009 - 103 - cut - x - x - x

As you see the sample in line 2 and three for position 102 there are 2 operations so if there are no operations 'x' must be defaulted.similarly in line 3 there is one operation hence there are 3 x.
Re: Fix blank columns [message #429025 is a reply to message #429012] Sun, 01 November 2009 03:37 Go to previous messageGo to next message
halim
Messages: 100
Registered: September 2008
Senior Member

I don't know whats the problem to give table script and sample data.

By the way, May be its help you..


CREATE TABLE ot_cutting_sheet_head (csh_sys_id VARCHAR2(10),csh_dt DATE);

INSERT INTO OT_CUTTING_SHEET_HEAD ( CSH_SYS_ID, CSH_DT ) VALUES ( 
'50',  TO_Date( '01/10/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
COMMIT;

CREATE TABLE  ot_cutting_sheet_detail (csd_csh_sys_id VARCHAR2(10),csd_sys_id VARCHAR2(10),csd_pos VARCHAR2(100))

INSERT INTO OT_CUTTING_SHEET_DETAIL ( CSD_CSH_SYS_ID, CSD_SYS_ID,
CSD_POS ) VALUES ( 
'100', '50', '101'); 
INSERT INTO OT_CUTTING_SHEET_DETAIL ( CSD_CSH_SYS_ID, CSD_SYS_ID,
CSD_POS ) VALUES ( 
'100', '50', '102'); 
INSERT INTO OT_CUTTING_SHEET_DETAIL ( CSD_CSH_SYS_ID, CSD_SYS_ID,
CSD_POS ) VALUES ( 
'100', '50', '103'); 
COMMIT;

CREATE TABLE ot_cutting_sheet_operation (cso_csd_sys_id VARCHAR2(10),csd_sys_id VARCHAR2(10), cso_oper_code VARCHAR2(100))

INSERT INTO OT_CUTTING_SHEET_OPERATION ( CSO_CSD_SYS_ID, CSO_SYS_ID,
CSO_OPER_CODE ) VALUES ( 
'101', '50', 'cut'); 
INSERT INTO OT_CUTTING_SHEET_OPERATION ( CSO_CSD_SYS_ID, CSO_SYS_ID,
CSO_OPER_CODE ) VALUES ( 
'101', '50', 'weld'); 
INSERT INTO OT_CUTTING_SHEET_OPERATION ( CSO_CSD_SYS_ID, CSO_SYS_ID,
CSO_OPER_CODE ) VALUES ( 
'101', '50', 'paint'); 
INSERT INTO OT_CUTTING_SHEET_OPERATION ( CSO_CSD_SYS_ID, CSO_SYS_ID,
CSO_OPER_CODE ) VALUES ( 
'101', '50', 'blast'); 
INSERT INTO OT_CUTTING_SHEET_OPERATION ( CSO_CSD_SYS_ID, CSO_SYS_ID,
CSO_OPER_CODE ) VALUES ( 
'102', '50', 'cut'); 
INSERT INTO OT_CUTTING_SHEET_OPERATION ( CSO_CSD_SYS_ID, CSO_SYS_ID,
CSO_OPER_CODE ) VALUES ( 
'102', '50', 'weld'); 
INSERT INTO OT_CUTTING_SHEET_OPERATION ( CSO_CSD_SYS_ID, CSO_SYS_ID,
CSO_OPER_CODE ) VALUES ( 
'103', '50', 'cut'); 
COMMIT;



and the Query you can use :

  SELECT  csh_dt, csd_pos, MAX (cut) cut, MAX (weld) weld, MAX (paint) paint,
         MAX (blast) blast
    FROM (SELECT csh_dt, csd_pos, DECODE (seqno, 1, cso_oper_code, 'X') cut,
                 DECODE (seqno, 2, cso_oper_code, 'X') weld,
                 DECODE (seqno, 3, cso_oper_code, 'X') paint,
                 DECODE (seqno, 4, cso_oper_code, 'X') blast
            FROM (SELECT   ROW_NUMBER () OVER (PARTITION BY csd_pos ORDER BY csd_pos)
                                                                        seqno,
                           csh_dt, cso_oper_code, csd_pos
                      FROM ot_cutting_sheet_head a,
                           ot_cutting_sheet_detail b,
                           ot_cutting_sheet_operation c
                     WHERE a.csh_sys_id = b.csd_sys_id
                       AND a.csh_sys_id = c.cso_sys_id
                       AND b.csd_pos = c.cso_csd_sys_id
                  ORDER BY csd_pos))
GROUP BY csh_dt,csd_pos
order by csh_dt,csd_pos



Regards
Halim

[Updated on: Sun, 01 November 2009 03:42]

Report message to a moderator

Re: Fix blank columns [message #429027 is a reply to message #429025] Sun, 01 November 2009 04:35 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Mr.Halim , thanks very much , i have resolved my problem using your query with slight modification .But can you explain me how this works in step if you can.I am sorry for bothering you too much.Can you tell me more about yourself like whereabouts or email id.Once again thanks very much.Jazak Allah
Re: Fix blank columns [message #429030 is a reply to message #429025] Sun, 01 November 2009 05:59 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Dear Mr.halim,

i modified this code as below for my requirement.

[CODE]
SELECT MAX (cut) cut, MAX (weld) weld, MAX (paint) paint,
MAX (blast) blast
FROM (SELECT cso_oper_code,CSD_PS_CODE, DECODE (cso_op_stage_no, 1, cso_oper_code, 'X') cut,
DECODE (cso_op_stage_no, 2, cso_oper_code, 'X') weld,
DECODE (cso_op_stage_no, 3, cso_oper_code, 'X') paint,
DECODE (cso_op_stage_no, 4, cso_oper_code, 'X') blast
FROM (SELECT ROW_NUMBER () OVER (PARTITION BY csd_ps_code ORDER BY csd_ps_code)
seqno,
csh_dt, cso_oper_code, csd_ps_code,cso_op_stage_no
FROM ot_cutting_sheet_head a,
ot_cutting_sheet_detail b,
ot_cutting_sheet_operation c
WHERE a.csh_sys_id = b.csd_csh_sys_id
AND b.csd_sys_id = c.cso_csd_sys_id
and b.csd_sys_id = 1540454
ORDER BY c.cso_op_stage_no))
GROUP BY cso_oper_code
[CODE\]


i am getting the result as below

CUT WELD PAINT BLAST
RE X X X
X HO X X

but i want it in 1 line

CUT WELD PAINT BLAST
RE HO X X

is that possible

Re: Fix blank columns [message #429063 is a reply to message #429030] Sun, 01 November 2009 23:27 Go to previous message
halim
Messages: 100
Registered: September 2008
Senior Member

Dear Mr. Arif
How can i able to run your query?
I have no such table structure and data.

how this works in step:--

1) --: get a sequential number value for each operation per stage:

SELECT   ROW_NUMBER () OVER (PARTITION BY csd_pos ORDER BY csd_pos) seqno,
                           csh_dt, cso_oper_code, csd_pos
                      FROM ot_cutting_sheet_head a,
                           ot_cutting_sheet_detail b,
                           ot_cutting_sheet_operation c
                     WHERE a.csh_sys_id = b.csd_sys_id
                       AND a.csh_sys_id = c.cso_sys_id
                       AND b.csd_pos = c.cso_csd_sys_id
                  ORDER BY csd_pos

2)--- use this sequential number in a decode/case construction.
  ---this allows to break the values in 4 different columns.
 


SELECT csh_dt, csd_pos, DECODE (seqno, 1, cso_oper_code, 'X') cut,
                 DECODE (seqno, 2, cso_oper_code, 'X') weld,
                 DECODE (seqno, 3, cso_oper_code, 'X') paint,
                 DECODE (seqno, 4, cso_oper_code, 'X') blast
            FROM (SELECT   ROW_NUMBER () OVER (PARTITION BY csd_pos ORDER BY csd_pos)
                                                                        seqno,
                           csh_dt, cso_oper_code, csd_pos
                      FROM ot_cutting_sheet_head a,
                           ot_cutting_sheet_detail b,
                           ot_cutting_sheet_operation c
                     WHERE a.csh_sys_id = b.csd_sys_id
                       AND a.csh_sys_id = c.cso_sys_id
                       AND b.csd_pos = c.cso_csd_sys_id
                  ORDER BY csd_pos)


3) --In above we distributed the values over 4 columns
   --Now need to aggregate the values using a MAX()... GROUP BY:

SELECT  csh_dt, csd_pos, MAX (cut) cut, MAX (weld) weld, MAX (paint) paint,
         MAX (blast) blast
    FROM (SELECT csh_dt, csd_pos, DECODE (seqno, 1, cso_oper_code, 'X') cut, ---2)  
                 DECODE (seqno, 2, cso_oper_code, 'X') weld,
                 DECODE (seqno, 3, cso_oper_code, 'X') paint,
                 DECODE (seqno, 4, cso_oper_code, 'X') blast
            FROM (SELECT   ROW_NUMBER () OVER (PARTITION BY csd_pos ORDER BY csd_pos) --1)  
                        seqno,csh_dt, cso_oper_code, csd_pos
                      FROM ot_cutting_sheet_head a,
                           ot_cutting_sheet_detail b,
                           ot_cutting_sheet_operation c
                     WHERE a.csh_sys_id = b.csd_sys_id
                       AND a.csh_sys_id = c.cso_sys_id
                       AND b.csd_pos = c.cso_csd_sys_id
                  ORDER BY csd_pos))
GROUP BY csh_dt,csd_pos
order by csh_dt,csd_pos



more about myself :-
See my profile


Regards
Muhammad Abdul Halim

[Updated on: Sun, 01 November 2009 23:31]

Report message to a moderator

Previous Topic: oracle report builder link open window
Next Topic: printing extra page
Goto Forum:
  


Current Time: Mon Sep 26 19:39:51 CDT 2016

Total time taken to generate the page: 0.07427 seconds