Solved

Help with SQL using C# and DataGrid...

Posted on 2007-11-21
5
1,182 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now