Write SQL to list all Titles with no attached TIPPs

[iyawnis]

@desreid are some of these issues related to the 4.2 requirement DM-E ' More reporting options'? If so could you share the SQL once complete? Ian asked me to create jasper reports that will be included by default to KB+ addressing these issues. ( #790 , #791 )

[desreid]

@latusaki I'll certainly share the SQL that I write. The requests came through to me, but I'm afraid I don't know if they are connected to the 'more reporting options' in 4.2. If it's of use for that, that's great.

[ostephens]

@desreid we've been asked to build in some of these reports to the application in 4.2 so that the data managers can run these reports whenever they want rather than having to request them.

To give flexibility @iatusaki has been looking at an approach that would allow new reports to be added to the list in the future without the need for additional code to be written - so new reports can be added without re-deploying software etc.

I believe this is being done by enabling the upload of report templates defined in the Jasper report format. The upload of the templates can be done via the UI, and once a report has been uploaded the Data Managers can run it on demand. The templates can include prompts for parameters, so the reports can include some parameterisation.

[iyawnis]

That is correct. I have completed the implementation on KB+ side, and now I am just creating few 'default' reports, although since my SQL skills are not very good yet, I have been struggling with some of the more complex queries, thus I asked for the sql to be shared

[desreid]

@iatusaki Ok, well my plan is to post the SQL on each issue when I have something. I do know SQL, but you (in k-int) know the data model better, so between us hopefully we can get the correct output.

[desreid]

To achieve this I've selected rows from the title_instance table where there is no corresponding row in the title_instance_package_platform table. Does that sound right? There are 253 results. Here is the SQL

 SELECT ti.ti_id, ti.ti_key_title FROM title_instance ti LEFT JOIN title_instance_package_platform tipp ON ti.ti_id = tipp.tipp_ti_fk WHERE tipp.tipp_ti_fk IS NULL ORDER BY ti.ti_id;

[desreid]

The results of the above query are: +-------+-----------------------------------------------------------------------------------+ | ti_id | ti_key_title | +-------+-----------------------------------------------------------------------------------+ | 107 | energy high journal of physics | | 130 | journal new of physics | | 1043 | history library | | 1165 | advances aip | | 1855 | ecology journal of vector | | 1920 | wetlands | | 2853 | and characterization food journal measurement of | | 3469 | and environments fluids limnology oceanography | | 3786 | chemistry journal letters of physical the | | 3795 | division engineering ice papers | | 3796 | ice proceedings | | 4050 | cases educational in journal leadership of | | 4244 | and justice race | | 4669 | active and control frequency journal low noise of vibration | | 4952 | and health medical policy world | | 5443 | change climate nature | | 5444 | communications nature | | 5472 | american scientific | | 5517 | neuropsychopharmacology | | 5522 | journal pharmacogenomics the | | 5746 | computer frontiers of science | | 5756 | and civil engineering frontiers of structural | | 5871 | and characterization food journal measurement of | | 5884 | ichthyology journal of | | 5894 | journal microbio of robotics | | 6054 | psychological studies | | 6187 | ultrasound | | 6192 | and characterization food journal measurement of | | 6193 | journal microbio of robotics | | 6218 | annual medicine of review | | 6709 | review steinbeck | | 6722 | traditio | | 6749 | kadmos | | 6821 | communications heterocyclic | | 7104 | economic european history of review | | 7106 | cambridge quarterly the | | 7112 | and characterization food journal measurement of | | 7169 | counseling today | | 7229 | ultrasound | | 7252 | blood cancer journal | | 7255 | and cellular immunology molecular | | 7269 | health quarterley statistics | | 7271 | abstracts in international operations research | | 7272 | advancement educational international journal of | | 7280 | appraisal building journal of | | 7281 | biotechnology commercial journal of | | 7284 | asset digital journal management of | | 7293 | and journal leisure of property retail | | 7301 | migration quarterly report statistics | | 7307 | chemistry nature | | 7309 | cardiovascular clinical medicine nature practice | | 7310 | and clinical endocrinology metabolism nature practice | | 7311 | and clinical gastroenterology hepatology nature practice | | 7312 | clinical nature nephrology practice | | 7313 | clinical nature neurology practice | | 7314 | clinical nature oncology practice | | 7315 | clinical nature practice rheumatology | | 7316 | clinical nature practice urology | | 7318 | digest nature | | 7319 | biology nature structural | | 7323 | and branding diplomacy place public | | 7325 | population trends | | 7336 | accounts economic kingdom united | | 7343 | educational in journal needs of research special | | 7345 | abacus | | 7346 | acta neurologica scandinavica | | 7347 | ?publicationtitle | | 7352 | archaeology european journal of | | 7353 | american conservation for institute journal of the | | 7358 | and environmental health international journal occupational of | | 7359 | and history international journal local of regional | | 7364 | history library | | 7365 | chinese historical review the | | 7369 | and chemical engineering news | | 7389 | advances applied biodiversity in science | | 7396 | care health in quality | | 7483 | biology integrative | | 7484 | metallomics | | 7490 | biology integrative | | 7491 | metallomics | | 7522 | and asian business cases journal management of south | | 7523 | and asian finance journal macroeconomics of public south | | 9569 | american history literary | | 9570 | applied linguistics | | 9573 | early music | | 9574 | elt journal | | 9576 | criticism essays in | | 9577 | for forum language modern studies | | 9578 | french history | | 9579 | french studies | | 9581 | history journal workshop | | 9582 | and genocide holocaust studies | | 9583 | international journal lexicography of | | 9584 | design history journal of | | 9585 | journal of semantics | | 9586 | journal of semitic studies | | 9587 | academy american journal of religion the | | 9588 | collections history journal of the | | 9590 | library the | | 9591 | and computing linguistic literary | | 9592 | and literature theology | | 9593 | mind | | 9594 | and letters music | | 9596 | and notes queries | | 9598 | art journal oxford | | 9599 | and past present | | 9602 | british century history twentieth | | 9606 | islamic journal of studies | | 9607 | allied and history journal medicine of sciences the | | 9608 | judaism modern | | 9611 | mathematica philosophia | | 9625 | environmental reviews | | 9782 | and assurance engineering international journal management of system | | 9803 | materials sciencepoland | | 9805 | heilberufescience | | 9824 | and desalination ida journal of reuse water | | 9849 | american mind scientific | | 9897 | animal applied journal nutrition of | | 9914 | earth edinburgh of royal sciences society the transactions | | 9916 | international law reports | | 10326 | education journal leadership of on research | | 10330 | a journal nineteenthcentury of victoriographies writing ? | | 10612 | atmospheric letters science | | 10660 | and biochemistry biology ed molecular | | 11132 | differentiation | | 11348 | european journal of pain supplements | | 12042 | interactive journal marketing of | | 12353 | and biology in magnetic materials medicine physics resonance | | 12810 | in orthodontics progress | | 13457 | and industrial organizational psychology | | 13458 | and bone journal mineral of research | | 13459 | political quarterly science | | 13460 | acta endoscopica | | 13466 | ambio | | 13468 | and applied biochemistry biotechnology | | 13472 | chemical chinese in research universities | | 13477 | collectanea mathematica | | 13494 | botany economic | | 13497 | and chemical engineering frontiers of science | | 13501 | and engineering fuzzy information | | 13505 | and biology cellular developmental in plant vitro | | 13506 | indian journal of virology | | 13507 | advances and applied engineering in international journal mathematics of sciences | | 13509 | and control dynamics international journal of | | 13510 | and environmental health international journal medicine occupational of | | 13511 | international journal of plastics technology | | 13514 | coastal conservation journal of | | 13516 | computing grid journal of | | 13517 | hematopathology journal of | | 13521 | diseases journal of parasitic | | 13522 | and biochemistry biotechnology journal of plant | | 13524 | academy indian journal of science the wood | | 13526 | journal of ultrasound | | 13531 | opsearch | | 13535 | journal sema | | 13540 | botanical review the | | 13542 | and environmental health sciences toxicology | | 13543 | indian institute metals of the transactions | | 13579 | arethusa | | 13583 | catholic historical review | | 13586 | classical world | | 13587 | comparative drama | | 13589 | a and arts criticism for literature quarterly the | | 13592 | american early literature | | 13654 | and hearing in language schools services speech | | 14020 | muqarnas | | 15619 | compass sociology | | 15757 | contemporary womens writing | | 15780 | agricultural economics of review | | 15787 | environmental history review | | 15789 | and conservation forest history | | 15804 | bmj student | | 15999 | lubrication scientific | | 16209 | audiences history readers reception texts | | 16310 | bulletin de france la mathematique societe | | 16487 | asia asiascape digital | | 16488 | affairs asian central | | 16559 | international journal medical of research | | 16569 | applications en et marketing recherche | | 16570 | applications en english et marketing recherche | | 16802 | balances countries energy nonoecd of | | 16804 | balances countries energy oecd of | | 16813 | countries energy nonoecd of statistics | | 16815 | countries energy oecd of statistics | | 17197 | information oil | | 17199 | and coal electricity gas oil | | 17429 | africa islamic | | 17432 | mediaevalia | | 17435 | french in studies women | | 17436 | america bulletin cervantes of society the | | 17497 | construction ice law quarterly | | 17542 | and engineering high laser power science | | 17887 | in neurosciences reviews the | | 18000 | health integrated international journal menopause of postreproductive the | | 18053 | geosphere | | 18055 | and disposition drug metabolism | | 18067 | advances in medical sciences | | 18086 | applied biomedicine journal of | | 18152 | for journal of spirituality study the | | 18222 | ageing in quality | | 18314 | archaeology internet | | 18331 | advances in integrative medicine | | 18804 | economics quantitative | | 18806 | economics theoretical | | 18831 | business economics | | 18833 | and derivatives regulation trading use | | 18835 | development european journal of research the | | 18836 | european political science | | 18838 | health systems | | 18842 | interactive marketing | | 18844 | advocacy and groups interest | | 18846 | and disclosure governance international journal of | | 18848 | economics international journal maritime of | | 18850 | international politics reviews | | 18852 | analytics journal marketing of | | 18855 | insight or | | 18858 | and branding diplomacy place public | | 18860 | a cultural journal medieval of postmedieval studies | | 18862 | and culture psychoanalysis society | | 18865 | alcoholism and clinical experimental research | | 18908 | analytical journal of psychology | | 18913 | culture journal of popular | | 19517 | international journal medicine of sports | | 19550 | and apsipa information on processing signal transactions | | 19553 | and environmental law management | | 19589 | an international quarterly research social | | 19606 | business ethics quarterly | | 19733 | and film journal of video | | 19735 | cinema journal | | 19739 | advertising international journal of | | 19946 | allergo international journal | | 19947 | and customer needs solutions | | 19948 | global social welfare | | 19949 | current medicine reports tropical | | 20085 | adults ageing and in older quality | | 20143 | adults learning | | 20173 | atomic bulletin of scientists the | | 20176 | european journal of probation | | 20188 | journal medical scottish | | 20882 | in orthodontics progress | | 21224 | and borderline disorder dysregulation emotion personality | | 21237 | cancer imaging | | 21238 | and canine epidemiology genetics | | 21239 | and canine epidemiology genetics | | 21257 | and ataxias cerebellum | | 21258 | and ataxias cerebellum | | 21461 | circadian journal of rhythms | | 21613 | in orthodontics progress | | 21614 | in orthodontics progress | | 21689 | canadian international journal of studies | | 21810 | asthma practice research | | 22101 | perspectives | | 22364 | abstracts and language linguistics teaching | +-------+-----------------------------------------------------------------------------------+

[ostephens]

Looks OK to me, just one note which is that the journal title itself is stored in title_instance.ti_title The ti_key_title field is a form of the title used for duplicate checking - see TitleInstance.groovy for how this is created.

Once amended with the ti_title are you happy to send onto Magaly, or shall I do that - happy either way.

[desreid]

OK, I can adjust that and send to Magaly. Is she on the kbplus developer email list?

[desreid]

Corrected SQL

 SELECT ti.ti_id, ti.ti_title FROM title_instance ti LEFT JOIN title_instance_package_platform tipp ON ti.ti_id = tipp.tipp_ti_fk WHERE tipp.tipp_ti_fk IS NULL ORDER BY ti.ti_id;

[desreid]

Info now sent to Magaly, closing.

[desreid]

Reopening to add a refinement from Magaly, which she describes like this:

We have had a look to the list and some of the floating titles are attached to "deleted" packages. Could you run a query to find out the floating titles that are attached to a "deleted" package?

[ostephens]

This doesn't make sense as a requirement.

If this list only include titles that have no TIPP, then they can't be 'attached' to a deleted package - the whole point of this report is that these titles don't have TIPPs, and a TIPP is the only way a title is linked to a package.

I think some more information from Magaly would be required to understand what she is asking for - some examples from her might help?

[desreid]

OK, understood. I'll get back to Magaly for more information.

[desreid]

Now have received response from Christina with different requirements, closing this ticket, and have opened a different one with the new requirement.