get data sql dateadd function

[ChetMeinzer]

i'm trying to get all rows where value in column x is < (column y plus some days)
figured this would work
DATEADD(T0."opdate",3,'m') >= T1."svcdate"

but it just doesn't for some reason


I read in the command syntax  manual

Note that if you are using the function in an SQL query, use single quotation marks (' ') instead of double quotation marks (" ") when specifying the Interval parameter.


my full query

  /SQL='SELECT T0."case", min(T1."svcdate") AS "svcdate_min" FROM { oj '+
    '"postgres"."public"."episodes" T0 LEFT OUTER JOIN "postgres"."public"."dbsvc" T1 ON T0."case" '+
    ''+
    '= T1."case" }  WHERE (T1.svcdate >= {ts ''2015-05-01 00:00:00''}) and (T0."opdate" <= T1."svcdate") and '+
   '(T0."opdate" >= {ts ''2015-05-01 00:00:00''}  and ((DATEADD(T0."opdate",3,'m') >= T1."svcdate") ) GROUP BY T0."case"'
  /ASSUMEDSTRWIDTH=255.

Am i way off track, btw?  The query works without the dateadd function part.

[ChetMeinzer]

I realize that i put
'm', 3
in the wrong order when i posted this question.

that isn't the problem, i just pasted the wrong example

should be
DATEADD(T0.'opdate','m',3)

[ChetMeinzer]

sorry!
One more edit.

Does it matter that i'm connecting to a postgresSQL db?

[Albert-Jan Roskam-3]

Hi Chet,

"PostgreSQL does not provide DATEADD function similar to SQL Server, Sybase or MySQL"
http://www.sqlines.com/postgresql/how-to/dateadd

Albert-Jan

> Date: Fri, 28 Aug 2015 11:47:27 -0700
> From: [e-mail]
> Subject: Re: [SPSSX-L] get data sql dateadd function
> To: [e-mail]
>
> I realize that i put
> 'm', 3
> in the wrong order when i posted this question.
>
> that isn't the problem, i just pasted the wrong example
>
> should be
> DATEADD(T0.'opdate','m',3)
>
>
>
> --
> View this message in context: http://spssx-discussion.1045642.n5.nabble.com/get-data-sql-dateadd-function-tp5730546p5730548.html
> Sent from the SPSSX Discussion mailing list archive at Nabble.com.
>
> =====================
> To manage your subscription to SPSSX-L, send a message to
> [e-mail] (not to SPSSX-L), with no body text except the
> command. To leave the list, send the command
> SIGNOFF SPSSX-L
> For a list of commands to manage subscriptions, send the command
> INFO REFCARD

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[ChetMeinzer]

Thank you again!
I was thinking the spss sql language was special/limited to only a few functions and some how odbc converted the syntax to postgres query language.

Have a great weekend.

Inviato da iPhone

Il giorno 28/ago/2015, alle ore 23:42, Albert-Jan Roskam-3 [via SPSSX Discussion] <[hidden email]<mailto:[hidden email]>> ha scritto:

Hi Chet,

"PostgreSQL does not provide DATEADD function similar to SQL Server, Sybase or MySQL"
http://www.sqlines.com/postgresql/how-to/dateadd

Albert-Jan

> Date: Fri, 28 Aug 2015 11:47:27 -0700
> From: [hidden email]<mailto:[hidden email]>
> Subject: Re: [SPSSX-L] get data sql dateadd function
> To: [hidden email]<mailto:[hidden email]>
>
> I realize that i put
> 'm', 3
> in the wrong order when i posted this question.
>
> that isn't the problem, i just pasted the wrong example
>
> should be
> DATEADD(T0.'opdate','m',3)
>
>
>
> --
> View this message in context: http://spssx-discussion.1045642.n5.nabble.com/get-data-sql-dateadd-function-tp5730546p5730548.html
> Sent from the SPSSX Discussion mailing list archive at Nabble.com<http://Nabble.com>.
>
> =====================
> To manage your subscription to SPSSX-L, send a message to
> [hidden email]<mailto:[hidden email]> (not to SPSSX-L), with no body text except the
> command. To leave the list, send the command
> SIGNOFF SPSSX-L
> For a list of commands to manage subscriptions, send the command
> INFO REFCARD

