Click here to Skip to main content
15,867,308 members
Home / Discussions / Database
   

Database

 
AnswerMaybe this is a bad idea, so I abandoned it. Did a work around instead. Pin
jkirkerx21-Nov-22 9:35
professionaljkirkerx21-Nov-22 9:35 
AnswerRe: TSQL - Create a JOIN, knowing that it may not exist, but show the initial records at least instead of nothing [bad idea] - abandoned it Pin
Graham Breach21-Nov-22 10:29
Graham Breach21-Nov-22 10:29 
GeneralRe: TSQL - Create a JOIN, knowing that it may not exist, but show the initial records at least instead of nothing [solved] - May run with this Pin
jkirkerx21-Nov-22 11:04
professionaljkirkerx21-Nov-22 11:04 
AnswerRe: TSQL - Create a JOIN, knowing that it may not exist, but show the initial records at least instead of nothing [bad idea] - abandoned it Pin
Richard Deeming21-Nov-22 22:02
mveRichard Deeming21-Nov-22 22:02 
GeneralRe: TSQL - Create a JOIN, knowing that it may not exist, but show the initial records at least instead of nothing [bad idea] - abandoned it Pin
jkirkerx22-Nov-22 6:58
professionaljkirkerx22-Nov-22 6:58 
QuestionOrder by several static string values, error "Conversion failed when converting the varchar value 'FX' to data type int" Pin
jkirkerx14-Nov-22 10:44
professionaljkirkerx14-Nov-22 10:44 
AnswerRe: Order by several static string values, error "Conversion failed when converting the varchar value 'FX' to data type int" [sort of solved] Pin
jkirkerx14-Nov-22 11:29
professionaljkirkerx14-Nov-22 11:29 
GeneralRe: Order by several static string values, error "Conversion failed when converting the varchar value 'FX' to data type int" [sort of solved] Pin
jsc4222-Nov-22 10:31
professionaljsc4222-Nov-22 10:31 
Instead of
jkirkerx wrote:
ORDER BY CASE
WHEN vendor.Delete_Flag = '1' THEN 0
WHEN proj_cost.pref_vendor <> 'Y' THEN 1
WHEN proj_cost.pref_vendor = 'Y' THEN 2

I'd rather do
SQL
ORDER BY
   vendor.Delete_Flag desc,   -- '1' then '0'
   proj_cost.pref_vendor asc  -- 'N' then 'Y'
than make the ORDER BY have to do CASE statements. This allows the optimiser to have greater control rather than second guessing how it works internally.

This is just a personal preference - there may be other respondents who would be horrified by this simplistic approach.

Your original
SQL
ORDER BY CASE 
    WHEN Proj_Job.job_type = 'FX' THEN 0
    WHEN Proj_Job.job_type = 'OP' THEN 1
    WHEN Proj_Job.job_type = 'EQ' THEN 2
END
is a bit harder, but dropping the test for 'EQ' and just having ELSE 2 might be easier / quicker. e.g.
SQL
ORDER BY CASE 
    WHEN Proj_Job.job_type = 'FX' THEN 0
    WHEN Proj_Job.job_type = 'OP' THEN 1
    ELSE 2
END

GeneralRe: Order by several static string values, error "Conversion failed when converting the varchar value 'FX' to data type int" [sort of solved] Pin
jkirkerx22-Nov-22 10:50
professionaljkirkerx22-Nov-22 10:50 
GeneralRe: Order by several static string values, error "Conversion failed when converting the varchar value 'FX' to data type int" [sort of solved] Pin
jsc4222-Nov-22 23:26
professionaljsc4222-Nov-22 23:26 
GeneralRe: Order by several static string values, error "Conversion failed when converting the varchar value 'FX' to data type int" [sort of solved] Pin
jkirkerx23-Nov-22 6:11
professionaljkirkerx23-Nov-22 6:11 
QuestionCan any body share the schema database for security and share. Pin
Nguyễn Tuấn Anh 9216-Nov-22 5:40
Nguyễn Tuấn Anh 9216-Nov-22 5:40 
AnswerRe: Can any body share the schema database for security and share. Pin
Sam Hobbs6-Nov-22 10:05
Sam Hobbs6-Nov-22 10:05 
AnswerRe: Can any body share the schema database for security and share. Pin
jschell14-Nov-22 6:49
jschell14-Nov-22 6:49 
QuestionHow to avoid duplicate records while using a UNION - SOLVED Pin
Richard Andrew x6428-Oct-22 15:50
professionalRichard Andrew x6428-Oct-22 15:50 
AnswerRe: How to avoid duplicate records while using a UNION - SOLVED Pin
Richard Deeming30-Oct-22 23:01
mveRichard Deeming30-Oct-22 23:01 
GeneralRe: How to avoid duplicate records while using a UNION - SOLVED Pin
Richard Andrew x6431-Oct-22 1:13
professionalRichard Andrew x6431-Oct-22 1:13 
AnswerRe: How to avoid duplicate records while using a UNION - SOLVED Pin
Andrea Simonassi2-Nov-22 19:55
Andrea Simonassi2-Nov-22 19:55 
AnswerRe: How to avoid duplicate records while using a UNION - SOLVED Pin
PIEBALDconsult6-Nov-22 5:54
mvePIEBALDconsult6-Nov-22 5:54 
GeneralRe: How to avoid duplicate records while using a UNION - SOLVED Pin
Richard Andrew x646-Nov-22 6:56
professionalRichard Andrew x646-Nov-22 6:56 
QuestionAzure Synapse Where myString like '%Pattern1%Pattern2%' Pin
j11codep3-Oct-22 15:02
j11codep3-Oct-22 15:02 
AnswerRe: Azure Synapse Where myString like '%Pattern1%Pattern2%' Pin
CHill606-Oct-22 1:41
mveCHill606-Oct-22 1:41 
QuestionSimple Unique Identity Value Pin
Richard Andrew x6430-Sep-22 10:30
professionalRichard Andrew x6430-Sep-22 10:30 
AnswerRe: Simple Unique Identity Value Pin
Richard Deeming2-Oct-22 22:04
mveRichard Deeming2-Oct-22 22:04 
GeneralRe: Simple Unique Identity Value Pin
Richard Andrew x646-Oct-22 2:00
professionalRichard Andrew x646-Oct-22 2:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.