Board index » delphi » how to convert this SQL from MS-SQL server to Interbase

how to convert this SQL from MS-SQL server to Interbase

    Any SQL expert can help?

Select Replace(Cast(Cast(Str(Day(Abs.dtup), 2) As char(2)) +
Cast(Str(Month(Abs.dtup), 2) As char(2)) + Cast(Year(Abs.dtup) As char(4))
As char(8)), '' '', ''0''), username

From Abs, Tz0Usr
Where Abs.usucod = Tz0Usr.usucod

Group By Replace(Cast(Cast(Str(Day(Abs.dtup), 2) As char(2)) +
        Cast(Str(Month(Abs.dtup), 2) As char(2)) +
        Cast(Year(Abs.dtup) As char(4)) As char(8)), '' '', ''0''),
Tz0usr.username

Order By Replace(Cast(Cast(Str(Day(Abs.dtup), 2) As char(2)) +
        Cast(Str(Month(Abs.dtup), 2) As char(2)) +
         Cast(Year(Abs.dtup) As char(4)) As char(8)), '' '', ''0'')

    How to convert this dateformat from interbase, i have success to
concat date, like (Extract(Day From Abs.dtup) || Extract(Month From
Abs.dtup) || Extract(Year From Abs.dtup)), but
this result in 112001, and i need 01012001.

    All help will be very appreciated!

    Thanx all

 

Re:how to convert this SQL from MS-SQL server to Interbase


Quote
"Cristian" <crist...@ivirtua.com.br> wrote in message

news:3c14dcce_1@dnews...

Quote

>     Any SQL expert can help?

> Select Replace(Cast(Cast(Str(Day(Abs.dtup), 2) As char(2)) +
> Cast(Str(Month(Abs.dtup), 2) As char(2)) + Cast(Year(Abs.dtup) As char(4))
> As char(8)), '' '', ''0''), username

> From Abs, Tz0Usr
> Where Abs.usucod = Tz0Usr.usucod

> Group By Replace(Cast(Cast(Str(Day(Abs.dtup), 2) As char(2)) +
>         Cast(Str(Month(Abs.dtup), 2) As char(2)) +
>         Cast(Year(Abs.dtup) As char(4)) As char(8)), '' '', ''0''),
> Tz0usr.username

> Order By Replace(Cast(Cast(Str(Day(Abs.dtup), 2) As char(2)) +
>         Cast(Str(Month(Abs.dtup), 2) As char(2)) +
>          Cast(Year(Abs.dtup) As char(4)) As char(8)), '' '', ''0'')

Cristian, are you aware that this is an abuse of MsSql power? Unless MsSql
has enough IQ to understand duplicate expressions, you are forcing it to
execute three times the same construction. I think that your sort and
grouping is very bizarre. Are you sure that you want to sort first by day,
then by month and then by year? Your string creates
day month year

For me, this is
select abs.dtup, Tz0usr.username
from Abs JOIN Tz0Usr
ON Abs.usucod = Tz0Usr.usucod
order by abs.dtup

Get used to the SQL standard JOIN syntax instead of the WHERE condition to
join two tables. Even MS discourages the old syntax.
I don't see the point in your GROUP BY. There's no summary data. Are you
trying to remove duplicates? In this case, you only need DISTINCT:

select DISTINCT abs.dtup, Tz0usr.username
from Abs JOIN Tz0Usr
ON Abs.usucod = Tz0Usr.usucod
order by abs.dtup

Quote
>     How to convert this dateformat from interbase, i have success to
> concat date, like (Extract(Day From Abs.dtup) || Extract(Month From
> Abs.dtup) || Extract(Year From Abs.dtup)), but
> this result in 112001, and i need 01012001.

To solve that problem, you write a selectable stored proc. I don't have idea
what's the datatype of the "username" field, so I will use char(10) for now:

set term ^;
create procedure p /* no input params */
returns(dtup char(8), uname char(10))
as
declare variable date_temp date;
declare variable n int;
begin
FOR select DISTINCT abs.dtup, Tz0usr.username
from Abs JOIN Tz0Usr
ON Abs.usucod = Tz0Usr.usucod
order by abs.dtup
INTO :date_temp, :uname
DO BEGIN
n = extract(day from date_temp);
if (n<10) then
dtup = '0' || n;
else dtup = n; /* gets translated to string automagically. */
n = extract(month from date_temp);
if (n<10)
then dtup = dtup || '0' || n;
else dtup = dtup || n;
/* here I assumed your years are greater than 999,
hence no padding code needed */
dtup = dtup || extract(year from date_temp);
SUSPEND;
END
end ^
set term ;^

Then,
select dtup, uname from p;
is all you need. It works like it was a table.

Don't get offended, buy you may realize that's cheaper to sort by a field
before decomposing it into an expression (that's the same date after all)
than after it's transformed. Same for the group by clause. Doing the same
expression thrice is inefficient. It would be different if you omitted some
fields, but you only changed the layout of the date. Hence, values that were
different are still different and values that were the same are still the
same after the transformation. And DISTINCT makes clearer what you want.

Again, I have to repeat: do you really want to sort by day number first? The
procedure above sorts by date (yyy-mm-dd) but if you really need your
strange order, you have to do:
- delete the "order by abs.dtup" from inside the procedure
- do  select dtup, uname from p sort by dtup;
Since the procedure already gives you your string in the new layout, the
"sort by" applies to the string, effectively doing what your original
sentence tried to do.

Let me know what's your experience with this, please.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase? WebRing

Re:how to convert this SQL from MS-SQL server to Interbase


Quote
> Again, I have to repeat: do you really want to sort by day number first?
The
> procedure above sorts by date (yyy-mm-dd) but if you really need your
> strange order, you have to do:
> - delete the "order by abs.dtup" from inside the procedure
> - do  select dtup, uname from p sort by dtup;
> Since the procedure already gives you your string in the new layout, the
> "sort by" applies to the string, effectively doing what your original
> sentence tried to do.

> Let me know what's your experience with this, please.

    Thanx a lot for all your complete comments, will be so
usefull in next SQLs that i make on interbase, i already fix
my problem, making some changes in the program code
to better support SQL results, i know that stored
procedures will be make everything more easy to do, but
the problem is, that i am converting a cross database
system, the application support 6 difference databases
using native code, if i will use stored procedures everything
will be more complicated.

    I need more pure SQL are possible to do, i have the
systems work fine on MS-SQL, Oracle, My-SQL, all
using pure SQL code, and now to convert system to
interbase i need to use stored procedures, UDF declarations,
ok, is the interbase style. Now i have all the system working
on the interbase, much functions i cant convert yet,
because a lot of SQL implementation from other database
need a lot of external work. But will be implemented
as soon as possible...

    The order by of my sql is so strange, now thinking what
you say, i think that is wrong, will be order by day only, i dont
understand how anybody warning me yet. :)

    Just to clear, firebird is 100% compatible with interbase
support, my application use Zeos IB components, i look in
firebird homepage and sound like a interbase 6.0 open source
implementation... Anybody has experience with cross interbase
applications to firebird... need some modification, are correct
say that my interbase application is 100% compatible with
firebird database?

    Again, thanx, all your explanation is very appreciated!!

    Bye

Re:how to convert this SQL from MS-SQL server to Interbase


Quote
Cristian wrote:
>     Just to clear, firebird is 100% compatible with interbase
> support, my application use Zeos IB components, i look in
> firebird homepage and sound like a interbase 6.0 open source
> implementation... Anybody has experience with cross interbase
> applications to firebird... need some modification, are correct
> say that my interbase application is 100% compatible with
> firebird database?

The new rows syntax is not yet in Firebird.  Otherwise, at a code
and call level they are quite compatible.

Regards,

Ann

Re:how to convert this SQL from MS-SQL server to Interbase


Quote
"Cristian" <crist...@ivirtua.com.br> wrote in message

news:3c17bbd4_2@dnews...

Quote

> i know that stored
> procedures will be make everything more easy to do, but
> the problem is, that i am converting a cross database
> system, the application support 6 difference databases
> using native code, if i will use stored procedures everything
> will be more complicated.

You should weight one problem: throwing expressions that should evaluated
several times won't help IB performance. You certainly can do tricks using
views with UNIONS, long JOINS, but in the long time, the procedure will be
smaller to write, more understandable and will run faster. Besides, your
call syntax doesn't say you have a procedure. You already know you use
SUSPEND and then
select * from procedure
or
select f1, f2..., fN from procedure
does the trick. It's like selecting from a table.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase? WebRing

Other Threads