February 11, 2016 at 2:03 am
HI All
I have an issue that I cant seem to resolve. I have a two views joined together that call out only certain values. However the one value is a numeric field and when I input a value into this numeric field it duplicates onto all entries. Also the numeric field is to only return results if the adverse_event field is a yes otherwise it should just stay empty:
Here is my relating code:
View 1:
select A.NumericCol,
B.Client_Id,
B.TextCol AS NOTE,
B.DateCol AS dATE
from AMGR_User_Fields A
INNER JOIN AMGR_Notes B ON A.Client_Id = B.Client_Id
WHERE A.Type_Id = 145 --AND A.Code_Id = 0
GROUP BY B.Client_Id, A.NumericCol, B.TextCol, B.DateCol
View 2:
SELECT D.First_Name + ' ' + D.Name as Contact,
D.Phone_1 AS MAIN,
D.Phone_3 AS CELL,
A.DateCol as Date_of_contact,
A.TextCol as NOTE,
MAX(case when C.Type_Id = 146 then C.Description end) As Adverse_Event,
MAX(case when C.Type_Id = 138 then C.Description end) As Identified_by_med_info,
b.Client_Id AS CLIENT_ID,
b.Contact_Number as Contact_Number,
a.Type as type
from AMGR_Notes A
inner join AMGR_User_Fields B on b.Client_Id = a.Client_Id
inner join AMGR_User_Field_Defs C on b.Code_Id = c.Code_Id AND C.Type_Id = B.Type_Id
inner join AMGR_CSCases E on A.client_id = e.Case_Id
inner join AMGR_Client D on d.Client_Id = E.Client_Id
WHERE C.Type_Id in (138,146)
AND A.Entity_Type = 5
and b.Contact_Number in ('0','1')
--and A.datecol between @StartDate and @EndDate
And a.Type = 9
--and A.CONTACT_DATE between @StartDate and @EndDate
GROUP BY B.Client_Id, A.TextCol, A.DateCol, d.First_Name, d.Name, D.Phone_1, D.Phone_3, b.Contact_Number, a.Type
View 2 works great however view one is the issue.
here is my joining statement:
sELECT B.CONTACT AS CONTACT,
B.MAIN AS PHONE,
B.CELL AS CELL,
B.DATE_OF_CONTACT AS DATE,
B.NOTE AS REFERENCE,
B.ADVERSE_EVENT AS AE,
B.IDENTIFIED_BY_MED_INFO AS IBMF,
A.NUMERICCOL AS ACTION_TAKEN,
B.type as Type
FROM CSV A
INNER JOIN CSV1 B ON A.client_id = b.client_id
GROUP BY A.NUMERICCOL, B.ADVERSE_EVENT, B.IDENTIFIED_BY_MED_INFO, B.CONTACT, B.NOTE, B.DATE_OF_CONTACT, B.MAIN, B.CELL, b.type
What happens is the following, only the entries that have a value in the numericcol are returned however I don't want that, I want all entries regardless if they have a value or not to be returned.
Please help!!
February 11, 2016 at 6:20 am
Stix83 (2/11/2016)
View 2 works great however view one is the issue.here is my joining statement:
sELECT B.CONTACT AS CONTACT,
B.MAIN AS PHONE,
B.CELL AS CELL,
B.DATE_OF_CONTACT AS DATE,
B.NOTE AS REFERENCE,
B.ADVERSE_EVENT AS AE,
B.IDENTIFIED_BY_MED_INFO AS IBMF,
A.NUMERICCOL AS ACTION_TAKEN,
B.type as Type
FROM CSV A
INNER JOIN CSV1 B ON A.client_id = b.client_id
GROUP BY A.NUMERICCOL, B.ADVERSE_EVENT, B.IDENTIFIED_BY_MED_INFO, B.CONTACT, B.NOTE, B.DATE_OF_CONTACT, B.MAIN, B.CELL, b.type
What happens is the following, only the entries that have a value in the numericcol are returned however I don't want that, I want all entries regardless if they have a value or not to be returned.
Please help!!
Hello, I did not study the query very intensly, but maybe a 'OUTER JOIN' does help,
change the 'INNER JOIN' into 'RIGHT OUTER JOIN' to get all rows from CSV1.
Tip!
Use the Query Designer in studio manager.
Set the select in focus. (The complete select statement should be selected as if you want to run the statement).
Press Ctrl/Shift/Q, this brings you into the Query designer.
A join should be visible, right clicking on the diamond shape gives you the option to select all rows from either (or both) of the tables.
Please tell us if this did help.
Ben
February 15, 2016 at 4:50 am
No the right join brings all the rows however the action taken field is duplicated with the same number for each entry. It only has to apply to the entry that has the value added to the action numeric field
February 15, 2016 at 1:20 pm
Please help us help you. Post the following to make it easier for us:
1. CREATE TABLE statements for fake tables to mimic the results of your two views. (They work, right? And for the query it is irrelevant wheter you combined views or tables);
2. INSERT statements with a few rows of sample data, carefully chosen to illustrate the requirement and the edge cases;
3. Expected results based on that sample data.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply