Skip to content
+1-888-319-3663

COMMUNITY FORUM

Finding it difficult to run queries with Extender tables

Lewis asked 4 years ago
I thought it would be helpful to create an extender checkbox to establish “Active Segments” in my general ledger, instead of manually keeping a list (like in my query below).  This list is sent up to a web-application.  However, I’m finding it difficult using the ext01100 and ext01103 to mimic this query… 
I get the impression there are limitations to the ability to query the extender tables?  Or is there a way to accomplish using the extender to replace manually keeping track of each account segment
 
— Program
SELECT
DB_NAME() as Company,
RTRIM(GL00100.ACTNUMBR_4) AS CODE,
RTRIM(GL00100.ACTNUMBR_3) AS ref1,
RTRIM(GL40200.DSCRIPTN) AS NAME
FROM GL00100
INNER JOIN GL40200 ON GL00100.ACTNUMBR_4 = GL40200.SGMNTID AND GL40200.SGMTNUMB = ‘4’

where GL00100.ACTNUMBR_3 in (
’00’,
’14’,
’15’,
’16’,
’78’,
’80’
)
and
GL00100.ACTNUMBR_4 in (
‘000’,
‘001’,
‘010’,
‘895’,
‘900’,
‘998’,
‘999’
)

GROUP BY GL00100.ACTNUMBR_4, GL00100.ACTNUMBR_3, GL40200.DSCRIPTN 
Answers
Patrick Roth Staff answered 4 years ago
Lewis:
I get the impression there are limitations to the ability to query the extender tables? 
Extender data is stored in SQL tables like any other GP data and so can be queried like any other data.  But the structure of that Extender SQL data isn’t normalized since we can make any “table” of up to 15 fields with all the different datatypes.  So it is spread across the multiple tables making it much more difficult to get at.
What I would suggest is using the Extender Views window as the ‘sa’ user and let Extender build you a view of the Extender data.  Then you just query the view to get the much friendlier results.
Or is there a way to accomplish using the extender to replace manually keeping track of each account segment.
Sure- you can make an Extender Window/Data based on the Account Segment Setup window.
 
Lewis replied 4 years ago

Patrick,
Thank you for the quick reply..
I’ll try using the extender views. however I’m new to the extender product, can you point me to some examples on how to use that feature?

Patrick Roth Staff replied 4 years ago

1. log in as sa or DYNSA
2. open extender
3. choose New | Views

Lewis replied 4 years ago

Patrick – I was able to create Queries off the Extender view, which helped me resolve my reporting needs.

Thank you for your assistance!


If you would like to submit an answer or comment, please sign in to the eOne portal.