Database

5-Jan-16 0:47

Hi everyone,

First of all sorry for any mistake I might give as English it is not my natural language.

My issues is: I built a query where I had to join 8 tables. The query should only be returning 4 rows but instead it is returning 8 rows as one of the tables I'm using in the join's has a field type to identify , for example if it is source and destination(0 or 1). The final result should return only one row with either with source and destination information base on the type. Two different columns (source,destination) but same row.

Any ideas how to solve this?
I appreciate any help.

Many thanks.

Michael_Davies5-Jan-16 1:02

Michael_Davies

5-Jan-16 1:02

My English is bad and I'm English but my mind reading is atrocious. - Show the SQL

jonatan_5565-Jan-16 3:03

5-Jan-16 3:03

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.

Richard Deeming5-Jan-16 7:50

Richard Deeming

5-Jan-16 7:50

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

jonatan_55617-Jan-16 8:06

17-Jan-16 8:06

Hi Richard,

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

Richard Deeming5-Jan-16 2:35

Richard Deeming

5-Jan-16 2:35

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

jonatan_5565-Jan-16 3:09

Unable to open a file from the running application after the file name has been updated in the database

5-Jan-16 3:09

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.

Stephen Holdorf4-Jan-16 5:05

Stephen Holdorf

4-Jan-16 5:05

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.

Re: Unable to open a file from the running application after the file name has been updated in the database

Mycroft Holmes4-Jan-16 13:10

4-Jan-16 13:10

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

plsql related issue

Member 1191972229-Dec-15 18:35

Member 11919722

29-Dec-15 18:35

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

Re: plsql related issue

Mycroft Holmes4-Jan-16 13:06

Creating .cst file from .csp file CodeSmith

4-Jan-16 13:06

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

indian14328-Dec-15 21:11

indian143

28-Dec-15 21:11

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."

Re: Creating .cst file from .csp file CodeSmith

Mycroft Holmes4-Jan-16 13:03

4-Jan-16 13:03

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

Sql_Index

Member 1116162527-Dec-15 19:34

Member 11161625

27-Dec-15 19:34

Hi,

My question is that,when we can create Clustered and NonClustered Index on tables???

What is the purpose behind it???

Thanks...

Re: Sql_Index

Mycroft Holmes27-Dec-15 19:38