|
create or replace function Registration_Click(paitent_1 IN character varying,card_no IN character varying,dis_name IN character varying,taluka_name IN character varying,village_name IN character varying,po_name IN character varying,co_name IN character varying,occupation IN character varying,doc_name IN character varying,RefByType_name IN character varying,city_name IN character varying,state_name IN character varying,country_name IN character varying,body_name IN character varying,mob_no IN character varying,opd_id IN character varying,comboboxtitle_title_name IN character varying,comboboxco_title_name IN character varying,name_1 OUT character varying, id_246 OUT character varying,name_2 OUT character varying,max_date_id OUT character varying,max_dis_id OUT character varying,max_taluk_id OUT character varying,max_village_id OUT character varying,max_po_id OUT character varying,max_co_id OUT character varying,max_occupation OUT character varying,max_doc_id OUT character varying,mob_no_o OUT character varying, paitent_2 OUT character varying,id_1918 OUT character varying,f_name OUT character varying,comboboxtitle_title_id OUT character varying, comboboxco_title_id OUT character varying,auth_1 OUT character varying,city_name_o OUT character varying,state_name_o OUT character varying,std_code IN character varying,country_name_o OUT character varying,body_name_o OUT character varying )
AS $body$
declare
temp varchar;
BEGIN
SELECT st_fname into name_1 FROM sc_register WHERE st_patient_id = paitent_1;
SELECT st_value into id_246 from sc_setting where st_id =246;
SELECT st_fname into name_2 from sc_register where st_pt_cardno = card_no;
SELECT MAX(dt_doj) into max_date_id FROM sc_register;
IF dis_name <> '' THEN
SELECT si_district_id into max_dis_id FROM sc_district where st_districtname = dis_name;
if max_dis_id IS NULL THEN
SELECT max(si_district_id)into max_dis_id FROM sc_district;
INSERT INTO sc_district values(max_dis_id,dis_name);
END IF;
END IF;
IF taluka_name <> '' THEN
SELECT si_talukid into max_taluk_id FROM sc_taluk where st_talukname=taluka_name;
IF max_taluk_id IS NULL THEN
SELECT max(si_talukid) into max_taluk_id FROM sc_taluk;
INSERT INTO sc_taluk values(max_taluk_id,taluka_name);
END IF;
END IF;
IF village_name <> '' THEN
SELECT si_villageid INTO max_village_id FROM sc_village where st_villagename=village_name;
IF max_village_id IS NULL THEN
SELECT max(si_villageid) into max_village_id FROM sc_village;
INSERT INTO sc_village values(max_village_id,village_name);
END IF;
END IF;
IF po_name <> '' THEN
SELECT si_poid into max_po_id FROM sc_po where st_poname=po_name;
IF max_po_id IS NULL THEN
SELECT max(si_poid) into max_po_id FROM sc_po;
INSERT INTO sc_po values(max_po_id,po_name);
END IF;
END IF;
IF co_name <> '' THEN
select si_co_id into max_co_id from insurance_company_det where st_co_name =co_name;
IF max_co_id IS NULL THEN
select MAX(si_co_id) into max_co_id from insurance_company_det;
insert into insurance_company_det values(max_co_id,co_name);
END IF;
END IF;
IF occupation <> '' THEN
SELECT st_occupation INTO max_occupation FROM sc_occupation where st_occupation = occupation;
IF max_occupation IS NULL THEN
SELECT max(si_occ_id) INTO max_occupation FROM sc_occupation;
INSERT INTO sc_occupation values(max_occupation,occupation);
END IF;
END IF;
IF doc_name <> '' THEN
SELECT si_ref_doc_id into max_doc_id FROM sc_ref_by_doctors where st_doc_name = doc_name;
IF max_doc_id IS NULL THEN
SELECT si_ref_by_type_id into temp FROM sc_ref_by_types where st_type_name = RefByType_name;
select max(si_ref_doc_id) into max_doc_id from sc_ref_by_doctors;
insert into sc_ref_by_doctors values(max_doc_id,doc_name,temp);
END IF;
END IF;
IF city_name <> '' THEN
select st_cityname INTO city_name_O from sc_cityname where st_cityname = city_name;
IF city_name_O IS NULL THEN
insert into sc_cityname values(city_name,state_name,std_code,country_name);
END IF;
END IF;
IF state_name <> '' THEN
select st_statename into state_name_o from sc_statename where st_statename = state_name;
IF state_name_o IS NULL THEN
insert into sc_statename values(state_name);
END IF;
END IF;
IF country_name <> '' THEN
select st_country into country_name_o from sc_country where st_country = country_name;
IF country_name_o IS NULL THEN
insert into sc_country values(country_name);
END IF;
END IF;
IF body_name <> '' THEN
select st_body_type_name into body_name_o from sc_body_type where st_body_type_name = body_name;
IF body_name_o IS NULL THEN
insert into sc_body_type values(body_name);
END IF;
END IF;
SELECT st_mob_no,st_patient_id into mob_no_o,paitent_2 FROM sc_register WHERE st_mob_no = mob_no;
SELECT st_value into id_1918 from sc_setting where st_id =1918;
SELECT st_fname into f_name from inpatient.ipd_reg_master where st_opd_patient_id = opd_id;
SELECT si_title_id into comboboxtitle_title_id FROM sc_title WHERE st_title_name = comboboxtitle_title_name;
SELECT si_title_id INTO comboboxco_title_id FROM sc_title WHERE st_title_name = comboboxco_title_name;
SELECT st_usr_name INTO auth_1 FROM sc_usr WHERE ch_athority =1;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE;
above is my plsql code and when i run above function i get follwoing errors
SELECT * FROM Registration_Click('LB20150200001','LB20150200001','DARBHANGA','BISANPUR','PATORI','PATORI BASANT','','BUSINESS','CHANDRIKA ANAND','PATIENT','BANGALORE','KARNATAKA','INDIA','','9341234566','NF20150200012','Mr','Mrs')
function to_tsvector(unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown) does not exist
LINE 1: SELECT * FROM Registration_Click(to_tsvector('LB20150200001'...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
whats the solution please ...give the best solution
Thankks
|
|
|
|
|
Haha, I love it, dump in your school work code, don't ask a question but ask for a solution. Try reading the guidelines on asking a question.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi All,
I am new to codesmith, but my company is using, I got a project which is using CodeSmith for ORM. But the source code doesn't have cst file, they only have .csp file. Is there anyway that I can create .cst file from .csp file?
Any help like a link or a suggestion or a code snippet would help, I am also searching but any help would be appreciated.
Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
I was going to make some sarcastic comment about their support forum! But they don't have one  .
Sorry, don't use the product.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
My question is that,when we can create Clustered and NonClustered Index on tables???
What is the purpose behind it???
Thanks...
|
|
|
|
|
The minimum research will get you this[^] reference
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
A clustered index means that when you get to the end the end leaf of the index, you are at the physical location of the data you are looking for. This makes reading data very fast. None clustered indexes by contrast point to a physical location of data - this means that when you do reach the end leaf on a non-clustered index the disk head may have to move to get you your data.
The one downside of a clustered index is that if you have a lot of inserts to the indexed table a clustered index will be disadvantageous. This is because every insert into the table will probably require a reorganisation of the physical location of some rows in the table that are not connected to the rows that you are inserting.
The simple answer is - a clustered index is very useful when you have a lot of reading and not much writing.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Greetings,
I have a database creation script. I've run the script after changed the path within the 'FILENAME' option to create the database on an external hard disk and I was able to see and select data from tables as the script run with no errors. However, when I tried to connect to the database from an application and read data from database tables, an exception thrown for SQL Server error 823 "The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000132000 in file 'Database name'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.". I've tried to run database consistency commands against it but they did not carried out and I got an error message. Also when I tried to drop or recover the database, dropping and recovering process failed. Finally, I had to make the database offline then I dropped it. I need to create such database on my external hard disk not on my primary one. Can anyone help?!!!
|
|
|
|
|
Why on an external HD?
SQL Server does not support plug & play; you may loose data if the drive is removed without detaching the database. If you just want to protect the data then I'd suggest backup up to the external HD and not using it as the servers' datasource.
IIRC, then only network drives are 'disallowed'. If you want to setup the data-files to be on an external disc, you'd be interested in this[^] thread.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
HI,
I want to know that while creating cascading referential integrity constraint between tables having foreign key relationships.
For e.g:-
I have two tables Employee and EmployeeLeave,now i want to use cascading referential integrity constraint
code:-
Alter Table dbo.EmployeeLeave
Add Constraint rfkcEmployeeID Foreign Key(EmployeeID)
References dbo.Employee(EmployeeID)
ON DELETE NO ACTION ON UPDATE NO ACTION
So, My question is that i can even use this Constraint at the time of Creating Table
For e.g:-
Create Table dbo.EmployeeLeave
(
EmployeeID int constraint rfkcEmployeeID Foreign Key(EmployeeID)References dbo.Employee(EmployeeID)
ON DELETE NO ACTION ON UPDATE NO ACTION
)
Is this code is correct???
Is it will work???
Thanks...
|
|
|
|
|
What happened when you tried it?
|
|
|
|
|
Member 11161625 wrote: Is this code is correct???
Syntactic it is correct, but it will do nothing, as you instructed it. That is the default behaviour.
If you want the "leave" days to be removed from the database when the employee is removed from the database, then you'd want a cascading delete. (ON DELETE CASCADE, not "NO ACTION"). That way you can prevent getting "orphaned" records.
If you still need the leave-data from that person and don't want to wipe the records, I'd recommend "ON DELETE SET NULL", since the key would have become invalid - but then the record isn't deleted, just the key is removed - handy if you need the leave-data for statistics or other stuff.
Last, there is also a "SET DEFAULT" option; if a manager is deleted from the database, then you might want to automatically update all affected workers to some default-manager.
Member 11161625 wrote: Is it will work???
Depends on the specs. Press F5 and see if it does what you expect. Disclaimer; various database-engines may use different keywords and not support them all.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Federal Reserve note and no issued by a private bank in 1944 at the in world war two the Breton Woods Accord was made in world war two United States was recognized as the developing worldwide economy and by then thereto Woods accord pegged all monetary forms to the US dollars that universal exchange could be could be smoothed out and starting there for essentially Cash Camp all merchandise were evaluated in US dollar the US dollar was pegged at the at the it was picked to gold and the peg.
http://t-rexmusclefacts.com/cash-camp/[^]
|
|
|
|
|
create or replace FUNCTION Activation2(v_status OUT varchar,v_status1 OUT varchar)
returns setof sc_register AS $$
declare
begin
SELECT st_value into v_status FROM sc_activation_dtls WHERE si_slno = 2;
select * from sc_register ;
end;
$$
LANGUAGE 'plpgsql' VOLATILE
aftr writng above plsql functiopn in postgresql pane m getting follwing error
function result type must be record because of OUT parameters
doest it mean that we cant use output parameter and return statent togather.
since my requirement is to get some value of table through output parameters along with records of different table ..in one function only.
Thanks
|
|
|
|
|
Hi,
I want to know that while creating suqueries in SQL Server,it is mandatory to have Primary key and Foreign key relationships between the tables???
In short:-I want to know if we create subqueries without p.k and F.k realtionships,then it will work???
What is the main purpose of Subqueries???
Thanks...
|
|
|
|
|
In Short: Yes
The relationship structure is an integral part of the data structure but is not required for sub queries. Sub queries are just another tool for getting your data from the database the way you want it. Like CTEs and the various joins they are all aspects of TSQL.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have often wondered this myself. Since I can perform a join simply by saying "WHERE a.field = b.field", what is the ultimate purpose of setting up a relationship?
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
Richard Andrew x64 what is the ultimate purpose of setting up a relationship?
Referential Integrity[^]
Wonde Tadesse
|
|
|
|
|
Wonde nailed it, IMHO referential integrity and constraints are the last line of defense to keep the garbage in. They also help with optimisation of course.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
There are number of laptops installed a window application connected with local database and all these machines works in remote area where no internet connectiviy is there. we have another web application connected to a central db.the Idea to synchroze database is that every machines export there data at the end of day and save the exported file in removable disk and place all the exported file to a specific location using FTP.
My requirement is to import all the exported files into central database and vice-versa. there are around 15 table out of 100 on which need to keep the synchronization.
Please suggest.
|
|
|
|
|
There may be a method to do this using replication but I have never explored that avenue, I have used a brute force method by designing my database to used GUIDs for the primary key and therefore the tables should be mergable.
Another way is to add a machine id to each table/record so the records are unique across databases.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Can we achieve this using Microsoft sync framework or some framework?
modified 15-Dec-15 5:07am.
|
|
|
|
|
I doubt it, I have not worked with any of them but I have never heard of of them synching database content. You would still need to identify each record uniquely across all the databases.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
There would be some examples on how to synchronize Sql Server Express/CE with Sql Server, so yes. Then again, these samples are built specifically to show some specific functionality.
Means it is no silver bullet; you may want to print the documentation and set up a test-environment.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I'm dealing with a strange (for me) situation in SQL Server.
I'm having a process that locks the same table twice. If the same time that the first process is locking the table a second process tries to lock the same table a deadlock happens immediately. Is this normal?
I wrote a script and run it twice from different connections.
Here is the script:
begin tran myTran
create table anyTable (anyColumn varchar(10), primary key (anyColumn))
declare @exec datetime, @i int, @m varchar(2), @x int
set @exec = getdate()
set @m = substring(convert(varchar, @exec, 120), 15, 2)
set @x = convert(int, @m) + 1
print 'Before Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate())
update anyTable set anyColumn = anyColumn
print 'After Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate())
while datediff(ms, @exec, convert(datetime, substring(convert(varchar, @exec, 120), 1, 14) + right(rtrim(ltrim(convert(varchar, @x))), 2) + ':00.000')) > 0
begin
print 'Waiting. Time = ' + convert(varchar(40), @exec)
set @exec = getdate()
end
print 'Before Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate())
update anyTable set anyColumn = anyColumn
print 'After Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate())
drop table anyTable
commit tran myTran
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
