5 Replies Latest reply: Nov 6, 2012 12:53 AM by KhaledFarouk RSS

    view  lexical parameters coulumns

    KhaledFarouk
      Hi All
      Operating system windows xp
      Report Builder 10.1.2.0.2

      I've Created a quiry based on lexical parameters on ( coulumns + table + where clause ) & i'ts working fine till now at the table & where clause but not working fine with columns.
      the coulumns @ runtime display the column name not the data


      here is my select statment
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      select b.spec_id , b.SPEC_NAME ,t.SPEC_DESC ,b.SPEC_VERS ,i.item_no , i.item_desc1 , meaning status
      &col1 &col2
      from gmd_specifications_b b , gmd_specifications_tl t ,ic_item_mst_b I , gmd_qc_status_tl S
      &tab1 &tab2 &tab3
      WHERE b.spec_id = t.spec_id
      AND t.LANGUAGE = USERENV ('LANG')
      and i.ITEM_ID = b.ITEM_ID
      AND S.status_code = b.SPEC_STATUS
      AND entity_type = 'S'
      AND S.LANGUAGE = USERENV ('LANG')
      and (b.SPEC_ID = :V_SPEC_ID or :V_SPEC_ID is null)
      and (SPEC_STATUS = :V_SPEC_STATUS or :V_SPEC_STATUS is null)
      &where1
      &where2
      &where3
      &where4
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      I've created before report trigger contains

      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      if :vaildate_rep = 'Y' then
      :col1      :=',gst.test_code ';
           :col2      :=',tc.test_desc ';
      --     select ',gst.test_code ' , ',tc.test_desc test_desc' into :col1 , :col2 from dual;
           :tab1      :=',gmd_qc_tests_b gst';
           :tab2      :=',gmd_qc_tests_tl tc';
           :tab3      :=',gmd_spec_tests_b gstb';
           :where1 :='and gst.test_id = tc.test_id';
           :where2 := 'AND tc.LANGUAGE = t.LANGUAGE';
           :where3 :='and gstb.spec_id = b.spec_id';
           :where4 :='and gstb.test_id = gst.test_id';
      elsif :vaildate_rep = 'N' then
           
           :col1      :=' ';
           :col2      :=' ';
           :tab1      :=' ';
           :tab2      :=' ';
           :tab3      :=' ';
           :where1 :=' ';
           :where2 :=' ';
           :where3 :=' ';
           :where4 :=' ';
           
           
           end if;
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        • 1. Re: view  lexical parameters coulumns
          kikolus
          Hi,
          Using dynamic columns in reports are not an good idea. If you really need it you can do that, but your initial value of parameters (col1,col2 etc) should points to some real tables so Report builder will be able to map columns in your query, otherwise he points to constant and even you replace column with real name then you get just constant (column name instead of data). Nota, that column sizes (after change) must be the same. Consider such example:
           
          select &p_1 
          from dual 
          If parameter p_1 is declared as character 1 then your column in query has data type Char 1.
          So, if you change parameter in Before report trigger to:
           
          :p_1 := '''11111'' x'; 
          You'll get '1' as an output - data will be truncated without any warning/error messages.
          • 2. Re: view  lexical parameters coulumns
            KhaledFarouk
            Hi : kikolus      
            Thanks for your replay
            the target of using dynamic columns in my case is to show mater data only or (master & detail data) at the report runtime.


            I've Tried creating simple Sql statment with column elias an it's working but when tring to make multi table Sql statment error started to appear

            for example
            -------------------------------------------------------------------------------------------------------------------------------------------------
            -------------------------------------------------------------------------------------------------------------------------------------------------
            select &spec_id spec_id , &SPEC_NAME SPEC_NAME
            from gmd_specifications_b b
            -------------------------------------------------------------------------------------------------------------------------------------------------
            -------------------------------------------------------------------------------------------------------------------------------------------------
            this statment is runnig perfect but by adding more table it's craching.
            i don't know why.
            • 3. Re: view  lexical parameters coulumns
              kikolus
              Hi,
              Did you get any error message? It's seams that after change your query is no longer valid. What's your new variable values?

              Regards

              Edited by: kikolus on 2012-11-05 12:04
              • 4. Re: view  lexical parameters coulumns
                KhaledFarouk
                Hi

                i've just noticed that when the column based on dynamic table it's craching. and amessag appears Ora-00903 invalid table name.
                but if there is a dynamic columns based on static table it's working

                example
                select SPEC_NAME
                from &gmd_specifications_b
                wish if there is a solution for this isseu

                also i've found a complex sql statment @ this link
                http://www.allinterview.com/showanswers/29009.html



                Regards
                • 5. Re: view  lexical parameters coulumns
                  KhaledFarouk
                  Hi
                  I've just found a solution for this isseu it's not the perfect one but it'S working.
                  the problem i've found that i can't query with columns on a random table. so i've create a static statment with some random variables
                  and change the static query @ before report trigeer

                  here is my final statment.
                  &s1 - &s2 for columns
                  &s3 - &s4 for tables
                  &s5 - &s6 for where clasuse
                  -----------------------------------------------------------------------------------------------------------------------------------------------------
                  -----------------------------------------------------------------------------------------------------------------------------------------------------
                  select B.SPEC_ID , b.SPEC_NAME ,t.SPEC_DESC ,b.SPEC_VERS ,i.item_no , i.item_desc1 , meaning status

                  &S1 ,gst.test_code ,tc.test_desc &S2

                  from gmd_specifications_b b , gmd_specifications_tl t ,ic_item_mst_b I , gmd_qc_status_tl S

                  &S3 ,gmd_qc_tests_b gst ,gmd_qc_tests_tl tc ,gmd_spec_tests_b gstb &S4

                  WHERE b.spec_id = t.spec_id
                  AND t.LANGUAGE = USERENV ('LANG')
                  and i.ITEM_ID = b.ITEM_ID
                  AND S.status_code = b.SPEC_STATUS
                  AND entity_type = 'S'
                  AND S.LANGUAGE = USERENV ('LANG')
                  and (b.SPEC_ID = :V_SPEC_ID or :V_SPEC_ID is null)
                  and (SPEC_STATUS = :V_SPEC_STATUS or :V_SPEC_STATUS is null)

                  &S5

                  and gst.test_id = tc.test_id
                  AND tc.LANGUAGE = t.LANGUAGE--USERENV ('LANG')
                  and gstb.spec_id = b.spec_id
                  and gstb.test_id = gst.test_id

                  &S6
                  -----------------------------------------------------------------------------------------------------------------------------------------------------
                  -----------------------------------------------------------------------------------------------------------------------------------------------------
                  function BeforeReport return boolean is
                  begin
                  if :vaildate_rep = 'Y' then
                       
                       
                  :S1:= '/*';
                  :S2:= '*/ ,gst.test_code ,tc.test_desc ';
                  :S3:= '/*';
                  :S4:= '*/,gmd_qc_tests_b gst ,gmd_qc_tests_tl tc ,gmd_spec_tests_b gstb';
                  :S5:= '/*';
                  :S6:= '*/
                  and gst.test_id = tc.test_id
                  AND tc.LANGUAGE = t.LANGUAGE
                  and gstb.spec_id = b.spec_id
                  and gstb.test_id = gst.test_id';

                       
                  elsif :vaildate_rep = 'N' then

                  :S1:= '/* ';
                  :S2:= '*/, null , null';
                  :S3:= '/*';
                  :S4:= '*/';
                  :S5:= '/*';
                  :S6:= '*/'      ;
                       end if;
                  return (TRUE);
                  end;
                  -----------------------------------------------------------------------------------------------------------------------------------------------------
                  -----------------------------------------------------------------------------------------------------------------------------------------------------

                  IT'S WORKING FINE NOW :D