===================== To manage your subscription to SPSSX-L, send a message to [hidden email]</user/SendEmail.jtp?type=node&node=5730551&i=0> (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

________________________________
If you reply to this email, your message will be added to the discussion below:
http://spssx-discussion.1045642.n5.nabble.com/get-data-sql-dateadd-function-tp5730546p5730551.html
To unsubscribe from get data sql dateadd function, click here<
NAML<http://spssx-discussion.1045642.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>

[ChetMeinzer]

Thanks Albert-Jan!

I'm still stuck.  are you saying something like this would work?

GET DATA
  /TYPE=ODBC
  /CONNECT='DSN=test-pg;DATABASE=postgres;SERVER=10.144.201.18;PORT=5432;UID=chet;PWD=-y!P#z-E#T,'+
    'E$J/S;SSLmode=disable;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning='+    
    '0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize='+
    '255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=0;Ksqo=1;UseDeclareFetch='+
    '0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt='+
    '0;ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableCursors=1;DisallowPremature='+
    '0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=1;LowerCaseIdentifier='+
    '0;GssAuthUseGSS=0;XaOpt=1'
  /SQL='SELECT T0."case", min(T1."svcdate") AS "svcdate_min" FROM { oj '+
    '"postgres"."public"."episodes" T0 LEFT OUTER JOIN "postgres"."public"."dbsvc" T1 ON T0."case" '+
    ''+
    '= T1."case" }  WHERE (T1.svcdate >= {ts ''2015-05-01 00:00:00''}) and (T0."opdate" <= T1."svcdate") and '+
   '(T0.opdate >= {ts ''2015-05-01 00:00:00''}  and (T0.opdate * INTERVAL ''30 day'' >= T1.svcdate) ) GROUP BY T0.case'
  /ASSUMEDSTRWIDTH=255.
CACHE.
EXECUTE.



i was thinking that spss limited what sql operators it accepts (i.e. i get this error
>SQLExecDirect failed :ERROR: operator does not exist: date * interval;
>Error while executing the query

[Jon K Peck]

Statistics does not limit operators in the SQL.  That's left to whatever the ODBC driver can do.


Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: [phone]




From:        ChetMeinzer <[hidden email]>
To:        [hidden email]
Date:        09/01/2015 07:22 AM
Subject:        Re: [SPSSX-L] get data sql dateadd function
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Thanks Albert-Jan!

I'm still stuck.  are you saying something like this would work?

GET DATA
 /TYPE=ODBC

/CONNECT='DSN=test-pg;DATABASE=postgres;SERVER=10.144.201.18;PORT=5432;UID=chet;PWD=-y!P#z-E#T,'+
 
'E$J/S;SSLmode=disable;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning='+    
 
'0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize='+
 
'255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=0;Ksqo=1;UseDeclareFetch='+
 
'0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt='+
 
'0;ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableCursors=1;DisallowPremature='+
 
'0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=1;LowerCaseIdentifier='+
   '0;GssAuthUseGSS=0;XaOpt=1'
 /SQL='SELECT T0."case", min(T1."svcdate") AS "svcdate_min" FROM { oj '+
   '"postgres"."public"."episodes" T0 LEFT OUTER JOIN
"postgres"."public"."dbsvc" T1 ON T0."case" '+
   ''+
   '= T1."case" }  WHERE (T1.svcdate >= {ts ''2015-05-01 00:00:00''}) and
(T0."opdate" <= T1."svcdate") and '+*
  '(T0.opdate >= {ts ''2015-05-01 00:00:00''}  and (T0.opdate * INTERVAL
''30 day'' >= T1.svcdate) ) *GROUP BY T0.case'
 /ASSUMEDSTRWIDTH=255.
CACHE.
EXECUTE.



