Oracle query help urgent !! | Newbie | | Join Date: Aug 2008
Posts: 8
| | |
Hi,
This is the query i am trying to build
I have attached the same in the file....
while running its throwing an error ........
NVL((SELECT USAGEMODE FROM KKTYPEMAST_AP B WHERE B.TYPE_ID_USG=A.TYPE_ID_USG AND
B.JURISDICTION=A.JURISDICTION),(SELECT USAGEMODE FROM KKTYPEMAST_AP B WHERE B.T
YPE_ID_USG=A.TYPE_ID_USG AND ROWNUM=1)),
*
ERROR at line 15:
ORA-06550: line 15, column 6:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
ORA-06550: line 15, column 112:
PLS-00103: Encountered the symbol "," when expecting one of the following:
; return returning and or
ORA-06550: line 15, column 199:
PLS-00103: Encountered the symbol ")" when expecting one of the following:
, ; for <an identifier>
<a double-quoted delimited-identifier> group having intersect
minus order start union where connect
ORA-06550: line 18, column 5:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
. , @ ; for <an identifier>
<a double-quoted delimited-identifier> group having intersect
minus order p
Pls help me for the error
|  | Moderator | | Join Date: Mar 2007 Location: Hyderabad, India
Posts: 2,192
| | | re: Oracle query help urgent !!
You did not give the Alias name to the NVL functions: -
SELECT BILL_REF_NO,
-
USAGEMODE,
-
BILLED_AMOUNT,
-
DISCOUNT,
-
SOU,
-
UNITS
-
FROM (
-
SELECT
-
A.BILL_REF_NO,
-
NVL(
-
(SELECT USAGEMODE
-
FROM KKTYPEMAST_AP B
-
WHERE B.TYPE_ID_USG=A.TYPE_ID_USG
-
AND B.JURISDICTION=A.JURISDICTION),
-
(SELECT USAGEMODE
-
FROM KKTYPEMAST_AP B
-
WHERE B.TYPE_ID_USG=A.TYPE_ID_USG
-
AND ROWNUM=1)
-
) usagemode,
-
A.BILLED_AMOUNT,
-
A.DISCOUNT,
-
NVL2(
-
(SELECT 1
-
FROM
-
KKTYPEMAST_AP
-
WHERE TYPE_ID_USG=A.TYPE_ID_USG
-
AND (UPPER(TYPEDESC) LIKE '%MARKUP%' OR UPPER(TYPEDESC) LIKE '%TAX%' OR (UPPER(TYPEDESC) LIKE '%PSTN%'
-
AND TYPE_ID_USG LIKE '%2'))
-
AND ROWNUM=1),A.SOU,0
-
) sou,
-
NVL2(
-
(SELECT 1
-
FROM KKTYPEMAST_AP
-
WHERE TYPE_ID_USG=A.TYPE_ID_USG
-
AND (UPPER(TYPEDESC) LIKE '%MARKUP%' OR UPPER(TYPEDESC) LIKE '%TAX%'
-
OR (UPPER(TYPEDESC) LIKE '%PSTN%' AND TYPE_ID_USG LIKE '%2'))
-
AND ROWNUM=1),A.UNITS,0
-
) units
-
FROM (SELECT /*+ ordered*/ CB.ACCOUNT_NO,
-
CB.SUBSCR_NO,
-
CB.SUBSCR_NO_RESETS,
-
CB.BILL_REF_NO,
-
CB.BILL_REF_RESETS,
-
CB.TYPE_ID_USG,
-
CD.JURISDICTION,
-
SUM(CB.BILLED_AMOUNT)/100 BILLED_AMOUNT,
-
SUM(CB.DISCOUNT)/100 DISCOUNT,
-
SUM(PRIMARY_UNITS) SOU,
-
SUM(CD.RATED_UNITS) UNITS
-
FROM
-
CD_CU4@CU4 CD,
-
CB_CU4@CU4 CB
-
WHERE CD.CDR_DATA_PARTITION_KEY=CB.CDR_DATA_PARTITION_KEY
-
AND CD.MSG_ID=CB.MSG_ID
-
AND CD.MSG_ID2=CB.MSG_ID2
-
AND CD.MSG_ID_SERV=CB.MSG_ID_SERV
-
AND CD.SPLIT_ROW_NUM=CB.SPLIT_ROW_NUM
-
AND (CB.BILL_REF_NO,CB.BILL_REF_RESETS) IN (SELECT BILL_REF_NO,BILL_REF_RESETS FROM KK_MOU_DUMP_CU4)
-
GROUP BY CB.ACCOUNT_NO,
-
CB.SUBSCR_NO,
-
CB.SUBSCR_NO_RESETS,
-
CB.BILL_REF_NO,
-
CB.BILL_REF_RESETS,
-
CB.TYPE_ID_USG,
-
CD.JURISDICTION) A);
-
|  |
Similar Oracle Database bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,467 network members.
|
