Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
Click Here
|
Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
|
WHERE Filter conditions for sp_report |
Author |
Message |
|
Posted : 2006-10-04 05:46:26 |
I tried to apply AccountFilterFullNames in SalesbyItemDetail, but the result is empty. I get error 3120 Object x cannot be found. While all account names I tried show up if the parameter is omitted. Is it possible to add filter conditions by means of WHERE clauses in addition to the report parameters? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-10-04 09:05:49 |
Yes, you can use the WHERE clause in a sp_report like this:
sp_report SalesByItemDetail show Text, Blank, TxnType, Date, RefNumber, Memo, Name, Quantity, UnitPrice, Amount, RunningBalance, AccountFullName parameters DateMacro = 'ThisYearToDate' where AccountFullName='Hardware:Doorknobs Std' or AccountFullName='Appliance'
|
|
|
|
|
Posted : 2006-11-29 07:26:39 |
Hi, I tried filtering by a ModifiedTime time stamp according to this post. The statement is: sp_report SalesByItemDetail show Account, TxnType, Date, RefNumber, Item, Memo, Name, Quantity, UnitPrice, Amount, ModifiedTime WHERE "ModifiedTime" >= {ts '2006-11-20 00:00:00.000'}
However, I either get the error "invalid operand for operator >=" with the above syntax. Or when I try to change the time stamp format, I get "expected lexical element not found: <identifier>"
What is the correct format/syntax that needs to be applied? Your help is appreciated as always. |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-11-29 12:01:12 |
The ModifiedTime value is currently being returned as an invalid format in SP_REPORT that isn't useable in SQL comparsions etc.
ModifiedTime is returned as: 09/25/2002 11:54:25 instead of the normal SQL timestamp format as: 2002-09-25 11:54:25
I have requested this to be corrected by the QODBC Software Engineer on Ticket ID: BR00000041. Once the format is corrected you could do:
sp_report SalesByItemDetail show Account, TxnType, Date, RefNumber, Item, Memo, Name, Quantity, UnitPrice, Amount, ModifiedTime WHERE {fn CONVERT(ModifiedTime, SQL_TYPE_TIMESTAMP)} >= {ts '2006-11-20 00:00:00.000'}
in the meantime, you will need to use the Date (not as good):
sp_report SalesByItemDetail show Account, TxnType, Date, RefNumber, Item, Memo, Name, Quantity, UnitPrice, Amount, ModifiedTime WHERE Date >= {d '2006-11-20'}
|
|
|
|
Lynn |
|
Group | : Members |
Posts | : 1 |
Joined | : 2008-05-31 |
|
Profile |
|
Posted : 2008-05-31 14:54:02 |
Hello. I tried running the following query in VB Demo 32:
sp_report TxnDetailByAccount show Account, Amount, Date, Memo, ModifiedTime, Name, TxnNumber where {fn CONVERT(ModifiedTime, SQL_TYPE_TIMESTAMP)} >= {ts '2008-05-20 00:00:00.000'}
The query returned no rows. However, there are entries in the report with ModifiedTime > '2008-05-20'. How do I correctly filter the report by ModifiedTime? Is this functionality working now?
Thanks, Lynn |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-06-02 07:31:48 |
Sorry, the ModifiedTime value is still being returned as an invalid format in SP_REPORT that isn't useable in ANY SQL comparsions etc |
|
|
|
|