Board index » delphi » Interbase equivalent to UNION clause

JEchavez

Delphi Developer
Tue, 25 May 2004 13:07:05 GMT

Interbase equivalent to UNION clause

How do make a SQL statement using UNION (as it is in SQL server) clause in
Interbase?
With the following lines of code, I get an error message saying "data type
unknown".

select 'HighestDate' , LastTradeDte from Table1
where IssueID = 123 and
extract(year from LastTradeDte)=2000 and
Highest in (select max(Highest) from Table1
         where IssueID = 509 and extract(year from LastTradeDte)= 2000)
union
select 'LowestDate' , LastTradeDte from Table1
where IssueID = 123 and
extract(year from LastTradeDte)=2000 and
Lowest in (select min(Lowest) from Table1
         where IssueID = 509 and extract(year from LastTradeDte) = 2000)

Please help!

JEchavez

 
JEchave

Delphi Developer
Tue, 25 May 2004 13:56:17 GMT

Re:Interbase equivalent to UNION clause


Oh I got it! The problem is in the 'HighestDate' and 'LowestDate' strings!
If I exclude them, the query runs well....sad thing is, I don't know how to
put the
'HighestDate' and 'LowestDate'  texts as part of the result of the SQL
statement.

Quote
"JEchavez" <jecha...@idsfinance.com> wrote in message

news:3c104e20_2@dnews...
Quote
> How do make a SQL statement using UNION (as it is in SQL server) clause in
> Interbase?
> With the following lines of code, I get an error message saying "data type
> unknown".

> select 'HighestDate' , LastTradeDte from Table1
> where IssueID = 123 and
> extract(year from LastTradeDte)=2000 and
> Highest in (select max(Highest) from Table1
>          where IssueID = 509 and extract(year from LastTradeDte)= 2000)
> union
> select 'LowestDate' , LastTradeDte from Table1
> where IssueID = 123 and
> extract(year from LastTradeDte)=2000 and
> Lowest in (select min(Lowest) from Table1
>          where IssueID = 509 and extract(year from LastTradeDte) = 2000)

> Please help!

> JEchavez

Kade

Delphi Developer
Tue, 25 May 2004 14:19:14 GMT

Re:Interbase equivalent to UNION clause


 Please Try this:

Quote
> > select 'HighestDate' , LastTradeDte from Table1

      select 'HighestDate'   as fieldA, LastTradeDte from Table1

Quote
> > where IssueID = 123 and
> > extract(year from LastTradeDte)=2000 and
> > Highest in (select max(Highest) from Table1
> >          where IssueID = 509 and extract(year from LastTradeDte)= 2000)
> > union
> > select 'LowestDate' , LastTradeDte from Table1

     select 'LowestDate'  as fieldA, LastTradeDte from Table1

Quote
> > where IssueID = 123 and
> > extract(year from LastTradeDte)=2000 and
> > Lowest in (select min(Lowest) from Table1
> >          where IssueID = 509 and extract(year from LastTradeDte) = 2000)

Kadee
Arno Brinkman [BISIT

Delphi Developer
Tue, 25 May 2004 16:56:36 GMT

Re:Interbase equivalent to UNION clause


Cast names to same type / length !

for example :

 SELECT
  CAST("LowestDate" As varChar(15)) As "Date",
  LastTradeDte
 FROM
  Table1
 WHERE IssueID = 509
UNION
 SELECT
  CAST("HighestDate" As varChar(15)),
  LastTradeDte
 FROM
  Table1
 WHERE IssueID = 509

Greetz,

--
Arno Brinkman
BISIT engineering b.v.
Netherlands

"JEchavez" <jecha...@idsfinance.com> schreef in bericht
news:3c1059af_2@dnews...

Quote
> Oh I got it! The problem is in the 'HighestDate' and 'LowestDate' strings!
> If I exclude them, the query runs well....sad thing is, I don't know how
to
> put the
> 'HighestDate' and 'LowestDate'  texts as part of the result of the SQL
> statement.

> "JEchavez" <jecha...@idsfinance.com> wrote in message
> news:3c104e20_2@dnews...
> > How do make a SQL statement using UNION (as it is in SQL server) clause
in
> > Interbase?
> > With the following lines of code, I get an error message saying "data
type
> > unknown".

> > select 'HighestDate' , LastTradeDte from Table1
> > where IssueID = 123 and
> > extract(year from LastTradeDte)=2000 and
> > Highest in (select max(Highest) from Table1
> >          where IssueID = 509 and extract(year from LastTradeDte)= 2000)
> > union
> > select 'LowestDate' , LastTradeDte from Table1
> > where IssueID = 123 and
> > extract(year from LastTradeDte)=2000 and
> > Lowest in (select min(Lowest) from Table1
> >          where IssueID = 509 and extract(year from LastTradeDte) = 2000)

> > Please help!

> > JEchavez

JEchave

Delphi Developer
Fri, 28 May 2004 13:26:20 GMT

Re:Interbase equivalent to UNION clause


Indeed, that did it right! Thanks!

JEchavez

"Arno Brinkman [BISIT]" <a...@bisit.nl> wrote in message
news:3c10843e$1_2@dnews...

Quote
> Cast names to same type / length !

> for example :

>  SELECT
>   CAST("LowestDate" As varChar(15)) As "Date",
>   LastTradeDte
>  FROM
>   Table1
>  WHERE IssueID = 509
> UNION
>  SELECT
>   CAST("HighestDate" As varChar(15)),
>   LastTradeDte
>  FROM
>   Table1
>  WHERE IssueID = 509

> Greetz,

> --
> Arno Brinkman
> BISIT engineering b.v.
> Netherlands

> "JEchavez" <jecha...@idsfinance.com> schreef in bericht
> news:3c1059af_2@dnews...
> > Oh I got it! The problem is in the 'HighestDate' and 'LowestDate'
strings!
> > If I exclude them, the query runs well....sad thing is, I don't know how
> to
> > put the
> > 'HighestDate' and 'LowestDate'  texts as part of the result of the SQL
> > statement.

> > "JEchavez" <jecha...@idsfinance.com> wrote in message
> > news:3c104e20_2@dnews...
> > > How do make a SQL statement using UNION (as it is in SQL server)
clause
> in
> > > Interbase?
> > > With the following lines of code, I get an error message saying "data
> type
> > > unknown".

> > > select 'HighestDate' , LastTradeDte from Table1
> > > where IssueID = 123 and
> > > extract(year from LastTradeDte)=2000 and
> > > Highest in (select max(Highest) from Table1
> > >          where IssueID = 509 and extract(year from LastTradeDte)=
2000)
> > > union
> > > select 'LowestDate' , LastTradeDte from Table1
> > > where IssueID = 123 and
> > > extract(year from LastTradeDte)=2000 and
> > > Lowest in (select min(Lowest) from Table1
> > >          where IssueID = 509 and extract(year from LastTradeDte) =
2000)

> > > Please help!

> > > JEchavez

Other Threads

1. Parameterised queries and UNION clause

2. Memo fields in select clause of union queries

3. using UNION and ORDER BY clauses

4. The clause UNION

5. UNION clause in SQL select

6. SQL Union clause (Local SQL)

7. Union clause with paradox tables

8. Possible BUG in LOCAL SQL UNION clause

9. SQL: UNION in SELECT clause

10. UNION clause