ODBC and Date Format

mbrown

I've been having trouble with the date format in my ODBC sql statement. I've seen code in here and copied it exactly and still nothing works. I get the error message to the effect of "not correct operator for operand: =". My code is:......

....AND "Posting Date = {d '2001-01-01'}

My sql statement works fine if this statement is removed. I'm using ODBC Driver ver. 2.6. Any suggestions? <img border="0" title="" alt="" src="images/smiles/icon_confused.gif" />

John_Tegelaar

I have used
("Posting Date"={ts '2001-01-03 00:00:00'})
for successfully retrieving dates through ODBC.

John

mbrown

Thanks John, that did it! Have you ever considered writing an ODBC manual?

John_Tegelaar

LOL - perhaps if some time is left. Between 5 and 6 AM, or so... <img border="0" title="" alt="" src="images/smiles/icon_smile.gif" />

My "trick" for ODBC stuff is to use Excel's Get External Data function and see what queries are composed, then use the query tool to find the simplifications (if possible).

John

kmocai

Hi John, what if I needed to 'back feed' a date range into the Date Filter of the Customer table so that I could pull correct values into Excel for some of the flowfields?

John_Tegelaar

Kelly, in that case there's more involved than just plugging in some values. As you may have seen, flowfields and flowfilters are not stored in the table definition of SQL, but Navision is maintaining these separately. You cannot change the value in a flowfilter field through ODBC directly; you must do this in Navision, or you have to build yourself the query to the table where the SIFT values are stored.

For each flowfield you design, a separate table is created in SQL, referring to the table where the values are related to.

For example: A flowfield in the Customer table gets its values from the Customer Ledger Entries, which is Table 21. So, in SQL you will find a table named "CRONUS International Ltd.$21$0" The entry values for the filter combinations ("buckets") are stored in more or less cryptic coded fields in this table. Navision is using some special stored procedure to retrieve a flowfield value.

An example of what's done to get values:
<BLOCKQUOTE><font size="1" face="Verdana, Arial">code:</font><HR><pre>exec sp_executesql N'SELECT SUM("sum1") FROM
(SELECT SUM("s16") AS "sum1" FROM "Cronus 260E"."dbo"."CRONUS International Ltd.$21$1"
WHERE ("bucket"=@P1 AND (("f3"=@P2)) AND (("f36"=@P3)) AND "f37"<=@P4 AND "f37">=@P5)
UNION ALL
SELECT SUM("s16") AS "sum1" FROM "Cronus 260E"."dbo"."CRONUS International Ltd.$21$1"
WHERE ("bucket"=@P6 AND (("f3"=@P7)) AND (("f36"=@P8)) AND "f37">=@P9 AND "f37"<=@P10)
UNION ALL
SELECT SUM("s16") AS "sum1" FROM "Cronus 260E"."dbo"."CRONUS International Ltd.$21$1"
WHERE ("bucket"=@P11 AND (("f3"=@P12)) AND (("f36"=@P13)) AND "f37">=@P14
AND "f37"<=@P15)
UNION ALL
SELECT SUM("s16") AS "sum1" FROM "Cronus 260E"."dbo"."CRONUS International Ltd.$21$1"
WHERE ("bucket"=@P16 AND (("f3"=@P17)) AND (("f36"=@P18)) AND "f37">=@P19 AND "f37"<=@P20)) AS
[Sums]', N'@P1 int,@P2 varchar(20),@P3 tinyint,
@P4 datetime,@P5 datetime,@P6 int,@P7 varchar(20),
@P8 tinyint,@P9 datetime,@P10 datetime,
@P11 int,@P12 varchar(20),@P13 tinyint,
@P14 datetime,@P15 datetime,@P16 int,
@P17 varchar(20),@P18 tinyint,@P19 datetime,
@P20 datetime', 4, '10000', 1, 'Jan 3 1753 12:00AM',
'Jan 1 2000 12:00AM', 5, '10000', 1, 'Jan 3 1753 12:00AM',
'Jan 3 1753 12:00AM', 6, '10000', 1, 'Jan 1 1753 12:00AM',
'Jan 3 1753 12:00AM', 6, '10000', 1, 'Jan 1 2001 12:00AM',
'Jan 25 2001 12:00AM'</pre><HR></BLOCKQUOTE>

This is a part of the queries executed to get the values on screen for the Statistics of Customer No. 10000.

If you have the time and courage and SQL knowledge, you may want to develop SQL Statements like these yourself, but perhaps it's easier to update the Excel cells from within Navision through an Automation Object connection. You can find several examples of connecting Excel and Navision in this forum.

John

[ 31-01-2002: Message edited by: John Tegelaar ]