Solved

Help with SQL using C# and DataGrid...

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

732 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