Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Help with SQL using C# and DataGrid...

Posted on 2007-11-21
5
Medium Priority
?
1,204 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 7

Assisted Solution

by:lucky_james
lucky_james earned 100 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 200 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

604 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