Grid from 2 tables-relationship

Scriptcase 9 General
#1

in sql of grid,i want to select * from 2 tables, stock_avg & stock_fifo
why grid not running? error is attached.

SELECT *, ‘stock_fifo’ AS source_table FROM stock_fifo WHERE co_id = ‘62’ AND journal_type = ‘pur_trans’ AND logged_in_fin_yr = ‘2026-2027’ AND trans_no = 519 UNION ALL SELECT *, ‘stock_avg’ AS source_table FROM stock_avg WHERE co_id = ‘62’ AND journal_type = ‘pur_trans’ AND logged_in_fin_yr = ‘2026-2027’ AND trans_no = 519;

grid-2-tables
grid-2-tables1600×900 136 KB

#2

When doing an UNION, be sure the 2 tables have exactly the same number of field, if not, list the field don’t use *

Also test your SQL with DBeaver, HeidiSQL or what other tools you use to manage your database.When your SQL work outside of Scriptcase then include it in your grid

#3

ohh.no, i need as seperate line record from each table.

plz give select code…

#4

Scriptcase cannot parse every type of SELECT statement for use in a grid. For more complex queries, the best approach is to create a database view and use a SELECT statement targeting that view within Scriptcase; this employs much simpler syntax that the tool can easily process.

#5

we got this code working in grid :-
SELECT stock_avg.stock_name
FROM stock_avg
WHERE stock_avg.co_id = 62
AND stock_avg.logged_in_fin_yr = ‘2026-2027’
AND stock_avg.trans_no = ‘531’
AND stock_avg.journal_type = ‘pur_trans’

UNION ALL

SELECT stock_fifo.stock_name
FROM stock_fifo
WHERE stock_fifo.co_id = 62
AND stock_fifo.logged_in_fin_yr = ‘2026-2027’
AND stock_fifo.trans_no = ‘531’
AND stock_fifo.journal_type = ‘pur_trans’;

#6

When dealing with app grids, fetching data from a derived table or subquery is often the best approach

SELECT stock_name FROM 
(
SELECT stock_avg.stock_name
FROM stock_avg
WHERE stock_avg.co_id = 62
AND stock_avg.logged_in_fin_yr = ‘2026-2027’
AND stock_avg.trans_no = ‘531’
AND stock_avg.journal_type = ‘pur_trans’

UNION ALL

SELECT stock_fifo.stock_name
FROM stock_fifo
WHERE stock_fifo.co_id = 62
AND stock_fifo.logged_in_fin_yr = ‘2026-2027’
AND stock_fifo.trans_no = ‘531’
AND stock_fifo.journal_type = ‘pur_trans’
) AS T
1 Like