• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1404
  • Last Modified:

Convert SQL expressions to dBase expressions?

Need to write some simple dBase expressions for a filter in Goldmine to filter all records that have been updated in the last year.  I can struggle thru them in SQL, but Goldmine has to have a dBase equivalent expression to make it work.

Here's what I have in SQL:

WHERE c1.LASTDATE >= DATEADD(dd,-365,GETDATE())

Is there any software that could take an SQL expression like this & show me the dBase equivalent?

Thanks.....
0
cricketman
Asked:
cricketman
  • 2
  • 2
1 Solution
 
CarlWarnerCommented:
I know of no magic bullet software.

As far as an expression for dBASE, dBASE likes to see a FOR construct in its queries.

Simply try that same construct withjust the SQL WHERE keyword in your Goldmine expression builder.

This logocal condition would be added to the the dBASE FOR clause:
c1.LASTDATE >= DATEADD(dd,-365,GETDATE())

Since the success of the query depends on entities it works on being active, it could fail.  But, you need to find that out by testing it.  IOW, is "c1" an active/open table or is it an alias supplied to SQL?  Is GETDATE() an available function call?  Is DATEADD() also an available function that can get executed to return a real date that can be evaluated?
0
 
cricketmanAuthor Commented:
GM must actually "assume" the WHERE and FOR commands, as when I looked at the SQL query again it really was just:

c1.LASTDATE >= DATEADD(dd,-365,GETDATE())

Through trial & error I figured out what I needed in the dBase expression to match it, which is:

DtoS(contact1->lastdate) >= DtoS(DATE()-365)

Got it working.... still hoping someone will point me towards a solution that I can use routinely.  If not "magic software"... perhaps just a list of comparable expressions?  (... "this" in SWQL = "this" in dBase, etc).

Thanks....
0
 
CarlWarnerCommented:
Have you determined that the use of DtoS() is absolutely necessary?  It seems that the comparison of dates without that string conversion should work just fine and the DtoS() may be redundant.

Also, I see c1 was an alias for contact1.
0
 
cricketmanAuthor Commented:
NO, don't know if it is absolutely necessary or not... just copied it from another GM filter that was working, and played with the syntax till I got the expected results.

And yes, c1 was an alias for table contact1.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now