Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ralph.devlin
Starting Member
4 Posts |
 Posted - 2008-07-21 : 19:00:43
|
| I have two requests. First is there a syntax to make any NULL value returned in a SQL Query to show as blank rather than NULL.Second, We use SQL for our ticketing system to log helpdesk requests. We then pull that data out using the raw data into Crystal, or sometimes use views to get the data formatted correctly. I am trying to do a SQL query that returns Parent Work Orders that are still open, while the Child Work Orders are closed. Below is raw data from the table. that includes the necessary columns:WO_NUM STATUS PARENTWOID WOID WORKORDERTYPEID175073 NULL 175073 175073 0175074 NULL 175073 175074 1175075 NULL 175073 175075 1These are currently open tickets. The 0 in workorderTYPEID means that it is a parent ticket. The PARENTWOID indicates what ticket is the parent of it. If it was closed then the status would be completed.Any help to only select only the parent tickets which are open or NULL that have child tickets closed would be great. ThanksRalph |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-21 : 19:43:37
|
1) Yes. COALESCE or ISNULL will substitute NULL values to another predefined value.2) How many levels of parents can there be? Infinite? Maximum two levels (Parent-Child)? Maximum three levels (Grandparent-Parent-Child)?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
ralph.devlin
Starting Member
4 Posts |
Posted - 2008-07-22 : 01:13:43
|
| There can be an infinite number of childs, so I was thinking it may have to almost be a loop. Most queries I can do no problem, but to do this I'm almost thinking variables would have to be used as well, not sure. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-22 : 03:31:13
|
You can write a recursive cte to handle the parent-child resultset.See Books Online for more details.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-22 : 05:25:26
|
| http://msdn.microsoft.com/en-us/library/ms186243.aspx |
|
|
|
ralph.devlin
Starting Member
4 Posts |
Posted - 2008-07-22 : 17:37:42
|
| Thanks. Would it still be necessary to write a CTE to pull out what is parent to what is child when the raw data tells me which WO_NUM is the parent ticket on each WO and the WORKORDERTYPEID tells me if it is 0 that it is a parent ticket and if if equals a 1 it is a child ticket? Using that data I just need it to analyze each of the Child tickets = 1 and if they are closed to look at the parent ticket = 0 and is references in the PARENTWOID column and then be selected if it is still open when the child tickets are closed.Ralph |
|
|
|
|
|
|
SQL Query Request