Board index » delphi » Interbase equivalent to UNION clause

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

 

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

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

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

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