Accpac XAPI - Joint Query 1

[Simboti1]

I have the following SQL query in VB6

SELECT A.GLREF, A.GLDESC, A.AMTEXTNDHC,
B.GLBATCH, B.GLENTRY
FROM APPJD AS A INNER JOIN APPJH AS B ON
A.POSTSEQNCE = B.POSTSEQNCE
AND A.TYPEBTCH = B.TYPEBTCH
AND A.CNTBTCH = B.CNTBTCH
AND A.CNTITEM = B.CNTITEM
AND A.IDACCT = '401099999'

1. I am converting this application so that is uses ACCPAC XAPI.

2. How do I create a "Joint view" that combines the results of AP0510 view and AP0511 in the same manner the above SQL query is generating a recordset? (AP0510 and AP0511 are the views for APPJD and
APPJH tables respectively)

 

[aggyey1]

You can use the same query in Accpac XAPI.

 

[Simboti1]

Hi aggyey

Thanks for your quick response.

For a single view related to GL0018, I am using the following approach: -
-----------------------------------------------------------
Public ACCPAC_Session As AccpacSession
Public RsGLPOST As AccpacCOMAPI.AccpacView
Public strFilter as string

Set mDBLinkCmpRO = ACCPAC_Session.OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READONLY) mDBLinkCmpRO.OpenView "GL0018", RsGLPOST

strFilter = "ACCTID = 401099999 AND FISCALYR = 2009"

RsGLPOST.Browse strFilter, True
------------------------------------------------------------
My questions are:
1. What is the syntax for declaring AP0510 and AP0511 views using the Set statement?
2. What is the syntax for the query that will populate strFilter ?

Please advise

 

[DjangMan]

What version of Accpac are you using?

 

[Simboti1]

Hi DjangMan

The code is for an Add-On that will be using ACCPAC 5.4A and 5.5A

Regards

Simboti

 

[DjangMan]

5.4 and 5.5 have a View CS0120 that will allow you to use SQL queries.

[b]Dim[/b] mDBLinkCmpRW [b]As[/b] AccpacCOMAPI.AccpacDBLink
[b]Dim[/b] CSQry [b]As[/b] AccpacCOMAPI.AccpacView

[b]On[/b] [b]Error[/b] [b]GoTo[/b] ACCPACErrorHandler [green][i]' Set error handler
[/i][/green]
[green][i]'Open dblink
[/i][/green][b]Set[/b] mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)

[green][i]'Open view
[/i][/green]mDBLinkCmpRW.OpenView [navy]"CS0120"[/navy], CSQry

[b]Dim[/b] sSql [b]As[/b] [b]String[/b]
sSql = [navy]""[/navy]
sSql = sSql & [navy]"SELECT ICITEM.ITEMNO, max(ICHIST.TRANSDATE) as LastTransDate "[/navy]
sSql = sSql & [navy]"FROM ICITEM LEFT OUTER JOIN "[/navy]
sSql = sSql & [navy]" ICHIST ON ICHIST.ITEMNO = ICITEM.ITEMNO "[/navy]
sSql = sSql & [navy]"WHERE (ICHIST.TRANSTYPE IS NULL) or (ICHIST.TRANSTYPE IN (4,5,19,12,13)) "[/navy]
sSql = sSql & [navy]"GROUP BY ICITEM.ITEMNO "[/navy]

CSQry.Browse sSql, True
CSQry.InternalSet (256)

[b]Dim[/b] i

[b]Dim[/b] iRow [b]As[/b] [b]Long[/b]
iRow = 1
For i = 0 [b]To[/b] CSQry.Fields.Count - 1
MsgBox CSQry.Fields(i).[b]Name[/b]
[b]Next[/b] i

[b]Do[/b] [b]While[/b] CSQry.Fetch
msgbox CSQry.Fields([navy]"ITEMNO"[/navy]).Value
[b]If[/b] CSQry.Fields([navy]"LastTransDate"[/navy]).Value = 0 [b]Then[/b]
msgbox [navy]"Never"[/navy]
[b]Else[/b]
msgbox CSQry.Fields([navy]"LastTransDate"[/navy]).Value
[b]End[/b] [b]If[/b]
[b]Loop[/b]
CSQry.[b]Close[/b]

[green][i]'Cleanup
[/i][/green][b]Set[/b] CSQry = [b]Nothing[/b]
[b]Set[/b] mDBLinkCmpRW = [b]Nothing[/b]

 

[Simboti1]

Thanks DjangMan

 

[Simboti1]

Hi DjangMan

The "InternalSet" qualifier seems not to be recognised in VB6. I say so because when I put CSQry. I do not see InternalSet on the list. However, if I type CSQry. I can see Browse. Iam referring to the code below: -
-------------------------------------------------------
CSQry.Browse sSql, True
CSQry.InternalSet (256)
-------------------------------------------------------

What is the significance of this difference?

Regards

Simboti

 

[Jay Converse]

.InternalSet was added in 5.5.