Getting this error when testing sql action. SQL works on database. How do I know what database SQL action is executing from. Module uses multiple database connections
Hello Tony, how are you calling the table in SQL1?
Are you tring to call Outsystems entittes or external (with extesntion)?
Can you show the SQL?
Please see the documentation here: https://success.outsystems.com/Documentation/11/Developing_an_Application/Use_Data/Query_Data/SQL_Queries
Tony Mazur wrote:
Hi Luis, This is what I have
Thanks for the reply.
As you have the table referenced in extension, you just need to use the Outsystems nomenclature:
Select {CFRoleVerification}.[Name_ID] (and other fields...)
From {CFRoleVerification}
Can you test it?
BR,
Luis
I only added database name to see if it would take care of error. It did not. I do not want to use hard coded database name.
Why you need/want to do that? You need to create a integration for each database
When I used your suggestion it has error Unknown Entity
When we promote to different environments (TEST, QA, PROD)
Did you configure the database connection on those environments?
Can you call the entity as you made here?
Environments are configured. Not sure I understand 'Can you call the entity '
How does it know what database to look at?'
I am refereing to the print screen, can you select the entity?
You need to define the database connection for each extension:
And you need to configure the database connection in ServiceCenter
Please see documentation here: https://success.outsystems.com/Documentation/11/Extensibility_and_Integration/Integrate_with_an_External_Database
I can select entity CFRoleVerification. It is empty as I expected. It does not find function 'Invalid Object'
Can you sent the print of your query?
The function uses 3 tables from one database and 1 table from another database. The one table is not defined in OutSystems. But that should not make a difference because we are using function that returns table to OutSystems, correct
/* ===========================================================================================
Author:Mills
Date:04/07/2020
Description:
Paramters:
Return Value:
None
Used by:
--------------------------------------------------------------------------------------
Change ID DeveloperDescription
=========================================================================================== */
ALTER FUNCTION [dbo].[CFNameCourtDurationFN]
(
@NAME_IDint,
@COURT_NUMBERchar(12)
)
RETURNS TABLE
AS
RETURN
/**********************************/
/* Get current member information */
with step_0 as
select a.NAME_ID, IsNull(H.ADULT_CODE, 'N') as ADULT_CODE, IsNull(b.VOTE_CODE,'N') as VOTE_CODE
from PNAME a
inner join CF_MBR b
on a.NAME_ID = b.NAME_ID
Outer Apply (Select Top (1) OneYrEarlierDate
From CFConventionCntrl as Dte
Where GetDate() Between StartDate and EndDate) as SDte
Outer Apply (Select Top (1) ADULT_CODE-- Adult code must originate from CF_MBR_HIST. Court Number does not apply in this case.
From [CFDataPrd].[dbo].[CF_MBR_HIST]
Where NAME_ID = a.NAME_ID
--and COURT_NUMBER = @COURT_NUMBER
and MONTH_END_DATE <= SDte.OneYrEarlierDate
Order By MONTH_END_DATE desc) as H
where a.NAME_ID = @NAME_ID
),
/*********************************************/
/* Get amount of months in the current court */
step_01 as
SELECT
NAME_ID,
COURT_NUMBER,
min(MONTH_END_DATE) as BEGIN_MONTH,
max(MONTH_END_DATE) as END_MONTH,
datediff(m, min(MONTH_END_DATE), max(MONTH_END_DATE)) as MonthsInCourt,
'N' as Merged_Court
FROM [CFDataPrd].[dbo].[CF_MBR_HIST]
where@COURT_NUMBER = [COURT_NUMBER] and
@NAME_ID = NAME_ID and
MONTH_END_DATE <= SDte.OneYrEarlierDate
group by [NAME_ID], [COURT_NUMBER]
/************************************************************************/
/* Get the amount of months the member spent in the previous court.*/
/* This would occur if there was a previous court transfer.*/
step_02 as
select
a.FROM_AFFILIATE_NO,
min(b.MONTH_END_DATE) as BEGIN_MONTH,
max(b.MONTH_END_DATE) as END_MONTH,
datediff(m, min(b.MONTH_END_DATE), max(b.MONTH_END_DATE)) as MonthsInCourt,
'Y' as MergedCourt
from PASS_GAS a
inner join [CFDataPrd].[dbo].[CF_MBR_HIST] b
on a.FROM_AFFILIATE_NO = b.COURT_NUMBER
where@COURT_NUMBER = a.[TO_AFFILIATE_NO] and
b.NAME_ID = @NAME_ID and
a.TRANSFER_TYPE = 'C' and
b.MONTH_END_DATE <= SDte.OneYrEarlierDate
group by [NAME_ID], a.FROM_AFFILIATE_NO
selecta.NAME_IDas NAME_ID,
IsNull(a.ADULT_CODE, 'N')as ADULT_CODE,
a.VOTE_CODEas VOTE_CODE,
isnull(b.MonthsInCourt,0)as MonthsInCurrentCourt,
isnull(c.MonthsInCourt,0)as MonthsInMergedCourt,
isnull(IsNull(b.MonthsInCourt, 0) + IsNull(c.MonthsInCourt,0), 0)as TotalMonthsInCourt,
isnull(c.MergedCourt,' ') as MergedCourt
from step_0 a
left outer join step_01 b
left outer join step_02 c
on a.NAME_ID = c.NAME_ID
We have many projects in OutSystems. This is 1st time we are using function
Hello Tony,
Can you please describe with more detail what you wanto to do in the Outsystems application?
I don't get your point yet...
Thanks
Table [CFDataPrd].[dbo].[CF_MBR_HIST] is not in service studio
We were trying to execute table function. Have error could not find function. This morning we changed approach to use stored procedure to execute function. We get error Could not find stored procedure. I have structure for items returned.