Getting empty rows while adding two inner join conditions in information link

[gurusai sankar]

Hi All,

i am getting empty rows in below information link while applying two joining conditions. when we trying with one joining condition, it is working fine. but when we trying both - getting empty rows. i have also tried by using insert rows by giving the same joining conditions,. but here also getting empty rows while applying 2 joining cinditions (one join condition is working fine)

SQ in information link:

SELECT

F1."ASSET_CLASS" AS "ASSETCLASS",

F1."BSL" AS "BSL",

F1."BSL_NAME" AS "BSLNAME",

F1."BU_NAME" AS "BUNAME",

F1."BUSINESS_LINE" AS "BUSINESSLINE",

F1."BUSINESS_OBJECTIVE" AS "BUSINESSOBJECTIVE",

F1."BUSINESS_UNIT" AS "BUSINESSUNIT",

F1."COST" AS "COST",

F1."COST_CATEGORY" AS "COSTCATEGORY",

F1."CUMULATIVE_CATEGORY" AS "CUMULATIVECATEGORY",

F1."END_PERIOD_NAME" AS "ENDPERIODNAME",

F1."IT_LEAD_NAME" AS "ITLEADNAME",

F1."IT_TOWER_NAME" AS "ITTOWERNAME",

F1."P_NAME" AS "PNAME",

F1."IT_TOWER" AS "ITTOWER",

F1."PERIOD_ID" AS "PERIODID",

F1."PERIOD_NAME" AS "PERIODNAME",

F1."PROG_MGR_NAME" AS "PROGMGRNAME",

F1."PROG_NAME" AS "PROGNAME",

F1."PROJ_HEALTH" AS "PROJHEALTH",

F1."PROJ_PROGRESS" AS "PROJPROGRESS",

F1."PROJ_REQ_ID" AS "PROJREQID",

F1."PROJ_STATUS" AS "PROJSTATUS",

F1."PROJECT_CODE" AS "PROJECTCODE",

F1."PROP_REQUEST_ID" AS "PROPREQUESTID",

F1."START_PERIOD_NAME" AS "STARTPERIODNAME",

F1."TYPE_OF_COST" AS "TYPEOFCOST",

P2."CAR_COST" AS "CARCOST",

P2."CAR_EXPENSE_TYPE" AS "CAREXPENSETYPE",

P2."CAR_NO" AS "CARNO",

P2."CAR_REQ_ID" AS "CARREQID",

P2."CAR_STATUS_NAME" AS "CARSTATUSNAME",

P2."CAR_YEAR" AS "CARYEAR",

P2."LINKED_PROJ_REQ_ID" AS "LINKEDPROJREQID",

P2."LINKED_PROP_REQ_ID" AS "LINKEDPROPREQID"

FROM

"PPM_USR"."FIN_SUMMARY_DATA" F1,

"PPM_USR"."PPM_CAR_RECORD_V" P2

WHERE

(F1."PROP_REQUEST_ID" = P2."LINKED_PROP_REQ_ID")

AND (F1."PROJ_REQ_ID" = P2."LINKED_PROJ_REQ_ID")

AND

Requirement:FIN_SUMMARY_DATA view having proposal and project information with cost (the data is in month level) andPPM_CAR_RECORD_V view having car information with cost (the data is in year level)

i should create both the costs in a single chart based on the filters (yearwise , quarter wise ). and want to display 2 sections i.e proposal & Projects, car list (cross table ). if we select propsoal or project in 1st section- it shoud show all the cars which are realated with proposal and projects in cars section (2nd section)

Note: each proposal will have multiple cars , each car will have multiple projects. based on this when joined as mentined in the above- will definatly get multiple duplicte records with the same cost. we should take distinct cost in single line.

Questions:

1. Please corrcet me whether i am joining correctly as per the requirement. suggest if we make any chnges in the sql code

2. plesae suggest why i am getting empty rows, suggest some best solution to achive this

3. is it possible to fulfill all my requireemnts in a single page

4. is it possible to show the data per yearwise & quarter wise as expected (eventhogh the data is showing month (date format) wise (i.e Jan_2019) in fin_summary_data view and year (string format) wise in CAR view )

4. if it is not feasible to add as per the requirement.- kindly suggest some best alternative solution.

 

 

Regards,

Gurur sankar

[Swen Vogel]

Hi,

 

I suggest you need to check the data tables and the query manually with sql developer or another software, depends on you data base (for mysql HeidiSQL is a nice tool). It is hard to judge without seeing the data.

You have definded an inner join so empty rows should not appear, except there are IDs with empty rows in your second table.

It depends on the data base. SQLhave many aggregation functions and with a query with subquerys you can do a lot. In my eyes information links ar a bit limited. Easybrowsing through data bases is an important task, but no one takes care about it.Oneway could be to develop an Addinwith the spotfire API, where we can browse through our data, use custom queries anddefine complicated joins. Another way can be trying outthecustom data source with ODBC drivers from spotfire. Maybe there are more options for you.

You can aggregate the data in the query or you extract it on the lowest aggregation level (month) and aggregate it afterwards with a pivot or directly in the plot withthe over function (e.E. Sum(Sales) over quarter)

 

best regards

swen