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  |
 |
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 #587021 is a reply to message #587013] |
Wed, 12 June 2013 07:42  |
|
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 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
There's something strange ... I tested a simple query and it fails:
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 |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Feb 06 22:07:15 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