i was thinking that spss limited what sql operators it accepts (i.e. i get
this error
>SQLExecDirect failed :ERROR: operator does not exist: date * interval;
>Error while executing the query




--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/get-data-sql-dateadd-function-tp5730546p5730559.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD



===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[Albert-Jan Roskam-3]

> Date: Mon, 31 Aug 2015 17:52:12 -0700

> From: [hidden email]

> Subject: Re: [SPSSX-L] get data sql dateadd function
> To: [hidden email]
>
> Thanks Albert-Jan!
>
> I'm still stuck. are you saying something like this would work?
>
> GET DATA
> /TYPE=ODBC
>
> /CONNECT='DSN=test-pg;DATABASE=postgres;SERVER=10.144.201.18;PORT=5432;UID=chet;PWD=-y!P#z-E#T,'+
>
> 'E$J/S;SSLmode=disable;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning='+
>
> '0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize='+
>
> '255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=0;Ksqo=1;UseDeclareFetch='+
>
> '0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt='+
>
> '0;ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableCursors=1;DisallowPremature='+
>
> '0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=1;LowerCaseIdentifier='+
> '0;GssAuthUseGSS=0;XaOpt=1'
> /SQL='SELECT T0."case", min(T1."svcdate") AS "svcdate_min" FROM { oj '+
> '"postgres"."public"."episodes" T0 LEFT OUTER JOIN
> "postgres"."public"."dbsvc" T1 ON T0."case" '+
> ''+
> '= T1."case" } WHERE (T1.svcdate >= {ts ''2015-05-01 00:00:00''}) and
> (T0."opdate" <= T1."svcdate") and '+*
> '(T0.opdate >= {ts ''2015-05-01 00:00:00''} and (T0.opdate * INTERVAL
> ''30 day'' >= T1.svcdate) ) *GROUP BY T0.case'
> /ASSUMEDSTRWIDTH=255.
> CACHE.
> EXECUTE.
>
>
>
> i was thinking that spss limited what sql operators it accepts (i.e. i get
> this error
> >SQLExecDirect failed :ERROR: operator does not exist: date * interval;
> >Error while executing the query

Hi,

I think "T0.opdate * INTERVAL '30 day'" is incorrect because T0.opdate is a date, not an integer.
Perhaps you can CAST it to an INT. However, I do not know what you intend to do with your query.
Given that date fields are epochs, could you not simply do
(T0.opdate >= T1.svcdate)
...instead of
(T0.opdate * INTERVAL '30 day' >= T1.svcdate)

Also, I prefer using a file DSN because then your username and password won' t be echoed in your .spv and .jnl file.
You can ignore the .dsn in your version control system if you use that.

I formatted the SQL query a bit so it becomes easier to read. On two lines I replaced the outer single quotes with double quotes
so the inner single quotes do not need to be escaped by doubling them.

* in e.g. c:\temp\postgres.dsn
* add more non-default settings as needed, see original syntax.
[ODBC]
DRIVER=PostgreSQL
DATABASE=postgres
SERVER=10.144.201.18
UID=chet
PWD=Secret!

* at the beginning of the syntax.
* file handle will not work here, not even with CD (Production Facility).
DEFINE !file_dsn () "c:\temp\postgres.dsn" !ENDDEFINE. 

* Perhaps you can also remove the double quotes surrounding the column names (unless they'd have spaces or are reserved words!).
GET DATA
 /TYPE=ODBC
 /CONNECT='FILEDSN=' + !file_dsn
 /SQL='SELECT T0."case", MIN(T1."svcdate") AS "svcdate_min" ' +
      'FROM { oj "postgres"."public"."episodes" T0 ' +
      'LEFT OUTER JOIN "postgres"."public"."dbsvc" T1 ' +
      'ON T0."case" = T1."case" } ' +
      'WHERE (T1.svcdate >= {ts ''2015-05-01 00:00:00''}) AND ' +
      '       (T0.opdate <= T1.svcdate) AND ' +
      "       (T0.opdate >= {ts '2015-05-01 00:00:00'} AND " +
      "       (T0.opdate * INTERVAL '30 day' >= T1.svcdate) " +
      '       )' +
      'GROUP BY T0.case'
 /ASSUMEDSTRWIDTH=255.
CACHE.
EXECUTE.


regards,
Albert-Jan

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD