SQL error in Firebird 2.0

Dev Shed Forums Sponsor:

October 11th, 2007, 04:58 AM

mrtyler81

Registered User

Join Date: Oct 2007

Posts: 9

Time spent in forums: 2 h 3 m 10 sec
Reputation Power: 0

SQL error in Firebird 2.0

hi everybody

i use a software for business intelligence
this software has a warehouse built on a database
but i want to use firebird for datawarehouse

now this software i use needs some records
for expalme this query:

SELECT
concat(concat(WHTBLME.WHTBLME_CODICE,'§§'),WHTBLME.WHTBLME_DESCRIZIONE) AS D_001020_COD_TAB_ME,
concat(concat(WHTBLME.WHTBLME_DESCRIZIONE,'§§'),WHTBLME.WHTBLME_CODICE) AS K_D_001020_COD_TAB_ME,
concat(concat(WHART.WHART_CODICE,'§§'),WHART.WHART_DESCRIZIONE) AS D_001010_COD_ART,
concat(concat(WHART.WHART_DESCRIZIONE,'§§'),WHART.WHART_CODICE) AS K_D_001010_COD_ART,
sum( WHFMFAT.FMFAT_IMP_EUR ) AS M_000_001_VAL_FAT_EUR,
sum(0.0000000000) AS M_001_001_VAL_FAT_EUR
FROM
WHTBLME WHTBLME,
WHFMFAT WHFMFAT,
WHART WHART
WHERE
WHTBLME.WHTBLME_CODICE=WHFMFAT.FMFAT_TAB_ME AND WHTBLME.WHTBLME_DIT=WHFMFAT.FMFAT_DIT AND
cast(WHFMFAT.FMFAT_ART as char(15)) =WHART.WHART_CODICE AND WHART.WHART_DIT=cast(WHFMFAT.FMFAT_DIT as char(3)) AND
(WHFMFAT.FMFAT_DATE between {d '2006-01-01'} AND {d '2006-04-30'})
GROUP BY
WHTBLME.WHTBLME_CODICE,
WHTBLME.WHTBLME_DESCRIZIONE,
WHART.WHART_CODICE,
WHART.WHART_DESCRIZIONE

UNION ALL

SELECT
concat(concat(WHTBLME.WHTBLME_CODICE,'§§'),WHTBLME.WHTBLME_DESCRIZIONE) AS D_001020_COD_TAB_ME,
concat(concat(WHTBLME.WHTBLME_DESCRIZIONE,'§§'),WHTBLME.WHTBLME_CODICE) AS K_D_001020_COD_TAB_ME,
concat(concat(WHART.WHART_CODICE,'§§'),WHART.WHART_DESCRIZIONE) AS D_001010_COD_ART,
concat(concat(WHART.WHART_DESCRIZIONE,'§§'),WHART.WHART_CODICE) AS K_D_001010_COD_ART,
sum(0.0000000000) AS M_000_001_VAL_FAT_EUR,
sum( WHFMFAT.FMFAT_IMP_EUR ) AS M_001_001_VAL_FAT_EUR
FROM
WHTBLME WHTBLME,
WHFMFAT WHFMFAT,
WHART WHART
WHERE
WHTBLME.WHTBLME_CODICE=WHFMFAT.FMFAT_TAB_ME AND WHTBLME.WHTBLME_DIT=WHFMFAT.FMFAT_DIT AND
cast(WHFMFAT.FMFAT_ART as char(15)) =WHART.WHART_CODICE AND WHART.WHART_DIT=cast(WHFMFAT.FMFAT_DIT as char(3)) AND
(WHFMFAT.FMFAT_DATE between {d '2007-01-01'} AND {d '2007-04-30'})
GROUP BY
WHTBLME.WHTBLME_CODICE,
WHTBLME.WHTBLME_DESCRIZIONE,
WHART.WHART_CODICE,
WHART.WHART_DESCRIZIONE

the software didnt work because an error occurred

i pasted the query into Interbase & Firebird Sql Manager

and returns me this error

fmIBSQLScript.DataSet.SelectQuery:
Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 15, column 37.
'2006-01-01'.

i tried the same query with another date format, but doesnt work

by the way, the software successfull connect to the firebirdDB, indeed he let me explore the table records

please any suggest or help?

thanks a lot
Tony

October 11th, 2007, 05:23 AM

pabloj

Modding: Oracle MsSQL Firebird

Dev Shed God 7th Plane (8000 - 8499 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 8,066

Time spent in forums: 2 Months 4 Weeks 1 h 56 m 57 sec
Reputation Power: 281

First of all, use proper code formatting with or without syntax highlingting, then you have invalid chars in your query

sql Code:

Original - sql Code

SELECT
CONCAT(CONCAT(WHTBLME.WHTBLME_CODICE,'§§'),WHTBLME.WHTBLME_DESCRIZIONE) AS D_001020_COD_TAB_ME,
CONCAT(CONCAT(WHTBLME.WHTBLME_DESCRIZIONE,'§§'),WHTBLME.WHTBLME_CODICE) AS K_D_001020_COD_TAB_ME,
CONCAT(CONCAT(WHART.WHART_CODICE,'§§'),WHART.WHART_DESCRIZIONE) AS D_001010_COD_ART,
CONCAT(CONCAT(WHART.WHART_DESCRIZIONE,'§§'),WHART.WHART_CODICE) AS K_D_001010_COD_ART,
SUM( WHFMFAT.FMFAT_IMP_EUR ) AS M_000_001_VAL_FAT_EUR,
SUM(0.0000000000) AS M_001_001_VAL_FAT_EUR
FROM
WHTBLME WHTBLME,
WHFMFAT WHFMFAT,
WHART WHART
WHERE
WHTBLME.WHTBLME_CODICE=WHFMFAT.FMFAT_TAB_ME AND WHTBLME.WHTBLME_DIT=WHFMFAT.FMFAT_DIT AND
CAST(WHFMFAT.FMFAT_ART AS CHAR(15)) =WHART.WHART_CODICE AND WHART.WHART_DIT=CAST(WHFMFAT.FMFAT_DIT AS CHAR(3)) AND
(WHFMFAT.FMFAT_DATE BETWEEN {d '2006-01-01'} AND {d '2006-04-30'})
GROUP BY
WHTBLME.WHTBLME_CODICE,
WHTBLME.WHTBLME_DESCRIZIONE,
WHART.WHART_CODICE,
WHART.WHART_DESCRIZIONE
 
UNION ALL
 
SELECT
CONCAT(CONCAT(WHTBLME.WHTBLME_CODICE,'§§'),WHTBLME.WHTBLME_DESCRIZIONE) AS D_001020_COD_TAB_ME,
CONCAT(CONCAT(WHTBLME.WHTBLME_DESCRIZIONE,'§§'),WHTBLME.WHTBLME_CODICE) AS K_D_001020_COD_TAB_ME,
CONCAT(CONCAT(WHART.WHART_CODICE,'§§'),WHART.WHART_DESCRIZIONE) AS D_001010_COD_ART,
CONCAT(CONCAT(WHART.WHART_DESCRIZIONE,'§§'),WHART.WHART_CODICE) AS K_D_001010_COD_ART,
SUM(0.0000000000) AS M_000_001_VAL_FAT_EUR,
SUM( WHFMFAT.FMFAT_IMP_EUR ) AS M_001_001_VAL_FAT_EUR
FROM
WHTBLME WHTBLME,
WHFMFAT WHFMFAT,
WHART WHART
WHERE
WHTBLME.WHTBLME_CODICE=WHFMFAT.FMFAT_TAB_ME AND WHTBLME.WHTBLME_DIT=WHFMFAT.FMFAT_DIT AND
CAST(WHFMFAT.FMFAT_ART AS CHAR(15)) =WHART.WHART_CODICE AND WHART.WHART_DIT=CAST(WHFMFAT.FMFAT_DIT AS CHAR(3)) AND
(WHFMFAT.FMFAT_DATE BETWEEN {d '2007-01-01'} AND {d '2007-04-30'})
GROUP BY
WHTBLME.WHTBLME_CODICE,
WHTBLME.WHTBLME_DESCRIZIONE,
WHART.WHART_CODICE,
WHART.WHART_DESCRIZIONE

Those are {d and the closing one, that's not the way to write the between

__________________
My blog
Tutorials about OSS databases, DBMonster ...
Contribute to OSS Development, fill bug reports!
Developer Shed eSupport
Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK)
An introduction to database normalization
Natural or Surrogate key
Custom ordering for your results
Correlated and uncorrelated subqueries
Don't turn your outer joins into inner joins
Random data (with a bias)

October 11th, 2007, 05:57 AM

mrtyler81

Registered User

Join Date: Oct 2007

Posts: 9

Time spent in forums: 2 h 3 m 10 sec
Reputation Power: 0

thanks but...

now seems that there is another problem

fmIBSQLScript.DataSet.SelectQuery:
An error was found in the application program input parameters for the SQL statement. Dynamic SQL Error.
SQL error code = .804.
Function unknown.
CONCAT.

is there any list of all SQL commands that are not supported by Firebird 2.0?

thanks again buddies

October 11th, 2007, 06:38 AM

mrtyler81

Registered User

Join Date: Oct 2007

Posts: 9

Time spent in forums: 2 h 3 m 10 sec
Reputation Power: 0

oops...

sorry

i'm really a noob

i made the correction

i used

(WHTBLME.WHTBLME_CODICE+'§§'+WHTBLME.WHTBLME_DESCRIZIONE) AS D_001020_COD_TAB_ME,
(WHTBLME.WHTBLME_DESCRIZIONE+'§§'+WHTBLME.WHTBLME_CODICE) AS K_D_001020_COD_TAB_ME,
(WHART.WHART_CODICE+'§§'+WHART.WHART_DESCRIZIONE) AS D_001010_COD_ART,
(WHART.WHART_DESCRIZIONE+'§§'+WHART.WHART_CODICE) AS K_D_001010_COD_ART,

instead of the CONCAT command

but anyway... now there is another error...

is it correct to use +???

thanks again

October 11th, 2007, 06:54 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 8,066

Time spent in forums: 2 Months 4 Weeks 1 h 56 m 57 sec
Reputation Power: 281

Would you mind telling us from which database are you porting this query to Firebird?
Note that the SQL standard concatenation operator is ||

October 11th, 2007, 07:21 AM

mrtyler81

Registered User

Join Date: Oct 2007

Posts: 9

Time spent in forums: 2 h 3 m 10 sec
Reputation Power: 0

i cannot use +???

October 11th, 2007, 07:23 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 8,066

Time spent in forums: 2 Months 4 Weeks 1 h 56 m 57 sec
Reputation Power: 281

Did you answer my questions?

October 11th, 2007, 07:51 AM

mrtyler81

Registered User

Join Date: Oct 2007

Posts: 9

Time spent in forums: 2 h 3 m 10 sec
Reputation Power: 0

sorry, i forgot to answer

it's a software that generates SQL queries

this queries are supported by DBmaker

October 11th, 2007, 08:47 AM

pabloj

Modding: Oracle MsSQL Firebird

Join Date: Jun 2001

Location: Outside US

Posts: 8,066

Time spent in forums: 2 Months 4 Weeks 1 h 56 m 57 sec
Reputation Power: 281

Quote:

Originally Posted by mrtyler81

i cannot use +???

No, use the standard concatenation operator.

#10

October 11th, 2007, 11:43 AM

mrtyler81

Registered User

Join Date: Oct 2007

Posts: 9

Time spent in forums: 2 h 3 m 10 sec
Reputation Power: 0

really thanks a lot

i solved my problem

thaaanks

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: