This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Sep 18, 2013 1:17 AM by Ivan2405 RSS

Select query in a report

Ivan2405 Newbie
Currently Being Moderated

Hi!

 

Is it possible to modify a select query for a report in the following way:

 

I have created two fields where users input values and operators.

Operators selection is a static list consisting of: =, like , in

Value field is a text field.


The current query is:

 

SELECT

A,

B,

C

from TABLE

 

I would like to add:

 

WHERE

A : P1_OPERATOR : P1_VALUE

 

but it doesn't work; I get a notification that the operator is incorrect.

It has a defaulft value = and when I set let's say 5 in the value field the whole query should be:

 

SELECT

A,

B,

C

from TABLE

WHERE

A = 5

 

Regards!

  • 1. Re: Select query in a report
    nvncro Newbie
    Currently Being Moderated

    It probably doesn't work because you get string from items, so your query looks like

     

    SELECT

    A,

    B,

    C

    from TABLE

    WHERE

    A '=' '5'

     

     

    Try to set function returning SQL query where you will pass these parameters and then return sql string

  • 2. Re: Select query in a report
    Ivan2405 Newbie
    Currently Being Moderated

    I tried to create a procedure that would generate a statement that would then be executed.

    However, I get an error that SELECT keyword is expected.

    So I don't know how to bypass that...

     

    Regards,

    Ivan

  • 3. Re: Select query in a report
    nvncro Newbie
    Currently Being Moderated

    What is the query your function returns?

     

    You dont even need to write function if you have simple functionality as above mentioned, just write for example

     

    return SELECT A,B,C from TABLE WHERE A '  || :P1_OPERATOR ||  :P1_VALUE;

  • 4. Re: Select query in a report
    Ivan2405 Newbie
    Currently Being Moderated

    I tried that already, and it didn't work. I don't quite understand the one ' after A.
    This is the exact where clause:

    where :P7_X_FC1 || :P7_X_O1 || :P7_X_O1

     

    where the values are column name, operator, value.


    Regards,
    Ivan

  • 5. Re: Select query in a report
    nvncro Newbie
    Currently Being Moderated

    http://apex.oracle.com/pls/apex/f?p=4550:1:0:::::

    MULTICOM

    FORUMHELP

    IVAN

     

     

    page 2, look at source of report, I recreated your problem

    When you choose operator and dept no press reset

  • 6. Re: Select query in a report
    Ivan2405 Newbie
    Currently Being Moderated

    That is Workspace, Username and Password?
    I can't login with those parameters...


    Regards,

    Ivan

  • 7. Re: Select query in a report
    nvncro Newbie
    Currently Being Moderated

    Sorry , wrong password, please try again, I have updated password above

  • 8. Re: Select query in a report
    Ivan2405 Newbie
    Currently Being Moderated

    Hmm, I get the following error:

     

    Updatable report parse error:

    ORA-20001: Query must begin with SELECT or WITH


    The only difference I see is that your type is SQL Query (PL/SQL function body returning SQL query) while I have SQL Query (updateable report) hardcoded.

    I have APEX 4.2.2, I'm not quite sure how to change this...

    This is my code:

     

    begin

     

     

    :P7_QUERY := 'select ROWID,BNK_ID,MSR_PRD_ID,SRC_STM_ID,ID,ID_RETKA,RSP,OZNAKA_RETKA,DATUM_STANJA,OZNAKA_IZVJESCA,OZNAKA_KOMITENTA,MBR_KOMITENTA,KOMITENT_NEREZ,ZUPANIJA,DRZAVA,SEKTOR_NEREZIDENTA,VRSTA_POVEZANOSTI,INSTRUMENT,ISIN,VALUTA,OTKAZNI_ROK,IZVORNO_DOSPIJECE,VRSTA_INDEKSACIJE,VALUTA_INDEKSACIJE,PORTFELJ,UTRZIVOST_KREDITA,ZNACAJKE_KAPITALA,RIZICNA_SKUPINA,UGRADJENI_DERIVAT,ODNOSNA_VARIJABLA,PREDZNAK,IZNOS,IZNOS_ACTUAL,VRSTA_IZNOSA,KOMITENT_PBR,UDJELI_POVEZ_C,AR_ID,AU_ID,ACT_AR_BAL_KN,ACT_AR_BAL,AR_BUSS_ID,MTI_CCY_TP_ID,REG_NO,REG_SFX,JMBG_ID_NO,IP_ID,NO_DYS_OO,TAX_ID_NO,INSTRUMENT_OLD,PREDZNAK_OLD,IZNOS_ACTUAL_OLD,ACT_AR_BAL_KN_OLD,ACT_AR_BAL_OLD,NAPOMENA,NOVI_POSAO_F,LISTA_SUMARNA,LISTA_REKAP,POSTOTAK1,POSTOTAK2,POSTOTAK3,DZS_IDY_CL_ID,HNB_IP_CL_ID,EXG_RT_CRD_RSK_F from DWP.IZV_SLOG_DET

    where ' || :P7_X_FC1 || :P7_X_O1 || :P7_X_O1;

     

     

    return :P7_QUERY;

     

     

    end;


    I have also set Use Generic Column Names (parse query at runtime only) nad P7_QUERY as page item to submit.

     

    Regards,

    Ivan

  • 9. Re: Select query in a report
    nvncro Newbie
    Currently Being Moderated

    You dont have option SQL Query (PL/SQL function body returning SQL query) ?

     

    provide some screen shots please, it is easyer to do it like that.

     

    Is that report you use classic/interacitve report?

  • 10. Re: Select query in a report
    Ivan2405 Newbie
    Currently Being Moderated

    I can't seem to upload a picture, the icon is gray.

    Do you have an e-mail address?

    I used classic report.

     

    Btw. when I ran your query I got the same error:

     

    failed to parse SQL query: ORA-00920: invalid relational operator 


    Regards,

    Ivan

  • 11. Re: Select query in a report
    nvncro Newbie
    Currently Being Moderated

    upload image here

    http://www.deviantpics.com/

    and post direct link for image

     

    you put my query in your application or?

    You have to press reset button refresh the report in my example

  • 12. Re: Select query in a report
    Ivan2405 Newbie
    Currently Being Moderated

    I can't open the link while on my computer on work, the page is blocked.

    While I'm in your application I go to Page 2 and then run. But now it works.

    Like I said before, I modified your query to suit my table, but I get the error:

     

    begin

     

     

    :P7_QUERY := 'select ROWID,BNK_ID,MSR_PRD_ID,SRC_STM_ID,ID,ID_RETKA,RSP,OZNAKA_RETKA,DATUM_STANJA,OZNAKA_IZVJESCA,OZNAKA_KOMITENTA,MBR_KOMITENTA,KOMITENT_NEREZ,ZUPANIJA,DRZAVA,SEKTOR_NEREZIDENTA,VRSTA_POVEZANOSTI,INSTRUMENT,ISIN,VALUTA,OTKAZNI_ROK,IZVORNO_DOSPIJECE,VRSTA_INDEKSACIJE,VALUTA_INDEKSACIJE,PORTFELJ,UTRZIVOST_KREDITA,ZNACAJKE_KAPITALA,RIZICNA_SKUPINA,UGRADJENI_DERIVAT,ODNOSNA_VARIJABLA,PREDZNAK,IZNOS,IZNOS_ACTUAL,VRSTA_IZNOSA,KOMITENT_PBR,UDJELI_POVEZ_C,AR_ID,AU_ID,ACT_AR_BAL_KN,ACT_AR_BAL,AR_BUSS_ID,MTI_CCY_TP_ID,REG_NO,REG_SFX,JMBG_ID_NO,IP_ID,NO_DYS_OO,TAX_ID_NO,INSTRUMENT_OLD,PREDZNAK_OLD,IZNOS_ACTUAL_OLD,ACT_AR_BAL_KN_OLD,ACT_AR_BAL_OLD,NAPOMENA,NOVI_POSAO_F,LISTA_SUMARNA,LISTA_REKAP,POSTOTAK1,POSTOTAK2,POSTOTAK3,DZS_IDY_CL_ID,HNB_IP_CL_ID,EXG_RT_CRD_RSK_F from DWP.IZV_SLOG_DET

    where ' || :P7_X_FC1 || :P7_X_O1 || :P7_X_O1;

     

     

    return :P7_QUERY;

     

     

    end;

     

    I think that it won't work because I cant choose SQL Query (PL/SQL function body returning SQL query)...

  • 13. Re: Select query in a report
    Ivan2405 Newbie
    Currently Being Moderated

    I have created a new report from scratch. Now I can choose SQL Query (PL/SQL function body returning SQL query).

    I'm still working on it, I'll get back to you once I create all the elements.


    Regards,

    Ivan

  • 14. Re: Select query in a report
    nvncro Newbie
    Currently Being Moderated

    yes for sure you have to choose SQL Query (PL/SQL function body returning SQL query)

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points