sql syntax in M:M

[Nifrabar]

Hi!
I have a rowsource for a listbox as hereunder.
The construct is to get a cursor for printing labels and printing a list holding that data.
cp.Etiket (red in code) is a logical field acting as flag wether or not to print labels.
WITHOUT the red part the construct is OK but with the red part an error occurs.
I don't know why.
Is this probably for I pass it after subqueries?
I don't believe for !ISNULL(cp.id) should give error than as well though?
Any suggestions?
-Bart

lcBoxNaam	='allt(cP.Achternaam) +", " + allt(cP.lstV_naam) + " " + allt(cP.TussenNaam) '
lcBoxAdres	='cP.straat'
lcBoxPlaats ='proper(cP.plaats)'

Regel1	='cP.aanhef'
Regel2	='allt(cP.lblV_naam) + " " + allt(cP.TussenNaam) + " " + allt(cP.Achternaam) '
Regel3	='cP.straat'
Regel4	='allt(cP.Postcode) + " " + allt(cP.Plaats) '

lstPostCode = 'allt(cP.Postcode)'
lstPlaats   = 'allt(cP.Plaats)'

	.lstAdres.rowsource=;	
	'select distinct				 '	+	;
	lcBoxNaam 	+ ' as boxNaam  	,'	+	;
	lcBoxAdres	+ ' as boxAdres 	,'	+	;
	lcBoxPlaats + ' as boxplaats	,'	+	;
	'cp.ID							,'	+ 	;
	lstPostcode	+ ' as lijstPostcode,'	+	;
	lstPlaats	+ ' as lijstPlaats	,'	+	;
	Regel1 		+ ' as regel1 		,'	+	;
	Regel2 		+ ' as regel2 		,'	+	;
	Regel3 		+ ' as regel3 		,'	+	;
	Regel4 		+ ' as regel4  			;
	from crsPersoon as cP;
	right outer join t_pers_func as MM on MM.pers_ID = cP.ID ;
	where MM.wrkgrp_ID in (select * from crsGroepen)   and not MM.pers_ID in (select pers_id from (.ctMailEx)) and !ISNULL(cp.id) [COLOR=red] and cp.Etiket[/color];
	into cursor crsNAW;
	order by 1'

 

[Mike Lewis]

Bart,

Most important question: What error are you seeing?

Is this probably for I pass it after subqueries?


I don't think the position of the cp.Etiket test within the WHERE clause will make any differenc, but why don't you check by placing it before the sub-queries, just to see what happens.

I don't believe for !ISNULL(cp.id) should give error than as well though?

Again, you could try taking that, to see if it makes a difference.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[terosoft]

Try this:

  right outer join t_pers_func as MM on MM.pers_ID = cP.ID ;
    where MM.wrkgrp_ID in (select * from crsGroepen)   and not MM.pers_ID in (select pers_id from (.ctMailEx)) and !ISNULL(cp.id) and [COLOR=#ff0000]cp.Etiket ;[/color]
    into cursor crsNAW;
    order by 1'

Note that I added a space between Etiket and the semicolon.

Gerardo Czajkowski

 

[KoenPiller]

Bart,

is this a typo? What is the 1st['] doing in line

'cp.ID ,' + ;


Koen

 

[Nifrabar]

Mike:
The displayed error:
Command contains unrecognized phrase/keyword.
I aslo tried putting the directly after the 'where' clause but same error.

Gerardo:
No the space does not make any difference

Koen:
it's not a typo. ['] is doing same as 1st ['] in
lcBoxNaam + ' as boxNaam ,' + ;


The strange thing is that even when I replace [COLOR=red ]and cp.Etiket [/ color] with .T. or 1=1 same error occurs. By the way, the errormessage happens once I try to close/save the form in which the code is written.

-Bart

 

[Olaf Doschke]

Hi Nifrabar,

you can't continue a string this way:

lcString = "Hello ;
World"

Instead you have to close one part in one line and open another string part at the next line:

lcString = "Hello "+;
   "World"

Now take a look at your code again...

Also take a look at what Textmerge() and/or TEXT...ENDTEXT offer to you.

Bye, Olaf.

 

[Nifrabar]

Hi Olaf,
Thanks for your reply but my string is correct. Please see my original code at the top of this threat.
Meanwhile the coode works once I leave out the red-colored part and as you can see no quotes(') do effect that.
-Bart

 

[Olaf Doschke]

Hi Nifrabar,

I was indeed referring to your original code at the top of this thread. Maybe in the original code you use the part after Regel4+ is all in one line, but here you posted several lines wrongly seperated.

Maybe you exceeded the maximum length the .Rowsource can hold/interprete/execute?

Bye, Olaf.

 

[Nifrabar]

Hi Olaf,
Possibly you are watching the above code in a narrow window?
I am using a wide-screen and so I don't see any typos which might result in e.g.:

"myFirst ;
+ mySecond"

"myFirst ; mySecond" ?

Your suggestion about max. digits in the rowsource-string might be true.
I will investigate that.

-Bart

 

[Mike Lewis]

Your suggestion about max. digits in the rowsource-string might be true.

I think Olaf has hit the solution. Aren't string properties limited to 254 (or 255?) characters? If so, that would explain why the last bit of the SELECT fails.

Bart, you can verify that by changing the position of "and cp.Etiket" withing the string.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[Olaf Doschke]

Well, there are many spaces, which could be deleted...

But again, if you have a string in one line that has ; in it, this will throw an error!

works:

.rowsource = "select * from mytable into mycursor"

doesn't work:

.rowsource = "select * from mytable ; into mycursor"

It would perhaps work if you put CHR(13)+CHR(10) within the string after the semicolon, but that'll be a waste of unneded characters again.

Bye, Olaf.

 

[Nifrabar]

Hi Mike and Olaf,

I got it!
You pointed me to the right direction. The string was to long.
Knowing that I can look for a way to create a work around.
Pity that VFP didnot told me that the stringlength was the problem.

-Bart