SPSSX Discussion

SQL

classic Classic list List threaded Threaded
♦
♦
 4 messages Options
Sean McKenzie
Reply | Threaded
Open this post in threaded view
♦
♦
|

SQL

Sean McKenzie
I get the below error when I run my program:

>Warning.  Command name: GET DATA
>SQLExecDirect failed :[Microsoft][ODBC Microsoft Access Driver] Too few
>parameters. Expected 2.

I am converting from using a complex access query in effect using just one
table, to attempting to embed the underlying sql in SPSS.

Could someone send a query using multiple tables that works?

Can someone give me a hint as to the above error message?

Anything else?

My SQL works, but what are the discrepancies in formatting when a plae it in
SPSS.

GET DATA /TYPE=ODBC /CONNECT=
'DSN=MS Access'+
' Database;DBQ=H:\ChildCare\SMcKenzie\iccistous\dbtous090506.mdb;DriverId='+
'25;FIL=MS Access;MaxBufferSize=2048;'
  'PageTimeout=5;'
/SQL = 'SELECT  [FamilyMain].[FamilyID],  [FamilyMain].[OfficeCode],
[FamilyMain].[OfficeName],  [FamilyMain].[OfficeUnit], '+
' [FamilyMain].[CCAWorkerID],  [FamilyMain].[CCAWorkerName],
[FamilyMain].[LocCommunity],  [FamilyMain].[MailCity],  '
' [FamilyMember].[MemberID],  [FamilyMember].[RelationshipCode],
[FamilyMain].[ProgramTypeCode], [FamilyAuthVerify].[InvoiceMonth], '
' [FamilyMain].[P1MemberName], [FamilyMain].[ProgramType],
[FamilyMember].[DevelopDisabled], [FamilyMember].[SpecialNeeds], '
' [FamilyAuthVerify].[VerifyComment], [FamilyAuthItem].[ChildMaxStateAmt],
[FamilyAuthItem].[SpecialNeedsPct], [FamilyAuthItem].[SpecialNeedsAmt], '
' [FamilyAuthItem].[Relative], [ProviderMain].[ProviderName],
[ProviderMain].[CCProvTypeCode], [FamilyAuthVerify].[VerifyAuthAmt],
[FamilyAuthVerify].[VerifyRegFeeAmt], '
' [FamilyAuthVerify].[VerifySupplAmt] '
' FROM (((FamilyMain LEFT JOIN FamilyMember ON [FamilyMain].[FamilyID] =
[FamilyMember].[FamilyID]) '
' LEFT JOIN FamilyAuthVerify ON ([FamilyMember].[FamilyID] =
[FamilyAuthVerify].[FamilyID]) AND '
' ([FamilyMember].[MemberID] = [FamilyAuthVerify].[ChildMemberID])) LEFT
JOIN FamilyAuthItem ON '
' [FamilyAuthVerify].[AuthID] = [FamilyAuthItem].[AuthID]) LEFT JOIN
ProviderMain ON '
' [FamilyAuthVerify].[ProviderID] = [ProviderMain].[ProviderID] '
' WHERE ((([FamilyMember].[RelationshipCode])="ch") AND
(([FamilyAuthVerify].[InvoiceMonth])=200605) '
' AND (([FamilyAuthVerify].[ExtractDate])=20060802) AND
(([FamilyAuthItem].[ExtractDate])=20060802) '
' AND (([ProviderMain].[ExtractDate])="08/02/2006") AND
(([FamilyMember].[ExtractDate])=20060802) '
' AND (([FamilyMain].[ExtractDate])=20060802)) '
' ORDER BY [FamilyMain].[FamilyID], [FamilyMember].[MemberID];'.
djhurio
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: SQL

djhurio
I am not sure but probably there is problem with brackets in FROM
clause. It seems that there is one closing bracket missing. But as I
sad I am not sure.

To test does query with multiple tables works in SPSS you can try
building query from menus: File / Open Database / New query ... (using
some sample db).

Best,
Martins


On 26/09/06, Sean McKenzie <[hidden email]> wrote:

> I get the below error when I run my program:
>
> >Warning.  Command name: GET DATA
> >SQLExecDirect failed :[Microsoft][ODBC Microsoft Access Driver] Too few
> >parameters. Expected 2.
>
> I am converting from using a complex access query in effect using just one
> table, to attempting to embed the underlying sql in SPSS.
>
> Could someone send a query using multiple tables that works?
>
> Can someone give me a hint as to the above error message?
>
> Anything else?
>
> My SQL works, but what are the discrepancies in formatting when a plae it in
> SPSS.
>
> GET DATA /TYPE=ODBC /CONNECT=
> 'DSN=MS Access'+
> ' Database;DBQ=H:\ChildCare\SMcKenzie\iccistous\dbtous090506.mdb;DriverId='+
> '25;FIL=MS Access;MaxBufferSize=2048;'
>   'PageTimeout=5;'
> /SQL = 'SELECT  [FamilyMain].[FamilyID],  [FamilyMain].[OfficeCode],
> [FamilyMain].[OfficeName],  [FamilyMain].[OfficeUnit], '+
> ' [FamilyMain].[CCAWorkerID],  [FamilyMain].[CCAWorkerName],
> [FamilyMain].[LocCommunity],  [FamilyMain].[MailCity],  '
> ' [FamilyMember].[MemberID],  [FamilyMember].[RelationshipCode],
> [FamilyMain].[ProgramTypeCode], [FamilyAuthVerify].[InvoiceMonth], '
> ' [FamilyMain].[P1MemberName], [FamilyMain].[ProgramType],
> [FamilyMember].[DevelopDisabled], [FamilyMember].[SpecialNeeds], '
> ' [FamilyAuthVerify].[VerifyComment], [FamilyAuthItem].[ChildMaxStateAmt],
> [FamilyAuthItem].[SpecialNeedsPct], [FamilyAuthItem].[SpecialNeedsAmt], '
> ' [FamilyAuthItem].[Relative], [ProviderMain].[ProviderName],
> [ProviderMain].[CCProvTypeCode], [FamilyAuthVerify].[VerifyAuthAmt],
> [FamilyAuthVerify].[VerifyRegFeeAmt], '
> ' [FamilyAuthVerify].[VerifySupplAmt] '
> ' FROM (((FamilyMain LEFT JOIN FamilyMember ON [FamilyMain].[FamilyID] =
> [FamilyMember].[FamilyID]) '
> ' LEFT JOIN FamilyAuthVerify ON ([FamilyMember].[FamilyID] =
> [FamilyAuthVerify].[FamilyID]) AND '
> ' ([FamilyMember].[MemberID] = [FamilyAuthVerify].[ChildMemberID])) LEFT
> JOIN FamilyAuthItem ON '
> ' [FamilyAuthVerify].[AuthID] = [FamilyAuthItem].[AuthID]) LEFT JOIN
> ProviderMain ON '
> ' [FamilyAuthVerify].[ProviderID] = [ProviderMain].[ProviderID] '
> ' WHERE ((([FamilyMember].[RelationshipCode])="ch") AND
> (([FamilyAuthVerify].[InvoiceMonth])=200605) '
> ' AND (([FamilyAuthVerify].[ExtractDate])=20060802) AND
> (([FamilyAuthItem].[ExtractDate])=20060802) '
> ' AND (([ProviderMain].[ExtractDate])="08/02/2006") AND
> (([FamilyMember].[ExtractDate])=20060802) '
> ' AND (([FamilyMain].[ExtractDate])=20060802)) '
> ' ORDER BY [FamilyMain].[FamilyID], [FamilyMember].[MemberID];'.
>


--
Martins Liberts
[hidden email]
--
http://djhurio.wordpress.com/
http://twitter.com/djhurio
http://www.linkedin.com/in/martinsliberts
http://www.csb.gov.lv/
Jason Burke
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: SQL

Jason Burke
In reply to this post by Sean McKenzie
Hi Sean,

I think you need only make a minor change!

Replace the quotations in your SQL WHERE clause with double
apostrophes. For example with RelationshipCode in FamilyMember table,
and ExtractDate in the ProviderMain table.

This is a requirement imposed by ODBC .

HTH


Jason

On 9/26/06, Sean McKenzie <[hidden email]> wrote:

> I get the below error when I run my program:
>
> >Warning.  Command name: GET DATA
> >SQLExecDirect failed :[Microsoft][ODBC Microsoft Access Driver] Too few
> >parameters. Expected 2.
>
> I am converting from using a complex access query in effect using just one
> table, to attempting to embed the underlying sql in SPSS.
>
> Could someone send a query using multiple tables that works?
>
> Can someone give me a hint as to the above error message?
>
> Anything else?
>
> My SQL works, but what are the discrepancies in formatting when a plae it in
> SPSS.
>
> GET DATA /TYPE=ODBC /CONNECT=
> 'DSN=MS Access'+
> ' Database;DBQ=H:\ChildCare\SMcKenzie\iccistous\dbtous090506.mdb;DriverId='+
> '25;FIL=MS Access;MaxBufferSize=2048;'
>  'PageTimeout=5;'
> /SQL = 'SELECT  [FamilyMain].[FamilyID],  [FamilyMain].[OfficeCode],
> [FamilyMain].[OfficeName],  [FamilyMain].[OfficeUnit], '+
> ' [FamilyMain].[CCAWorkerID],  [FamilyMain].[CCAWorkerName],
> [FamilyMain].[LocCommunity],  [FamilyMain].[MailCity],  '
> ' [FamilyMember].[MemberID],  [FamilyMember].[RelationshipCode],
> [FamilyMain].[ProgramTypeCode], [FamilyAuthVerify].[InvoiceMonth], '
> ' [FamilyMain].[P1MemberName], [FamilyMain].[ProgramType],
> [FamilyMember].[DevelopDisabled], [FamilyMember].[SpecialNeeds], '
> ' [FamilyAuthVerify].[VerifyComment], [FamilyAuthItem].[ChildMaxStateAmt],
> [FamilyAuthItem].[SpecialNeedsPct], [FamilyAuthItem].[SpecialNeedsAmt], '
> ' [FamilyAuthItem].[Relative], [ProviderMain].[ProviderName],
> [ProviderMain].[CCProvTypeCode], [FamilyAuthVerify].[VerifyAuthAmt],
> [FamilyAuthVerify].[VerifyRegFeeAmt], '
> ' [FamilyAuthVerify].[VerifySupplAmt] '
> ' FROM (((FamilyMain LEFT JOIN FamilyMember ON [FamilyMain].[FamilyID] =
> [FamilyMember].[FamilyID]) '
> ' LEFT JOIN FamilyAuthVerify ON ([FamilyMember].[FamilyID] =
> [FamilyAuthVerify].[FamilyID]) AND '
> ' ([FamilyMember].[MemberID] = [FamilyAuthVerify].[ChildMemberID])) LEFT
> JOIN FamilyAuthItem ON '
> ' [FamilyAuthVerify].[AuthID] = [FamilyAuthItem].[AuthID]) LEFT JOIN
> ProviderMain ON '
> ' [FamilyAuthVerify].[ProviderID] = [ProviderMain].[ProviderID] '
> ' WHERE ((([FamilyMember].[RelationshipCode])="ch") AND
> (([FamilyAuthVerify].[InvoiceMonth])=200605) '
> ' AND (([FamilyAuthVerify].[ExtractDate])=20060802) AND
> (([FamilyAuthItem].[ExtractDate])=20060802) '
> ' AND (([ProviderMain].[ExtractDate])="08/02/2006") AND
> (([FamilyMember].[ExtractDate])=20060802) '
> ' AND (([FamilyMain].[ExtractDate])=20060802)) '
> ' ORDER BY [FamilyMain].[FamilyID], [FamilyMember].[MemberID];'.
>
Sean McKenzie
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: SQL

Sean McKenzie
Thanks.  I'll try it.  Possibly sidetracked the next few days, but I'll see.


>From: Jason Burke <[hidden email]>
>Reply-To: Jason Burke <[hidden email]>
>To: [hidden email]
>Subject: Re: SQL
>Date: Tue, 26 Sep 2006 19:52:17 +1000
>
>Hi Sean,
>
>I think you need only make a minor change!
>
>Replace the quotations in your SQL WHERE clause with double
>apostrophes. For example with RelationshipCode in FamilyMember table,
>and ExtractDate in the ProviderMain table.
>
>This is a requirement imposed by ODBC .
>
>HTH
>
>
>Jason
>
>On 9/26/06, Sean McKenzie <[hidden email]> wrote:
>>I get the below error when I run my program:
>>
>> >Warning.  Command name: GET DATA
>> >SQLExecDirect failed :[Microsoft][ODBC Microsoft Access Driver] Too few
>> >parameters. Expected 2.
>>
>>I am converting from using a complex access query in effect using just one
>>table, to attempting to embed the underlying sql in SPSS.
>>
>>Could someone send a query using multiple tables that works?
>>
>>Can someone give me a hint as to the above error message?
>>
>>Anything else?
>>
>>My SQL works, but what are the discrepancies in formatting when a plae it
>>in
>>SPSS.
>>
>>GET DATA /TYPE=ODBC /CONNECT=
>>'DSN=MS Access'+
>>'
>>Database;DBQ=H:\ChildCare\SMcKenzie\iccistous\dbtous090506.mdb;DriverId='+
>>'25;FIL=MS Access;MaxBufferSize=2048;'
>>  'PageTimeout=5;'
>>/SQL = 'SELECT  [FamilyMain].[FamilyID],  [FamilyMain].[OfficeCode],
>>[FamilyMain].[OfficeName],  [FamilyMain].[OfficeUnit], '+
>>' [FamilyMain].[CCAWorkerID],  [FamilyMain].[CCAWorkerName],
>>[FamilyMain].[LocCommunity],  [FamilyMain].[MailCity],  '
>>' [FamilyMember].[MemberID],  [FamilyMember].[RelationshipCode],
>>[FamilyMain].[ProgramTypeCode], [FamilyAuthVerify].[InvoiceMonth], '
>>' [FamilyMain].[P1MemberName], [FamilyMain].[ProgramType],
>>[FamilyMember].[DevelopDisabled], [FamilyMember].[SpecialNeeds], '
>>' [FamilyAuthVerify].[VerifyComment], [FamilyAuthItem].[ChildMaxStateAmt],
>>[FamilyAuthItem].[SpecialNeedsPct], [FamilyAuthItem].[SpecialNeedsAmt], '
>>' [FamilyAuthItem].[Relative], [ProviderMain].[ProviderName],
>>[ProviderMain].[CCProvTypeCode], [FamilyAuthVerify].[VerifyAuthAmt],
>>[FamilyAuthVerify].[VerifyRegFeeAmt], '
>>' [FamilyAuthVerify].[VerifySupplAmt] '
>>' FROM (((FamilyMain LEFT JOIN FamilyMember ON [FamilyMain].[FamilyID] =
>>[FamilyMember].[FamilyID]) '
>>' LEFT JOIN FamilyAuthVerify ON ([FamilyMember].[FamilyID] =
>>[FamilyAuthVerify].[FamilyID]) AND '
>>' ([FamilyMember].[MemberID] = [FamilyAuthVerify].[ChildMemberID])) LEFT
>>JOIN FamilyAuthItem ON '
>>' [FamilyAuthVerify].[AuthID] = [FamilyAuthItem].[AuthID]) LEFT JOIN
>>ProviderMain ON '
>>' [FamilyAuthVerify].[ProviderID] = [ProviderMain].[ProviderID] '
>>' WHERE ((([FamilyMember].[RelationshipCode])="ch") AND
>>(([FamilyAuthVerify].[InvoiceMonth])=200605) '
>>' AND (([FamilyAuthVerify].[ExtractDate])=20060802) AND
>>(([FamilyAuthItem].[ExtractDate])=20060802) '
>>' AND (([ProviderMain].[ExtractDate])="08/02/2006") AND
>>(([FamilyMember].[ExtractDate])=20060802) '
>>' AND (([FamilyMain].[ExtractDate])=20060802)) '
>>' ORDER BY [FamilyMain].[FamilyID], [FamilyMember].[MemberID];'.
>>
Free forum by Nabble Edit this page