nuBuilder Forum

Posted: **Thu Jun 27, 2019 6:31 pm**

I just tried to do something similar to https://forums.nubuilder.cloud/viewtopic. ... 848#p18607 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
    tblCurrentRentedUnits.UnitNumber,
    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,
    tblTenants.TenantID,
    tblCurrentRentedUnits.MoveInDate,
    tblCurrentRentedUnits.Rate,
    tblTenants.HomePhone,
    tblTenants.BusPhone

FROM
    tblCurrentRentedUnits
       JOIN tblTenants ON tblCurrentRentedUnits.AssocTenantID = tblTenants.TenantID

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

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

$d = "CREATE TABLE DataForRptMasterListOneLocation AS SELECT
	tblUnits.UnitID,
    IF(DataForRptAllUnitsMasterListSub1.TenantID IS NULL, 'VACANT', 'Rented') AS UnitCondition,
    tblUnits.Location,
    tblUnits.Size,
    IF(DataForRptAllUnitsMasterListSub1.TenantID IS NULL, '', DataForRptAllUnitsMasterListSub1.FullName) AS RentedBy,
    DataForRptAllUnitsMasterListSub1.HomePhone,
    DataForRptAllUnitsMasterListSub1.BusPhone,
    DataForRptAllUnitsMasterListSub1.MoveInDate,
    DataForRptAllUnitsMasterListSub1.Rate,
    tblUnits.UnitSort
    
FROM
	tblUnits
    LEFT JOIN DataForRptAllUnitsMasterListSub1 ON tblUnits.UnitID = DataForRptAllUnitsMasterListSub1.UnitNumber
    INNER JOIN tblLocationToUse ON tblUnits.Location = tblLocationToUse.Location
    
    ORDER BY
    	tblUnits.UnitSort;";

nuRunQuery($d);


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

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

Posted: **Sat Jun 29, 2019 12:52 am**

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.

Posted: **Mon Jul 01, 2019 6:23 pm**

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

Posted: **Thu Jul 04, 2019 1:35 am**

chpwebmaster,

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.

https://wiki.nubuilder.cloud/ ... orts#Table

Steven

nuBuilder Forum

Can't find procedure in report table selection

Can't find procedure in report table selection

Re: Can't find procedure in report table selection

Re: Can't find procedure in report table selection

Re: Can't find procedure in report table selection