GLAMF/GLAMFO 1

[iwells]

How do I create a view that's a combination of GLAMF and GLAMFO so that I can .browse on ACSEGVAL01 and OPTFIELD in the same filter OR is there a way to do a substring/mid statement in a .browse like "Mid(ACCTID, 1, 4) = ""XXXX""" so that I only need GLAMFO?

I'm trying to create a macro to loop through and create accounts and optional fields, but we use multiple segments in our GL accounts and segment 1 is our main account segment while segment 2 is our "project" segment so what I'm trying to do is look in GLAMFO and to see if ACSEGVAL01 exists, if it does, create it for the new supplied ACSEGVAL02.

 

[DjangMan]

Do a trace to make sure that the query, in its entirety, is making to SQL.

Or break the line up such as the following (and watch out for what I've hard coded). I've seen queries run this way not handled correctly by VB/VBA and only sending part of the SQL command to view. Breaking up the string and using vbCrLf to break up the lines eliminates this problem.

Dim sql as string
sql = ""
sql = sql & "SELECT GLAMF.ACSEGVAL01" & vbcrlf
sql = sql & " ,GLAMFO.OPTFIELD" & vbcrlf
sql = sql & " ,GLAMFO.VALUE" & vbcrlf
sql = sql & " ,GLAMFO.AUDTDATE" & vbcrlf
sql = sql & " ,GLAMFO.AUDTTIME" & vbcrlf
sql = sql & "FROM GLAMF" & vbcrlf
sql = sql & "INNER JOIN GLAMFO ON GLAMF.ACCTID = GLAMFO.ACCTID" & vbcrlf
sql = sql & "INNER JOIN (" & vbcrlf
sql = sql & " SELECT GLAMF.ACSEGVAL01" & vbcrlf
sql = sql & " ,GLAMFO.OPTFIELD" & vbcrlf
sql = sql & " ,GLAMFO.AUDTDATE" & vbcrlf
sql = sql & " ,MAX(GLAMFO.AUDTTIME) AUDTTIME" & vbcrlf
sql = sql & " FROM GLAMF" & vbcrlf
sql = sql & " INNER JOIN GLAMFO ON GLAMF.ACCTID = GLAMFO.ACCTID" & vbcrlf
sql = sql & " INNER JOIN (" & vbcrlf
sql = sql & " SELECT GLAMF.ACSEGVAL01" & vbcrlf
sql = sql & " ,GLAMFO.OPTFIELD" & vbcrlf
sql = sql & " ,MAX(GLAMFO.AUDTDATE) AUDTDATE" & vbcrlf
sql = sql & " FROM GLAMF" & vbcrlf
sql = sql & " INNER JOIN GLAMFO ON GLAMF.ACCTID = GLAMFO.ACCTID" & vbcrlf
sql = sql & " GROUP BY GLAMF.ACSEGVAL01" & vbcrlf
sql = sql & " ,GLAMFO.OPTFIELD" & vbcrlf
sql = sql & " ) GLAMFO_I ON GLAMF.ACSEGVAL01 = GLAMFO_I.ACSEGVAL01" & vbcrlf
sql = sql & " AND GLAMFO.OPTFIELD = GLAMFO_I.OPTFIELD" & vbcrlf
sql = sql & " AND GLAMFO.AUDTDATE = GLAMFO_I.AUDTDATE" & vbcrlf
sql = sql & " GROUP BY GLAMF.ACSEGVAL01" & vbcrlf
sql = sql & " ,GLAMFO.OPTFIELD" & vbcrlf
sql = sql & " ,GLAMFO.AUDTDATE" & vbcrlf
sql = sql & " ) GLAMFO_O ON GLAMF.ACSEGVAL01 = GLAMFO_O.ACSEGVAL01" & vbcrlf
sql = sql & " AND GLAMFO.OPTFIELD = GLAMFO_O.OPTFIELD" & vbcrlf
sql = sql & " AND GLAMFO.AUDTDATE = GLAMFO_O.AUDTDATE" & vbcrlf
sql = sql & " AND GLAMFO.AUDTTIME = GLAMFO_O.AUDTTIME" & vbcrlf
sql = sql & " WHERE GLAMF.ACSEGVAL01 = '1000'" & vbcrlf
vCSQRY.Browse sql, True
vCSQRY.InternalSet 256
while vCSQRY.Fetch
...

 

[iwells]

That doesn't work either ... I wish I could show someone this issue because I'm totally baffled.

 

[iwells]

looking at it through SQL profile and for some reason in profiler it shows multiple 's around the number, not just one set so its ''4000'' not '4000'

 

[DjangMan]

That's odd. What if you remove the original set of apostrophes? (Which is crazy but at this point we're dealing with crazy.)

 

[iwells]

Here's what I get in profiler, but I think that's ok ... here's a side by side comparison of something that worked:

-- ACCTBAL query
declare @p1 int
set @p1=180150037
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=-1
exec sp_cursoropen @p1 output,N'SELECT GLAMF.ACSEGVAL01
,GLAMFO.OPTFIELD
,GLAMFO.VALUE
,GLAMFO.AUDTDATE
,GLAMFO.AUDTTIME
FROM GLAMF
INNER JOIN GLAMFO ON GLAMF.ACCTID = GLAMFO.ACCTID
INNER JOIN (
SELECT GLAMF.ACSEGVAL01
,GLAMFO.OPTFIELD
,GLAMFO.AUDTDATE
,MAX(GLAMFO.AUDTTIME) AUDTTIME
FROM GLAMF
INNER JOIN GLAMFO ON GLAMF.ACCTID = GLAMFO.ACCTID
INNER JOIN (
SELECT GLAMF.ACSEGVAL01
,GLAMFO.OPTFIELD
,MAX(GLAMFO.AUDTDATE) AUDTDATE
FROM GLAMF
INNER JOIN GLAMFO ON GLAMF.ACCTID = GLAMFO.ACCTID
GROUP BY GLAMF.ACSEGVAL01
,GLAMFO.OPTFIELD
) GLAMFO_I ON GLAMF.ACSEGVAL01 = GLAMFO_I.ACSEGVAL01
AND GLAMFO.OPTFIELD = GLAMFO_I.OPTFIELD
AND GLAMFO.AUDTDATE = GLAMFO_I.AUDTDATE
GROUP BY GLAMF.ACSEGVAL01
,GLAMFO.OPTFIELD
,GLAMFO.AUDTDATE
) GLAMFO_O ON GLAMF.ACSEGVAL01 = GLAMFO_O.ACSEGVAL01
AND GLAMFO.OPTFIELD = GLAMFO_O.OPTFIELD
AND GLAMFO.AUDTDATE = GLAMFO_O.AUDTDATE
AND GLAMFO.AUDTTIME = GLAMFO_O.AUDTTIME
WHERE GLAMF.ACCTTYPE = ''B''',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

-- ACSEGVAL01 query
declare @p1 int
set @p1=180150041
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=-1
exec sp_cursoropen @p1 output,N'SELECT GLAMF.ACSEGVAL01
,GLAMFO.OPTFIELD
,GLAMFO.VALUE
,GLAMFO.AUDTDATE
,GLAMFO.AUDTTIME
FROM GLAMF
INNER JOIN GLAMFO ON GLAMF.ACCTID = GLAMFO.ACCTID
INNER JOIN (
SELECT GLAMF.ACSEGVAL01
,GLAMFO.OPTFIELD
,GLAMFO.AUDTDATE
,MAX(GLAMFO.AUDTTIME) AUDTTIME
FROM GLAMF
INNER JOIN GLAMFO ON GLAMF.ACCTID = GLAMFO.ACCTID
INNER JOIN (
SELECT GLAMF.ACSEGVAL01
,GLAMFO.OPTFIELD
,MAX(GLAMFO.AUDTDATE) AUDTDATE
FROM GLAMF
INNER JOIN GLAMFO ON GLAMF.ACCTID = GLAMFO.ACCTID
GROUP BY GLAMF.ACSEGVAL01
,GLAMFO.OPTFIELD
) GLAMFO_I ON GLAMF.ACSEGVAL01 = GLAMFO_I.ACSEGVAL01
AND GLAMFO.OPTFIELD = GLAMFO_I.OPTFIELD
AND GLAMFO.AUDTDATE = GLAMFO_I.AUDTDATE
GROUP BY GLAMF.ACSEGVAL01
,GLAMFO.OPTFIELD
,GLAMFO.AUDTDATE
) GLAMFO_O ON GLAMF.ACSEGVAL01 = GLAMFO_O.ACSEGVAL01
AND GLAMFO.OPTFIELD = GLAMFO_O.OPTFIELD
AND GLAMFO.AUDTDATE = GLAMFO_O.AUDTDATE
AND GLAMFO.AUDTTIME = GLAMFO_O.AUDTTIME
WHERE GLAMF.ACSEGVAL01 = ''4000''',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

If I then take that query and turn around and use it in SQL management studio (less the extra 's then it works.

DjangMan ... you have some contact info? At this point I'm willing to pay for outside support and consulting for review/support.

 

[DjangMan]

It looks like it is simply how the profiler is showing the string values. Two single quotes will be interpreted as one. If the top query works then the two single quotes theory is shot.

Let's try one other route first. Can you turn your query into a view and then use CS0120 to call that view?

 

[iwells]

Either I'm doing something wrong (which I'm willing to accept that's very possible) or something odd is going on here ... in either case I'm serious if you have a way to legit bill me because at this point I'm willing to pay someone to look at this and tell me what's wrong unless someone has a better idea?

What I've actually done now is hard code

mDBLinkCmpRO.OpenView "CS0120", vCSQRY

sql = "SELECT '4000' ACSEGVAL01 ,'GLGOV' OPTFIELD ,'TEST' VALUE ,'20000000' AUDTDATE ,'0000000000' AUDTTIME"

vCSQRY.Browse sql, True
vCSQRY.InternalSet 256

Do While vCSQRY.Fetch = True

When the .Browse statement executes in my watch vCSQRY.Fetch = True but then as soon as I step to the next line of code for .InternalSet 256 it switches to False.

 

[Jay Converse]

"SELECT '4000' ACSEGVAL01 ,'GLGOV' OPTFIELD ,'TEST' VALUE ,'20000000' AUDTDATE ,'0000000000' AUDTTIME" is not a valid SQL statement. At all.

 

[iwells]

Sure it is ... it will return those values exactly, try running it in SSMS ... its the same as running SELECT 1 + 4

 

[Jay Converse]

Not in my world it's not. "SELECT * FROM GLAMF WHERE ABC = 'XYZ'" is a valid SQL statement.

 

[iwells]

I've altered the query many ways and it doesn't seem to work, at this point I'm interested in contacting people for support that my company is willing to pay for if you guys have separate consulting services.

If so, post an email so I can contact you.

It's odd because in another thread I had a similar issue with CSQRY and .Browse/Fetch and I've tried .GoNext as Ettienne suggested but that doesn't work either. In the end in my other thread I ended up switching over to ADO to solve the issue, but I really don't want to keep doing that anymore.

 

[DjangMan]

Is your dbLink read-only? If so, it doesn't work properly using a read-only connection. I ask because your dblink variable name is mDBLinkCmpRO.

 

[DjangMan]

There's also only one record returned in your results set to .fetch will return false. You can't examine the .fetch value without invoking the method which will cause the record set to move forward, not find a record and thus return false.

 

[iwells]

Thanks Djangman, so my next question is how do I test if there's a record?

 

[DjangMan]

Two ways:
Run a count(*) query first.

Second:
[pre]If .fetch then
do

until .fetch = false
end if[/pre]

 

[iwells]

With this method won't it fail if there's 1 record? With 1 record you need to do a count(*) correct?

If .fetch then
do

until .fetch = false
end if

 

[DjangMan]

No, fortunately. The first .fetch will make the first record available. While the .browse technically runs the query, isn't the important part of the loop you're looking for. .Browse will run the query. .InternalSet 256 will review the result set to set up the fields and data types and the first .fetch will move from BOF to the first record.

 

[iwells]

Thank you ... that's exactly that explanation I've needed.

 

[iwells]

Now that I understand that a bit better I'm still trying to make sense of what's actually happening because it still doesn't work. I've switched the RO to RW and progress is being made. After the switch I've added watches on vCSQRY.Fetch and 2 other later lines of code that would contain the values of the optional field and it's value and they're reflecting the actual value,.

Here's what happens with my watches:

after .Browse
.fetch = true / vCSQRY.Fields("OPTFIELD").Value = '' / vCSQRY.Fields("VALUE").Value = ''

after .internalset 256
.fetch = false / vCSQRY.Fields("OPTFIELD").Value = 'GLGOV' / vCSQRY.Fields("VALUE").Value = '101300'

So by the time I get to the if .fetch then it appears as though the record is already at BOF somehow ... going to adjust the code and look at it using the .GoNext way that Ettienne mentioned before.

 

[DjangMan]

.Fetch is not the same as .EOF. .Fetch is a method and .EOF is a property.

.Fetch will move the record pointer to the next record _and_ return if it was able to find another record.