Date:         Tue, 26 Sep 2006 08:47:53 +0300
Reply-To:     Martins Liberts <martins.liberts@gmail.com>
Sender:       "SPSSX(r) Discussion" <SPSSX-L@LISTSERV.UGA.EDU>
From:         Martins Liberts <martins.liberts@gmail.com>
Subject:      Re: SQL
Comments: To: Sean McKenzie <mckenzie_sean@hotmail.com>
In-Reply-To:  <BAY119-F1466315AE0085B729AA1EB91240@phx.gbl>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

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 <mckenzie_sean@hotmail.com> 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
Martins.Liberts@gmail.com