|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| SubString on InvoiceLine table columns doesn't work |
| Author |
Message |
|
|
| Posted : 2007-01-25 14:20:02 |
Following sql returns 10 rows:
select InvoiceLineItemRefFullName
from InvoiceLIne
where TxnDate <= {d '2005-02-18'}
1 Granite:Black Galaxy:3cm:BLGX3-5120-1101
2 Granite:Black Galaxy:3cm:BLGX3-5120-1102
3 Granite:Black Galaxy:3cm:BLGX3-5120-1111
4 Granite:Peacock Green:3cm:PKGR3-4C28-1401
5 Granite:Peacock Green:3cm:PKGR3-4C28-1402
6 Granite:Golden Juprana:3cm:GDJN3-5124-0601
7 Granite:Ubatuba:3cm:UBTB3-5207-1401
8 Granite:Ubatuba:3cm:UBTB3-5207-1402
9 Granite:Florence Yellow:3cm:FLYL3-5208-0501
10 Granite:Florence Yellow:3cm:FLYL3-5208-0502
BUT when I use SubString nothing is returned:
select {fn SubString("InvoiceLineItemRefFullName", 1, 9)}
from InvoiceLIne
where TxnDate <= {d '2005-02-18'}
What am I doing wrong. I am able to successfully use SubString function on "parentreffullname" in ItemInventory table.
Your help is appreciated.
|
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-01-25 21:07:35 |
You are correct, our pass-through to the SQLite backend that QODBC Optimizer uses processes the query incorrectly. I have reported the error. In the meantime you can bypass the QODBC Optimzer in this query by adding the UNOPTIMIZED tag:
select {fn SubString("InvoiceLineItemRefFullName", 1, 9)} as firstnine
from InvoiceLIne unoptimized
where TxnDate <= {d '2005-02-18'} |
|
|
|
|
|
| Posted : 2007-01-26 03:52:54 |
Thanks Tom that solved my problem as I was struggling for long time. I amd getting error when using SubString function in group by clause:
select {fn substring(InvoiceLineItemRefFullName, 9, {fn LOCATE(':', InvoiceLineItemRefFullName, 9)} - 5 )}, Count(*)
from InvoiceLIne UNOPTIMIZED
where InvoiceLineItemRefFullName LIKE 'Granite%'
and InvoiceLineItemRefFullName IS NOT NULL
and TxnDate >= {d '2005-03-16'}
and TxnDate <= {d '2005-03-21'}
group by {fn substring(InvoiceLineItemRefFullName, 9, {fn LOCATE(':', InvoiceLineItemRefFullName, 9)} - 5 )}
What am I doing wrong? If I can't use SubString in group by then it doesn't return the desired result. Please help. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-01-26 12:15:24 |
The group by needs to be a valid column, it can't be the result of a function, sorry:
select {fn substring(InvoiceLineItemRefFullName, 9, {fn LOCATE(':', InvoiceLineItemRefFullName, 9)} - 5 )}, Count(*)
from InvoiceLIne UNOPTIMIZED
where InvoiceLineItemRefFullName LIKE 'Granite%'
and InvoiceLineItemRefFullName IS NOT NULL
and TxnDate >= {d '2005-03-16'}
and TxnDate <= {d '2005-03-21'}
group by InvoiceLineItemRefFullName |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-04-04 09:00:17 |
|
|
|
|