BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

 
Take control of your CMS with Infolytic (Opens a new window)  

General Notice: No events within the next 45 days.

Each Calculated Column must have an explicit name


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
rit372002
Principal Member
Principal Member



Joined: 01 Nov 2006

Posts: 281


flag
PostPosted: Mon Mar 22, 2010 12:35 pm 
Post subject: Each Calculated Column must have an explicit name

Hi,

I am trying to convert this Free Hand SQL to the derived table and I am getting an error as: "Each Calculated Column must have an explicit name"

This code works fine in SQL server. I tried to put "AS" in front of sum and count but I get error. Any suggestion would be really helpful. Thanks!

Here is the code:

Code:
SELECT TREATYNUMBER = COALESCE(BUSINESSVALIDATION.TREATYNUMBER, ACTUALS.TREATYNUMBER),

BENEFITTYPE = COALESCE(BUSINESSVALIDATION.BENEFITTYPE, ACTUALS.BENEFITTYPE),

COVERAGECOUNT_PEX = ISNULL(COVERAGECOUNT_PEX, 0), NAR_PEX = ISNULL(NAR_PEX, 0),

COVERAGECOUNT_STAGE1 = ISNULL(COVERAGECOUNT_STAGE1, 0), NAR_STAGE1 = ISNULL(NAR_STAGE1, 0),

COVERAGECOUNT_ACTUAL = ISNULL(COVERAGECOUNT_ACTUAL, 0), NAR_ACTUAL = ISNULL(NAR_ACTUAL, 0),

DUPLICATECHECK_ACTUAL = ISNULL(DUPLICATIONCHECK_ACTUAL, 0),

SIDESFOUND = CASE

WHEN BUSINESSVALIDATION.TREATYNUMBER IS NOT NULL AND ACTUALS.TREATYNUMBER IS NOT NULL THEN 'Both'

WHEN BUSINESSVALIDATION.TREATYNUMBER IS NOT NULL AND ACTUALS.TREATYNUMBER IS NULL THEN 'BV'

WHEN BUSINESSVALIDATION.TREATYNUMBER IS NULL AND ACTUALS.TREATYNUMBER IS NOT NULL THEN 'A'

END

FROM

(SELECT TREATYNUMBER = dbo.AILCESSIONFILEVALIDATIONLINE.TREATYID,

BENEFITTYPE = BENEFITTYPE_PEVALIDATION.NAME,

COVERAGECOUNT_PEX = dbo.AILCESSIONFILEVALIDATIONLINE.NUMBEROFCOVERAGESFROMPOLICYEXHIBIT,

NAR_PEX = dbo.AILCESSIONFILEVALIDATIONLINE.NARPERBENEFITTYPEFROMPOLICYEXHIBIT_QUANTITY,

COVERAGECOUNT_STAGE1 = dbo.AILCESSIONFILEVALIDATIONLINE.NUMBEROFCOVERAGES,

NAR_STAGE1 = dbo.AILCESSIONFILEVALIDATIONLINE.NARPERBENEFITTYPE_QUANTITY

FROM dbo.AILCOMPANY COMPANY_CESSIONFILE WITH (NOLOCK)

INNER LOOP JOIN dbo.AILCESSIONFILE WITH (NOLOCK)

ON COMPANY_CESSIONFILE.ID = dbo.AILCESSIONFILE.REFSENDINGCOMPANY_ID AND

COMPANY_CESSIONFILE.NSID = dbo.AILCESSIONFILE.REFSENDINGCOMPANY_NSID

INNER LOOP JOIN dbo.AILCMLISTYPEOFFILE WITH (NOLOCK)

ON dbo.AILCMLISTYPEOFFILE.ID = dbo.AILCESSIONFILE.REFTYPEOFFILE_ID AND

dbo.AILCMLISTYPEOFFILE.NSID = dbo.AILCESSIONFILE.REFTYPEOFFILE_NSID

INNER LOOP JOIN dbo.AILCESSIONFILEVALIDATIONLINE WITH (NOLOCK)

ON dbo.AILCESSIONFILE.ID = dbo.AILCESSIONFILEVALIDATIONLINE.REFCESSIONFILE_ID AND

dbo.AILCESSIONFILE.NSID = dbo.AILCESSIONFILEVALIDATIONLINE.REFCESSIONFILE_NSID

INNER LOOP JOIN dbo.AILBENEFITTYPE BENEFITTYPE_PEVALIDATION WITH (NOLOCK)

ON dbo.AILCESSIONFILEVALIDATIONLINE.REFBENEFITTYPE_ID = BENEFITTYPE_PEVALIDATION.ID AND

dbo.AILCESSIONFILEVALIDATIONLINE.REFBENEFITTYPE_NSID = BENEFITTYPE_PEVALIDATION.NSID
WHERE
COMPANY_CESSIONFILE.NAME + ' - ' +
   convert(varchar,convert(datetime,(cast(convert(datetime,'1-JAN-1900')as integer)+
      dbo.AILCESSIONFILE.REPORTPERIODENDINGDATE)),101) + ' - ' +
               CASE
               WHEN dbo.AILCESSIONFILE.FILECANCELLED = 0 THEN 'Active'
               WHEN dbo.AILCESSIONFILE.FILECANCELLED = 1 THEN 'Cancelled'
               ELSE 'Unhandled Decode'  END
                     + '  (' + dbo.AILCMLISTYPEOFFILE.NAME + ')' = 'Banner'

and
COMPANY_CESSIONFILE.NAME ='Active (Cession files for Banner LIC (559))'
and
dbo.AILCESSIONFILEVALIDATIONLINE.TREATYID  !=  'Aggregate'
) BUSINESSVALIDATION

FULL OUTER JOIN

