[firebird-support] Re: update statement Excel Sheet
Alan Dear,
I dont know whats wrong, Thats why I asking, its simple and should work easly.
I tried to use IBexpert V2.5.0.49 and RazorSQL V5.6.3 to excute the command and
still getting the same error msg. also I copied the sql you just sent to me as
it is and still giving the same error msg
when is invalid.
my firebird server is V1.0.3.973 maybe its old thats why ?
Regards.
Ahmad sarhan
--- In firebird-support@yahoogroups.com, Alan J Davies Alan.Davies@... wrote:
Hi mahdoom_a, I've just created this table MNGRNT (like yours)
RFRNC AQNO
50001
50002
50003
I then ran this code:
update mngrnt
set rfrnc=
case
when aqno = 50001 then '155'
when aqno = 50002 then '166'
when aqno = 50003 then '1331'
end
This is the message - successful update
Plan
PLAN (MNGRNT NATURAL)
3 record(s) was(were) updated in MNGRNT
This is the table after update
RFRNC AQNO
155 50001
166 50002
1331 50003
You must be doing something that you're not telling us because this
just works and is as simple as it gets really.
What else are you trying to do?
Regards
Alan
Alan J Davies
Aldis
On 02/07/2012 16:23, mahdoom_a wrote:
aqno is intger
rfrnc is varchar
I tried another format of case statement
like:
update mngrnt set rfrnc = case aqno
when 50001 then '155'
when 50002 then '166'
end
I got same SQL error but unknown token is aqno
Regards.
--- In firebird-support@yahoogroups.com
mailto:firebird-support%40yahoogroups.com, Alan J Davies
Alan.Davies@ wrote:
What is aqno? An integer or character field?
If its an integer try without ' '
Is it in the same table as rfrnc?
Otherwise there is nothing wrong with the code.
Alan
Alan J Davies
Aldis
On 02/07/2012 07:40, mahdoom_a wrote:
Great Alan,
I tried this
update mngrnt
set rfrnc=
case
when aqno = '50001' then 155
when aqno = '50002' then 166
when aqno = '50003' then 1331
end
I got SQL error
Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 4, char 1.
when.
'when' is identified(in bold black)but I don't know why its invalid.
Best Regards
Ahmad Sarhan
--- In firebird-support@yahoogroups.com
mailto:firebird-support%40yahoogroups.com
mailto:firebird-support%40yahoogroups.com, Alan J Davies
Alan.Davies@ wrote:
update T1
set Col1 =
case
when Col2 = No1
then Val1
when Col2 = No2
then Val2
when Col2 = No3
then Val3
end
Alan J Davies
Aldis
On 01/07/2012 17:53, Hans wrote:
Using the SQL CASE in your update statement may work.
Best Regards
Hans
Sent from my iWatuski
On 2012-07-01, at 9:17 AM, Ahmad F. Sarhan mahdoom_a@
mailto:mahdoom_a%40yahoo.com wrote:
Hey everyone,
I need your help in one of 2 questions please,
1st.
i need to update my table with multi values , each value has
its own
condition
I mean
update T1
set Col1 = Val1 where Col2 = No1
set Col1 = Val2 where Col2 = No2
set Col1 = Val3 where Col2 = No3
.
.
.
Set Col1 = Val 600 where Col2 = No600
and so on. I tried the simple lines as I wrote above but its not
working.
2nd.
I want to update my Table from excel sheet how can firebird
read from
excel sheet.
Thanks
~{ Ahmad F. Sarhan }~
[Non-text portions of this message have been removed]
++
Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++
Yahoo! Groups Links
Re: [firebird-support] Database Corruption
Hello, Consider our Firebird recovery support service: http://ib-aid.com/services/recovery Payment is after successfully recovery. Regards, Alexey Kovyazin IBSurgeon I have a customer database that is returning internal gds software consistency check (applied differences will not fit in record (177)) whenever I access a particular table. I've run gbak with -g switch and it fails on the one particular table I've run it with and without running gfix -v -full before the gbak. It still fails. One thread I found suggested changing the database to read_only before running the gbak command. It still errors with the same message referred to at the top of the post on the same table. In the firebird.log, there is a deadlock error which occurred 90 minutes before the ...applied differences... error. This is a Firebird 1.5 database. Any help is appreciated. [Non-text portions of this message have been removed]
[firebird-support] Re: update statement Excel Sheet
Dear Thomas,
I have update the FB server to 2.5 Same problem :(
Regards,
ahmad sarhan
--- In firebird-support@yahoogroups.com, Thomas Steinmaurer ts@... wrote:
Hello,
I dont know whats wrong, Thats why I asking, its simple and should work
easly.
I tried to use IBexpert V2.5.0.49 and RazorSQL V5.6.3 to excute the command
and still getting the same error msg. also I copied the sql you just sent
to me as it is and still giving the same error msg
when is invalid.
my firebird server is V1.0.3.973 maybe its old thats why ?
Yes. The CASE construct has been added in V1.5.
Regards,
Thomas
Regards.
Ahmad sarhan
--- In firebird-support@yahoogroups.com, Alan J DaviesAlan.Davies@ wrote:
Hi mahdoom_a, I've just created this table MNGRNT (like yours)
RFRNC AQNO
50001
50002
50003
I then ran this code:
update mngrnt
set rfrnc=
case
when aqno = 50001 then '155'
when aqno = 50002 then '166'
when aqno = 50003 then '1331'
end
This is the message - successful update
Plan
PLAN (MNGRNT NATURAL)
3 record(s) was(were) updated in MNGRNT
This is the table after update
RFRNC AQNO
15550001
16650002
1331 50003
You must be doing something that you're not telling us because this
just works and is as simple as it gets really.
What else are you trying to do?
Regards
Alan
Alan J Davies
Aldis
On 02/07/2012 16:23, mahdoom_a wrote:
aqno is intger
rfrnc is varchar
I tried another format of case statement
like:
update mngrnt set rfrnc = case aqno
when 50001 then '155'
when 50002 then '166'
end
I got same SQL error but unknown token is aqno
Regards.
--- In firebird-support@yahoogroups.com
mailto:firebird-support%40yahoogroups.com, Alan J Davies
Alan.Davies@ wrote:
What is aqno? An integer or character field?
If its an integer try without ' '
Is it in the same table as rfrnc?
Otherwise there is nothing wrong with the code.
Alan
Alan J Davies
Aldis
On 02/07/2012 07:40, mahdoom_a wrote:
Great Alan,
I tried this
update mngrnt
set rfrnc=
case
when aqno = '50001' then 155
when aqno = '50002' then 166
when aqno = '50003' then 1331
end
I got SQL error
Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 4, char 1.
when.
'when' is identified(in bold black)but I don't know why its
invalid.
Best Regards
Ahmad Sarhan
--- In firebird-support@yahoogroups.com
mailto:firebird-support%40yahoogroups.com
mailto:firebird-support%40yahoogroups.com, Alan J Davies
Alan.Davies@ wrote:
update T1
set Col1 =
case
when Col2 = No1
then Val1
when Col2 = No2
then Val2
when Col2 = No3
then Val3
end
Alan J Davies
Aldis
On 01/07/2012 17:53, Hans wrote:
Using the SQL CASE in your update statement may work.
Best Regards
Hans
Sent from my iWatuski
On 2012-07-01, at 9:17 AM, Ahmad F. Sarhanmahdoom_a@
mailto:mahdoom_a%40yahoo.com wrote:
Hey everyone,
I need your help in one of 2 questions please,
1st.
i need to update my table with multi values , each value has
its own
condition
I mean
update T1
set Col1 = Val1 where Col2 = No1
set Col1 = Val2 where Col2 = No2
set Col1 = Val3 where Col2 = No3
.
.
.
Set Col1 = Val 600 where Col2 = No600
and so on. I tried the simple lines as I wrote above but
its not
working.
2nd.
I want to update my Table from excel sheet how can firebird
read from
excel sheet.
Thanks
~{ Ahmad F. Sarhan }~
[Non-text portions of this message have been removed]
++
Visit http://www.firebirdsql.org and click the Resources
item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++
Yahoo! Groups
[firebird-support] Re: update statement Excel Sheet
my firebird server is V1.0.3.973 maybe its old thats why ? Yes. The CASE construct has been added in V1.5. I have update the FB server to 2.5 Same problem :( Because your database still has an old ODS? Fb 2.5 can still open old Fb versions, but you don't get access to new things (like CASE) unless you upgrade the ODS. Backup your database using gbak from Firebird 1.0 (or it might work using Fb 2.5 - I don't know) and restore it using gbak from Fb 2.5. HTH, Set
[firebird-support] Enabling only numbers at field varchar(11)
As I could validate that an alone field of varchar(11) enabled numbers and that these had 11 characters of length? Best Regards = || ISMAEL || =
Re: [firebird-support] Enabling only numbers at field varchar(11)
-- Original Message ---
From: Ismael L. Donis Garcia ism...@citricos.co.cu
As I could validate that an alone field of varchar(11) enabled numbers
and that these had 11 characters of length?
--- End of Original Message ---
http://www.firebirdsql.org/refdocs/langrefupd25-similar-to.html
I've not used FB regular expressions yet, but my first stab would be:
alter table ... add constraint ... check (value similar to '[[:DIGIT:]]{11}');
-Philip
[firebird-support] Is there possibility to return dynamic number of columns from EXECUTE BLOCK /SP?
Hello. I am pretty sure that is not possible. I've read all info I could get on the web and it clearly says that output variables /columns must be defined for stored procedure or execute block. But maybe I am wrong? Maybe it is somehow possible to do something like this? SET TERM ^ ; EXECUTE BLOCK AS DECLARE S varchar(255); BEGIN S = 'SELECT ' || DYNAMIC_COL1 || ' , ' || DYNAMIC_COL2 || ' FROM MY_TABLE'; EXECUTE STATEMENT S; END^ The thing is that I am trying to build a query that will return a different number of columns (with varying names) basing on the input parameters. But I guess that is not possible?:( Thanks for your time.
Re: [firebird-support] Is there possibility to return dynamic number of columns from EXECUTE BLOCK /SP?
-- Original Message --- From: un_spoken brucedickin...@wp.pl I am pretty sure that is not possible. I've read all info I could get on the web and it clearly says that output variables /columns must be defined for stored procedure or execute block. But maybe I am wrong? Maybe it is somehow possible to do something like this? SET TERM ^ ; EXECUTE BLOCK AS DECLARE S varchar(255); BEGIN S = 'SELECT ' || DYNAMIC_COL1 || ' , ' || DYNAMIC_COL2 || ' FROM MY_TABLE'; EXECUTE STATEMENT S; END^ The thing is that I am trying to build a query that will return a different number of columns (with varying names) basing on the input parameters. But I guess that is not possible?:( Thanks for your time. --- End of Original Message --- Unlike SQLServer, Firebird wants all selectable blocks (anonymous as above, or named as stored procedures) to have well-defined resultset metadata. In particular, it must be possible to prepare the statement, examine the metadata to know what columns will be coming back, and then start fetching data. Also, remember that in Firebird, you can't just execute statement at the end of a stored procedure (or execute-block) to return a resultset; you must at the very least do for execute statement :f into :a, :b, :c, ..., :z do suspend; where a-z must be somehow related to the returns () clause of your block. (This also means a stored procedure can only return exactly one resultset, as declared in its header.) Your options are: a) if it's just a SELECT, build the SELECT at runtime, and run it (by the time you prepare the statement, it's already clear to the server what columns you want) b) if there's more to it, dynamically build up the EXECUTE BLOCK as you did above, then execute the whole thing as a statement (keeping in mind that you need to build up a statement that looks like: execute block (parameter names and types...) returns (column names and types...) as begin for select ... into ... do suspend; end -Philip
