int conversion gives SQL outer join requirement error

[RubenV]

Hi everyone,

this is my code:

SELECT CDDEBITEUR, INVOICENR
FROM DEBINVOICE
WHERE INVOICENR > & lngInvoiceNr &
ORDER BY INVOICENR

InvoiceNr is something like 05003456 or 55763243
(so either it starts with a zero or it doesn't)

Suppose lngInvoiceNr = 05003456
Because I converted it to int, it's 5003456 (without the preceding zero)

When leaving the statement like this, it doesn't show me the invoices after invoiceNr 5003456 but it shows me all invoices where invoiceNr doesn't start with a zero...

So I though I had to convert INVOICENR.
I wrote the following statement:

SELECT CDDEBITEUR, INVOICENR
FROM DEBINVOICE
WHERE TO_NUMBER(INVOICENR) > & lngInvoiceNr &
ORDER BY INVOICENR

However, I get the following error while executing this statement:
Run-time error '-2147217900 (80040e14)':
[MERANT][ODBC SQLBase driver][SQLBase]00979 PRS PSR Plus sign required for outer join

Can someone please help me out?
I'm working with VB 6 on a SQLBase driven Database.

Many thanks for your time.

 

[jebenson]

Try putting some quotes around the part that isn't the variable:

"SELECT CDDEBITEUR, INVOICENR
FROM DEBINVOICE
WHERE TO_NUMBER(INVOICENR) >" & lngInvoiceNr &
"ORDER BY INVOICENR"

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[RubenV]

actually, this is my full vb statement:

strSearchFacturen = "SELECT CDDEBITEUR, FACTNUMMER, FACTDATUM, VERVALDATUM1, VERVALDATUM2, FACTBEDRAG, BEDRAGBTW " & _
"FROM DEBFACTUUR " & _
"WHERE TO_NUMBER(FACTNUMMER) > " & intSearchFrom & " " & _
"ORDER BY FACTNUMMER"

I guess my quotes are placed ok?

Tanks for your reply already.

 

[RubenV]

Normally, I shouldn't convert to NUMBER because even if it's a string, the conversion would happen automatically because the string consists of nothing but numbers...

So, I'm still having the issue with the preceding zero...
I therefore eliminiated the possibility of fetching numbers starting with something else then zero.

Now, the lowest invoice number is 03000001 and the highest is 05009913

Suppose I want to start from 05009875, lngInvoiceNr would be 5009875.
Still, I'm given all invoices.
So actually: from 03000001 till 05009913.

I tried to not to covert to int so that 05009875 would not loose it's preceding zero and use strInvoiceNr instead, but the result is the same...

Hopefully someone can be of assistance.

Regards

 

[ChewDoggie]

Instead of converting the InvoiceNr variable, you may want to try to DECLARE InvoiceNr as a "variant" datatype, rather than a numeric or string datatype.

dim InvoiceNr as Variant

HTH




ciao for niao!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[DrJavaJoe]

It looks to me like you are storing invoice number in the database as a string in which case 03000001 thru 05009913 is all greater then 5009875.


Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[RubenV]

AMACycleLoony: I tried declaring as a Variant, but this had the same result.

DrJavaJoe: What you say is correct indeed. Still, I'm stuck to resolve this...
Because I considered invoice number being a string in the database, I used
WHERE TO_NUMBER(INVOICENR) >" & lngInvoiceNr &

But this gave me the outer join error.
This either means that the structure of my select statement is wrong (but I humbly don't think so) or TO_NUMBER is simply not recognized as an operational function for SQLBase.

It's just so frustrating that something bully like this can jam the advancement on our project. The deadline was for yesterday which would have been manageable without this issue (too bad I don't make up the deadlines...).

Thanks a lot to everyone for your responses already.
I hope someone might be able to guide me further on how to resolve this.

Cheers mates.

 

[RubenV]

I have found the solution.

It's a rather dumb error I made but for some reason I managed not to see it.
Guess I was just focussing on the wrong thing.

Anyways, this works great and it's just two strings compared with eachother:

"SELECT CDDEBITEUR, FACTNUMMER, FACTDATUM, VERVALDATUM1, VERVALDATUM2, FACTBEDRAG, BEDRAGBTW " & _
"FROM DEBFACTUUR " & _
"WHERE (CDDAGBOEK = 'VF' OR CDDAGBOEK = 'VCN') " & _
"AND FACTNUMMER >= '" & strSearchFrom & "' " & _
"ORDER BY FACTNUMMER"

The solution was brought by the brackets around the first prerequisite (WHERE .....)
and strSearchFrom still contains the preceding zero.

Thanks a lot to everyone,
Ruben

 

[jadoogar]

What about Cast(variable name as integer) in query

 

[RubenV]

This gives me the same error as described above.
It's not about the join though, it only means my query contains an error.

So basically, the driver I use to access the SQLBase database is just too limited to execute conversions etc... in a query.