Build SQL on fly with constant value for a column 2

[Earth]

Hi,

I believe that a query such as:
SELECT AsAtDate, BankMnemonic='ALL', StartOfDay etc etc

Will return the relevant rows with a column named "BankMnemonic" filled with the constant 'ALL' for each row (as well as the other normal columns).

I'm attempting to build my SQL statement on the fly:

SET @SQL = 'SELECT AsAtDate, BankMnemonic, StartOfDay, etc etc'

How do I combine these two things? ie. set BankMnemonic to be equal to 'ALL', but also build query on fly, without confusing SQL with all the apostrophes?

SET @SQL = 'SELECT AsAtDate, BankMnemonic=' + 'All' + ', StartOfDay, etc etc '

That's what I'm trying atm, but is not working...

Thanks for your help.

 

[essa2000]

Dear ;

I think you are trying to display ALL in the column BankMnemonic so it can be done like this.

SELECT AsAtDate, 'ALL' as BankMnemonic , StartOfDay from ABC

It will return data of AsAtDate and StartOfDay and it will return All constant word in the Column BankMnemonic.

Please , let me know I am getting it in the right direction or you want to do something else. i.e. you want to combine constant word All after the values of BankMnemonic. If you are doing so then you will have to use this SQL .

SELECT AsAtDate, BankMnemonic + '=All' , StartOfDay from ABC

Please , let know if you still have problem in it.

Regards,
Muhammad Essa Mughal

 

[Earth]

Essa,

The problem is that SQL gets confused with the apostrophes you have surrounding ALL when I try to dynamically build the SQL statement.

Thanks for your help though.

 

[tlbroadbent]

You can use two single quotes around ALL.

SET @SQL =
'SELECT AsAtDate, BankMnemonic=''All'', StartOfDay, etc '

You can use char(39) which for the sinle quotes around ALL.

SET @SQL =
'SELECT AsAtDate, BankMnemonic=' +
char(39) +'All' + char(39) +
', StartOfDay, etc ' Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Earth]

That works perfectly Broadbent, thanks.