OpenQuery - error in timestamp

[snufse]

I am connectiong to an Advanatge data base and have problems with a db timestamp field. The query runs on sequel server 2005.

SELECT *
  FROM OPENQUERY(ADS_RGGT_SERVER, 'SELECT MAX(SHIFT_STARTED) as shift_date,
                                          SUM(CASE WHEN customer_description_1 = ''Ranger Construction Industries''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS intercompany_qty,
                                          SUM(CASE WHEN customer_description_1 <> ''Ranger Construction Industries''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS outside_qty
                                     FROM salestkt
                                     WHERE (void is null or void = false) and
                                           incoming_material = false and 
                                           shift_started = (SELECT MAX(STK.shift_started) as shift_started
                                                             FROM salestkt AS STK
                                                              WHERE EXISTS(SELECT *
                                                                                FROM SHFTDATE AS SD
                                                                                 WHERE SD.shift_started is not Null and
                                                                                       SD.shift_started = STK.shift_started))')

Getting error:
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

If there are no records selected I get error in the "shift_started" field.

SALESTKT table:

2/23/2010 4:08:00 PM


SHFTDATE table ( 2 records)

ASTEC      5/25/2007 11:25:00 AM
R              Nothing

Now, if I leave out the line "MAX(SHIFT_STARTED) as shift_date," from my query things work fine, in this case no records are found and both my quantity fields appear with zeros (which is correct). For some reason it does not like when the "shift_started" is nothing (or not found).

Can anyone help, I have working on this issue for several days. Thank you.

[mikr0s]

try converting the MAX(SHIFT_STARTED) to varchar at the sybase side


....FROM OPENQUERY(ADS_RGGT_SERVER, 'SELECT CONVERT(varchar(30), MAX(SHIFT_STARTED), 2) as shift_date, ...

--HTH--

[snufse]

Tried:

SELECT *
  FROM OPENQUERY(ADS_RGGT_SERVER, 'SELECT CONVERT(varchar(30), MAX(SHIFT_STARTED), 2) as shift_date,
                                          SUM(CASE WHEN customer = ''32''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS intercompany_qty,
                                          SUM(CASE WHEN customer <> ''32''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS outside_qty
                                     FROM salestkt

Getting error:

OLE DB provider "MSDASQL" for linked server "ADS_RGGT_SERVER" returned message "[Extended Systems][Advantage SQL][ASA] Error 7200: AQE Error: State = S0000; NativeError = 2159; [Extended Systems][Advantage SQL Engine]Invalid argument to scalar function: CONVERT -- Location of error in the SQL statement is: 8".

[snufse]

Got it working:

SELECT *
  FROM OPENQUERY(ADS_RGGT_SERVER, 'SELECT CONVERT(MAX(shift_started), SQL_VARCHAR) as shift_date,
                                          SUM(CASE WHEN customer = ''32''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS intercompany_qty,
                                          SUM(CASE WHEN customer <> ''32''
                                                        THEN qty_shipped_today
                                                   ELSE 0
                                              END) AS outside_qty
                                     FROM salestkt
                                     WHERE ((void is null or void = false) or
                                           incoming_material = false) and 
                                           shift_started = (SELECT MAX(STK.shift_started) as shift_started
                                                             FROM salestkt AS STK
                                                              WHERE EXISTS(SELECT *
                                                                                FROM SHFTDATE AS SD
                                                                                 WHERE SD.shift_started is not Null and
                                                                                       SD.shift_started = STK.shift_started))')

Thank you.

[rhettss]

I had to chuckle when I read '...sequel server 2005' in the original post. (Please take no offense.)
Reminds me of the time when a new developer on my team once, guilelessly, pronounced 'SQL SERVER 2000' as "Squirrel Server 2000.'