plsql related issue - Database Discussion Boards

Hi Michael,

Thanks for replying.

Please find below a sample of the SQL. It is not the real script but reflects the scenario I tried to describe.

"select shipId,number,reference,[weight],SA.typeOfAddress,addressLine as Source, addressLine as Destination
FROM Shipments AS S INNER JOIN ShipmentAddresses AS SA ON SA.shipmentId = S.shipId
INNER JOIN Addresses as A ON A.addressId = SA.addressId"

This query is returning 2 rows:
shipId | number | reference | weight | typeAddress| Source | Destination |
1 1 ref1 12 0 London Road London Road
1 1 ref1 12 1 LiverPool Lliverpool

I would like a single row:
shipId | number | reference | weight | Source | Destination |
1 1 ref1 12 London Road LiverPool

Note: the type of address in the first example of results was just to indicate that 0 is source and 1 is destination.

Many thanks for the help.

Try something like this:

SQL

SELECT
    S.shipId,
    S.number,
    S.reference,
    S.[weight],
    SA.addressLine As Source,
    DA.addressLine As Destination
FROM
    Shipments As S
    
    -- Source address:
    INNER JOIN ShipmentAddresses As SSA
    ON SSA.shipmentId = S.shipId And SSA.typeOfAddress = 0
    INNER JOIN Addresses As SA
    ON SA.addressId = SSA.addressId
    
    -- Destination address
    INNER JOIN ShipmentAddresses As DSA
    ON DSA.shipmentId = S.shipId And DSA.typeOfAddress = 1
    INNER JOIN Addresses As DA
    ON DA.addressId = DSA.addressId
;

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Hi Richard,

Sorry the delay in answering.
the strategy you described above worked perfectly on my real production scenario.
Many thanks.

Can you recreate the problem in a simple example on SQL Fiddle[^]? Without seeing your table structure, query, sample data, and expected output, it's almost impossible to answer your question.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Hi Richard,

Hope you are alright and thanks for replying.

Could you please see my above answer to Michael?

Any help will be appreciated.
Many thanks.

I found the MS SQL database for a specific application and needed to change a record in the database. What needs to be changed is a field with the name of a pdf that the points to a server folder that holds all of the application pdf files. What I did was rename the record field to the correct new pdf file name and copy the new pdf file to the server pdf folder. When I try to access the pdf file from the application I select view pdf link and the new pdf does not opens. In the application when the same link was select the original pdf opened to show the pdf content. After that I tied to change the pdf field in the database back to the name of the original file name and now when I select the same link that worked before with the original pdf file that opened before it will not open and appears to have the same problem as what I changed for the new pdf. I made sure that the file is still in the application directory and everything is back to the way I started and it still does open either the original or updated file. When I checked both file permissions I don't see anything wrong. I don't know what could of caused this.

Debug through the application code and see what is being retrieved and the execution method for viewing the PDF file.

Never underestimate the power of human stupidity
RAH

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')

C#

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 Blush | :O

.

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 Suspicious | :suss:

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 Suspicious | :suss:

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