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

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

[IDT 4.x] 4.1 SP6:Calling Scalar function from IDT


 
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
ozcel600
Forum Member
Forum Member



Joined: 12 Feb 2020

Posts: 2



PostPosted: Wed Feb 12, 2020 8:41 am 
Post subject: 4.1 SP6:Calling Scalar function from IDT

Hey fellow BOB-ers,

i created a function to calculate the work/business days between 2 dates. When i execute the function in Managment Studio, it works perfect. But when I validate it in IDT i get the following error code:
Quote:
Cannot find either column "dbo" or the user-defined function or aggregate or the name is ambiguous.


I've searched endlessly on forums, but i can't seem to find the answer. Hoping you can help me. What have I done so far:
    * Gave execute/select rights on the function to the same user that i use in IDT
    * Changed it from dimension to measure
    * removed the [dbo]
    * viewed the generated script and copy/paste it into management studio > works icon_eek.gif


I'm out of ideas... Someone please???

Here is the function is created:
Code:

USE [AnalyzIT_DWH_ONTW]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_werkdagen]    Script Date: 2/12/2020 2:37:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_werkdagen] (@startDatum DATE, @eindDatum DATE)

RETURNS INT AS
BEGIN
   DECLARE @werkdagen INT

   SELECT
   @werkdagen = SUM(d.ind_werkdag - CASE WHEN d.ind_werkdag = 1 AND d.ind_feestdag = 1 THEN 1 ELSE 0 END)
   FROM dm.dim_datum d
   WHERE d.datum  BETWEEN @startDatum AND @eindDatum
   Return @werkdagen
END
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 11522
Location: Manchester, UK


flag
PostPosted: Fri Feb 14, 2020 10:34 am 
Post subject: Re: 4.1 SP6:Calling Scalar function from IDT

I assume that your connection in IDT is to AnalyzIT_DWH_ONTW

If that's the case, your object definition should be:

Code:
[dbo].[fn_werkdagen] (param1, param2)


Question: Where are you sourcing param1 and param2 from?

_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
ozcel600
Forum Member
Forum Member



Joined: 12 Feb 2020

Posts: 2



PostPosted: Fri Feb 14, 2020 10:56 am 
Post subject: Re: 4.1 SP6:Calling Scalar function from IDT

Hi Mark, thanks for your reply.

The connection is indeed to AnalyzIT_DWH_ONTW.

I used
Code:
[dbo].[fn_werkdagen] (param1, param2)
, but it still gives the same error.
Param1 and 2 (both dates) are from the same connection, same dim_date table, but each from a different alias dim_date
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.0273 seconds using 17 queries. (SQL 0.0023 Parse 0.0008 Other 0.0241)
CCBot/2.0 (https://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