(SELECT TREATYNUMBER = ISNULL(TREATY_CEDEDCOVERAGE.REFERENCE, '0000'),

BENEFITTYPE = ISNULL(BT.NAME,

CASE

WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'LC' THEN 'LIFE'

WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'DWP' THEN 'DWP'

WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'WOP' THEN 'DWP'

WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'WP' THEN 'DWP'

WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'ADB' THEN 'ADB' 

ELSE NULL

END),

COVERAGECOUNT_ACTUAL = count(DISTINCT DBO.AILCEDEDCOVERAGE.ID),

DUPLICATIONCHECK_ACTUAL = count(*) - count (DISTINCT DBO.AILCEDEDCOVERAGE.ID),

NAR_ACTUAL = sum(CASE

WHEN DBO.AILCEDEDBUSINESSTRANSACTION.ID IS NOT NULL THEN DBO.AILCEDEDBUSINESSTRANSACTION.NETAMOUNTOFRISK_QUANTITY

ELSE dbo.AILCEDEDCOVERAGE.NETAMOUNTOFRISK_QUANTITY

END)

FROM dbo.AILCOMPANY AS COMPANY_CESSIONFILE WITH (NOLOCK)

INNER LOOP JOIN dbo.AILCESSIONFILE WITH (NOLOCK)

ON COMPANY_CESSIONFILE.ID = dbo.AILCESSIONFILE.REFSENDINGCOMPANY_ID AND

COMPANY_CESSIONFILE.NSID = dbo.AILCESSIONFILE.REFSENDINGCOMPANY_NSID

INNER LOOP JOIN dbo.AILCMLISTYPEOFFILE WITH (NOLOCK)

ON dbo.AILCMLISTYPEOFFILE.ID = dbo.AILCESSIONFILE.REFTYPEOFFILE_ID AND

dbo.AILCMLISTYPEOFFILE.NSID = dbo.AILCESSIONFILE.REFTYPEOFFILE_NSID

INNER LOOP JOIN dbo.AILCEDEDCOVERAGE WITH (NOLOCK)

ON dbo.AILCESSIONFILE.ID = dbo.AILCEDEDCOVERAGE.REFMATCHEDBYCESSIONFILE_ID AND

dbo.AILCESSIONFILE.NSID = dbo.AILCEDEDCOVERAGE.REFMATCHEDBYCESSIONFILE_NSID

LEFT OUTER LOOP JOIN dbo.AILREINSURANCECOVERAGE AS RC_CEDEDCOVERAGE WITH (NOLOCK)

ON dbo.AILCEDEDCOVERAGE.REFREINSURANCECOVERAGE_ID = RC_CEDEDCOVERAGE.ID AND

dbo.AILCEDEDCOVERAGE.REFREINSURANCECOVERAGE_NSID = RC_CEDEDCOVERAGE.NSID

LEFT OUTER LOOP JOIN dbo.AILREINSURANCECOVERAGETYPE AS RCTYPE WITH (NOLOCK)

ON RC_CEDEDCOVERAGE.REFOFFEREDSERVICE_ID = RCTYPE.ID AND

RC_CEDEDCOVERAGE.REFOFFEREDSERVICE_NSID = RCTYPE.NSID

LEFT OUTER LOOP JOIN dbo.AILBENEFITTYPE AS BT WITH (NOLOCK)

ON RCTYPE.REFBENEFITTYPE_ID = BT.ID AND

RCTYPE.REFBENEFITTYPE_NSID = BT.NSID

LEFT OUTER LOOP JOIN dbo.AILTREATY TREATY_CEDEDCOVERAGE WITH (NOLOCK)

ON RC_CEDEDCOVERAGE.REFCONTRACT_ID = TREATY_CEDEDCOVERAGE.ID AND

RC_CEDEDCOVERAGE.REFCONTRACT_NSID = TREATY_CEDEDCOVERAGE.NSID

LEFT OUTER LOOP JOIN dbo.AILCEDEDBUSINESSTRANSACTION WITH (NOLOCK)

ON dbo.AILCEDEDBUSINESSTRANSACTION.REFOBJECT_ID = dbo.AILCEDEDCOVERAGE.ID AND

dbo.AILCEDEDBUSINESSTRANSACTION.REFOBJECT_NSID = dbo.AILCEDEDCOVERAGE.NSID AND

dbo.AILCEDEDBUSINESSTRANSACTION.REFCESSIONLOADFILE_ID = dbo.AILCESSIONFILE.ID AND

dbo.AILCEDEDBUSINESSTRANSACTION.REFCESSIONLOADFILE_NSID = dbo.AILCESSIONFILE.NSID --AND

WHERE
COMPANY_CESSIONFILE.NAME + ' - ' +
   convert(varchar,convert(datetime,(cast(convert(datetime,'1-JAN-1900')as integer)+
      dbo.AILCESSIONFILE.REPORTPERIODENDINGDATE)),101) + ' - ' +
               CASE
               WHEN dbo.AILCESSIONFILE.FILECANCELLED = 0 THEN 'Active'
               WHEN dbo.AILCESSIONFILE.FILECANCELLED = 1 THEN 'Cancelled'
               ELSE 'Unhandled Decode'  END
                     + '  (' + dbo.AILCMLISTYPEOFFILE.NAME + ')' ='Banner'

and
COMPANY_CESSIONFILE.NAME = 'Active (Cession files for Banner LIC (559))'
AND IsNull(dbo.AILCEDEDCOVERAGE.ACTIVATIONSTATUS,0) <> 3

AND IsNull(dbo.AILCEDEDBUSINESSTRANSACTION.NSID,318769241) = 318769241

AND IsNull(dbo.AILCEDEDBUSINESSTRANSACTION.STATUS,0) IN (0, 4, 5)

AND ISNull(dbo.AILCEDEDBUSINESSTRANSACTION.TRANSACTIONTYPE,0) <> 43

GROUP BY

ISNULL(TREATY_CEDEDCOVERAGE.REFERENCE,'0000'),
ISNULL
   (
   BT.NAME,
   CASE
   WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'LC'  THEN 'LIFE'
   WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'DWP' THEN 'DWP'
   WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'WOP' THEN 'DWP'
   WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'WP'  THEN 'DWP'
   WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'ADB' THEN 'ADB'
   ELSE NULL
   END
   )

) ACTUALS


ON
BUSINESSVALIDATION.TREATYNUMBER = ACTUALS.TREATYNUMBER AND
BUSINESSVALIDATION.BENEFITTYPE = ACTUALS.BENEFITTYPE

ORDER BY TREATYNUMBER, BENEFITTYPE

OPTION (MAXDOP 1)
Back to top
Mark P
Forum Groupie
Forum Groupie



Joined: 03 Feb 2003

Posts: 8496
Location: Manchester, UK


flag
PostPosted: Mon Mar 22, 2010 12:43 pm 
Post subject: Re: Each Calculated Column must have an explicit name

Instead of
Code:
SELECT TREATYNUMBER = COALESCE(BUSINESSVALIDATION.TREATYNUMBER, ACTUALS.TREATYNUMBER)


Try
Code:
SELECT COALESCE(BUSINESSVALIDATION.TREATYNUMBER, ACTUALS.TREATYNUMBER) TREATYNUMBER

_________________
BusinessObjects - versions 3, 4, 5, 6, XI, XIr2, XI3.1, 4.1, SAP Integration
Data Warehousing, SQL Server 2000/2005/2008/2012 (inc. SSIS, SSRS, etc), Oracle 7.3-11g, Netezza, Sybase, Cognos
______________________________________
Democracy: Where two idiots outvote a genius
Back to top
rit372002
Principal Member
Principal Member



Joined: 01 Nov 2006

Posts: 281


flag
PostPosted: Mon Mar 22, 2010 1:10 pm 
Post subject: Re: Each Calculated Column must have an explicit name

Thanks Mark. Changed as you suggested but still getting the same error. I think I am missing more columns. Any more suggestions? Thanks!

Code:

SELECT COALESCE(BUSINESSVALIDATION.TREATYNUMBER, ACTUALS.TREATYNUMBER) TREATYNUMBER,

COALESCE(BUSINESSVALIDATION.BENEFITTYPE, ACTUALS.BENEFITTYPE) BENEFITTYPE,

ISNULL(COVERAGECOUNT_PEX, 0) COVERAGECOUNT_PEX,

ISNULL(NAR_PEX, 0) NAR_PEX ,

ISNULL(COVERAGECOUNT_STAGE1, 0) COVERAGECOUNT_STAGE1,

ISNULL(NAR_STAGE1, 0) NAR_STAGE1,

ISNULL(COVERAGECOUNT_ACTUAL, 0) COVERAGECOUNT_ACTUAL,

ISNULL(NAR_ACTUAL, 0) NAR_ACTUAL,

ISNULL(DUPLICATIONCHECK_ACTUAL, 0) DUPLICATECHECK_ACTUAL,

CASE

WHEN BUSINESSVALIDATION.TREATYNUMBER IS NOT NULL AND ACTUALS.TREATYNUMBER IS NOT NULL THEN 'Both'

WHEN BUSINESSVALIDATION.TREATYNUMBER IS NOT NULL AND ACTUALS.TREATYNUMBER IS NULL THEN 'BV'

WHEN BUSINESSVALIDATION.TREATYNUMBER IS NULL AND ACTUALS.TREATYNUMBER IS NOT NULL THEN 'A'

END as SIDESFOUND

FROM

(SELECT TREATYNUMBER = dbo.AILCESSIONFILEVALIDATIONLINE.TREATYID,

BENEFITTYPE = BENEFITTYPE_PEVALIDATION.NAME,

COVERAGECOUNT_PEX = dbo.AILCESSIONFILEVALIDATIONLINE.NUMBEROFCOVERAGESFROMPOLICYEXHIBIT,

NAR_PEX = dbo.AILCESSIONFILEVALIDATIONLINE.NARPERBENEFITTYPEFROMPOLICYEXHIBIT_QUANTITY,

COVERAGECOUNT_STAGE1 = dbo.AILCESSIONFILEVALIDATIONLINE.NUMBEROFCOVERAGES,

NAR_STAGE1 = dbo.AILCESSIONFILEVALIDATIONLINE.NARPERBENEFITTYPE_QUANTITY

FROM dbo.AILCOMPANY COMPANY_CESSIONFILE WITH (NOLOCK)

INNER LOOP JOIN dbo.AILCESSIONFILE WITH (NOLOCK)

ON COMPANY_CESSIONFILE.ID = dbo.AILCESSIONFILE.REFSENDINGCOMPANY_ID AND

COMPANY_CESSIONFILE.NSID = dbo.AILCESSIONFILE.REFSENDINGCOMPANY_NSID

INNER LOOP JOIN dbo.AILCMLISTYPEOFFILE WITH (NOLOCK)

ON dbo.AILCMLISTYPEOFFILE.ID = dbo.AILCESSIONFILE.REFTYPEOFFILE_ID AND

dbo.AILCMLISTYPEOFFILE.NSID = dbo.AILCESSIONFILE.REFTYPEOFFILE_NSID

INNER LOOP JOIN dbo.AILCESSIONFILEVALIDATIONLINE WITH (NOLOCK)

ON dbo.AILCESSIONFILE.ID = dbo.AILCESSIONFILEVALIDATIONLINE.REFCESSIONFILE_ID AND

dbo.AILCESSIONFILE.NSID = dbo.AILCESSIONFILEVALIDATIONLINE.REFCESSIONFILE_NSID

INNER LOOP JOIN dbo.AILBENEFITTYPE BENEFITTYPE_PEVALIDATION WITH (NOLOCK)

ON dbo.AILCESSIONFILEVALIDATIONLINE.REFBENEFITTYPE_ID = BENEFITTYPE_PEVALIDATION.ID AND

dbo.AILCESSIONFILEVALIDATIONLINE.REFBENEFITTYPE_NSID = BENEFITTYPE_PEVALIDATION.NSID
WHERE
COMPANY_CESSIONFILE.NAME + ' - ' +
   convert(varchar,convert(datetime,(cast(convert(datetime,'1-JAN-1900')as integer)+
      dbo.AILCESSIONFILE.REPORTPERIODENDINGDATE)),101) + ' - ' +
               CASE
               WHEN dbo.AILCESSIONFILE.FILECANCELLED = 0 THEN 'Active'
               WHEN dbo.AILCESSIONFILE.FILECANCELLED = 1 THEN 'Cancelled'
               ELSE 'Unhandled Decode'  END
                     + '  (' + dbo.AILCMLISTYPEOFFILE.NAME + ')' =
@Variable('Please choose a cession file','A','Cession File\Cession File Abstract',mono,constrained)
and
COMPANY_CESSIONFILE.NAME LIKE substring(@Variable('Please choose a cession file'),1,1) + '%'
and
dbo.AILCESSIONFILEVALIDATIONLINE.TREATYID  !=  'Aggregate'
) BUSINESSVALIDATION

FULL OUTER JOIN

