what is the use of that second query in the stored procedure? It seems to have nothing to do with the stored procedure?
This fieldname = b.name is part of the values returned in the app but unfortunately, there is no relationship key between any table on the database and the tables in the second query.
The only way they are able to get a value for that b.name fieldname is the way they wrote it.
That's the hand I was dealt.
proc name is spGetLogs, which of the 2 selects returns what you need?
create proc spGetLogs
as
begin
select 'Chicken Wing' as QueryOne;
select 'Waffle House All you can eat' as QueryTwo;
end
go
exec spGetLogs
returns
I don't understand what your question is sir.
The sample result you showed doesn't even match the query.
In any case, the two separate queries I posted like I stated, work indepently.
I would like them to work as one because if they do, whatever value I pass to the appropriate variable, will either return results or display a message of No data found as opposed to the error that I am getting.
Thank you very much
that is just a sample proc to demonstrate what I am trying to ask you. in principle it matches in the sense that it returns 2 separate queries. Does your app expect 2 select results
In my app, I am returning the following:
cl.phone_number,
callerid,
qp.call_list_id,
startttime,
camapign_id,
q.queueName,
c.name as call_type,
connecttime,
endtime,
qp.duration,
fullduration,
ca.campName,
ct.call_termination_type,
qp.roll_id,
(e.first_name +' '+ e.last_name) employee,
i.name as cause_code,
box_id,
trunk_name,
qp.uuid,
customer_id
from the first query and b.name as teleservers from the second query so that they are laid out like:
Caller ID | Call List ID | Start Time | Connect Time | End Time | Duration | Full Duration | Campaign | Name | Queue Name | Call Type | Employee | ISDN Cause | Code Disposition | Trun Name | UUID | Teleservers
Since there is no key to join Teleservers from second query to any table from the first query, that second query is how they are getting values for Teleservers.
Those column header names are on one row.
I could not fit them into one row here. I had to separate them with the '|' separator.
So you want the value from the second query to be repeated for each row returned in the first query? If so - can you guarantee that only one value can ever be returned from the second query?
If the second query returns more than a single row - how should that be included in the results of the first query?
Also - you need to get in the habit of writing your queries using join syntax - or more appropriately - get out of the habit of joining using a comma...
SELECT b.name AS teleservers
FROM cti.server_settings a
INNER JOIN cti.servers b ON b.id = a.server_id
WHERE a.attribute = 'asterisk_box_id'
AND a.value = (SELECT q.box_id FROM cti.qpcdr q WHERE q.uuid = '12345');
Also - make sure you alias tables in sub-queries, because sub-queries have access to the columns in the outer query you could 'accidentally' create a sub-query that is always true and will return invalid results:
SELECT b.name AS teleservers
FROM cti.server_settings a
INNER JOIN cti.servers b ON b.id = a.server_id
WHERE a.attribute = 'asterisk_box_id'
AND a.value = (SELECT name FROM cti.qpcdr WHERE uuid = '12345');
In the above query - if cti.qpcdr does not have a name column - the query will still result in a true value because name exists in the outer query.
Jeff, first of all, thank you for your response.
Your question?
can you guarantee that only one value can ever be returned from the second query?
Great question!
I can guarantee that by making one small change to the subquery:
AND a.value = (SELECT TOP 1 box_id FROM cti.qpcdr WHERE uuid = '12345')
I just added the TOP qualifier.
Does that help?
I just have one question.
How does this solution you provided solve the issue that I am having?
The original second question I posted returns results, the correct results.
It is only when I try to combine it with the first, long query so they can use same value for whatever variable is being passed to it.
That's where it is failing me and that's where I am having issues.
what if there are different box_id values? what makes you decide the 1st one?
I have not provided a solution yet - it all depends on how you want to display the column values. Since the query - by itself - can return multiple rows, how do you want to display those rows?
You can use TOP 1 to get a single row...but is it the right one? Should it be the same value for every row returned in the first query? Or should it be a different row based on some criteria from the row in the first query?
This needs to be determined before I can provide a solution.
OK, I see where you are coming from.
The first query has 6 input parameters including @uuid
I believe that when a user chooses to search records with @uuid, then the second query should return a result based on the value of @uuid.
The second query may return null if user searches by any parameter other than @uuid.
It is already complicated as it is.
Otherwise, passing the uuid value from first query to the WHERE of the second query will gurantee a return of a value for b.name.
Thanks so much
Your first query appears to already have the UUID - does the second query return a unique name per UUID or does it return multiple values? If it returns multiple - are they all the same?
You could use a CROSS\OUTER APPLY:
SELECT ...
FROM {first query tables}
CROSS APPLY (
SELECT b.name AS teleservers
FROM cti.server_settings a
INNER JOIN cti.servers b ON b.id = a.server_id
WHERE a.attribute = 'asterisk_box_id'
AND a.value = (SELECT q.box_id FROM cti.qpcdr q WHERE q.uuid = '12345')
) AS ts
WHERE ...
The '12345' value will either come from a parameter - or from the table in your first query that is returning that value now. If it is possible that you get multiple values back - modify the query using either TOP 1 or DISTINCT (if b.name is the same for every row returned). If it is possible that you can get no rows - change the CROSS APPLY to an OUTER APPLY.
It is also possible to include the tables directly:
SELECT ...
FROM {first query joins}
LEFT JOIN cti.server_settings ss ON ss.value = qp.box_id AND ss.attribute = 'asterisk_box_id'
LEFT JOIN cti.servers s ON s.id = ss.server_id
Not sure - it looks like you already have that table in the first query so it should work.
Thank you very Jeff.
This is very creative.
On the cross apply, do I join anything to ts?
Running it right now as is returns no data found message when I passed a legitimate uuid value that was returning a value before.
On the second solution you proposed (sorry for the silly question), do I select from the first query, add s.name as teleservers (from second query) then the inner joins, then the left joins?
Adding s.name as teleservers as part of the first query returned the same error - televerservsers field name not found.
This is what I did with the LEFT JOIN:
SELECT
cl.phone_number,
callerid,
qp.call_list_id,
startttime,
camapign_id,
q.queueName,
c.name as call_type,
connecttime,
endtime,
qp.duration,
fullduration,
ca.campName,
ct.call_termination_type,
qp.roll_id,
(e.first_name +' '+ e.last_name) employee,
i.name as cause_code,
box_id,
trunk_name,
qp.uuid,
customer_id,
s.name as telephnyservers
FROM cti.qpcdr qp
inner join cti.call_types c on qp.call_type_id = c.id
inner join cti.queues q on qp.queue_id = q.queueId
inner join cti.campaign ca on qp.camapign_id = ca.campaign_id
inner join cti.isdn_cause_code i On qp.cause_code = i.cause_code
inner join cti.employee e on qp.employee_id = e.employee_id
inner join cti.call_history ch On qp.call_list_id = ch.call_list_id
inner join cti.call_list cl on qp.call_list_id = cl.call_list_id
inner join cti.call_history_qpcdr_link cq On cq.call_history_id = ch.call_history_id
inner join cti.call_terminations ct On ch.call_termination_id = ct.call_termination_id
LEFT JOIN cti.server_settings ss ON ss.value = qp.box_id AND ss.attribute = 'asterisk_box_id'
LEFT JOIN cti.servers s ON s.id = ss.server_id
WHERE 1 = 1
AND ((@uuid IS NULL OR qp.uuid Like '%' + @uuid + '%')
AND (@callerlist IS NULL OR qp.call_list_id Like '%' + @callerlist + '%')
AND (@phone IS NULL OR cl.phone_number Like '%' + @phone + '%')
AND (@start IS NULL OR qp.startttime Like '%' + @start + '%')
AND (@Endd IS NULL OR qp.endtime Like '%' + @Endd + '%')
AND (@calltype IS NULL OR c.id Like '%' + @calltype + '%')
AND cl.phone_number IS NOT NULL)
END
I didn't want to make the query too long by posting how I tried the CROSS APPLY.
NEVERMIND Jeff.
I misspelled the alias.
The LEFT JOIN WORKED!!!!
YEA!!!
This is simply awesome; wow.
Even when I searched with a different parameter, it returned several rows and each row of s.name as teleservers has the correct value.
Thank you very, very much.
You are welcome - glad I could help.
As for the question on CROSS/OUTER APPLY - no these do not have an ON portion to the join. The APPLY is performed for every row.