Click here to Skip to main content
16,020,714 members
Home / Discussions / Database
   

Database

 
QuestionTSQL - Create a JOIN, knowing that it may not exist, but show the initial records at least instead of nothing [bad idea] - abandoned it Pin
jkirkerx21-Nov-22 9:15
professionaljkirkerx21-Nov-22 9:15 
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 
I had the wrong column name to sort on.

[edit 11/21/2022]
But I worked on it some more and figured out the logic behind doing things like this. And applied it to another database call where I wanted vendors that went out of business first or marked as deleted with a delete flag, and then vendors that are not marked deleted, and finally the default vendor at the end and it works pretty good.

This ORDER BY CASE is pretty slick, and is coming in very handy. Maybe in the future if I think about it more, I can figure out how to handle vendors that retired, and still show them in past projects and reports, yet move on to the new vendors that replace them.
SELECT
    proj_invoice.Recnum,
    proj_job.Recnum,    
    proj_invoice.Proj_ID,
    proj_invoice.Invoice_Class,
    proj_invoice.Invoice_Oper,
    proj_invoice.Invoice_Number, 
    proj_invoice.Invoice_Date, 
    proj_invoice.Invoice_Desc, 
    proj_invoice.Invoice_Amt, 
    proj_invoice.Due_Date,
    proj_invoice.Vendor_Id,
    proj_job.EST_COST, 
    proj_job.ACT_COST,            
    Catalog.Cat_ID,
    Catalog.Cat_Name,
    Catalog.Default_Vendor            
FROM proj_invoice
RIGHT JOIN Proj_Job ON Proj_Job.Proj_ID = proj_invoice.Proj_ID AND Proj_Job.Job_Oper = Proj_Invoice.Invoice_Oper
RIGHT JOIN catalog ON catalog.cat_id = Proj_Job.Job_Oper            
RIGHT JOIN vendor ON proj_job.Vendor_id = proj_invoice.Vendor_ID        
WHERE proj_invoice.Proj_ID = '$projectNumber'
AND CONVERT(VARCHAR, proj_invoice.Delete_Flag) <> '1' 
AND Proj_Job.EST_COST <> 0.00 
AND Proj_Job.ACT_COST = proj_invoice.Invoice_Amt 
AND vendor.Vendor_ID = proj_invoice.Vendor_ID
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

This one is slick on the ORDER BY CASE
SELECT 
    proj_cost.Vend_ID, 
    proj_cost.pref_vendor,
    vendor.Delete_Flag
FROM proj_cost 
INNER JOIN vendor ON proj_cost.Vend_ID = vendor.Vendor_ID
WHERE proj_cost.Proj_ID = '$projectNumber' 
AND proj_cost.Proj_Stage = '$projectStage' 
AND proj_cost.Vers_ID = '$versionNumber' 
AND proj_cost.Task_Oper = '$taskOperator'
GROUP BY proj_cost.Vend_Id, proj_cost.pref_vendor, vendor.Delete_Flag 
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

END
If it ain't broke don't fix it
Discover my world at jkirkerx.com


modified 21-Nov-22 17:22pm.

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
jsc4222-Nov-22 10:31 
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
jsc4222-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 

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.