(SELECT TREATYNUMBER = ISNULL(TREATY_CEDEDCOVERAGE.REFERENCE, '0000'),

BENEFITTYPE = ISNULL(BT.NAME,

CASE

WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'LC' THEN 'LIFE'

WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'DWP' THEN 'DWP'

WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'WOP' THEN 'DWP'

WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'WP' THEN 'DWP'

WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'ADB' THEN 'ADB'

ELSE NULL

END),

COVERAGECOUNT_ACTUAL = count(DISTINCT DBO.AILCEDEDCOVERAGE.ID),

DUPLICATIONCHECK_ACTUAL = count(*) - count (DISTINCT DBO.AILCEDEDCOVERAGE.ID),

NAR_ACTUAL = sum(CASE

WHEN DBO.AILCEDEDBUSINESSTRANSACTION.ID IS NOT NULL THEN DBO.AILCEDEDBUSINESSTRANSACTION.NETAMOUNTOFRISK_QUANTITY

ELSE dbo.AILCEDEDCOVERAGE.NETAMOUNTOFRISK_QUANTITY

END)

FROM dbo.AILCOMPANY AS COMPANY_CESSIONFILE WITH (NOLOCK)

INNER LOOP JOIN dbo.AILCESSIONFILE WITH (NOLOCK)

ON COMPANY_CESSIONFILE.ID = dbo.AILCESSIONFILE.REFSENDINGCOMPANY_ID AND

COMPANY_CESSIONFILE.NSID = dbo.AILCESSIONFILE.REFSENDINGCOMPANY_NSID

INNER LOOP JOIN dbo.AILCMLISTYPEOFFILE WITH (NOLOCK)

ON dbo.AILCMLISTYPEOFFILE.ID = dbo.AILCESSIONFILE.REFTYPEOFFILE_ID AND

dbo.AILCMLISTYPEOFFILE.NSID = dbo.AILCESSIONFILE.REFTYPEOFFILE_NSID

INNER LOOP JOIN dbo.AILCEDEDCOVERAGE WITH (NOLOCK)

ON dbo.AILCESSIONFILE.ID = dbo.AILCEDEDCOVERAGE.REFMATCHEDBYCESSIONFILE_ID AND

dbo.AILCESSIONFILE.NSID = dbo.AILCEDEDCOVERAGE.REFMATCHEDBYCESSIONFILE_NSID

LEFT OUTER LOOP JOIN dbo.AILREINSURANCECOVERAGE AS RC_CEDEDCOVERAGE WITH (NOLOCK)

ON dbo.AILCEDEDCOVERAGE.REFREINSURANCECOVERAGE_ID = RC_CEDEDCOVERAGE.ID AND

dbo.AILCEDEDCOVERAGE.REFREINSURANCECOVERAGE_NSID = RC_CEDEDCOVERAGE.NSID

LEFT OUTER LOOP JOIN dbo.AILREINSURANCECOVERAGETYPE AS RCTYPE WITH (NOLOCK)

ON RC_CEDEDCOVERAGE.REFOFFEREDSERVICE_ID = RCTYPE.ID AND

RC_CEDEDCOVERAGE.REFOFFEREDSERVICE_NSID = RCTYPE.NSID

LEFT OUTER LOOP JOIN dbo.AILBENEFITTYPE AS BT WITH (NOLOCK)

ON RCTYPE.REFBENEFITTYPE_ID = BT.ID AND

RCTYPE.REFBENEFITTYPE_NSID = BT.NSID

LEFT OUTER LOOP JOIN dbo.AILTREATY TREATY_CEDEDCOVERAGE WITH (NOLOCK)

ON RC_CEDEDCOVERAGE.REFCONTRACT_ID = TREATY_CEDEDCOVERAGE.ID AND

RC_CEDEDCOVERAGE.REFCONTRACT_NSID = TREATY_CEDEDCOVERAGE.NSID

LEFT OUTER LOOP JOIN dbo.AILCEDEDBUSINESSTRANSACTION WITH (NOLOCK)

ON dbo.AILCEDEDBUSINESSTRANSACTION.REFOBJECT_ID = dbo.AILCEDEDCOVERAGE.ID AND

dbo.AILCEDEDBUSINESSTRANSACTION.REFOBJECT_NSID = dbo.AILCEDEDCOVERAGE.NSID AND

dbo.AILCEDEDBUSINESSTRANSACTION.REFCESSIONLOADFILE_ID = dbo.AILCESSIONFILE.ID AND

dbo.AILCEDEDBUSINESSTRANSACTION.REFCESSIONLOADFILE_NSID = dbo.AILCESSIONFILE.NSID --AND

WHERE
COMPANY_CESSIONFILE.NAME + ' - ' +
   convert(varchar,convert(datetime,(cast(convert(datetime,'1-JAN-1900')as integer)+
      dbo.AILCESSIONFILE.REPORTPERIODENDINGDATE)),101) + ' - ' +
               CASE
               WHEN dbo.AILCESSIONFILE.FILECANCELLED = 0 THEN 'Active'
               WHEN dbo.AILCESSIONFILE.FILECANCELLED = 1 THEN 'Cancelled'
               ELSE 'Unhandled Decode'  END
                     + '  (' + dbo.AILCMLISTYPEOFFILE.NAME + ')' =
@Variable('Please choose a cession file','A','Cession File\Cession File Abstract',mono,constrained)
and
COMPANY_CESSIONFILE.NAME LIKE substring(@Variable('Please choose a cession file'),1,1) + '%'
AND IsNull(dbo.AILCEDEDCOVERAGE.ACTIVATIONSTATUS,0) <> 3

AND IsNull(dbo.AILCEDEDBUSINESSTRANSACTION.NSID,318769241) = 318769241

AND IsNull(dbo.AILCEDEDBUSINESSTRANSACTION.STATUS,0) IN (0, 4, 5)

AND ISNull(dbo.AILCEDEDBUSINESSTRANSACTION.TRANSACTIONTYPE,0) <> 43

GROUP BY

ISNULL(TREATY_CEDEDCOVERAGE.REFERENCE,'0000'),
ISNULL
   (
   BT.NAME,
   CASE
   WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'LC'  THEN 'LIFE'
   WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'DWP' THEN 'DWP'
   WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'WOP' THEN 'DWP'
   WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'WP'  THEN 'DWP'
   WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'ADB' THEN 'ADB'
   ELSE NULL
   END
   )

) ACTUALS


ON
BUSINESSVALIDATION.TREATYNUMBER = ACTUALS.TREATYNUMBER AND
BUSINESSVALIDATION.BENEFITTYPE = ACTUALS.BENEFITTYPE

ORDER BY TREATYNUMBER, BENEFITTYPE

OPTION (MAXDOP 1)
Back to top
Mak 1
Forum Devotee
Forum Devotee



Joined: 06 Jan 2005

Posts: 11642
Location: London


flag
PostPosted: Mon Mar 22, 2010 2:16 pm 
Post subject: Re: Each Calculated Column must have an explicit name

Try:-
Quote:

SELECT COALESCE(BUSINESSVALIDATION.TREATYNUMBER, ACTUALS.TREATYNUMBER) AS TREATYNUMBER


Mark, you have to use the "As" keyword as there is an engine that runs the derived table syntax, between the SQL that is actually running, if that makes sense? Obviously, like you, I try and avoid derived tables, if possible..icon_wink.gif.

_________________
Business Objects Versions 4,5,6, XI R2, 3.2, 4.1 SP3
Oracle, SQL Server, Sybase, Netezza, SAP BW.

Red Adair: "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur."
Back to top
Debbie
Forum Enthusiast
Forum Enthusiast



Joined: 01 Mar 2005

Posts: 2317
Location: Cheshire, England


flag
PostPosted: Tue Mar 23, 2010 3:34 am 
Post subject: Re: Each Calculated Column must have an explicit name

You've also got another one here that you haven't changed as per Mark P's suggestion:

Code:

FULL OUTER JOIN

(SELECT TREATYNUMBER = ISNULL(TREATY_CEDEDCOVERAGE.REFERENCE, '0000')


Debbie
Back to top
Mark P
Forum Groupie
Forum Groupie



Joined: 03 Feb 2003

Posts: 8496
Location: Manchester, UK


flag
PostPosted: Tue Mar 23, 2010 5:26 am 
Post subject: Re: Each Calculated Column must have an explicit name

Mak 1 wrote:
Try:-
Quote:

SELECT COALESCE(BUSINESSVALIDATION.TREATYNUMBER, ACTUALS.TREATYNUMBER) AS TREATYNUMBER


Mark, you have to use the "As" keyword as there is an engine that runs the derived table syntax, between the SQL that is actually running, if that makes sense? Obviously, like you, I try and avoid derived tables, if possible..icon_wink.gif.


Worked fine when I tested it before recommending that solution.
Might be an Oracle vs SQL Server thing.

_________________
BusinessObjects - versions 3, 4, 5, 6, XI, XIr2, XI3.1, 4.1, SAP Integration
Data Warehousing, SQL Server 2000/2005/2008/2012 (inc. SSIS, SSRS, etc), Oracle 7.3-11g, Netezza, Sybase, Cognos
______________________________________
Democracy: Where two idiots outvote a genius
Back to top
Mak 1
Forum Devotee
Forum Devotee



Joined: 06 Jan 2005

Posts: 11642
Location: London


flag
PostPosted: Tue Mar 23, 2010 6:09 am 
Post subject: Re: Each Calculated Column must have an explicit name

Quote:
Might be an Oracle vs SQL Server thing.


Yes, Mark, actually, I believe you are correct.
As it is SQL Server then, what you suggessted, should work for him...

_________________
Business Objects Versions 4,5,6, XI R2, 3.2, 4.1 SP3
Oracle, SQL Server, Sybase, Netezza, SAP BW.

Red Adair: "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur."
Back to top
rit372002
Principal Member
Principal Member



Joined: 01 Nov 2006

Posts: 281


flag
PostPosted: Mon Mar 29, 2010 12:55 pm 
Post subject: Re: Each Calculated Column must have an explicit name

Still not working.

This works on SQL server (Query executes without any error):

But in the Universe, I am now getting "Statement Can not be prepared"/

There was a prompt in the original query and I commented it out. Still not sure what's happening? One more question: Can we do prompt in the Derived table?

Here's the code:


Code:

SELECT COALESCE(BUSINESSVALIDATION.TREATYNUMBER, ACTUALS.TREATYNUMBER) as TREATYNUMBER,

COALESCE(BUSINESSVALIDATION.BENEFITTYPE, ACTUALS.BENEFITTYPE) as BENEFITTYPE,

ISNULL(COVERAGECOUNT_PEX, 0) as COVERAGECOUNT_PEX,

ISNULL(NAR_PEX, 0) as NAR_PEX ,

ISNULL(COVERAGECOUNT_STAGE1, 0) as COVERAGECOUNT_STAGE1,

ISNULL(NAR_STAGE1, 0) as NAR_STAGE1,

ISNULL(COVERAGECOUNT_ACTUAL, 0) as COVERAGECOUNT_ACTUAL,

ISNULL(NAR_ACTUAL, 0) as NAR_ACTUAL,

ISNULL(DUPLICATIONCHECK_ACTUAL, 0) as DUPLICATECHECK_ACTUAL,

CASE

WHEN BUSINESSVALIDATION.TREATYNUMBER IS NOT NULL AND ACTUALS.TREATYNUMBER IS NOT NULL THEN 'Both'

WHEN BUSINESSVALIDATION.TREATYNUMBER IS NOT NULL AND ACTUALS.TREATYNUMBER IS NULL THEN 'BV'

WHEN BUSINESSVALIDATION.TREATYNUMBER IS NULL AND ACTUALS.TREATYNUMBER IS NOT NULL THEN 'A'

END as SIDESFOUND

FROM

(SELECT dbo.AILCESSIONFILEVALIDATIONLINE.TREATYID as TREATYNUMBER,

BENEFITTYPE_PEVALIDATION.NAME AS BENEFITTYPE,

dbo.AILCESSIONFILEVALIDATIONLINE.NUMBEROFCOVERAGESFROMPOLICYEXHIBIT as COVERAGECOUNT_PEX,

dbo.AILCESSIONFILEVALIDATIONLINE.NARPERBENEFITTYPEFROMPOLICYEXHIBIT_QUANTITY as NAR_PEX,

dbo.AILCESSIONFILEVALIDATIONLINE.NUMBEROFCOVERAGES as COVERAGECOUNT_STAGE1,

dbo.AILCESSIONFILEVALIDATIONLINE.NARPERBENEFITTYPE_QUANTITY as NAR_STAGE1

FROM dbo.AILCOMPANY COMPANY_CESSIONFILE WITH (NOLOCK)

INNER LOOP JOIN dbo.AILCESSIONFILE WITH (NOLOCK)

ON COMPANY_CESSIONFILE.ID = dbo.AILCESSIONFILE.REFSENDINGCOMPANY_ID AND

COMPANY_CESSIONFILE.NSID = dbo.AILCESSIONFILE.REFSENDINGCOMPANY_NSID

INNER LOOP JOIN dbo.AILCMLISTYPEOFFILE WITH (NOLOCK)

ON dbo.AILCMLISTYPEOFFILE.ID = dbo.AILCESSIONFILE.REFTYPEOFFILE_ID AND

dbo.AILCMLISTYPEOFFILE.NSID = dbo.AILCESSIONFILE.REFTYPEOFFILE_NSID

INNER LOOP JOIN dbo.AILCESSIONFILEVALIDATIONLINE WITH (NOLOCK)

ON dbo.AILCESSIONFILE.ID = dbo.AILCESSIONFILEVALIDATIONLINE.REFCESSIONFILE_ID AND

dbo.AILCESSIONFILE.NSID = dbo.AILCESSIONFILEVALIDATIONLINE.REFCESSIONFILE_NSID

INNER LOOP JOIN dbo.AILBENEFITTYPE BENEFITTYPE_PEVALIDATION WITH (NOLOCK)

ON dbo.AILCESSIONFILEVALIDATIONLINE.REFBENEFITTYPE_ID = BENEFITTYPE_PEVALIDATION.ID AND

dbo.AILCESSIONFILEVALIDATIONLINE.REFBENEFITTYPE_NSID = BENEFITTYPE_PEVALIDATION.NSID
WHERE

COMPANY_CESSIONFILE.NAME + ' - ' +
   convert(varchar,convert(datetime,(cast(convert(datetime,'1-JAN-1900')as integer)+
      dbo.AILCESSIONFILE.REPORTPERIODENDINGDATE)),101) + ' - ' +
               CASE
               WHEN dbo.AILCESSIONFILE.FILECANCELLED = 0 THEN 'Active'
               WHEN dbo.AILCESSIONFILE.FILECANCELLED = 1 THEN 'Cancelled'
               ELSE 'Unhandled Decode'  END
                     + '  (' + dbo.AILCMLISTYPEOFFILE.NAME + ')' = ''
--@Variable('Please choose a cession file','A','Cession File\Cession File Abstract',mono,constrained)
and
COMPANY_CESSIONFILE.NAME LIKE '%'
--substring(@Variable('Please choose a cession file'),1,1) + '%'
and


dbo.AILCESSIONFILEVALIDATIONLINE.TREATYID  !=  'Aggregate'
) as BUSINESSVALIDATION

FULL OUTER JOIN

(SELECT ISNULL(TREATY_CEDEDCOVERAGE.REFERENCE, '0000') as TREATYNUMBER,

ISNULL(BT.NAME,

CASE

WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'LC' THEN 'LIFE'

WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'DWP' THEN 'DWP'

WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'WOP' THEN 'DWP'

WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'WP' THEN 'DWP'

WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'ADB' THEN 'ADB'

ELSE NULL

END) as BENEFITTYPE,

count(DISTINCT DBO.AILCEDEDCOVERAGE.ID) as COVERAGECOUNT_ACTUAL,

count(*) - count (DISTINCT DBO.AILCEDEDCOVERAGE.ID) as DUPLICATIONCHECK_ACTUAL,

sum(CASE

WHEN DBO.AILCEDEDBUSINESSTRANSACTION.ID IS NOT NULL THEN DBO.AILCEDEDBUSINESSTRANSACTION.NETAMOUNTOFRISK_QUANTITY

ELSE dbo.AILCEDEDCOVERAGE.NETAMOUNTOFRISK_QUANTITY

END) as NAR_ACTUAL

FROM dbo.AILCOMPANY AS COMPANY_CESSIONFILE WITH (NOLOCK)

INNER LOOP JOIN dbo.AILCESSIONFILE WITH (NOLOCK)

ON COMPANY_CESSIONFILE.ID = dbo.AILCESSIONFILE.REFSENDINGCOMPANY_ID AND

COMPANY_CESSIONFILE.NSID = dbo.AILCESSIONFILE.REFSENDINGCOMPANY_NSID

INNER LOOP JOIN dbo.AILCMLISTYPEOFFILE WITH (NOLOCK)

ON dbo.AILCMLISTYPEOFFILE.ID = dbo.AILCESSIONFILE.REFTYPEOFFILE_ID AND

dbo.AILCMLISTYPEOFFILE.NSID = dbo.AILCESSIONFILE.REFTYPEOFFILE_NSID

INNER LOOP JOIN dbo.AILCEDEDCOVERAGE WITH (NOLOCK)

ON dbo.AILCESSIONFILE.ID = dbo.AILCEDEDCOVERAGE.REFMATCHEDBYCESSIONFILE_ID AND

dbo.AILCESSIONFILE.NSID = dbo.AILCEDEDCOVERAGE.REFMATCHEDBYCESSIONFILE_NSID

LEFT OUTER LOOP JOIN dbo.AILREINSURANCECOVERAGE AS RC_CEDEDCOVERAGE WITH (NOLOCK)

ON dbo.AILCEDEDCOVERAGE.REFREINSURANCECOVERAGE_ID = RC_CEDEDCOVERAGE.ID AND

dbo.AILCEDEDCOVERAGE.REFREINSURANCECOVERAGE_NSID = RC_CEDEDCOVERAGE.NSID

LEFT OUTER LOOP JOIN dbo.AILREINSURANCECOVERAGETYPE AS RCTYPE WITH (NOLOCK)

ON RC_CEDEDCOVERAGE.REFOFFEREDSERVICE_ID = RCTYPE.ID AND

RC_CEDEDCOVERAGE.REFOFFEREDSERVICE_NSID = RCTYPE.NSID

LEFT OUTER LOOP JOIN dbo.AILBENEFITTYPE AS BT WITH (NOLOCK)

ON RCTYPE.REFBENEFITTYPE_ID = BT.ID AND

RCTYPE.REFBENEFITTYPE_NSID = BT.NSID

LEFT OUTER LOOP JOIN dbo.AILTREATY TREATY_CEDEDCOVERAGE WITH (NOLOCK)

ON RC_CEDEDCOVERAGE.REFCONTRACT_ID = TREATY_CEDEDCOVERAGE.ID AND

RC_CEDEDCOVERAGE.REFCONTRACT_NSID = TREATY_CEDEDCOVERAGE.NSID

LEFT OUTER LOOP JOIN dbo.AILCEDEDBUSINESSTRANSACTION WITH (NOLOCK)

ON dbo.AILCEDEDBUSINESSTRANSACTION.REFOBJECT_ID = dbo.AILCEDEDCOVERAGE.ID AND

dbo.AILCEDEDBUSINESSTRANSACTION.REFOBJECT_NSID = dbo.AILCEDEDCOVERAGE.NSID AND

dbo.AILCEDEDBUSINESSTRANSACTION.REFCESSIONLOADFILE_ID = dbo.AILCESSIONFILE.ID AND

dbo.AILCEDEDBUSINESSTRANSACTION.REFCESSIONLOADFILE_NSID = dbo.AILCESSIONFILE.NSID

WHERE

COMPANY_CESSIONFILE.NAME + ' - ' +
   convert(varchar,convert(datetime,(cast(convert(datetime,'1-JAN-1900')as integer)+
      dbo.AILCESSIONFILE.REPORTPERIODENDINGDATE)),101) + ' - ' +
               CASE
               WHEN dbo.AILCESSIONFILE.FILECANCELLED = 0 THEN 'Active'
               WHEN dbo.AILCESSIONFILE.FILECANCELLED = 1 THEN 'Cancelled'
               ELSE 'Unhandled Decode'  END
                     + '  (' + dbo.AILCMLISTYPEOFFILE.NAME + ')' = ''
--@Variable('Please choose a cession file','A','Cession File\Cession File Abstract',mono,constrained)
and
COMPANY_CESSIONFILE.NAME LIKE '%'
--substring(@Variable('Please choose a cession file'),1,1) + '%'



and IsNull(dbo.AILCEDEDCOVERAGE.ACTIVATIONSTATUS,0) <> 3

AND IsNull(dbo.AILCEDEDBUSINESSTRANSACTION.NSID,318769241) = 318769241

AND IsNull(dbo.AILCEDEDBUSINESSTRANSACTION.STATUS,0) IN (0, 4, 5)

AND ISNull(dbo.AILCEDEDBUSINESSTRANSACTION.TRANSACTIONTYPE,0) <> 43

GROUP BY

ISNULL(TREATY_CEDEDCOVERAGE.REFERENCE,'0000'),
ISNULL
   (
   BT.NAME,
   CASE
   WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'LC'  THEN 'LIFE'
   WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'DWP' THEN 'DWP'
   WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'WOP' THEN 'DWP'
   WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'WP'  THEN 'DWP'
   WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'ADB' THEN 'ADB'
   ELSE NULL
   END
   )

) as ACTUALS


ON
BUSINESSVALIDATION.TREATYNUMBER = ACTUALS.TREATYNUMBER AND
BUSINESSVALIDATION.BENEFITTYPE = ACTUALS.BENEFITTYPE

ORDER BY TREATYNUMBER, BENEFITTYPE


OPTION (MAXDOP 1)





Thanks all for your help.

-rk
Back to top
Mak 1
Forum Devotee
Forum Devotee



Joined: 06 Jan 2005

Posts: 11642
Location: London


flag
PostPosted: Mon Mar 29, 2010 1:12 pm 
Post subject: Re: Each Calculated Column must have an explicit name

Code:
--@Variable('Please choose a cession file','A','Cession File\Cession File Abstract',mono,constrained)


You should be using @Prompt, instead of @Variable.

Does the code work without this?

_________________
Business Objects Versions 4,5,6, XI R2, 3.2, 4.1 SP3
Oracle, SQL Server, Sybase, Netezza, SAP BW.

Red Adair: "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur."
Back to top
rit372002
Principal Member
Principal Member



Joined: 01 Nov 2006

Posts: 281


flag
PostPosted: Mon Mar 29, 2010 1:21 pm 
Post subject: Re: Each Calculated Column must have an explicit name

When I un-comment it and use "Prompt" instead of "Variable" and check the syntax for the Derived table, I get : "Invalid Definition (UNV 0023): error.

Here is the modified code.

Code:


SELECT COALESCE(BUSINESSVALIDATION.TREATYNUMBER, ACTUALS.TREATYNUMBER) as TREATYNUMBER,

COALESCE(BUSINESSVALIDATION.BENEFITTYPE, ACTUALS.BENEFITTYPE) as BENEFITTYPE,

ISNULL(COVERAGECOUNT_PEX, 0) as COVERAGECOUNT_PEX,

ISNULL(NAR_PEX, 0) as NAR_PEX ,

ISNULL(COVERAGECOUNT_STAGE1, 0) as COVERAGECOUNT_STAGE1,

ISNULL(NAR_STAGE1, 0) as NAR_STAGE1,

ISNULL(COVERAGECOUNT_ACTUAL, 0) as COVERAGECOUNT_ACTUAL,

ISNULL(NAR_ACTUAL, 0) as NAR_ACTUAL,

ISNULL(DUPLICATIONCHECK_ACTUAL, 0) as DUPLICATECHECK_ACTUAL,

CASE

WHEN BUSINESSVALIDATION.TREATYNUMBER IS NOT NULL AND ACTUALS.TREATYNUMBER IS NOT NULL THEN 'Both'

WHEN BUSINESSVALIDATION.TREATYNUMBER IS NOT NULL AND ACTUALS.TREATYNUMBER IS NULL THEN 'BV'

WHEN BUSINESSVALIDATION.TREATYNUMBER IS NULL AND ACTUALS.TREATYNUMBER IS NOT NULL THEN 'A'

END as SIDESFOUND

FROM

(SELECT dbo.AILCESSIONFILEVALIDATIONLINE.TREATYID as TREATYNUMBER,

BENEFITTYPE_PEVALIDATION.NAME AS BENEFITTYPE,

dbo.AILCESSIONFILEVALIDATIONLINE.NUMBEROFCOVERAGESFROMPOLICYEXHIBIT as COVERAGECOUNT_PEX,

dbo.AILCESSIONFILEVALIDATIONLINE.NARPERBENEFITTYPEFROMPOLICYEXHIBIT_QUANTITY as NAR_PEX,

dbo.AILCESSIONFILEVALIDATIONLINE.NUMBEROFCOVERAGES as COVERAGECOUNT_STAGE1,

dbo.AILCESSIONFILEVALIDATIONLINE.NARPERBENEFITTYPE_QUANTITY as NAR_STAGE1

FROM dbo.AILCOMPANY COMPANY_CESSIONFILE WITH (NOLOCK)

INNER LOOP JOIN dbo.AILCESSIONFILE WITH (NOLOCK)

ON COMPANY_CESSIONFILE.ID = dbo.AILCESSIONFILE.REFSENDINGCOMPANY_ID AND

COMPANY_CESSIONFILE.NSID = dbo.AILCESSIONFILE.REFSENDINGCOMPANY_NSID

INNER LOOP JOIN dbo.AILCMLISTYPEOFFILE WITH (NOLOCK)

ON dbo.AILCMLISTYPEOFFILE.ID = dbo.AILCESSIONFILE.REFTYPEOFFILE_ID AND

dbo.AILCMLISTYPEOFFILE.NSID = dbo.AILCESSIONFILE.REFTYPEOFFILE_NSID

INNER LOOP JOIN dbo.AILCESSIONFILEVALIDATIONLINE WITH (NOLOCK)

ON dbo.AILCESSIONFILE.ID = dbo.AILCESSIONFILEVALIDATIONLINE.REFCESSIONFILE_ID AND

dbo.AILCESSIONFILE.NSID = dbo.AILCESSIONFILEVALIDATIONLINE.REFCESSIONFILE_NSID

INNER LOOP JOIN dbo.AILBENEFITTYPE BENEFITTYPE_PEVALIDATION WITH (NOLOCK)

ON dbo.AILCESSIONFILEVALIDATIONLINE.REFBENEFITTYPE_ID = BENEFITTYPE_PEVALIDATION.ID AND

dbo.AILCESSIONFILEVALIDATIONLINE.REFBENEFITTYPE_NSID = BENEFITTYPE_PEVALIDATION.NSID
WHERE

COMPANY_CESSIONFILE.NAME + ' - ' +
   convert(varchar,convert(datetime,(cast(convert(datetime,'1-JAN-1900')as integer)+
      dbo.AILCESSIONFILE.REPORTPERIODENDINGDATE)),101) + ' - ' +
               CASE
               WHEN dbo.AILCESSIONFILE.FILECANCELLED = 0 THEN 'Active'
               WHEN dbo.AILCESSIONFILE.FILECANCELLED = 1 THEN 'Cancelled'
               ELSE 'Unhandled Decode'  END
                     + '  (' + dbo.AILCMLISTYPEOFFILE.NAME + ')' =
@Prompt('Please choose a cession file','A','Cession File\Cession File Abstract',mono,constrained)
and
COMPANY_CESSIONFILE.NAME LIKE substring(@Prompt('Please choose a cession file'),1,1) + '%'
and


dbo.AILCESSIONFILEVALIDATIONLINE.TREATYID  !=  'Aggregate'
) as BUSINESSVALIDATION

FULL OUTER JOIN

(SELECT ISNULL(TREATY_CEDEDCOVERAGE.REFERENCE, '0000') as TREATYNUMBER,

ISNULL(BT.NAME,

CASE

WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'LC' THEN 'LIFE'

WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'DWP' THEN 'DWP'

WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'WOP' THEN 'DWP'

WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'WP' THEN 'DWP'

WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'ADB' THEN 'ADB'

ELSE NULL

END) as BENEFITTYPE,

count(DISTINCT DBO.AILCEDEDCOVERAGE.ID) as COVERAGECOUNT_ACTUAL,

count(*) - count (DISTINCT DBO.AILCEDEDCOVERAGE.ID) as DUPLICATIONCHECK_ACTUAL,

sum(CASE

WHEN DBO.AILCEDEDBUSINESSTRANSACTION.ID IS NOT NULL THEN DBO.AILCEDEDBUSINESSTRANSACTION.NETAMOUNTOFRISK_QUANTITY

ELSE dbo.AILCEDEDCOVERAGE.NETAMOUNTOFRISK_QUANTITY

END) as NAR_ACTUAL

FROM dbo.AILCOMPANY AS COMPANY_CESSIONFILE WITH (NOLOCK)

INNER LOOP JOIN dbo.AILCESSIONFILE WITH (NOLOCK)

ON COMPANY_CESSIONFILE.ID = dbo.AILCESSIONFILE.REFSENDINGCOMPANY_ID AND

COMPANY_CESSIONFILE.NSID = dbo.AILCESSIONFILE.REFSENDINGCOMPANY_NSID

INNER LOOP JOIN dbo.AILCMLISTYPEOFFILE WITH (NOLOCK)

ON dbo.AILCMLISTYPEOFFILE.ID = dbo.AILCESSIONFILE.REFTYPEOFFILE_ID AND

dbo.AILCMLISTYPEOFFILE.NSID = dbo.AILCESSIONFILE.REFTYPEOFFILE_NSID

INNER LOOP JOIN dbo.AILCEDEDCOVERAGE WITH (NOLOCK)

ON dbo.AILCESSIONFILE.ID = dbo.AILCEDEDCOVERAGE.REFMATCHEDBYCESSIONFILE_ID AND

dbo.AILCESSIONFILE.NSID = dbo.AILCEDEDCOVERAGE.REFMATCHEDBYCESSIONFILE_NSID

LEFT OUTER LOOP JOIN dbo.AILREINSURANCECOVERAGE AS RC_CEDEDCOVERAGE WITH (NOLOCK)

ON dbo.AILCEDEDCOVERAGE.REFREINSURANCECOVERAGE_ID = RC_CEDEDCOVERAGE.ID AND

dbo.AILCEDEDCOVERAGE.REFREINSURANCECOVERAGE_NSID = RC_CEDEDCOVERAGE.NSID

LEFT OUTER LOOP JOIN dbo.AILREINSURANCECOVERAGETYPE AS RCTYPE WITH (NOLOCK)

ON RC_CEDEDCOVERAGE.REFOFFEREDSERVICE_ID = RCTYPE.ID AND

RC_CEDEDCOVERAGE.REFOFFEREDSERVICE_NSID = RCTYPE.NSID

LEFT OUTER LOOP JOIN dbo.AILBENEFITTYPE AS BT WITH (NOLOCK)

ON RCTYPE.REFBENEFITTYPE_ID = BT.ID AND

RCTYPE.REFBENEFITTYPE_NSID = BT.NSID

LEFT OUTER LOOP JOIN dbo.AILTREATY TREATY_CEDEDCOVERAGE WITH (NOLOCK)

ON RC_CEDEDCOVERAGE.REFCONTRACT_ID = TREATY_CEDEDCOVERAGE.ID AND

RC_CEDEDCOVERAGE.REFCONTRACT_NSID = TREATY_CEDEDCOVERAGE.NSID

LEFT OUTER LOOP JOIN dbo.AILCEDEDBUSINESSTRANSACTION WITH (NOLOCK)

ON dbo.AILCEDEDBUSINESSTRANSACTION.REFOBJECT_ID = dbo.AILCEDEDCOVERAGE.ID AND

dbo.AILCEDEDBUSINESSTRANSACTION.REFOBJECT_NSID = dbo.AILCEDEDCOVERAGE.NSID AND

dbo.AILCEDEDBUSINESSTRANSACTION.REFCESSIONLOADFILE_ID = dbo.AILCESSIONFILE.ID AND

dbo.AILCEDEDBUSINESSTRANSACTION.REFCESSIONLOADFILE_NSID = dbo.AILCESSIONFILE.NSID

WHERE

COMPANY_CESSIONFILE.NAME + ' - ' +
   convert(varchar,convert(datetime,(cast(convert(datetime,'1-JAN-1900')as integer)+
      dbo.AILCESSIONFILE.REPORTPERIODENDINGDATE)),101) + ' - ' +
               CASE
               WHEN dbo.AILCESSIONFILE.FILECANCELLED = 0 THEN 'Active'
               WHEN dbo.AILCESSIONFILE.FILECANCELLED = 1 THEN 'Cancelled'
               ELSE 'Unhandled Decode'  END
                     + '  (' + dbo.AILCMLISTYPEOFFILE.NAME + ')' =
@Prompt('Please choose a cession file','A','Cession File\Cession File Abstract',mono,constrained)
and
COMPANY_CESSIONFILE.NAME LIKE
substring(@Prompt('Please choose a cession file'),1,1) + '%'



and IsNull(dbo.AILCEDEDCOVERAGE.ACTIVATIONSTATUS,0) <> 3

AND IsNull(dbo.AILCEDEDBUSINESSTRANSACTION.NSID,318769241) = 318769241

AND IsNull(dbo.AILCEDEDBUSINESSTRANSACTION.STATUS,0) IN (0, 4, 5)

AND ISNull(dbo.AILCEDEDBUSINESSTRANSACTION.TRANSACTIONTYPE,0) <> 43

GROUP BY

ISNULL(TREATY_CEDEDCOVERAGE.REFERENCE,'0000'),
ISNULL
   (
   BT.NAME,
   CASE
   WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'LC'  THEN 'LIFE'
   WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'DWP' THEN 'DWP'
   WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'WOP' THEN 'DWP'
   WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'WP'  THEN 'DWP'
   WHEN dbo.AILCEDEDCOVERAGE.REPORTEDCOVERAGERISKTYPE = 'ADB' THEN 'ADB'
   ELSE NULL
   END
   )

) as ACTUALS


ON
BUSINESSVALIDATION.TREATYNUMBER = ACTUALS.TREATYNUMBER AND
BUSINESSVALIDATION.BENEFITTYPE = ACTUALS.BENEFITTYPE

ORDER BY TREATYNUMBER, BENEFITTYPE


OPTION (MAXDOP 1)

Back to top
Mak 1
Forum Devotee
Forum Devotee



Joined: 06 Jan 2005

Posts: 11642
Location: London


flag
PostPosted: Mon Mar 29, 2010 1:26 pm 
Post subject: Re: Each Calculated Column must have an explicit name

Code:
@Prompt('Please choose a cession file','A','Cession File\Cession File Abstract',mono,constrained)
and
COMPANY_CESSIONFILE.NAME LIKE substring(@Prompt('Please choose a cession file'),1,1) + '%'



The second prompt is not defined correctly. You need to break the problem down and work out which part is causing the issue icon_smile.gif.

_________________
Business Objects Versions 4,5,6, XI R2, 3.2, 4.1 SP3
Oracle, SQL Server, Sybase, Netezza, SAP BW.

Red Adair: "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur."
Back to top
rit372002
Principal Member
Principal Member



Joined: 01 Nov 2006

Posts: 281


flag
PostPosted: Thu Apr 15, 2010 11:39 pm 
Post subject: Re: Each Calculated Column must have an explicit name

Finally, It is resolved now. The "order by" for some reason doesn't work in the Derived Table. There are few other changes as well. If anyone interested then I will post it here, otherwise it's ok.


Thanks for everyone's input.


-rk
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.1031 seconds using 17 queries. (SQL 0.0162 Parse 0.0688 Other 0.0180)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo