Manual Query Input
Answered
I want to define a complex Select Statement as an ECube. But to get the ‘Manual Query Input’ in ‘Action Query Properties’ I must select bevor an existing table name in the DB (here Action). In my opinion this is wrong because there is no correlation between the two objects (table name and Select-Statement). Have You any idea how can I eliminate this problem (Bug?) ?
After the build I can change the name of my ECiube, but the select statement “belongs” still the Table Action.
Have You got another solution in this case?
Sincerely:
Zoltán
-
Are you trying to create a table just from a SQL statement?
Can you give an actual example of a statement you are trying to run?
0 -
Hi Yoni, here an example:
/*
VErsion 3; Outside Cost added
Version 2; Working Due India added
Version 1 created at 23.02*/-- set transaction isolation level read uncommitted;
WITH CTE_COST
as
(SELECT /*convert (date, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) firstOfCurrentMonth*/
sum (ia.amount) sumAmount
,ISNULL (p2.inventionID,i2.InventionId) inventionID
FROM [Anaqua].[dbo].[Invoice] invo
inner join InvoiceAllocation IA on IA.InvoiceId =invo.invoiceID
inner join currency cur on cur.[CurrencyId]=invo.CurrencyId
left join invention i2 on i2.inventionID=ia.recordid
left join patent p2 on p2.patentID=ia.recordid
/*entweder hier schon ausgrenzen */
where InvoiceDate > convert (date,dateadd (MONTH,-13, DATEADD(month, DATEDIFF(month,0 , GETDATE()), 0)))
and InvoiceDate <convert (date, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
and cur.[CurrencyCode]='EUR'
group by isnull (p2.inventionID,i2.InventionId))
Select I.InventionId as 'InventionId
,R.ReferenceNumber as 'PF'
,i.InventionTitle as 'Title',
CASE
WHEN RST.IsActive = 1 -- StatusTypeName IN ('To Be Filed', 'Instructed', 'Filed', 'Published', 'Examination Requested') -- NOCH ZU KLÄREN s. deadorlive.sql
THEN 'alive'
ELSE 'dead'
END as 'PF Status'
,STUFF ((SELECT ', ' + c.ClassificationCode
FROM Classification c
INNER JOIN LnkRecordClassification lrc ON LRC.ClassificationId = C.ClassificationId AND C.ClassificationTreeId = 81024516 -- Legacy - Technical Classification (SGKZ)
WHERE lrc.RecordId = i.InventionId
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'TechField(SGKZ)'
,STUFF ((SELECT ', ' + c.ClassificationCode
FROM Classification c
INNER JOIN LnkRecordClassification lrc ON LRC.ClassificationId = C.ClassificationId AND C.ClassificationTreeId = 81025833 -- Technical Area
WHERE lrc.RecordId = i.InventionId
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'TechArea'
,STUFF ((SELECT ', ' + c.ClassificationDescription
FROM Classification c
INNER JOIN LnkRecordClassification lrc ON LRC.ClassificationId = C.ClassificationId AND C.ClassificationTreeId = 81025833 -- Technical Area
WHERE lrc.RecordId = i.InventionId
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'TechArea Text',STUFF ((SELECT distinct ', ' + dgi.Item
FROM LnkRecordClientBudget lrcb
inner join LnkIpRecordFields lirf on lirf.Recordid = lrcb.ClientId
inner join RefIpFields rif on rif.ipmapfieldid = lirf.ipmapfieldid and rif.FieldLabel = 'Division'
inner join DataGroupItem DGI on DGI.DataGroupItemId=try_convert (int,lirf.FieldText)
WHERE lrcb.RecordId = i.InventionId
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'Cost Center Division AGG'
,DGIDivision.item Division,STUFF ((SELECT concat (', ' , lirfs.FieldText ,' ' , lbhc.LegalName , ': ' , CAST(lrcb.Stakeholder as VARCHAR) + '%')
FROM LnkRecordClientBudget lrcb
INNER JOIN LegalBody lbc ON lbc.LegalBodyId = lrcb.ClientId
inner join LegalBodyHistory lbhc on lbhc.legalBodyHistoryId = lbc.LatestLegalBodyHistoryId
left join (LnkIpRecordFields lirfs
inner join RefIpFields rifs on rifs.ipmapfieldid = lirfs.ipmapfieldid and rifs.FieldLabel = 'Strategic Business Unit' ) on lirfs.Recordid = lrcb.ClientId
WHERE lrcb.RecordId = i.InventionId
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'Cost Centers'
,STUFF ((SELECT ', ' + lirfs.FieldText
FROM LnkRecordClientBudget lrcbs
inner join LnkIpRecordFields lirfs on lirfs.Recordid = lrcbs.ClientId
inner join RefIpFields rifs on rifs.ipmapfieldid = lirfs.ipmapfieldid and rifs.FieldLabel = 'Strategic Business Unit'
WHERE lrcbs.RecordId = i.InventionId
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'Strategic Business Unit'
,(select convert (date, max(e.EvaluationDate)) from evaluation e
where I.inventionid = e.recordid
group by RecordId) as 'Latest Patent Rewue Meeting'
,UIr.LongName as 'BASF Attorney'
,I.ToBeFiledDate as 'PF Priority Date'
,RST.StatusTypeName as 'PF Final Status'
,STUFF ((SELECT ', ' + case when p.ApplicationTypeId=29 then concat (replace (right (r.referencenumber,4),'01','') ,'-Prio') else replace (right (r.referencenumber,4),'01','') end
FROM Patent p
inner join record r on r.recordid = p.patentid
WHERE p.Inventionid = i.InventionId
and p.DateValue is not null -- inactive Date vorhanden
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'no longer active'
,STUFF ((SELECT ', ' + case when p.ApplicationTypeId=29 then concat (replace (right (r.referencenumber,4),'01','') ,'-Prio') else replace (right (r.referencenumber,4),'01','') end
FROM Patent p
inner join record r on r.recordid = p.patentid
inner join RefStatusType rsta on rsta.StatusTypeId = r.StatusTypeId
WHERE p.Inventionid = i.InventionId
and (rsta.StatusTypeName IN ('Granted', 'Granted (Final)')
or (p.DateValue is null -- active Date vorhanden
and p.GrantedDate is not null) )
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'granted and active'
,sndSQL.[not granted yet incl. Foreign Filings Scheduled]
--nur für patent nicht für filing plan !!! nachfragen
,FFSSQL.ForeignFilingsScheduled as 'Scheduled Application' --Bajohr fragen??,
,format (isnull (i.closeddate,
(select max (p.ExpiryDate) TermYear
from patent p
where p.DateValue is null -- inactive nicht gefüllt
and p.InventionId=i.inventionID
and p.ExpiryDate is not null)),'yyyy') as 'PF Term Year'
/*,(SELECT /*convert (date, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) firstOfCurrentMonth*/
sum (ia.amount) sumAmount
FROM [Anaqua].[dbo].[Invoice] invo
inner join InvoiceAllocation IA on IA.InvoiceId =invo.invoiceID
inner join currency cur on cur.[CurrencyId]=invo.CurrencyId
left join invention i2 on i2.inventionID=ia.recordid
left join patent p2 on p2.patentID=ia.recordid
/*entweder hier schon ausgrenzen */
where InvoiceDate > convert (date,dateadd (MONTH,-13, DATEADD(month, DATEDIFF(month,0 , GETDATE()), 0)))
and InvoiceDate <convert (date, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
and cur.[CurrencyCode]='EUR'
and isnull (p2.inventionID,i2.InventionId)=i.inventionID) as 'Outside PF Costs'*/
,CTE_COST.sumAmount as 'Outside PF Costs'
,(select count (distinct AgreementId) from LnkAgreementSubject las
where las.recordid = i.inventionid or las.recordid in (select patentid from patent p where p.InventionId=i.inventionid)) as'Licence Agreement Counts'
,
STUFF((SELECT CONCAT(CHAR(10) , CHAR(13) , ProductName, ' (', ReportingPeriodName, ')' )
from (
SELECT top 1 with ties
p.ProductName, rrp.ReportingPeriodName , rrp.toDate ,RANK () over ( partition by p.ProductName order by rrp.toDate desc , rrp.ReportingPeriodId desc ) rang
from LnkProductRemunerationReportingPeriodInvention LPRRPI
INNER JOIN ProductRemunerationReportingPeriod PRRP ON PRRP.ProductRemunerationReportingPeriodId = LPRRPI.ProductRemunerationReportingPeriodId
inner join RefReportingPeriod rrp on rrp.ReportingPeriodId = PRRP.ReportingPeriodId
inner join Product p on p.ProductId = PRRP.ProductId
INNER JOIN Record R ON R.RecordId = PRRP.ProductId
where R.StatusTypeId IN (11553, 20047) -- 'In Use' or 'Single Use'
AND LPRRPI.Inventionid = I.InventionId
order by rang
) SQLProductUse
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'Products'
--, (select format (max (latestUSE),'yyyy') latestuse from SQLProductUse) as 'End if Inventors Remuneration'
,(select format (max (rrp.toDate),'yyyy') latestuse
from LnkProductRemunerationReportingPeriodInvention LPRRPI
INNER JOIN ProductRemunerationReportingPeriod PRRP ON PRRP.ProductRemunerationReportingPeriodId = LPRRPI.ProductRemunerationReportingPeriodId
inner join RefReportingPeriod rrp on rrp.ReportingPeriodId = PRRP.ReportingPeriodId
inner join Product p on p.ProductId = PRRP.ProductId
INNER JOIN Record R ON R.RecordId = PRRP.ProductId
where R.StatusTypeId IN (11553, 20047) -- 'In Use' or 'Single Use'
AND LPRRPI.Inventionid = I.InventionId --81125279
) as 'End of Inventors Remuneration'
,
STUFF ((SELECT distinct ', ' + LBH.LegalName
FROM [dbo].[LnkRecordOwner] LRO
inner join LegalBodyHistory LBH on LBH.LegalBodyHistoryId=LRO.LegalBodyHistoryOwnerIdWHERE LRO.RecordId=i.InventionId
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'InventionOwner'
/*
STUFF ((SELECT distinct ', ' + lbhy.LegalName
FROM patent P
inner Join RecordProprietorRecordal rpr on rpr.recordid = p.patentid
inner join LnkRecordProprietorRecordalOwner lrpro on lrpro.RecordProprietorRecordalId = rpr.RecordProprietorRecordalId
inner join LegalBodyHistory lbhy on lbhy.LegalBodyHistoryId=lrpro.LegalBodyHistoryOwnerId
WHERE p.InventionId=i.InventionId
AND rpr.IsCurrentLegalOwner = 1
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'PatentOwner'
*/
,STUFF ((SELECT distinct ', ' + lbhy.LegalName
FROM LegalBodyHistory lbhy
inner join LnkIpApplicant lipa on lipa.ApplicantLegalBodyHistoryId = lbhy.LegalBodyHistoryId
INNER JOIN patent P on lipa.RecordId = p.PatentId
WHERE p.InventionId=i.InventionId
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'Applicant'
,STUFF ((SELECT ', ' + uio.LongName
FROM UserInformation uio
inner join LnkInventionInventor lii on lii.Inventorid = uio.userId
WHERE lii.InventionId = i.InventionId
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'Inventors'
,WorkinDue.[IN-Working-Due]
--Start From Clause
from Invention I
Join Record R ON I.InventionId = R.RecordId
Join RefStatusType RST On R.StatusTypeId = RST.StatusTypeIdLEFT Join (LnkIpContact LIPR
inner join UserInformation UIR ON LIPR.UserId = UIR.UserId)
ON i.InventionId = LIPR.RecordId AND LIPR.IpContactTypeId = '21' -- responsible Manager
left join CTE_Cost on CTE_COST.inventionID=i.inventionID
outer apply
(select STUFF ((SELECT ', ' + case when INNERSQL.ApplicationTypeId=29 then concat (INNERSQL.TerritoryCode ,'-Prio') else INNERSQL.TerritoryCode end ld
from
(select TerritoryCode, 'FP' Sourc, case when ifp.IsFirstFiling=1 then 29 else null end ApplicationTypeId
FROM InventionFilingPlan IFP
inner join territory t on t.TerritoryId = ifp.TerritoryId
where ifp.PatentId is null and ifp.ApprovedDate is not null -- geplant ohne patent Zuordnung
and IFP.Inventionid = i.InventionId
union all
select TerritoryCode, 'Patent' Sourc, p.ApplicationTypeId
FROM Patent p
inner join record r on r.recordid = p.patentid
inner join territory t on t.TerritoryId = r.TerritoryId
where p.InventionId=i.inventionID
and p.DateValue is null
and p.GrantedDate is null
) as innerSQL
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'not granted yet incl. Foreign Filings Scheduled'
) as sndSQL
outer apply
(select STUFF ((SELECT ', ' + case when INNERSQL.ApplicationTypeId=29 then concat (INNERSQL.TerritoryCode ,'-Prio') else INNERSQL.TerritoryCode end ld
from
(select TerritoryCode, 'FP' Sourc, case when ifp.IsFirstFiling=1 then 29 else null end ApplicationTypeId
FROM InventionFilingPlan IFP
inner join territory t on t.TerritoryId = ifp.TerritoryId
where ifp.PatentId is null and ifp.ApprovedDate is not null -- geplant ohne patent Zuordnung
and IFP.Inventionid = i.InventionId
union all
select TerritoryCode, 'Patent' Sourc, p.ApplicationTypeId
FROM Patent p
inner join record r on r.recordid = p.patentid
inner join territory t on t.TerritoryId = r.TerritoryId
inner join RefIpLinkType rilt on rilt.IpLinkTypeId = p.ApplicationTypeId
where p.InventionId=i.inventionID
and p.DateValue is null
and p.GrantedDate is null
/*??? Rückmeldung Bajohr, welche länder sind schon angemeldet?*/
and (((p.FiledDate is not null or p.NationalFiledDate is not null) and rilt.SystemTypeName not in ('DesignatedEP','DesignatedPCT','DesignatedAP','DesignatedEA'))
or ((p.NationalPhaseEnteredDate is not null or p.SecondaryAgentAcknowledgedDate is not null or p.SecondaryAgentInstructedDate is not null)
and rilt.SystemTypeName in ('DesignatedEP','DesignatedPCT','DesignatedAP','DesignatedEA')))
) innerSQL
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS 'ForeignFilingsScheduled'
) as FFSSQL
outer apply ( select distinct DGIDivision.Item
from LnkRecordClientBudget lrcb
inner join LnkIpRecordFields lirf on lirf.Recordid = lrcb.ClientId
inner join RefIpFields rif on rif.ipmapfieldid = lirf.ipmapfieldid and rif.FieldLabel = 'Division'
inner join DataGroupItem DGIDivision on DGIDivision.DataGroupItemId=try_convert (int,lirf.FieldText)
where lrcb.RecordId = i.InventionId
) as DGIDivision
--Working due India
outer apply (select top 1 with ties
rank () over (partition by p.inventionID order by case when actionDate <= getdate () and isclosed =0 then 1 else 2 end , isclosed, actionDate desc ) rang ,
concat (year (c.ActionDate), ' - task ', case when isclosed=0 then 'open' else 'closed' end ) 'IN-Working-Due'
from chaser c
inner join patent p on p.patentid=c.RecordId
where c.chasername like 'IN - Working Due'
and p.inventionID=i.InventionID
order by rang) as WorkinDue
where r.ReferenceNumber is not null
and RST.StatusTypeName not in ('Change Inventorship – Submitted','Change Inventorship - Draft')0 -
Sincerely: Zoltán
0 -
Hi Zoltan,
In order to define an expression you have to select a table. The UI requires you to select a table or view first, and then you can define the SQL however you like. You could also define a stored procedure if that's your business need.Best,
Orit
0
Post is closed for comments.
Comments
4 comments