May 18, 2010 at 5:32 pm
Can someone convert the following to SQL for me? I'm new to SQL and am stuck. I am running into a problem with the "balance" and "Where" parts.
SELECT
dbo_PURCHASE_ORDER.VENDOR_ID, dbo_VENDOR.NAME, dbo_PURCHASE_ORDER.ID, dbo_PURC_ORDER_LINE.LINE_NO, dbo_PURC_ORDER_LINE.PART_ID, dbo_PURC_ORDER_LINE.VENDOR_PART_ID, dbo_PURC_ORDER_LINE.ORDER_QTY, dbo_PURC_ORDER_LINE.TOTAL_USR_RECD_QTY, dbo_PURC_ORDER_LINE.MFG_NAME, dbo_PURC_ORDER_LINE.MFG_PART_ID, dbo_PURC_ORDER_LINE.SERVICE_ID, dbo_PURC_ORDER_LINE.DESIRED_RECV_DATE, dbo_PURC_ORDER_LINE.PROMISE_DATE, dbo_PURC_ORDER_LINE.UNIT_PRICE, dbo_PURCHASE_ORDER.BUYER, dbo_PURCHASE_ORDER.STATUS, dbo_PURC_ORDER_LINE!ORDER_QTY-dbo_PURC_ORDER_LINE!TOTAL_USR_RECD_QTY AS Balance, ([Balance]*[UNIT_PRICE]) AS extended
FROM
(dbo_PURCHASE_ORDER INNER JOIN dbo_VENDOR ON dbo_PURCHASE_ORDER.VENDOR_ID=dbo_VENDOR.ID) INNER JOIN dbo_PURC_ORDER_LINE ON dbo_PURCHASE_ORDER.ID=dbo_PURC_ORDER_LINE.PURC_ORDER_ID
WHERE
(((dbo_PURCHASE_ORDER.STATUS)="R") And ((dbo_PURC_ORDER_LINE!ORDER_QTY-dbo_PURC_ORDER_LINE!TOTAL_USR_RECD_QTY)>0))
ORDER BY dbo_PURCHASE_ORDER.VENDOR_ID;
May 18, 2010 at 9:02 pm
I think this is what you need:
SELECT
dbo.PURCHASE_ORDER.VENDOR_ID,
dbo.VENDOR.NAME,
dbo.PURCHASE_ORDER.ID,
dbo.PURC_ORDER_LINE.LINE_NO,
dbo.PURC_ORDER_LINE.PART_ID,
dbo.PURC_ORDER_LINE.VENDOR_PART_ID,
dbo.PURC_ORDER_LINE.ORDER_QTY,
dbo.PURC_ORDER_LINE.TOTAL_USR_RECD_QTY,
dbo.PURC_ORDER_LINE.MFG_NAME,
dbo.PURC_ORDER_LINE.MFG_PART_ID,
dbo.PURC_ORDER_LINE.SERVICE_ID,
dbo.PURC_ORDER_LINE.DESIRED_RECV_DATE,
dbo.PURC_ORDER_LINE.PROMISE_DATE,
dbo.PURC_ORDER_LINE.UNIT_PRICE,
dbo.PURCHASE_ORDER.BUYER,
dbo.PURCHASE_ORDER.STATUS,
dbo.PURC_ORDER_LINE.ORDER_QTY-dbo.PURC_ORDER_LINE.TOTAL_USR_RECD_QTY AS Balance,
((dbo.PURC_ORDER_LINE.ORDER_QTY-dbo.PURC_ORDER_LINE.TOTAL_USR_RECD_QTY)*[UNIT_PRICE]) AS extended
FROM
(dbo.PURCHASE_ORDER INNER JOIN
dbo.VENDOR ON
dbo.PURCHASE_ORDER.VENDOR_ID=dbo.VENDOR.ID) INNER JOIN
dbo.PURC_ORDER_LINE ON
dbo.PURCHASE_ORDER.ID=dbo.PURC_ORDER_LINE.PURC_ORDER_ID
WHERE
(
(
(dbo.PURCHASE_ORDER.STATUS)="R"
) And
(
(dbo.PURC_ORDER_LINE.ORDER_QTY-dbo.PURC_ORDER_LINE.TOTAL_USR_RECD_QTY)>0
)
)
ORDER BY
dbo.PURCHASE_ORDER.VENDOR_ID;
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 19, 2010 at 4:55 am
Hey,u just copied the same query and rearranged it neatly,but dont u think the status value 'R' should be placed within single quotes?
(dbo.PURCHASE_ORDER.STATUS)='R'
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
May 19, 2010 at 6:06 am
sunitkrishna (5/19/2010)
Hey,u just copied the same query and rearranged it neatly,but dont u think the status value 'R' should be placed within single quotes?(dbo.PURCHASE_ORDER.STATUS)='R'
Actually I made several changes. The OP wanted the query written to work in SQL Server so here they are:
1. Change "dbo_" to "dbo.". When you link a table in Access the schema is added to the name with an "_"
instead of a "."
2. There were several instances where there was a "!" instead of a "."
3. Changed the calculation for the extended column to no longer reference the Aliases [Balance] column because SQL Server does not allow that, but to re-use the calculation for the [Balance] column in the extended column calculation.
I did miss the double-quotes and they should be changed to single quotes, but except for that change the query I presented the OP with should work against SQL Server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 19, 2010 at 10:22 am
Thanks, that worked great.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply