Solved

Query help...

Posted on 2014-04-08
17
173 Views
Last Modified: 2014-04-30
Table1 exists:
Name	F1	F2	flag
A	7	3	c
B	13	4	c
C	0	6	c
A	11	8	e
B	19	9	e
C	4	0	e
			
Query1 to produce the following from Table1
Name	  F1	        F2	
A	7(11)	       3(8)	
B	13(19)	       4(9)	
C	-(4)	       6(-)	

Open in new window


Question: Could you please make SQL to produce Query1 from Table1?

The values in this query for columns F1 and F2 are displayed as c(e)  per "flag" field where null or zero values are substituted by "-".

Thank you.
0
Comment
Question by:Mike Eghtebas
  • 5
  • 4
  • 4
  • +3
17 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Assuming the flag has some meaning, create two queries.  One to select flag value c and the other to select flag value e.  Then create a third query that joins the first two.

Select q1.[Name], IIf(Nz(q1.F1,0) = 0 , "-", q1.F1) & "(" & IIf(Nz(q2.F1,0) = 0, "-", q2.F1) & ")" As fld1, IIf(Nz(q1.F2,0) = 0, "-", q1.F2) & "(" & IIf(Nz(q2.F2,0) = 0, "-", q2.F2) & "0" as fld2
From q1 inner join q2 on q1.[Name] = q2.[Name]
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
I made the following using single query for one field only, no errors but no result yet (which is an error by itself):

Select 
   c.[cmName]
   , IIf(Nz(c.onService,0) = 0 , "-", c.onService) & "(" & IIf(Nz(e.onService,0) = 0, "-", e.onService) & ")" As onServiceStr 
From (
   Select * From tStat Where CforCaseEforEvent="c") c 
   Inner Join  (Select * From tStat Where CforCaseEforEvent="e") e 
  on c.cmName=e.cmName

Open in new window


Is it possible to make this work?

Thanks,

Mike
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
place this codes in a regular module

Function getValues(vName As String, idx As Integer)
Dim rs As DAO.Recordset, sql As String, xVal As String

If idx = 1 Then
sql = "select f1 from table3 where [name]='" & vName & "' order by flag"
Else
sql = "select f2 from table3 where [name]='" & vName & "' order by flag"

End If

Set rs = CurrentDb.OpenRecordset(sql)
Do Until rs.EOF
    If xVal = "" Then
    xVal = IIf(rs(0) = 0 Or IsNull(rs(0)), "-", rs(0))
    Else
    xVal = xVal & "(" & IIf(rs(0) = 0 Or IsNull(rs(0)), "-", rs(0)) & ")"
    
    End If
rs.MoveNext
Loop
getValues = xVal
End Function

Open in new window



then create a query like this

SELECT Table1.Name, getvalues([name],1) AS F1, getvalues([name],2) AS F2
FROM Table1
GROUP BY Table1.Name, getvalues([name],1), getvalues([name],2);


.
0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
Comment Utility
SELECT	c.name
,	F1	= isnull(nullif(cast(c.F1 as varchar),0),'-')
		+ '('
		+ isnull(nullif(cast(e.F1 as varchar),0),'-')
		+ ')'
,	F2	= isnull(nullif(cast(c.F2 as varchar),0),'-')
		+ '('
		+ isnull(nullif(cast(e.F2 as varchar),0),'-')
		+ ')'
FROM	table1	c 
JOIN	table1	e 	ON	c.name = e.name
			AND	c.flag = 'c'
			AND	e.flag = 'e'

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
not entirely certain of access syntax, but it appears to be a group by that's needed with min() and max() values concatenated

select
  name
, min( IIf(Nz(F1,0) = 0 , "-", F1) ) & '(' + max( IIf(Nz(F1,0) = 0 , "-", F1) ) & ')' as F1
, min( IIf(Nz(F2,0) = 0 , "-", F2) ) & '(' + max( IIf(Nz(F2,0) = 0 , "-", F2) ) & ')' as F2
from yourtable
group by name
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
now checking the solution.

Hi Paul,

FYI, I will be using this in SQL Server. I am in process of converting an Access prototype application to asp version with SQL Server as backend.

Thanks,

Mike
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
mmmm, not quite what you asked for
    INSERT INTO yourtable
    	([Name], [F1], [F2], [flag])
    VALUES
    	('A', 7, 3, 'c'),
    	('B', 13, 4, 'c'),
    	('C', 0, 6, 'c'),
    	('A', 11, 8, 'e'),
    	('B', 19, 9, 'e'),
    	('C', 4, 0, 'e')
    ;

**Query 1**:

    SELECT
      name
    ,   MIN( CASE WHEN ISNULL(F1,0) = 0 THEN '-' ELSE convert(varchar(5),F1) END )
      + '('
      + MAX( CASE WHEN ISNULL(F1,0) = 0 THEN '-' ELSE convert(varchar(5),F1) END )
      + ')'
    ,   MIN( CASE WHEN ISNULL(F2,0) = 0 THEN '-' ELSE convert(varchar(5),F2) END )
      + '('
      + MAX( CASE WHEN ISNULL(F2,0) = 0 THEN '-' ELSE convert(varchar(5),F2) END )
      + ')'
    FROM yourtable
    GROUP BY name
    	
    	
    

**[Results][2]**:
    
    | NAME | COLUMN_1 | COLUMN_2 |
    |------|----------|----------|
    |    A |    11(7) |     3(8) |
    |    B |   13(19) |     4(9) |
    |    C |     -(4) |     -(6) |



  [1]: http://sqlfiddle.com/#!3/a7ce1/4

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
closer...
    INSERT INTO yourtable
    	([Name], [F1], [F2], [flag])
    VALUES
    	('A', 7, 3, 'c'),
    	('B', 13, 4, 'c'),
    	('C', 0, 6, 'c'),
    	('A', 11, 8, 'e'),
    	('B', 19, 9, 'e'),
    	('C', 4, 0, 'e') 
    ;

**Query 1**:

    SELECT
          name
        ,   CASE WHEN min(ISNULL(F1,0)) = 0 THEN '-' ELSE convert(varchar(5),min(f1)) END
          + '('
          + CASE WHEN max(ISNULL(F1,0)) = 0 THEN '-' ELSE convert(varchar(5),max(f1)) END
          + ')'
        ,   CASE WHEN min(ISNULL(F2,0)) = 0 THEN '-' ELSE convert(varchar(5),min(f2)) END
          + '('
          + CASE WHEN max(ISNULL(F2,0)) = 0 THEN '-' ELSE convert(varchar(5),max(f2)) END
          + ')'
    FROM yourtable
    GROUP BY name
    

**[Results][2]**:
    
    | NAME | COLUMN_1 | COLUMN_2 |
    |------|----------|----------|
    |    A |    7(11) |     3(8) |
    |    B |   13(19) |     4(9) |
    |    C |     -(4) |     -(6) |



  [1]: http://sqlfiddle.com/#!3/86767/1

Open in new window

Not sure how to get 6(-) for C / F2
Are you certain it's not -(6) ?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 31

Expert Comment

by:awking00
Comment Utility
What are the data types of F1 and F2?
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Is it possible to make this work?
Get rid of the subselect and use a straight join of the two queries.  That will allow you to test the individual pieces to see where the error is.

1.  Is your question - what is the syntax for formatting the fields as --- -(4)?  I showed you how to do it with IIf()'s inside the query.  Rey offered a function -  I don't created functions unless there is no other way since I use primarily SQL Server BE's and VBA/UDF functions cannot be sent to the server.  That means Access has to take apart the query and decide what can be sent and what can't and it doesn't always make the correct decision which causes Access to request way too much data.
2. Are you trying to do this with Access SQL syntax?  I ask because several folks have posted T-SQL solutions because you posted the question to the SQL Server forum.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
@Pat see ID: 39987516
>>"FYI, I will be using this in SQL Server. I am in process of converting an Access prototype application to asp version with SQL Server as backend."

