?
Solved

Help with SQL using C# and DataGrid...

Posted on 2007-11-21
5
Medium Priority
?
1,203 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

801 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