Page 1 of 1

Can't find procedure in report table selection

PostPosted: Fri Jun 28, 2019 2:01 am
by chpwebmaster
I just tried to do something similar to creating a temp table to act like an M$ Access subquery then creating a master table with the

Code: Select all
Create TABLE #TABLE_ID# AS ...

and also tried to create a second table then
Code: Select all
Create TABLE  #TABLE_ID# SELECT * FROM [Second Table]

no matter what I try the procedure DOES NOT show up in the table list for the report.

Here is the full code of my procedure
*note i'm working off of existing data that was poorly built 20 years ago in Access I've modified some of the column and table names so I don't have #'s and /'s all over my column and table names*

Code: Select all
$a = "DROP TABLE IF EXISTS DataForRptAllUnitsMasterListSub1;";
nuRunQuery ($a);
$b = "SELECT
    CONCAT(IF(tblTenants.BusinessName != '', tblTenants.BusinessName,''), IF(tblTenants.BusinessName != '' AND tblTenants.LastName != '', ' - ', ''), IF(tblTenants.LastName != '', tblTenants.LastName, ''), IF(tblTenants.LastName != '' AND tblTenants.FirstName != '', ', ', ''), IF(tblTenants.FirstName != '', tblTenants.FirstName, '')) AS FullName,

       JOIN tblTenants ON tblCurrentRentedUnits.AssocTenantID = tblTenants.TenantID

   tblCurrentRentedUnits.Vacated = False
   tblCurrentRentedUnits.UnitNumber ASC;";
nuRunQuery ("CREATE TABLE DataForRptAllUnitsMasterListSub1 AS $b;");

$c = "DROP TABLE IF EXISTS DataForRptMasterListOneLocation;";
nuRunQuery ($c);

$d = "CREATE TABLE DataForRptMasterListOneLocation AS SELECT
    IF(DataForRptAllUnitsMasterListSub1.TenantID IS NULL, 'VACANT', 'Rented') AS UnitCondition,
    IF(DataForRptAllUnitsMasterListSub1.TenantID IS NULL, '', DataForRptAllUnitsMasterListSub1.FullName) AS RentedBy,
    LEFT JOIN DataForRptAllUnitsMasterListSub1 ON tblUnits.UnitID = DataForRptAllUnitsMasterListSub1.UnitNumber
    INNER JOIN tblLocationToUse ON tblUnits.Location = tblLocationToUse.Location


$e = "Create TABLE #TABLE_ID# AS SELECT * FROM DataForRptMasterListOneLocation";

If I run it from run procedure i get three tables the two described and a __gibberishstring__ table with the correct data

Re: Can't find procedure in report table selection

PostPosted: Sat Jun 29, 2019 8:22 am
by Janusz
no matter what I try the procedure DOES NOT show up in the table list for the report.

maybe try to verify via Database (phpmyadmin) if the procedure exists in the zzzzsys_php

nuBuilder is displaying data generated by the following querry.
Code: Select all
SELECT * FROM zzzzsys_php
LEFT JOIN zzzzsys_form ON zzzzsys_form_id = sph_zzzzsys_form_id
WHERE (sph_system != '1' || sph_system IS NULL)
ORDER BY sph_code

Try to paste it to the to the SQL box in the phpmyadmin.
Verify the settings for sph_code.
I had some cases - but do not remember if it was for procedures or someting else that some parts were not diplayed and after correction of some flags it was OK.
As well depanding on the database version and your code there might be some tiny differences which are OK with one version of DB and NOK with the other.
For example on one of my DB when updating mariaDB from v10.3 to v10.4 I had to modify some tables regarding null values.

Re: Can't find procedure in report table selection

PostPosted: Tue Jul 02, 2019 1:53 am
by chpwebmaster
sph_run was blank I made it null to match the DOM procedure and sph_system was null I made it blank to match the DOM procedure it now shows up.

Thank you @Janusz for the help

Re: Can't find procedure in report table selection

PostPosted: Thu Jul 04, 2019 9:05 am
by admin

A Procedure won't show up in the list of possible Procedures unless it contains the string #TABLE_ID#.

This means you will only see Procedures that make a temp table that will be used by the Report.