1 2 Previous Next 16 Replies Latest reply: Aug 26, 2014 1:40 PM by TexasApexDeveloper RSS

    SQL query + html

    Ziut

      Hi

      I have a test_table (col1, col2, col3) on apex.oracle.com.

      Firstly:

      I would like to create report as type SQL Query:

       

      select col1, col2, col3 from test_table;

       

      I would like to change properties of col2 eg font or color during generation.

      In source of report I must write code (it is only example ???):

       

      select col1,

      '<span style="font-weight:bold;">' col2 '</span>',

      col3

      from test_table;

       

      I think it is wrong but I would like to know what are the first principles of linking sql and html.

       

      Secondly:

      I create report as type PLSQL function body returning SQL query:

      begin

      return 'select col1, '

      || ''<span style="font-weight:bold;">''|| 'col2 '||'</span>',

      ||'col3

      from test_table;';

      end;

       

      What is wrong in both examples? I can't  find it in documentation. Could you tell me something about principles.

      Regards

      Ziut

        • 1. Re: SQL query + html
          jariola

          Hi,

           

          When you edit report column attributes you can find under Column Formatting.

          You should enter column HTML there with column substitution e.g.

          <span style="font-weight:bold;">#COL2#</span>
          

           

          Regards,
          Jari

          • 2. Re: SQL query + html
            Ziut

            that is not the point.


            html should be inside sql query.

            Ziut


            • 3. Re: SQL query + html
              TexasApexDeveloper

              NO IT SHOULD NOT BE IN THE SQL, you SHOULD try and keep the presentation layer items such as Fonts & colors to the presentation layer such as the report layout.  Putting the font information and such in the query clutters it and makes it less maintainable for future usage..

               

              If anyone developing in my group starting doing that I would make sure they UNDERSTOOD how to develop reports in APEX and how to write sql...

               

               

              Thank you,

               

              Tony Miller
              LuvMuffin Software
              Ruckersville, VA

              • 4. Re: SQL query + html
                Ziut

                Tony

                But this construction exists. I saw it in blog and in forum. I would like to know how to do it during building dynamic report only. User should see  rows (fields) with different properties on his screen.

                 

                How to do it?

                Ziut

                • 5. Re: SQL query + html
                  Ziut

                  Jariola

                  But how to do it  as conditional:

                  ....

                  eg:

                  if #COL3# is null (or other) then

                       <span style="font-weight:bold;">#COL2#</span>

                  else

                       <span style="font-weight:....;">#COL2#</span>

                  end if;

                   

                  Ziut

                  • 6. Re: Re: SQL query + html
                    jariola

                    Hi,

                     

                    One way is query like

                    select col1, 
                    col2,
                    col3,
                    case when col1 = 'XXX' then
                     'bold'
                    else
                     'normal'
                    end as fontweight
                    from test_table;
                    

                     

                    Set FONTWEIGHT column as hidden.

                     

                    Then place to COL2 HTML expression

                    <span style="font-weight:#FONTWEIGHT#;">#COL2#</span>
                    

                     

                     

                    Regards,

                    Jari

                    • 7. Re: SQL query + html
                      Ziut

                      Thanks Jariola

                      I know this solution but I saw previous construction and  I would like to know how it may be used.

                      I still don't know.

                      Regards

                      Ziut

                      • 8. Re: SQL query + html
                        jariola

                        Ziut wrote:

                         

                        Thanks Jariola

                        I know this solution but I saw previous construction and  I would like to know how it may be used.

                        I still don't know.

                        Regards

                        Ziut

                        Hi,

                         

                        I don't understand what you looking for.

                         

                        Is your problem that report escape your column data and you see html tags ?

                        If you like embed html to query you need change column display as to standard report column. Is that what you mean?

                         

                        I just don't understand why you like do that, as it is not by "best practices" .

                         

                        Regards,

                        Jari

                        • 9. Re: SQL query + html
                          TexasApexDeveloper

                          So lets get this straight.. You have been advised on TWO different solutions to the issue and DENY them BOTH, instead you want to understand a THIRD solution that you have NOT even posted a link to to demonstrate it fully?

                           

                          Shesh, enough of this.  I would NOT recommend mixing the SQL and the formatting, since you can achieve this in the report layout, thus NOT polluting the sql of the report.  You do NOT want to look at this solution..

                           

                          'nuff said..

                           

                          Thank you,

                           

                          Tony Miller
                          LuvMuffin Software
                          Ruckersville, VA

                          • 10. Re: SQL query + html
                            Mike Kutz

                            Do not put the HTML inside the SQL!

                            Your sorts will be based on the HTML Code, not the column value!!!

                             

                            Example app:

                            https://apex.oracle.com/pls/apex/f?p=70399:16

                            name/password:  demo/demo

                             

                            MK

                            • 11. Re: SQL query + html
                              fac586

                              TexasApexDeveloper wrote:

                               

                              So lets get this straight.. You have been advised on TWO different solutions to the issue and DENY them BOTH, instead you want to understand a THIRD solution that you have NOT even posted a link to to demonstrate it fully?

                              And this is the third thread in what appears to be a series of XY questions dancing around the actual requirement:

                               

                               

                              in which they've rejected inputs from me, Denes, Tom, Jari, and Tony. They're rapidly running out of experts!

                              • 12. Re: SQL query + html
                                TexasApexDeveloper

                                You can lead a horse to water, but getting em to drink is the REAL task...  They know what they want and NO ONE can tell them otherwise, however stupid they sound arguing the point....

                                 

                                Thank you,

                                 

                                Tony Miller
                                LuvMuffin Software
                                Ruckersville, VA

                                • 13. Re: SQL query + html
                                  898914

                                  I am trying to unscsubscribe but there is no option to remove this option in the oracle community preference .

                                   

                                   

                                   

                                  Albert

                                   

                                   

                                  • 14. Re: SQL query + html
                                    TexasApexDeveloper

                                    Unsubscribe from WHAT exactly?  This thread? This forum? This world?

                                     

                                    Thank you,

                                     

                                    Tony Miller
                                    LuvMuffin Software
                                    Ruckersville, VA

                                    1 2 Previous Next