generating SQL statement gives error 3

[Koen Piller]

Hi,

I have this routine (to find duplicates in a table)

Select tblImporttst.*, Duplicates.nCount ;
	FROM  tblImporttst ;
	INNER JOIN ( Select Veld1, Veld2, COUNT(*) As nCount GROUP By Veld1, Veld2 ;
	FROM tblImporttst ;
	HAVING Count(*) > 1 ;
	) As Duplicates On Duplicates.Veld1 = tblImporttst.Veld1 ;
	AND Duplicates.Veld2 = tblImporttst.Veld2

it works fine.

Now chanching it into a more generic coding like :

text to Selecting
Select ;
	varcTbl.*, Duplicates.nCount ;
	FROM  varcTbl ;
		INNER JOIN ( Select varcFld1, varcFld2, COUNT(*) As nCount GROUP By varcFld1, varcFld2 ;
		FROM varcTbl ;
		HAVING Count(*) > 1 ;
		) As Duplicates On Duplicates.varcFld1 = varcTbl.varcFld1 ;
		AND Duplicates.varcFld2 = varcTbl.varcFld2 
	EndText 

	clea
Selecting=Strtran(Selecting,'varcFld1','VELD1')
Selecting=Strtran(Selecting,'varcFld2','VELD2')
Selecting=Strtran(Selecting,'varcTbl','tblImporttst')
Selecting=Strtran(Selecting,';',' ')

&Selecting

Browse normal

it will result in an error (17). What could possible be the matter?

Thanks in advance

Jockey(2)

 

[Olaf Doschke]

You are simply executing "SELECT "+CHR(13)+CHR(10), the rest is ignored. And that results in that error 17.

Either you leave the semicolons where they are or you replace ";"+chr(13)+chr(10) with " ", not ";" alone.

Bye, Olaf.

 

[Koen Piller]

Olaf,

I have deleted the line

Selecting=Strtran(Selecting,';',' ')

as you suggested, no difference still error 17 is making me miserable.

( btw, I forget to mention in my 1st message, that simply clicking on "Ignore" in the messagebox will give you the results. But I can hardly ask my endusers to click on ignore when on error)

Jockey(2)

 

[Stella740pl]

I may be missing something here, but I am puzzled by "text to Selecting" syntax. Is it supposed to be part of STORE _TEXT TO Selecting ? Or TEXT ... ENDTEXT should be there somwhere? Or something else?

 

[Koen Piller]

Stella,

Yes I am trying to make an TEXT ... ENDTEXT
for good understanding I have rearranged my coding, hope it is clear now

[COLOR=BLUE]TEXT TO[/COLOR] Selecting
Select ;
    varcTbl.*, Duplicates.nCount ;
    FROM  varcTbl ;
        INNER JOIN ( Select varcFld1, varcFld2, COUNT(*) As nCount GROUP By varcFld1, varcFld2 ;
        FROM varcTbl ;
        HAVING Count(*) > 1 ;
        ) As Duplicates On Duplicates.varcFld1 = varcTbl.varcFld1 ;
        AND Duplicates.varcFld2 = varcTbl.varcFld2
[COLOR=BLUE]ENDTEXT

clea
Selecting=Strtran(Selecting,'varcFld1','VELD1')
Selecting=Strtran(Selecting,'varcFld2','VELD2')
Selecting=Strtran(Selecting,'varcTbl','tblImporttst')
Selecting=Strtran(Selecting,';',' ')

&Selecting

Browse normal[/COLOR]

As mentionded before, the coding works, it only gives an irksome error.
Jockey(2)

 

[Mike Lewis]

Jockey2,

It is not enough to delete the semi-colons. You also need to delete the CHR(10)s and CHR(13)s. Remember, you are generating some text which you are going to macro-execute. The macro engine needs to see a single-line command.

I suggest you remove the semi-colons from the TEXT / ENDTEXT construct, and add PRETEXT 8 after TEXT TO Selecting. That will put the entire command on a single line.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Stella740pl]

I see.

I still use VFP76 where TEXT...ENDTEXT doesn't have the TO clause.

I usually do it the other way around, something like this:

varcFld1='VELD1'
varcFld2='VELD2'
varcTbl='tblImporttst'

Select &varcTbl..*, Duplicates.nCount ;
    FROM  &varcTbl ;
    INNER JOIN (Select &varcFld1, &varcFld2, COUNT(*) As nCount ; 
        GROUP By &varcFld1, &varcFld2 ;
        FROM &varcTbl ;
        HAVING Count(*) > 1) AS Duplicates ;
    ON Duplicates.&varcFld1. = &varcTbl..&varcFld1 ;
        AND Duplicates.&varcFld2 = &varcTbl..&varcFld2

 

[bborissov]

VFP76 WOW Thanks God, VFP lives

LOCAL lcFld1, lcFld2, lcTabl
lcFld1 = [Veld1]
lcFld2 = [Veld2]
lcTabl = [tblImporttst]
TEXT TO Selecting NOSHOW TEXTMERGE
     Select <<lcTabl>>.*, Duplicates.nCount
     FROM  <<lcTabl>>
     INNER JOIN (Select <<lcFld1>>,<<lcFld2>>, COUNT(*) As nCount
                 GROUP By varcFld1, varcFld2
                 FROM <<lcTabl>>
                 HAVING Count(*) > 1
        ) As Duplicates On Duplicates.<<lcFld1>> = <<lcTabl>>.<<lcFld1>>
        AND Duplicates.<<lcFld2>> = <<lcTabl>>.<<lcFld2>>
ENDTEXT
Selecting=Strtran(Selecting,CHR(13)+CHR(10),' ')
MessageBox(Selecting)
&Selecting
Browse normal

Borislav Borissov

 

[Stella740pl]

:-D
That would be VFP6, since VFP7 already has TO clause in TEXT...ENDTEXT, as I found on MSDN site.

 

[Koen Piller]

Boris, Stella, Mike,

Thanks a lot
Jockey2
P.S.

 

[bborissov]

All versions of fox has TO, not all has PRETEXT

Borislav Borissov

 

[Stella740pl]

Borislav,

Not really. And if it is there, it's not documented. This is from my help file:

[b]Syntax[/b]

TEXT
  [i]TextLines[/i]

ENDTEXT

Jockey2,
Thanks for the star. Does it work now?

 

[Stella740pl]

Oh, and to add.

In VFP6, SET TEXTMERGE is available only as a separate command that you've got to use along with TEXT...ENDTEXT, and _PRETEXT is there as a system variable.

SET TEXTMERGE
  [ON | OFF]
  [TO [FileName] [ADDITIVE]]
  [WINDOW WindowName]
  [SHOW | NOSHOW]

 

[Olaf Doschke]

Hmm,

I would have bet macro substitution also works with CHR(13) and CHR(10) *and* semicolons and only macrosubstituting more than one command does not work.

Okay, so you had to delete both semicolon and line break characters to make it work. I should have known.

Bye, Olaf.

 

[Koen Piller]

Hi Stella,

Yes it works, thanks to your, Boras' and Mike's suggestions.

[COLOR=blue]Text to [/color]Selecting NOSHOW textmerge
	Select  varcTbl.*, Duplicates.nCount 
	[COLOR=blue]FROM [/color] varcTbl 
	[COLOR=blue]INNER JOIN ( Select [/color] varcFld1  ,  varcFld2  , COUNT(*) [COLOR=blue]As [/color]nCount [COLOR=blue]GROUP By  [/color]varcFld1  ,  varcFld2   
	[COLOR=blue]FROM[/color]  varcTbl   
	[COLOR=blue]HAVING [/color]Count(*) > 1 
	[COLOR=blue]) As Duplicates On [/color]Duplicates. varcFld1   =  varcTbl.varcFld1   
	[COLOR=blue]AND [/color]Duplicates. varcFld2   =  varcTbl.varcFld2  
	[COLOR=blue]into cursor [/color]temp 
[COLOR=blue]EndText 

Selecting=Strtran([/color]Selecting,'varcFld1','VELD1'[COLOR=blue]) Selecting=Strtran([/color]Selecting,'varcFld2','VELD2'[COLOR=blue]) Selecting=Strtran([/color]Selecting,'varcTbl','tblImporttst'[COLOR=blue])
Selecting=Strtran([/color]Selecting,chr(13)+chr(10),' '[COLOR=blue])
&Selecting
[/color]

Jockey(2)

 

[Stella740pl]

I know.

But it's more than a habit or a preference now - it's also having to either be consistent or to go back and make changes in many, many places, then test it all. I don't think so.

Maybe in some totally new project I will drop all the bad habits at once - but they don't happen every day.

 

[Mike Lewis]

By the way, if you want to use TEXT / ENDTEXT to generate a SELECT for sending to SQL Server (via SQLEXEC()), there's no need to remove the CRLFs. SQL Server is happy with line ending and other white space in the middle of a command. I don't know if that's true of other back ends.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk