Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

Sorting Dynmaic sub-Query with condition

Hi all

Given the code below.. How can I use the column name in a case or if statment.
I need to send a specific code if Text1 column appear in the dynamic query.
i.e if  (ColumnName.'Text1' = Text1)
  begin
          process something
End
if  (ColumnName.'Text2' = Text2)
  begin
          process something
End

Thanks in Advance

SELECT
	@DynamicQuery = ISNULL(@DynamicQuery+' UNION ','') 
	+ 'SELECT  CONVERT(varchar(max),'+COLUMN_NAME+') as varColumnName, 
	'+@VarCusHeaderID+' as fk_Header_ID,  
	'+@varProviderID+' as fk_ProviderID, 
	'+@varCurIndex+' as PositionIndex, 
	'+@varIsStatus+' as IsStatus		    
	FROM '+@TransactionTable+
	' y' +ISNULL('  WHERE '+@TableWhere,'')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (table_name = @TransactionTable
AND COLUMN_NAME IN (
				'Text1',
				'Text2',
				'Text3',
				'Text4',
				'Text5',
				'Text6',
				'Text7',
				'Text8',
				'Text9',
				'Text10'
				)
			)
ORDER BY SUBSTRING(COLUMN_NAME,5,2)

Open in new window

0
ZURINET
Asked:
ZURINET
  • 3
  • 2
1 Solution
 
Harish VargheseProject LeaderCommented:
Hello,

Where do you want to use the CASE or IF statement. Is it within the above SELECT statement? One point to note is that you cannot use IF statement inside a SELECT statement. And you may not replace the functionality of IF statement by a CASE. Please exlain in detail what you really want to achieve.

-Harish
0
 
ZURINETAuthor Commented:
Hi Harish

One can use If in a select statement.. see code below

Here is what I want to achive..
given the following columns ('Text1_','Text2_', 'Text10_'.. ..)

Since I am sorting the column using ORDER BY SUBSTRING(COLUMN_NAME,4,1)
and I have columns with  Text1_ & Text10_
Both are returning the same index..
Hence I need a way to make sure that for the indexposition.. I will reference Text10_ as position 10 and not as position 1 as the case may be..









SELECT CAST(
             CASE 
                  WHEN Obsolete = 'N' or InStock = 'Y' 
                     THEN 1 
                  ELSE 0 
             END AS bit) as Salable, * 
FROM Product

Open in new window

0
 
Harish VargheseProject LeaderCommented:
Hello,

The code you showed in your previous post uses CASE, not IF.

Referring back to your previous question about same SQL query, I dont think you will get desired result by adding an order by at the end of your query as ORDER BY SUBSTRING(COLUMN_NAME,5,2), since that will only sort the SELECT statements in your dynamic query, not your result.

Coming back to your new question about Text1_ & Text10_. You can take two characters from 5th position, remove "_" and then convert to integer as below:
ORDER BY Convert(int, Replace(SUBSTRING(COLUMN_NAME,5,2) , '_', ''))

-Harish
0
 
ZURINETAuthor Commented:
It is not working.. but it is a good idea
0
 
Harish VargheseProject LeaderCommented:
Did you try my code to use a separate id to sort the data in your previous question? Please post a new question if you are still looking for a solution.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now