Home » Developer & Programmer » Reports & Discoverer » WITH clause in Report Builder (Report Builder 10.1.2.0.2)
WITH clause in Report Builder [message #587005] Wed, 12 June 2013 06:57 Go to next message
amauri
Messages: 18
Registered: May 2013
Location: Italy
Junior Member
Hi,

I'm trying to have RB use the following query:
WITH MY_DATASET
        AS (SELECT /*+ materialize */
                   DISTINCT
                   N.NAME_ID NID,
                   NVL (TO_CHAR (RN.CONFIRMATION_NO), 'ACCOMPAGNATORE') CONF,
                   RN.RESV_NAME_ID,
                   MEV.TAX1_NO PARTITA_IVA,
                   MEV.TAX2_NO CODICE_FISCALE,
                   N.COUNTRY NAZIONE,
                   DECODE (N.NAME_TYPE, 'D', 'INDIVIDUALE', 'ALTRO')
                      TIPOCLIENTE,
                   DECODE (N.NAME_TYPE,
                           'D', N.NAME || ', ' || N.FIRST,
                           N.COMPANY)
                      NOME
              FROM MFI_EXP_VIEW MEV,
                   TRX_ROUTING_INSTRUCTIONS TRI,
                   NAME_VIEW N,
                   RESERVATION_NAME RN
             WHERE     MEV.COMPUTED_RESV_STATUS = 'CHECKED IN'
                   AND TRI.RESV_NAME_ID(+) = MEV.RESV_NAME_ID
                   AND N.NAME_ID = TRI.BILL_TO_NAME_ID
                   AND TRI.FOLIO_VIEW IS NOT NULL
                   AND RN.RESV_NAME_ID = MEV.RESV_NAME_ID
            UNION
            SELECT /*+ materialize */
                   DISTINCT
                   N.NAME_ID NID,
                   NVL (TO_CHAR (RN.CONFIRMATION_NO), 'ACCOMPAGNATORE') CONF,
                   RN.RESV_NAME_ID,
                   MEV.TAX1_NO PARTITA_IVA,
                   MEV.TAX2_NO CODICE_FISCALE,
                   N.COUNTRY NAZIONE,
                   DECODE (N.NAME_TYPE, 'D', 'INDIVIDUALE', 'ALTRO')
                      TIPOCLIENTE,
                   DECODE (N.NAME_TYPE,
                           'D', N.NAME || ', ' || N.FIRST,
                           N.COMPANY)
                      NOME
              FROM MFI_EXP_VIEW MEV, NAME_VIEW N, RESERVATION_NAME RN
             WHERE     MEV.COMPUTED_RESV_STATUS = 'CHECKED IN'
                   AND N.NAME_ID = RN.NAME_ID
                   AND RN.RESV_NAME_ID = MEV.RESV_NAME_ID)
SELECT CONF, NOME, 'IT IND NOCF' TIPO
  FROM MY_DATASET
 WHERE     NAZIONE = 'IT'
       AND TIPOCLIENTE = 'INDIVIDUALE'
       AND CODICE_FISCALE IS NULL
UNION
SELECT CONF, NOME, 'IT AZ NOCF' TIPO
  FROM MY_DATASET
 WHERE     NAZIONE = 'IT'
       AND TIPOCLIENTE != 'INDIVIDUALE'
       AND CODICE_FISCALE IS NULL
       AND PARTITA_IVA IS NOT NULL
UNION
SELECT CONF, NOME, 'IT AZ NOPI' TIPO
  FROM MY_DATASET
 WHERE     NAZIONE = 'IT'
       AND TIPOCLIENTE != 'INDIVIDUALE'
       AND CODICE_FISCALE IS NOT NULL
       AND PARTITA_IVA IS NULL
UNION
SELECT CONF, NOME, 'IT AZ NOPICF' TIPO
  FROM MY_DATASET
 WHERE     NAZIONE = 'IT'
       AND TIPOCLIENTE != 'INDIVIDUALE'
       AND CODICE_FISCALE IS NULL
       AND PARTITA_IVA IS NULL
UNION
SELECT CONF, NOME, 'NOIT AZ NOPI' TIPO
  FROM MY_DATASET
 WHERE     NAZIONE != 'IT'
       AND TIPOCLIENTE != 'INDIVIDUALE'
       AND PARTITA_IVA IS NULL

But although perfectly working in SQLDeveloper, RB returns an ORA-942 table does not exist error, pointing at MY_DATASET as the offending name.
Any ideas?

[EDITED by LF: applied [code] tags, reformatted the query]

[Updated on: Wed, 12 June 2013 07:12] by Moderator

Report message to a moderator

Re: WITH clause in Report Builder [message #587007 is a reply to message #587005] Wed, 12 June 2013 07:09 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Create a view based on the query then use that in the report.
Re: WITH clause in Report Builder [message #587008 is a reply to message #587007] Wed, 12 June 2013 07:11 Go to previous messageGo to next message
amauri
Messages: 18
Registered: May 2013
Location: Italy
Junior Member
Thanks CookieMonster,

unfortunately that's indeed what I want to avoid Shocked
Re: WITH clause in Report Builder [message #587012 is a reply to message #587008] Wed, 12 June 2013 07:23 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
WITH clause shouldn't be a problem; I tried it right now, works as expected.

Which MY_DATASET does Reports Builder point to? Is it "with my_dataset" or some of "from my_dataset"?
Re: WITH clause in Report Builder [message #587013 is a reply to message #587012] Wed, 12 June 2013 07:25 Go to previous messageGo to next message
amauri
Messages: 18
Registered: May 2013
Location: Italy
Junior Member
Hi,

I know, I have other reports using WITH clause without any issue.
I was wondering whether the reson could be in the UNIONs in the main query...
Re: WITH clause in Report Builder [message #587021 is a reply to message #587013] Wed, 12 June 2013 07:42 Go to previous messageGo to next message
amauri
Messages: 18
Registered: May 2013
Location: Italy
Junior Member
Hi,

I guess I found a way around it:
WITH MY_DATASET
        AS (SELECT /*+ materialize */
                   DISTINCT
                   N.NAME_ID NID,
                   NVL (TO_CHAR (RN.CONFIRMATION_NO), 'ACCOMPAGNATORE') CONF,
                   RN.RESV_NAME_ID,
                   MEV.TAX1_NO PARTITA_IVA,
                   MEV.TAX2_NO CODICE_FISCALE,
                   N.COUNTRY NAZIONE,
                   DECODE (N.NAME_TYPE, 'D', 'INDIVIDUALE', 'ALTRO')
                      TIPOCLIENTE,
                   DECODE (N.NAME_TYPE,
                           'D', N.NAME || ', ' || N.FIRST,
                           N.COMPANY)
                      NOME
              FROM MFI_EXP_VIEW MEV,
                   TRX_ROUTING_INSTRUCTIONS TRI,
                   NAME_VIEW N,
                   RESERVATION_NAME RN
             WHERE     MEV.COMPUTED_RESV_STATUS = 'CHECKED IN'
                   AND TRI.RESV_NAME_ID(+) = MEV.RESV_NAME_ID
                   AND N.NAME_ID = TRI.BILL_TO_NAME_ID
                   AND TRI.FOLIO_VIEW IS NOT NULL
                   AND RN.RESV_NAME_ID = MEV.RESV_NAME_ID
            UNION ALL
            SELECT /*+ materialize */
                   DISTINCT
                   N.NAME_ID NID,
                   NVL (TO_CHAR (RN.CONFIRMATION_NO), 'ACCOMPAGNATORE') CONF,
                   RN.RESV_NAME_ID,
                   MEV.TAX1_NO PARTITA_IVA,
                   MEV.TAX2_NO CODICE_FISCALE,
                   N.COUNTRY NAZIONE,
                   DECODE (N.NAME_TYPE, 'D', 'INDIVIDUALE', 'ALTRO')
                      TIPOCLIENTE,
                   DECODE (N.NAME_TYPE,
                           'D', N.NAME || ', ' || N.FIRST,
                           N.COMPANY)
                      NOME
              FROM MFI_EXP_VIEW MEV, NAME_VIEW N, RESERVATION_NAME RN
             WHERE     MEV.COMPUTED_RESV_STATUS = 'CHECKED IN'
                   AND N.NAME_ID = RN.NAME_ID
                   AND RN.RESV_NAME_ID = MEV.RESV_NAME_ID)
SELECT CONF,
       NOME,
       CASE
          WHEN     NAZIONE = 'IT'
               AND TIPOCLIENTE = 'INDIVIDUALE'
               AND CODICE_FISCALE IS NULL
          THEN
             'IT IND NOCF'
          WHEN     NAZIONE = 'IT'
               AND TIPOCLIENTE != 'INDIVIDUALE'
               AND CODICE_FISCALE IS NULL
               AND PARTITA_IVA IS NOT NULL
          THEN
             'IT AZ NOCF'
          WHEN     NAZIONE = 'IT'
               AND TIPOCLIENTE != 'INDIVIDUALE'
               AND CODICE_FISCALE IS NOT NULL
               AND PARTITA_IVA IS NULL
          THEN
             'IT AZ NOPI'
          WHEN     NAZIONE = 'IT'
               AND TIPOCLIENTE != 'INDIVIDUALE'
               AND CODICE_FISCALE IS NULL
               AND PARTITA_IVA IS NULL
          THEN
             'IT AZ NOCFPI'
          WHEN     NAZIONE != 'IT'
               AND TIPOCLIENTE != 'INDIVIDUALE'
               AND PARTITA_IVA IS NULL
          THEN
             'NOIT AZ NOPI'
       END
          TIPO
  FROM MY_DATASET


[EDITED by LF: applied [code] tags, reformatted the code]

[Updated on: Wed, 12 June 2013 07:45] by Moderator

Report message to a moderator

Re: WITH clause in Report Builder [message #587023 is a reply to message #587013] Wed, 12 June 2013 07:43 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's something strange ... I tested a simple query and it fails:

/forum/fa/10861/0/

Of course, it works fine in SQL*Plus.

It seems that the last UNION makes problems, not the one used in WITH clause. I tried UNION ALL, no difference. I wouldn't know how to fix it (including your problem), sorry.

P.S. Great, you fixed it in the meantime!

[Updated on: Wed, 12 June 2013 07:44]

Report message to a moderator

Re: WITH clause in Report Builder [message #587029 is a reply to message #587023] Wed, 12 June 2013 07:48 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Try making the main select another with select and add a final select that selects from that.
Re: WITH clause in Report Builder [message #587031 is a reply to message #587029] Wed, 12 June 2013 07:50 Go to previous message
amauri
Messages: 18
Registered: May 2013
Location: Italy
Junior Member
Hi CookieMonster,

tried that as well but no success. The only way was to substitute the UNIONs with a CASE statement Smile

Alberto
Previous Topic: hide items in reports
Next Topic: Improving Report Server Performance
Goto Forum:
  


Current Time: Thu Mar 28 09:24:31 CDT 2024