Solved

Help with SQL using C# and DataGrid...

Posted on 2007-11-21
5
1,197 Views
Last Modified: 2009-12-16
At the minute I have a sql query extracting info from an oracle table, and the sql  looks like this:

string openSql = "SELECT DISTINCT HGR6.CALL_REQ.PERSID, HGR6.CALL_REQ.REF_NUM, HGR6.CALL_REQ.STATUS, HGR6.CALL_REQ.ASSIGNEE, HGR6.CALL_REQ.OPEN_DATE FROM HGR6.CALL_REQ WHERE HGR6.CALL_REQ.GROUP_ID='" + teamID + "'  AND (HGR6.CALL_REQ.OPEN_DATE >= '" + startSeconds.TotalSeconds+"') AND (HGR6.CALL_REQ.OPEN_DATE <= '"+endSeconds.TotalSeconds+"')AND HGR6.CALL_REQ.STATUS <> 'CL' AND HGR6.CALL_REQ.PARENT IS NULL";

At the minute I am binding the results to a datagrid in asp.net, the resulting table looks like this:

PERSID                REF_NUM          STATUS          ASSIGNEE            OPEN_DATE
cr:160709287     5992436               RIP                 55864293           1195494904
cr:160814117     5999741               ESC                12345678          1195573843
cr:160878008     6004760               ESC                 73773767        1195600721
cr:160885707     6005207              ESC                                           1195610604

Now, the assignee here is sometimes blank - that is ok.  But where the assignee is not blank, the assignee is an ID, I need to obtain the persons name from another table using SQL.
Here is the SQL I need to use:

string assigneeSql = "SELECT HGR6.C_LAST_NAME, HGR6.C_FIRST_NAME FROM HGR6.CTCT WHERE HGR6.ID =  '  assignne from the list '   ";

is there a way to do this ALL IN ONE SQL statement so it will do this BEFORE I bind it to the Datagrid?

Thanks
                        
0
Comment
Question by:sterankin
  • 2
  • 2
5 Comments
 
LVL 7

Assisted Solution

by:lucky_james
lucky_james earned 25 total points
ID: 20326823
problem is you want the assingee name instead of his ID.

solution is :
select ......, case asigneeID when NOT NULL then B.C_LAST_NAME, B.C_FIRST_NAME...........
FROM HGR6.CALL_REQ as A Join HGR6.CTCT as B on A.ASSIGNEE = B.ID

In short, use join and case advantages.

More on Case:
http://www.1keydata.com/sql/sql-case.html

On joins:
http://www.w3schools.com/sql/sql_join.asp
0
 
LVL 19

Accepted Solution

by:
Limbeck earned 50 total points
ID: 20326857
hi,
use a left join,something like this (not tested; might have a typo)

string openSql = "SELECT DISTINCT HGR6.CALL_REQ.PERSID, HGR6.CALL_REQ.REF_NUM, HGR6.CALL_REQ.STATUS, HGR6.CALL_REQ.ASSIGNEE, HGR6.CALL_REQ.OPEN_DATE,HGR6.CTCT.C_LAST_NAME, HGR6.CTCT.C_FIRST_NAME FROM HGR6.CALL_REQ
LEFT JOIN HGR6.CTCT ON HGR6.CALL_REQ.ASSIGNEE=HGR6.CTCT.ID
WHERE HGR6.CALL_REQ.GROUP_ID='" + teamID + "'  AND (HGR6.CALL_REQ.OPEN_DATE >= '" + startSeconds.TotalSeconds+"') AND (HGR6.CALL_REQ.OPEN_DATE <= '"+endSeconds.TotalSeconds+"')AND HGR6.CALL_REQ.STATUS <> 'CL' AND HGR6.CALL_REQ.PARENT IS NULL";
0
 

Author Comment

by:sterankin
ID: 20326862
Getting an error now:
Exception Details: System.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC driver for Oracle][Oracle]ORA-00936: missing expression


this is the sql executed:

"SELECT DISTINCT HGR6.CALL_REQ.PERSID, HGR6.CALL_REQ.REF_NUM, HGR6.CALL_REQ.STATUS, CASE HGR6.CALL_REQ.ASSIGNEE when NOT NULL then B.C_LAST_NAME, B.C_FIRST_NAME, HGR6.CALL_REQ.OPEN_DATE FROM HGR6.CALL_REQ as A Join HGR6.CTCT as B on A.ASSIGNEE = B.ID WHERE HGR6.CALL_REQ.GROUP_ID='49540961'  AND (HGR6.CALL_REQ.OPEN_DATE >= '1193896800') AND (HGR6.CALL_REQ.OPEN_DATE <= '1195711200')AND HGR6.CALL_REQ.STATUS <> 'CL' AND HGR6.CALL_REQ.PARENT IS NULL"


any ideas?
0
 
LVL 7

Expert Comment

by:lucky_james
ID: 20326877
you forgot to put a END in the case.

syntax should be
case ......
when ......... then .........
when ........ then .......
else
............
end
0
 

Author Comment

by:sterankin
ID: 20326884

the left join worked!
0

Featured Post

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question