View: 2735|Reply: 19
|
Stored Procedure problem
[Copy link]
|
|
Wahai kawan2 sekalian... sape teror stored prcedure? aku ade problem sikit ni... okla camni... dlm coding SP aku ade gunekan datatype date... so far sp tu takde problem, tapi bile execute dia akan return nilai 0.. adakah sp ni x accept datatype date? boleh tak kawan2 tolong sket? ke aku nak tempek coding kat sini? |
|
|
|
|
|
|
|
waaaa...ilmu baru...ni language ape? |
|
|
|
|
|
|
pijotfly This user has been deleted
|
waaaa...ilmu baru...ni language ape?
store procedure ni function yg ada sql statement untuk retrieve data dari database. kite simpan function ni di server directly.bile time programming just panggil function ni. fungsi die mungkin utk cepatkan masa retrieve data sbb function ni direct kat server (masih dalam kajian). antara lain..function ni tak replicate..sbb different user or programming tools kadang2 buat function yg sama tp berulang2..so bende ni boleh save resource.just guna bende yg sama utk retrieve data yg sama.itu lah yg cek paham nooo..
alamamda_girl use what database...so far guna datatype date die ok..check sql lah kot..sorry.. |
|
|
|
|
|
|
|
Originally posted by pijotfly at 4-1-2007 10:32 AM
store procedure ni function yg ada sql statement untuk retrieve data dari database. kite simpan function ni di server directly.bile time programming just panggil function ni. fungsi die mungkin ...
i guna oracle database... itula i tak tahu sama ada datatype tu compatible atau tidak dengan stored procedure i pijot, can u suggest any website for me to refer? |
|
|
|
|
|
|
|
haaa ni aku ade problem ngan date jgk tp aku gune informix... boleh tolong dak? ni coding aku...
create dba procedure "informix".procedure(a varchar(2),b varchar(2),c varchar(2),d varchar(1),e varchar(1),
f varchar(1), g varchar(1), trkhmula date, trkhakhir date)
returning decimal{2};
define sum decimal{2};
SELECT sum(amtkt )
INTO sum
FROM june
WHERE ( wca = a ) AND
(wcb = b ) AND
( wcc = c ) AND
( wcd= d ) AND
( wce = e ) AND
( wcf= f ) AND
( wcg = g) AND
( wctrkh >= trkhmula ) AND
( wctrkh <= trkhakhir );
if( sum is null) then let sum = 0;
end if;
return sum;
end procedure
sape2 yg terel boleh tolong aku dah berbulan2 dok ngadap menatang ni je..tertekan btol...sib baik ade topic ni... |
|
|
|
|
|
|
|
Reply #6 lolaxpdc's post
saya tak brape fasih dalam informix, tp u tak detailkan apa masalahnya... masalah tentang date tapi specifically apa masalahnya? dapat sum = 0 ke? |
|
|
|
|
|
|
|
ops..sorry tak detail the prob...haah.. sum saya return 0...
tapi bila saya amik sql statement tu and paste kat database painter, dia ada nilai..tp bile saya run program dia boleh retun 0...saya syak kat date tu sbb bila saya create stored procedure tanpa date dia boleh return value....please sape2....thanx
[ Last edited by lolaxpdc at 22-2-2007 04:15 PM ] |
|
|
|
|
|
|
|
Reply #8 lolaxpdc's post
if you think that the date is the problem, buat ni: buang the part yang ada where clause yang specify criteria date tu, and run it again.
( wctrkh >= trkhmula ) AND
( wctrkh <= trkhakhir )
Kalau sum ada value, maknanya ok la tu, date ada problem. Tapi i suggest you modify this part:
SELECT sum(amtkt )
INTO sum
Tukar sum kepada perkataan lain sebab sum is a reserved word in SQL.
Satu lagi la saya nak tanya... sorry sebab saya tak fasih Informix and correct me if I am wrong, but u declared variables from a to g kan, tapi masa dalam select statement tu, ada wca, wcb, wcc, wcd... benda tu dia ambik dari mana? How does the code pass the argument values? |
|
|
|
|
|
|
|
ok sum tu saya dah guna variable lain untuk menggantikannya. still tak boleh gak.
wca, wcb onwards tu nama column dlm table (june) yg saya nak gunakan.
SELECT sum(amtkt )
INTO sum
FROM june
WHERE ( june.wca = a ) AND
(june.wcb = b ) AND
( june.wcc = c ) AND
( june.wcd= d ) AND
( june.wce = e ) AND
( june.wcf= f ) AND
( june.wcg = g) AND
camner aaa... gue dah sakit wotak lohhhhhhhh..ok skang gue dah inform DBA gue untuk buang date2 tu seme skang where untuk select statement di atas dah takde date... nak tgk table tu sendiri ok ke dah weng.. harap2 dba gue tak balik umah lagi coz dah kol 530 ni.. eii gue pun nak kene gi gym ni... adoiii... |
|
|
|
|
|
|
|
mungkin date, remove dulu.
biasanya date la yang jadi masalah bila nak buat select query. different RDBMS have different ways to parse dates, contoh macam ni:
Access:
select * from sometable where date >= #1 Jan 2007#
MySQL:
select * from sometable where date >= "2007-01-01"
So untuk Informix, mesti ada date formatnya yang dia boleh faham, I guess. |
|
|
|
|
|
|
|
Reply #11 shahnazz's post
knp tak guna just timestamp... |
|
|
|
|
|
|
|
aiiyoo... i dah buang date tp prob tu still occurs.. pening... baik aku buat keje lain ah... |
|
|
|
|
|
|
|
Stored procure utk database apa? SQL Server ke,Oracle ke,DB2 ke,MySQL ke,......setiap database ada dialect dia sendiri.... |
|
|
|
|
|
|
|
INFORMIX 7..time kacih... |
|
|
|
|
|
|
|
err...nak tanya soalan bodoh boleh tak? jgn marah ye...
kalu hanya buat
SELECT sum(amtkt )
INTO sum
FROM june
sum tu akan return value apa? |
|
|
|
|
|
|
pijotfly This user has been deleted
|
SELECT sum(amtkt )
INTO sum
FROM june
sum bg semua amtkt..kalo nak sepcific lg letaklah where ape.. |
|
|
|
|
|
|
|
[quote]Originally posted by <i>lolaxpdc</i> at 22-2-2007 05:24 PM<br />
ok sum tu saya dah guna variable lain untuk menggantikannya. still tak boleh gak.<br />
wca, wcb onwards tu nama column dlm table (june) yg saya nak gunakan.<br />
<br />
SELECT sum(amtkt )<br />
|
|
|
|
|
|
|
|
Originally posted by shahnazz at 22-2-2007 05:44 PM
mungkin date, remove dulu.
biasanya date la yang jadi masalah bila nak buat select query. different RDBMS have different ways to parse dates, contoh macam ni:
Access:
select * from sometab ...
informix punye format date = 'mm/dd/yyyy'
contoh '03/21/2007' |
|
|
|
|
|
|
|
dah setel ke masalah alamanda ??
ehmm kalo win nak wat comparison date ... (just assume masalah alamanda-sbb tak clear)
usually win buat cam gini ... sbg contoh le
begin
select to_char(col_date,'yyyymmdd') into var from table
if var >= to_char(sysdate,'yyyymmdd') then (assume nak compare date le)
var2:= 1;
else
var2:= 0;
end if;
return(var2);
end; |
|
|
|
|
|
|
| |
|