Click here to Skip to main content
15,565,146 members
Home / Discussions / Database
   

Database

 
GeneralRe: TSQL - JOIN cancels out my date filter Pin
jschell28-Nov-22 11:44
jschell28-Nov-22 11:44 
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 10:15
professionaljkirkerx21-Nov-22 10:15 
AnswerMaybe this is a bad idea, so I abandoned it. Did a work around instead. Pin
jkirkerx21-Nov-22 10:35
professionaljkirkerx21-Nov-22 10: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 11:29
Graham Breach21-Nov-22 11: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 12:04
professionaljkirkerx21-Nov-22 12: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 23:02
mveRichard Deeming21-Nov-22 23: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 7:58
professionaljkirkerx22-Nov-22 7:58 
QuestionOrder by several static string values, error "Conversion failed when converting the varchar value 'FX' to data type int" Pin
jkirkerx14-Nov-22 11:44
professionaljkirkerx14-Nov-22 11:44 
I thought it would be clever to sort the results by FX, OP, and finally EQ.
I modeled this in SQL Manager and it runs fine, well not the version I posted. But when I run this in PHP off the DEV server, it gives me this error ....

"Conversion failed when converting the varchar value 'FX' to data type int"

Maybe I'm totally off on this one, and I don't understand the "THEN" in
(CASE WHEN Proj_Job.job_oper = 'FX' THEN 15 END)
, then what ...

I used the number 15, because it's the 15th select column.
I did search for quite awhile but the examples where more sophisticated than what I wanted to do.
SELECT
    proj_invoice.Recnum,
    proj_job.Recnum,<br />
    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,<br />
    proj_job.EST_COST, 
    proj_job.ACT_COST,<br />
    vendor.Company_Name,
    Catalog.Cat_Name,
    Catalog.Default_Vendor,
    Proj_Job.job_oper
 FROM proj_invoice
 RIGHT JOIN Proj_Job ON Proj_Job.Proj_ID = proj_invoice.Proj_ID
 RIGHT JOIN vendor ON proj_job.Vendor_id = proj_invoice.Vendor_ID
 RIGHT JOIN catalog ON catalog.cat_id = Proj_Job.Job_Oper
 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_oper = 'FX' THEN 15 END),
 (Case WHEN Proj_Job.job_oper = 'OP' THEN 15 END),
 (CASE WHEN proj_job.job_oper = 'EQ' THEN 15 END)";
If it ain't broke don't fix it
Discover my world at jkirkerx.com

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 12:29
professionaljkirkerx14-Nov-22 12: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 11:31
professionaljsc4222-Nov-22 11: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 11:50
professionaljkirkerx22-Nov-22 11: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
jsc4223-Nov-22 0:26
professionaljsc4223-Nov-22 0: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 7:11
professionaljkirkerx23-Nov-22 7:11 
QuestionCan any body share the schema database for security and share. Pin
Nguyễn Tuấn Anh 9216-Nov-22 6:40
Nguyễn Tuấn Anh 9216-Nov-22 6:40 
AnswerRe: Can any body share the schema database for security and share. Pin
Sam Hobbs6-Nov-22 11:05
Sam Hobbs6-Nov-22 11:05 
AnswerRe: Can any body share the schema database for security and share. Pin
jschell14-Nov-22 7:49
jschell14-Nov-22 7:49 
QuestionHow to avoid duplicate records while using a UNION - SOLVED Pin
Richard Andrew x6428-Oct-22 16:50
professionalRichard Andrew x6428-Oct-22 16:50 
AnswerRe: How to avoid duplicate records while using a UNION - SOLVED Pin
Richard Deeming31-Oct-22 0:01
mveRichard Deeming31-Oct-22 0:01 
GeneralRe: How to avoid duplicate records while using a UNION - SOLVED Pin
Richard Andrew x6431-Oct-22 2:13
professionalRichard Andrew x6431-Oct-22 2:13 
AnswerRe: How to avoid duplicate records while using a UNION - SOLVED Pin
Andrea Simonassi2-Nov-22 20:55
Andrea Simonassi2-Nov-22 20:55 
AnswerRe: How to avoid duplicate records while using a UNION - SOLVED Pin
PIEBALDconsult6-Nov-22 6:54
professionalPIEBALDconsult6-Nov-22 6:54 
GeneralRe: How to avoid duplicate records while using a UNION - SOLVED Pin
Richard Andrew x646-Nov-22 7:56
professionalRichard Andrew x646-Nov-22 7:56 
QuestionAzure Synapse Where myString like '%Pattern1%Pattern2%' Pin
j11codep3-Oct-22 16:02
j11codep3-Oct-22 16:02 
AnswerRe: Azure Synapse Where myString like '%Pattern1%Pattern2%' Pin
CHill606-Oct-22 2:41
mveCHill606-Oct-22 2:41 
QuestionSimple Unique Identity Value Pin
Richard Andrew x6430-Sep-22 11:30
professionalRichard Andrew x6430-Sep-22 11:30 

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.