?
Solved

Providex Sysdate

Posted on 2009-04-30
14
Medium Priority
?
2,858 Views
Last Modified: 2013-12-20
What is the function and syntax for a MAS90 database if i want to create a where statement that does the following:
where DateUpdated > Sysdate()-1
or
where DateUpdated > date()-1

basically today minus 1 everything updated within the last day. Access has a Date() function not sure what it is here Help Please!!!
where DateUpdated > Sysdate()-1
or 
where DateUpdated > date()-1

Open in new window

0
Comment
Question by:Leo Torres
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 24280106
Is this written in VBS or some sort of SQL?

Leon
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24280686
http://www.accountingsoftwarenews.com/mas90/techmas90.htm
"Database - MAS 90 runs on an a proprietary ISAM database. MAS 200 runs on your choice       of either Microsoft SQL Server or a b-tree file system database."

http://www.accountingsoftwarenews.com/mas90/differsmas90.htm mentions an ODBC connection.

Since you mention ProvideX, that must be what you're using ...
Another related question ... http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_DB/Q_21917048.html

The manual is at ftp://www.pvx.com/docs/manuals/Reference/PVXLanguage.pdf
From page 557:
"DAY System Variable DAY Return Curent System Date Return Current System Date
String System Variable
Contents String, current system date, formatted.
Description The DAY variable contains the current system date (e.g., 11/15/00), in a format
based on the date style set in the DAY_FORMAT directive (MM/DD/YY by default).
Changes in DAY_FORMAT are reflected in the value returned in the DAY variable."

So ... how does this work?

where DateUpdated > DAY -1

Open in new window

0
 
LVL 29

Expert Comment

by:leonstryker
ID: 24281020
Never seen this before, but take a look at p 422 of the manual at the DTE( ) function. It may be what you are looking for.

Leon
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 6

Expert Comment

by:rr_miles
ID: 24288146
If using the MAS90 ODBC driver, try the standard SQL syntax "where DateUpdated > {d <date>}".
Also, try creating a Crystal Report with a date filter, then view the SQL statment sytax.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 24309382

Hello, DanielWilson,
{d '1998-07-12'} And {d '2006-07-12'}) is the correct for a specific date that's fine. I need the query to be more generic as it will be used to upload the prior day data into our data warehouse. This way I would have to remember everyday to update the upload valid date. That's why i need to incorporate DAY-1 some how  

I did try DAY - 1...............says DAY Error:
Server Msg: 1021, State: S0000, [ProvideX][ODBC Driver]
Column not found: DAY

The DTE fucntion is sipmly to convert i dont see how that woulf work. I think the DAY function is what I need beacuse it has the value imlooking for.

rr_miles: I Like your idea it crossed my mind but I have no Idea where to go and even look for the query in crystal of one of the reports so I never tried. I built a query on the table I put in a date it the only option cant put a fucntion or parameter there, but i cant find the SQL view you speak of?? I am Using crystal Reports 10.  

Thank you all for your prompt help.
Here is the code of what I am trying to do as well


select * from AR_InvoiceHistoryHeader
where DateUpdated < DAY-1 

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 24315176
By the way this is SQL. I think all those functions are for actually coding the program thats why I think they don't work.. I am really starting to think I am S O L.
0
 
LVL 6

Expert Comment

by:rr_miles
ID: 24315922
Reviewing your original question, I did not see how the query was built/executed.

If using Crystal:
Look on the menu. Select Database. There should be an option on the menu to Show SQL query. Hopefully I not thinking of version 8.5, but Im pretty sure it is the same as 10. When using a parameter in Crystal, everything is handled for you. Change the field comparison to a variable/function using one of the date types, you can get Yesterday.

If using ODBC/ADO/VB:
A little more complex. You must conform to the ProvideX style SQL syntax. {d xxxx} by building the query yourself.

=dateadd(d,-1,now)  yesterday

Remember, unless you work 7 days a week, running this on Monday will yield Sunday, not Friday.
0
 
LVL 6

Expert Comment

by:rr_miles
ID: 24316970
BTW, the above mentioned DTE syntax is: dte(jul(0,0,0)-1:"YYYYMMDD"). Basically, get todays Julian date, subtract 1 day, format in MAS90 date field format.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 24327667
this was a great idea but no cigar!! I open a report that has a date parameter and tried to show SQL and the first thing it did was as for the date then it gave me the SQL code. Which does not help me because if i run this query automatically nightly it has to know to pull today-1. But it seems that i cant find a way to express that with the Rrovidex driver on the ISAM database.  I already knew the {d '2009-05-07'} syntax its expression it as yesterday's date that has been a head ache.

Is there any way i can have that as a calculated string with in SQL 2008 Visual Studio Data Integration..??
just as a work around

Thank you all for your help,
I was breaking my head thinking this was going to be easy... But not so... I am open to any suggestions...  

 SELECT "POP_RecptHistoryHeader"."PurchaseOrderNumber", "POP_RecptHistoryHeader"."ReceiptNumber", "POP_RecptHistoryHeader"."ReceiptDate", "POP_RecptHistoryHeader"."Division", "POP_RecptHistoryHeader"."VendorNumber", "POP_RecptHistoryHeader"."ReceiptType", "POP_RecptHistoryHeader"."TransactionDate", "POM_RecptHistoryLine"."ItemNumber", "POM_RecptHistoryLine"."Description", "POM_RecptHistoryLine"."WarehouseCode", "POM_RecptHistoryLine"."ReceiptQty", "POM_RecptHistoryLine"."ReceiptAmount", "POM_RecptHistoryLine"."UnitCost", "POM_RecptHistoryLine"."RecordType"
 FROM   "POM_RecptHistoryLine" "POM_RecptHistoryLine", "POP_RecptHistoryHeader" "POP_RecptHistoryHeader"
 WHERE  ((("POM_RecptHistoryLine"."PurchaseOrderNumber"="POP_RecptHistoryHeader"."PurchaseOrderNumber") AND ("POM_RecptHistoryLine"."ReceiptType"="POP_RecptHistoryHeader"."ReceiptType")) AND ("POM_RecptHistoryLine"."ReceiptNumber"="POP_RecptHistoryHeader"."ReceiptNumber")) AND "POP_RecptHistoryHeader"."ReceiptDate"={d '2009-05-07'} AND "POM_RecptHistoryLine"."RecordType"='1' AND "POM_RecptHistoryLine"."ItemNumber" LIKE '%' AND "POP_RecptHistoryHeader"."ReceiptType"='G'
 ORDER BY "POM_RecptHistoryLine"."ItemNumber", "POP_RecptHistoryHeader"."ReceiptDate", "POP_RecptHistoryHeader"."ReceiptNumber"

Open in new window

0
 
LVL 6

Expert Comment

by:rr_miles
ID: 24327814
OK, lets be really clear. You are in SQL 200x. You are using TSQL or a stored proceedure. You want to run a query on a MAS90 database (ODBC for native files). Have I got it?
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 24329225
On SQL 2008. I am going to place the query in SQL Integration Services to upload previous day transactions on to my SQL database. Other wise I will have to make a stored procedure. Just to be on the safe side I have included a picture below. The red box must be the expression Today -1...

Thanks again
SQLsrv.bmp
0
 
LVL 6

Accepted Solution

by:
rr_miles earned 2000 total points
ID: 24329338
SELECT & FROM &  WHERE  & (date field) <= {fn curdate()}-1 .. ORDERBY ...
or
SELECT & FROM &  WHERE  & (date field) < {fn curdate()} ... ORDERBY ...

You may find this PDF document for Providex OBDC helpful. It can be downloaded from www.pvx.com. I have attached it here for your convenience. This info is for MAS90 version 4.x, mostly it is for Net Admins. For DBAs, ProvideX supported SQL syntax begins on p.41.  Scalar functions begin on p. 44. They are grouped by type: String, Numeric and Date (p. 46). Some examples of using SQL scalar functions are on p. 48. Remember that each vendor can choose their level of SQL conformance, so SQL syntax may very between vendors (Microsoft,ProvideX,Oracle). This is a good reference to keep close at hand when dealing with MAS90/200.

ODBC4x.pdf
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 24330544
WOW....................... dude you did it that is The Solution. I beend on that for almost a month that is great!!!!!!!!!!!!!
0
 
LVL 8

Author Closing Comment

by:Leo Torres
ID: 31576445
Awesome!!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

864 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