access to sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

access to sql

Author

Topic

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-05-10 : 15:48:05

just want to make sure if the code is right converted?
------------------------------------------------------------------------------------------------------
--------ACCESS CODE-----
SELECT
TBLPICKTICKETHDR.LOADID,
TBLLOAD.STATUS AS [Load ID Status],
TBLLOADSTATUSTYPE.DESCRIPTION AS [Load ID Description], TBLPICKTICKETHDR.WAVENO,
TBLPICKTICKETHDR.PICKTICKETNO,
TBLPICKTICKETHDR.ORDERNO,
TBLPICKTICKETDTL.SKU,
TBLPICKTICKETHDR.STATUSUNSHIPPED,
TBLPICKTICKETHDR.STATUS AS [Pick Ticket Status], TBLPICKTICKETSTATUSTYPE.DESCRIPTION AS [Pick Ticket Status Description], TBLPICKTICKETDTL.DESIREDSHIPDATE, TBLPICKTICKETHDR.DOWNLOADEDDATETIME, TBLPICKTICKETHDR.ORDERTYPE, CDate
TBLSHIPTO.SHIPTONAME

FROM TBLSHIPTO INNER JOIN
((((TBLPICKTICKETHDR INNER JOIN TBLPICKTICKETDTL ON TBLPICKTICKETHDR.PICKTICKETNO = TBLPICKTICKETDTL.PICKTICKETNO) LEFT JOIN TBLLOAD ON TBLPICKTICKETHDR.LOADID = TBLLOAD.LOADID) LEFT JOIN TBLLOADSTATUSTYPE ON TBLLOAD.STATUS = TBLLOADSTATUSTYPE.STATUSVALUE) INNER JOIN TBLPICKTICKETSTATUSTYPE ON TBLPICKTICKETHDR.STATUS = TBLPICKTICKETSTATUSTYPE.STATUSVALUE) ON TBLSHIPTO.SHIPTOCODE = TBLPICKTICKETHDR.SHIPTO

HAVING (((TBLPICKTICKETHDR.STATUSUNSHIPPED)="Y") AND ((TBLPICKTICKETHDR.ORDERTYPE)="C"));
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------

--------MODIFIED ACCESS CODE TO SQLSERVER-----
SELECT
TBLPICKTICKETHDR.LOADID,
TBLLOAD.STATUS AS [LoadIDStatus],
TBLLOADSTATUSTYPE.DESCRIPTION AS [Load ID Description],
TBLPICKTICKETHDR.WAVENO,
TBLPICKTICKETHDR.PICKTICKETNO,
TBLPICKTICKETHDR.ORDERNO,
TBLPICKTICKETDTL.SKU,
TBLPICKTICKETHDR.STATUSUNSHIPPED,
TBLPICKTICKETHDR.STATUS AS [PickTicketStatus],
TBLPICKTICKETSTATUSTYPE.DESCRIPTION AS [PickTicketStatusDescription],
TBLPICKTICKETDTL.DESIREDSHIPDATE,
TBLPICKTICKETHDR.DOWNLOADEDDATETIME,
TBLPICKTICKETHDR.ORDERTYPE,
TBLSHIPTO.SHIPTONAME

FROM TBLSHIPTO INNER JOIN
((((TBLPICKTICKETHDR
INNER JOIN TBLPICKTICKETDTL
ON TBLPICKTICKETHDR.PICKTICKETNO = TBLPICKTICKETDTL.PICKTICKETNO)
LEFT JOIN TBLLOAD
ON TBLPICKTICKETHDR.LOADID = TBLLOAD.LOADID)
LEFT JOIN TBLLOADSTATUSTYPE
ON TBLLOAD.STATUS = TBLLOADSTATUSTYPE.STATUSVALUE)
INNER JOIN TBLPICKTICKETSTATUSTYPE
ON TBLPICKTICKETHDR.STATUS = TBLPICKTICKETSTATUSTYPE.STATUSVALUE)
ON TBLSHIPTO.SHIPTOCODE = TBLPICKTICKETHDR.SHIPTO

and (((TBLPICKTICKETHDR.STATUSUNSHIPPED)='Y') AND ((TBLPICKTICKETHDR.ORDERTYPE)='C'))

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-10 : 16:25:36

converted from what into what?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-05-11 : 08:54:15

read again

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-11 : 09:37:21

I don't understand what you are asking us. Why don't you try running the code and see what happens?

- Jeff
http://weblogs.sqlteam.com/JeffS

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-05-11 : 09:47:49

access developer runs his access sql query locally. I modifed the access code to run it on SQL server and I'm running the code on the network, not locally. We both get different results. Does this mean I didn't modified the code correctly or there is something wrong running the code locally and on the network.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-11 : 10:30:38

maybe your data is different?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-11 : 10:34:43

Try adding this condition:

TBLPICKTICKETHDR.STATUSUNSHIPPED)='Y' AND TBLPICKTICKETHDR.ORDERTYPE ='C'

as a part of WHERE clause, not as a JOIN condition and see if you get different output.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources