Determining which columns to return

[pmcdaniel]

I want to write a SP that only returns certain rows when other rows are equal to 'Y'. Is there a way to do this and if so can someone tell me how?

thanks much

 

[bborissov]

??????
Are you speak about ROWs or COLUMNs?

Not sure what you want, can you post some simple data and desired result from it?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[pmcdaniel]

Oops, sorry, I mean column!

 

[markros]

select * from myTable where myColumnName = 'Y'

 

[pmcdaniel]

Here's an example of what I'm talking about:
If column1 = y select column1 & column2
If column3 = y select column3 & column4
If column5 = y select column5 & column6
If column7 = y select column7 & column8

If say column3 = n I don't want column4 to even be included in the recordset.

I'll only be retrieving one row at a time.

 

[markros]

In this case I would probably do this as a UNION, e.g.

select Column1, Column2 from myTable where Column1 = 'Y'
union all
select Column3, Column4 from myTable where Column3 = 'Y'
etc.

The Column1, Column3, etc. should be the same type and column2, column4, etc. should be the same type too.

 

[gmmastros]

Select Case When Column1 = 'Y' Then Column1
            When Column3 = 'Y' Then Column3
            When Column5 = 'Y' Then Column5
            When Column7 = 'Y' Then Column7
            End As Alias1Column,
       Case When Column1 = 'Y' Then Column2
            When Column3 = 'Y' Then Column4
            When Column5 = 'Y' Then Column6
            When Column7 = 'Y' Then Column8
            End As Alias2Column
From....

This is effectively the same as markros' advice, but this one will (probably) do one index scan instead of multiple index scans.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

pmcdaniel,
are all columns with the same type?
Isn't it easier to handle that in your front end?
I mean, get whole row and put the logic in your frontend what you want to display.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[pmcdaniel]

bborissov, The Y/N columns are of the same type, the columns I may or may not be returning can be of different type.

I currently use the front end and I'm beginning to think staying that way probably would be easier. I was just hoping there'd be a more efficient way through SQL....by not retrieving fields I don't need.

 

[bborissov]

Than you can't use UNION or you need to CAST so all fields to be the same type.
But i need to ask you:
OK, from your frontend you you call that SP but HOW do you know which fields will be returned?
Sometimes they will be Col1 and Col3 sometimes they will be Col2 and Col4, sometimes the be CHAR() and INT, sometimes they will be datetime and varbinary (that is just example not need to be true). But if you put the logic in your frontend you will know what you should expect.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[pmcdaniel]

In my VB code I'm always returning every column. If a column is equal to Y I'm doing something with the next column. If it's N I'm ignoring.....

Code snippet:

With rsSelectValues
  .CursorLocation = adUseClient
  .Open strSQL, gCn1, adOpenStatic

  If Not .EOF And Not .BOF Then
    .MoveFirst
    intCounter = 0
    Do Until .Fields.Item(intCounter).Name = "EOF"
      strFieldName = .Fields.Item(intCounter).Name
      If .Fields(strFieldName).Value = "Y" Then          
        intCounter = intCounter + 1
        [COLOR=red] 'Do something with next field. [/color]
      End If
      intCounter = intCounter + 1
    Loop
  End If
End With