additionally a self join isn't going to produce 3 rows
    CREATE TABLE yourtable
    	([Name] varchar(1), [F1] int, [F2] int, [flag] varchar(1))
    ;
    	
    INSERT INTO yourtable
    	([Name], [F1], [F2], [flag])
    VALUES
    	('A', 7, 3, 'c'),
    	('B', 13, 4, 'c'),
    	('C', 0, 6, 'c'),
    	('A', 11, 8, 'e'),
    	('B', 19, 9, 'e'),
    	('C', 4, 0, 'e') 
    ;

**Query 1**:

    select
    *
    From yourtable q1 
    inner join yourtable q2 on q1.[Name] = q2.[Name]
    

**[Results][2]**:
    
    | NAME | F1 | F2 | FLAG |
    |------|----|----|------|
    |    A |  7 |  3 |    c |
    |    A | 11 |  8 |    e |
    |    B | 13 |  4 |    c |
    |    B | 19 |  9 |    e |
    |    C |  0 |  6 |    c |
    |    C |  4 |  0 |    e |
    |    A |  7 |  3 |    c |
    |    A | 11 |  8 |    e |
    |    B | 13 |  4 |    c |
    |    B | 19 |  9 |    e |
    |    C |  0 |  6 |    c |
    |    C |  4 |  0 |    e |

Open in new window

0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
The self join produces a Cartesian Product.  You need to get set 1 and set 2 from the table and join set 1 to set 2.  Set 1 contains the records with the c flag.  set 2 contains the records with the e flag.  If you do this with SQL Server, you could use views instead of queries.  View 1 would select the c records, view 2 would select the e records.

If you have only the two flag values then the following self join might work:

select
    *
    From yourtable q1
    inner join yourtable q2 on q1.[Name] = q2.[Name] and q1.[Flag] <> q2.[Flag]

Also,
If you only want T-SQL syntax, you should specify that and not even post in the Access forum.  The functions I used to format the calculated column will not work in T-SQL either.  

Just because the BE is SQL Server does not mean that you can't use querydefs assuming that the FE will be Access.  If the FE is not Access and the BE is not Access, then the question is in the wrong category.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Good point regarding the topics Pat.

Regarding the self-join, my intended point was that a self-join isn't wanted for the solution as there are only 3 rows in the expected result (from the original 6). To reduce the rows a GROUP BY is required.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
A group by will not reduce the Cartesian Product to what the OP requested but my additional criteria should.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
Rey Obrero and PatHartman,
The final code will be transformed to be used in SQL Server. I have nor mentioned this in my original question (the first phase of my question). In the follow up question I am preparing to post shortly, I will request stored proc solution.

John_Vidmar,
Your solution works perfectly.


PortletPaul,
re:> Not sure how to get 6(-) for C / F2.... Are you certain it's not -(6) ?

Answer: The patern is c-value(e-value) which means we need to have 6(-) not -(6).

Your solution (ID: 39987537) works but it takes longer to display the results.

re:> If you only want T-SQL syntax, you should specify that and not even post in the Access forum.

Answer: You are right. This is what I will do next. This will be the second phase of my initial question.

awking00,
re:> What are the data types of F1 and F2?

Answer: They are integer.

================
I will close this question shortly.

These discussions have helped me with the direction the application needs to take.

tSource:
Name	F1	F2	flag
A	7	3	c
B	13	4	c
C	0	6	c
A	11	8	e
B	19	9	e
C	4	0	e
			
tTarget
Name	  F1	        F2	
A	7(11)	       3(8)	
B	13(19)	       4(9)	
C	-(4)	       6(-)

Open in new window


Now, in the new question I will post in SQL Server section shortly, I will ask for help to put together a stored procedure (possibly using cursor) to take data from tSource and put it in tTarget. This way, I can schedule to run the stored procedure once every hour (or on demand) instead of with each changes users make to the data.

Thank you all for very helpful inputs.

Mike

Here is the link to the new question:
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28410672.html
0
 
LVL 33

Author Closing Comment

by:Mike Eghtebas
Comment Utility
Sorry for the delay and thank you for good comments.

As the logo of EE (a race car flag indicates), the first (fastest) solution gets the points.

Regards,

Mike
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

771 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

11 Experts available now in Live!

Get 1:1 Help Now