question

David_G avatar image
David_G asked

How to subquery in query

Hi all,

I am a total newbie to SQL and am struggling to do a hopefully basic task! :(.

I have written code that brings back the correct results but I then need to query on he results, adding a field and then performing a lookup.

My issue is that I don't have a scoobie doo on how to do the last task???

Can anyone possibly help?

USE [xxx]

SELECT StageRuleGroups.RuleGroupDescription, StageRuleProfiles.RuleProfileName, StageRules.RuleDescription AS [Description], StageRules.RuleName AS [NS Entity Code],

StageRules.OutputValue AS [OS Entity Code],

FROM MemberProperty RIGHTOUTERJOIN

StageRuleProfileMembers LEFTOUTERJOIN

StageRuleGroups LEFTOUTERJOIN

StageRules ON StageRuleGroups.UniqueID = StageRules.RulesGroupKey ON

StageRuleProfileMembers.RuleGroupKey = StageRuleGroups.UniqueID RIGHTOUTERJOIN

StageRuleProfiles ON StageRuleProfileMembers.RuleProfileKey = StageRuleProfiles.UniqueID LEFTOUTERJOIN

Member ON Member.Name= StageRules.OutputValue ON

MemberProperty.MemberId = Member.MemberId

WHERE (StageRuleGroups.RuleGroupName ='NetSuite_Entities')AND(StageRuleProfiles.RuleProfileName =N'TR_MFI_MNGT_NS')AND(MemberProperty.DecimalValue =-100)

ORDERBY StageRules.RuleName

THIS IS MY ISSUE PART - I NEED TO THEN RESELECT THE DECIMAL VALUE FO RTHE RESULTS ABOVE AND PERFORM THE LOOKUP AS PER BELOW?????

CASE

WHEN MemberProperty.DecimalValue = 0 THEN'AED'

WHEN MemberProperty.DecimalValue = 1 THEN'AFN'

WHEN MemberProperty.DecimalValue = 2 THEN'ALL'

WHEN MemberProperty.DecimalValue = 3 THEN'AMD'

WHEN MemberProperty.DecimalValue = 4 THEN'ANG'

WHEN MemberProperty.DecimalValue = 5 THEN'AOA'

WHEN MemberProperty.DecimalValue = 6 THEN'ARS'

ENDAS SourceCurrency

sql querysubquery
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Jon Crawford avatar image
Jon Crawford answered

your SELECT doesn't include that. Put your CASE as the last field in your SELECT (or wherever you want it) and you have your solution

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.