Solved

Convert SQL expressions to dBase expressions?

Posted on 2006-10-20
6
1,373 Views
Last Modified: 2008-01-23
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
Comment
Question by:cricketman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 33

Accepted Solution

by:
CarlWarner earned 150 total points
ID: 17775842
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
 

Author Comment

by:cricketman
ID: 17776816
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
 
LVL 33

Expert Comment

by:CarlWarner
ID: 17776905
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
 

Author Comment

by:cricketman
ID: 17776972
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

Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question