October 5, 2010 at 1:17 pm
I know this post probably seems extremely dumb for some of you, but I am trying to self teach myself SQL for urgent reporting purposes within my company.
I had this report working when I had all of the SELECT statements broken into separate datasets, but when I tried to upload the report to the third party software that we are using, it errored out. I was told that there were too many SELECT statements/datasets and that I needed to condense my code into one statement and put this code in the Properties/Code tab.
Below is what I have been trying to get to work, but I am getting the "Incorrect syntax near the keyword 'FROM'" error when I try to run the query.
Does anyone see what I am doing wrong or have any ideas on a better way for me to do this? I still have one more dataset that I need to add to this query which I have listed below.
Any help would be greatly appreciated.
SELECT *
FROM PES_MACRO_INVESTMENT(inv, obj_UID = 508644, Name, Sponsor, ExecutiveSponsor, Program, ContractRegion, SponsoringLineOfBusiness, SponsorCostCenter, ProposalBudgetedbySponsor, SponsorsProposalBudgetAmo, BillingReimbursmentCode,
BudgetYear, DescribeCurrentState, Description,Whyisprojectneeded, PrimaryObjective, WhyChoosenOptionsConsider, ConsequencesofAvoidingthe, ScopeFlexability, QualityFlexability, ScheduleFlexability, CostFlexability,
TechnologyDomain, ResourceManagerRM, RMCostCenter, Project-Manager1, Rank, Rank-Weighted, Weight-Competitive, Weight-Corporate, Weight-Customer, Weight-Operational, Weight-Strategic, Weight-Risk, Value-Score, Risk-Score,
RC-Cost-CapEx-Labor.Plan, RC-Cost-OpEx-Labor.Plan, RC-Cost-CapEx-Non-Labor.Plan, RC-Cost-OpEx-Non-Labor.Plan, RC-Cost-CapEx-Total.Plan, RC-Cost-OpEx-Total.Plan, RCCostCapExHW.Plan, RCCostCapExSW.Plan, RCCostCapExOther.Plan, RCCostOpExHW.Plan, RCCostOpExSW.Plan, RCCostOpExOther.Plan, RC-Cost-Total.Plan )
INNER JOIN PES_MACRO_CONFIGURABLE_LOG(log1, obj_OwningInvestment = 508644 AND cls_UID = 43, ValidationBy, ValidationDate0, Description)
ON inv.obj_UID = log1.obj_OwningInvestment
INNER JOIN FROM PES_MACRO_CONFIGURABLE_LOG(log2, obj_OwningInvestment =508644 AND cls_UID=42, Name, Stakeholder, StakeholderRole, ImpacttoDepartment)
ON inv.obj_UID = log2.obj_OwningInvestment
INNER JOIN PES_MACRO_CONFIGURABLE_LOG(log3, obj_OwningInvestment = 508644 AND cls_UID=36, Name, ConstraintType, ConstrainedBy, Description )
ON inv.obj_UID = log3.obj_OwningInvestment
INNER JOIN PES_MACRO_CONFIGURABLE_LOG(log4, obj_OwningInvestment = 508644 AND cls_UID = 11, Name, obj_LogStatus, obj_LogPriority, obj_Impact, RiskLikelihood )
ON inv.obj_UID = log4.obj_OwningInvestment
INNER JOIN PES_MACRO_CONFIGURABLE_LOG(log5, obj_OwningInvestment = 508644 AND cls_UID = 50, Name, Description, ScopeInOut, ScopeType, ScopeCategory )
ON inv.obj_UID = log5.obj_OwningInvestment
*The dataset that I have not added yet.
SELECT DISTINCT ss.sks_Name, SUM(assn.tsk_Work) as hours, MIN(assn.tsk_Start) as Start, MAX(assn.tsk_Finish) as Finish FROM PES_MACRO_PROJECT_ASSIGNMENT(assn, obj_ProjectUID ="& Code.GetFirstId(Parameters!InvestmentId.Value) &", tsk_Work, tsk_Start, tsk_Finish) LEFT OUTER JOIN PES_ResourceAllocation alloc ON assn.obj_AllocationUID = alloc.obj_UID LEFT OUTER JOIN PES_Resource resource ON alloc.obj_ResourceUID = resource.obj_UID LEFT OUTER JOIN PES_SkillSet ss ON alloc.sks_SkillSetUID=ss.sks_UID GROUP BY ss.sks_Name
:crying:
October 5, 2010 at 2:16 pm
Just eyeballing the query really fast, looks like the syntax error barf is from the second inner join to the third table, you have "INNER JOIN FROM".
Thats not going to be your only syntax error though; if your functions are table-value functions then you will need to give a nickname to each function, as in
SELECT * FROM PES_MACRO_INVESTMENT(<params>) AS INV
INNER JOIN ...
Also, in your parameters you feed the functions, make sure each param is comma-delimited and not "AND" delimited, as in
INNER JOIN
PES_MACRO_CONFIGURABLE_LOG( log1,
obj_OwningInvestment = 508644
AND cls_UID = 43
should be
INNER JOIN
PES_MACRO_CONFIGURABLE_LOG( log1,
obj_OwningInvestment = 508644,
cls_UID = 43
If that doesn't set you on the right path then you may need to show some table and function definitons before we can continue to help...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply