Empty records instead of "Not Found" ?

[yourguide]

Is there some way to display empty fields or some default value for fields when I get no records found?

I want to maintain the look of my screen even when there are no records to display.

My own efforts have proven to be quite difficult to accomplish.

See Attached… I don’t want the “Records not found” parts… instead in order to make the Warehouse Grid filled in even when there are no records I had to create another table just like the warehouse table and then populate it with the default values, then use a long query to get it to fill in the blanks when there are any. In this case I wanted it to display all 3 warehouses, even if there were only records for two of them.

My Other tables will just not have records for that ItemCode…

Ill Attach Screenshots of my tables and here’s the MySQL Im using.
[SIZE=10px]

SELECT
    coalesce (w.WarehouseCode,Mast.WarehouseCode) as WarehouseCode,
    coalesce (w.QuantityOnHand,Mast.QuantityOnHand) as QuantityOnHand,
    coalesce (w.QuantityOnSalesOrder,Mast.QuantityOnSalesOrder) as QuantityOnSalesOrder,
    coalesce (w.QuantityOnPurchaseOrder,Mast.QuantityOnPurchaseOrder) as QuantityOnPurchaseOrder,
    coalesce (w.QuantityOnBackOrder,Mast.QuantityOnBackOrder) as QuantityOnBackOrder
FROM
    IM_ItemWarehouse_Master Mast
left join
     IM_ItemWarehouse w on
w.WarehouseCode = Mast.WarehouseCode
AND w.Itemcode = '[ATRNUM]' 
Order by WarehouseCode

[/SIZE]

[Giu]

Right now, the only way, is to make sure you use an SQL that always return some result. Maybe this can help you:
http://lists.mysql.com/mysql/219786

[yourguide]

Thanks for the link Giu… thats basically what I’ve done… my only problem now is that it works in the query… but Scriptcase seems to mangle the Select statement… Im going to build a stored procedure with the query then just call it from script case… that should work.

Its strange though because it works on another grid… I guess its just too long or something?

Works:

SELECT
    coalesce (w.WarehouseCode,Mast.WarehouseCode) as WarehouseCode,
    coalesce (w.QuantityOnHand,Mast.QuantityOnHand) as QuantityOnHand,
    coalesce (w.QuantityOnSalesOrder,Mast.QuantityOnSalesOrder) as QuantityOnSalesOrder,
    coalesce (w.QuantityOnPurchaseOrder,Mast.QuantityOnPurchaseOrder) as QuantityOnPurchaseOrder,
    coalesce (w.QuantityOnBackOrder,Mast.QuantityOnBackOrder) as QuantityOnBackOrder
FROM
    IM_ItemWarehouse_Master Mast
left join
     IM_ItemWarehouse w on
w.WarehouseCode = Mast.WarehouseCode
AND w.Itemcode = '[ATRNUM]' 
Order by WarehouseCode

Doesn’t Work:

SELECT coalesce (w.Amperage,Mast.Amperage) AS Amperage,
 coalesce (w.ANSICode,Mast.ANSICode) AS ANSICode,
 coalesce (w.Base,Mast.Base) AS Base,
 coalesce (w.BallastCode,Mast.BallastCode) AS BallastCode,
 coalesce (w.BeamSpread,Mast.BeamSpread) AS BeamSpread,
 coalesce (w.BurnPosition,Mast.BurnPosition) AS BurnPosition,
 coalesce (w.Candlepower,Mast.Candlepower) AS Candlepower,
 coalesce (w.ColorTemp,Mast.ColorTemp) AS ColorTemp,
 coalesce (w.Color,Mast.Color) AS Color,
 coalesce (w.CRI,Mast.CRI) AS CRI,
 coalesce (w.Diameter,Mast.Diameter) AS Diameter,
 coalesce (w.Filament,Mast.Filament) AS Filament,
 coalesce (w.Finish,Mast.Finish) AS Finish,
 coalesce (w.GlassSize,Mast.GlassSize) AS GlassSize,
 coalesce (w.HourLife,Mast.HourLife) AS HourLife,
 coalesce (w.InitialLumens,Mast.InitialLumens) AS InitialLumens,
 coalesce (w.LCL,Mast.LCL) AS LCL,
 coalesce (w.Length,Mast.Length) AS Length,
 coalesce (w.Lumens,Mast.Lumens) AS Lumens,
 coalesce (w.Voltage,Mast.Voltage) AS Voltage,
 coalesce (w.Wattage,Mast.Wattage) AS Wattage
FROM 
ip_Spec_Master Mast
LEFT JOIN
ip_Spec w ON
w.IP_RecNbr = Mast.IP_RecNbr OR w.IP_RecNbr = [IPRecNbr]

I get the error here… you can see that its not even the same select statement… its dropped all my COALESCE code… I got an error when I saved it.

[ATTACH=CONFIG]n63069[/ATTACH]

[aducom]

Well, scriptcase has it’s own sql parser because it needs to generate php and js code too. So for these statements they have implemented standard sql. coalesce is not standard sql. To solve issues like these, you need to create a view which does the job for you. For scriptcase it’s then a simple access to a table.

[yourguide]

Thanks for the information Albert… its odd though that the SQL works on one of my grids… just not the other one.
Since I need to filter the data I built a stored procedure with a variable that I am calling from Scriptcase in the SQL… but that won’t work either.

Is there a way to call a stored procedure from Scripcase other than putting it in the “SQL” part of a grid?

[aducom]

yes, but I never used it so can’t guide. But why don’t you create a view? It’s odd that the same query doesn’t work in both grids. There should be some difference. But creating a view will simplify things a lot.

[yourguide]

I love Views… but I am not sure how I could make one that would work for what I need. I have a table of items with ItemRecNbr as the primary key… My table of item specifications has lots of ItemRecNbr’s but not all of them, new ones are added by users.
When I view an item I want to see the specification column headers in the grid… even if there isn’t a row for that ItemRecNbr.
I don’t see how to do that in a view.

Another solution would be to try and keep the same number of records in the ItemSpecifications table as the Items table… I could trigger the DB to create a new blank row in the specifications table when a new row was added to the items table.

Setting the value to the fields themselves in Scriptcase won’t work because it won’t display the fields if there are no records in the specifications table…