Jet - SQL Query vs T-Sql Query

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

Other Forums

MS Access

Jet - SQL Query vs T-Sql Query

Author

Topic

Lawless
Starting Member

2 Posts

Posted - 2010-04-22 : 14:49:37

With T-SQL if you write something like: amount = 1 and amount is not a defined column in the table you are using in a query to draw data from, it will make a column named "amount" and fill it with the value 1

In Jet-SQL with Microsoft Access if you do this you get a pop up box that ask you to enter the parameter value for "amount".

Why and how can this be changed to get the same results that I get with T-SQL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-22 : 14:51:17

I've never seen T-SQL do that, but maybe you are explaining it wrong. Could you show us a sample query?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lawless
Starting Member

2 Posts

Posted - 2010-04-22 : 15:46:56

quote:
Originally posted by tkizer

I've never seen T-SQL do that, but maybe you are explaining it wrong. Could you show us a sample query?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

This is the T-Sql Query -- ( Please note that the "Case When statements are changed to "IIf() when it is used in Jet-SQL

in this query - which works perfectly in T-Sql, amount, cut_check, the_group, the_section and small_line_no are not columns in any of the tables used in this query. However, they become data populated columns when the query is run in T-Sql:

SELECT CLOSING.company_id,
CLOSING.closing_no,
CLOSING.property_address,
CLOSING.property_city,
CLOSING.property_state,
CLOSING.property_zip_code,
CLOSING.proj_closing_date_time,
CLOSING.actl_closing_date_time,
CLOSING.proj_disburse_date,
CLOSING.actl_disburse_date,
CLOSING.closer_user,
CLOSING_HUD_HEADER.borrowers,
CLOSING_HUD_HEADER.sellers,
CLOSING_HUD_HEADER.lenders,
CLOSING_HUD_LINE.line_no,
CLOSING_HUD_LINE.description,
amount=(CLOSING_HUD_LINE.buyer_allocation_amount),
cut_check='C',
CLOSING_HUD_LINE.hud_line_text,
the_section=(case when line_no = 101 then 1 when line_no between 102
and 105 then 2 when line_no between 106
and 112 then 2 when line_no between 201
and 219 then 3 when line_no between 703
and 704 then 2 when line_no between 801
and 822 then 2 when line_no between 901
and 910 then 2 when line_no between 1001
and 1016 then 2 when line_no between 1101
and 1128 then 2 when line_no between 1201
and 1210 then 2 when line_no between 1301
and 1310 then 2 else 3 end),the_group=(case when line_no = 101 then ''when line_no between 102
and 105 then 'ADDITIONAL CHARGES'when line_no between 106
and 112 then 'PRORATIONS'when line_no = 202 then 'NEW LOANS'when line_no between 204
and 209 then 'MISCELLANEOUS/ADJUSTMENTS'when line_no between 703
and 704 then 'COMMISSIONS'when line_no between 801
and 822 then 'NEW LOAN CHARGES'when line_no = 901 then 'INTEREST CHARGES'when line_no BETWEEN 902
AND 910 then 'INSURANCE PREMIUMS'when line_no between 1001
and 1026 then 'IMPOUNDS/RESERVES'when line_no between 1101
and 1128 then 'TITLE CHARGES'when line_no between 1200
and 1210 then 'RECORDING FEES/TRANSFER'else 'MISCELLANEOUS CHARGES' end),
small_line_no=line_no,
COMPANY.description,
COMPANY.address_line_1,
COMPANY.address_line_2,
COMPANY.city,
COMPANY.state,
COMPANY.zip_code,
CLOSING_HUD_HEADER.borrower_name_1,
CLOSING_HUD_HEADER.borrower_name_2,
CLOSING_HUD_HEADER.borrower_name_3,
CLOSING_HUD_HEADER.borrower_name_4,
CLOSING_HUD_HEADER.borrower_name_5,
seller_1=isnull(seller_name_1,
''),
seller_2=isnull(seller_name_2,
''),
seller_3=isnull(seller_name_3,
''),
seller_4=isnull(seller_name_4,
''),
seller_5=isnull(seller_name_5,
''),
CLOSING_HUD_LINE.hud_line_text,
closing_address=isnull(CLOSING_HUD_HEADER.closing_address,
''),
closing_city=isnull(CLOSING_HUD_HEADER.closing_city,
''),
closing_state=isnull(CLOSING_HUD_HEADER.closing_state,
''),
closing_zip_code=isnull(CLOSING_HUD_HEADER.closing_zip_code,
''),
seller_address=isnull(CLOSING_HUD_HEADER.seller_address,
''),
seller_city=isnull(CLOSING_HUD_HEADER.seller_city,
''),
seller_state=isnull(CLOSING_HUD_HEADER.seller_state,
''),
seller_zip_code=isnull(CLOSING_HUD_HEADER.seller_zip_code,
''),
company_name=isnull(CLOSING_HUD_HEADER.company_name,
''),
lender_address_1=isnull(CLOSING_HUD_HEADER.lender_address_line_1,
''),
lender_address_2=isnull(CLOSING_HUD_HEADER.lender_address_line_2,
''),
lender_city=isnull(CLOSING_HUD_HEADER.lender_city,
''),
lender_state=isnull(CLOSING_HUD_HEADER.lender_state,
''),
lender_zip_code=isnull(CLOSING_HUD_HEADER.lender_zip_code,
'')
FROM CLOSING,
CLOSING_HUD_LINE,
CLOSING_HUD_HEADER,
COMPANY
WHERE ( CLOSING.company_id = CLOSING_HUD_HEADER.company_id )
and ( CLOSING.closing_no = CLOSING_HUD_HEADER.closing_no )
and ( CLOSING_HUD_HEADER.company_id = CLOSING_HUD_LINE.company_id )
and ( CLOSING_HUD_HEADER.closing_no = CLOSING_HUD_LINE.closing_no )
and ( CLOSING_HUD_HEADER.hud_type in ('HUD-1', 'HUD-12') )
and( COMPANY.company_id = 13)
and ( ( CLOSING.company_id = 13 )
AND ( CLOSING.closing_no = 12743 )
AND( CLOSING_HUD_LINE.line_no not in ( 3032, 6032 ) )
and( CLOSING_HUD_LINE.line_no between 100
and 303 )
AND( CLOSING_HUD_LINE.buyer_allocation_amount <> 0 ) )

UNION ALL SELECT CLOSING.company_id, CLOSING.closing_no, CLOSING.property_address, CLOSING.property_city, CLOSING.property_state, CLOSING.property_zip_code, CLOSING.proj_closing_date_time, CLOSING.actl_closing_date_time, CLOSING.proj_disburse_date, CLOSING.actl_disburse_date, CLOSING.closer_user, CLOSING_HUD_HEADER.borrowers, CLOSING_HUD_HEADER.sellers, CLOSING_HUD_HEADER.lenders, CLOSING_HUD_LINE.line_no, CLOSING_HUD_LINE.description, amount=CLOSING_HUD_LINE.buyer_allocation_amount, 'C', CLOSING_HUD_LINE.hud_line_text, the_section=(case when line_no = 101 then 1when line_no between 102
and 105 then 2when line_no between 106
and 112 then 2when line_no between 201
and 219 then 3when line_no between 703
and 704 then 2when line_no between 801
and 822 then 2when line_no between 901
and 910 then 2when line_no between 1001
and 1016 then 2when line_no between 1101
and 1128 then 2when line_no between 1201
and 1210 then 2when line_no between 1301
and 1310 then 2 else 3 end),the_group=(case when line_no = 101 then ''when line_no between 102
and 105 then 'ADDITIONAL CHARGES'when line_no between 106
and 112 then 'PRORATIONS'when line_no = 202 then 'NEW LOANS'when line_no between 204
and 209 then 'MISCELLANEOUS/ADJUSTMENTS'when line_no between 703
and 704 then 'COMMISSIONS'when line_no between 801
and 822 then 'NEW LOAN CHARGES'when line_no = 901 then 'INTEREST CHARGES'when line_no BETWEEN 902
AND 910 then 'INSURANCE PREMIUMS'when line_no between 1001
and 1026 then 'IMPOUNDS/RESERVES'when line_no between 1101
and 1128 then 'TITLE CHARGES'when line_no between 1200
and 1210 then 'RECORDING FEES/TRANSFER'else 'MISCELLANEOUS CHARGES' end),
small_line_no=line_no,
COMPANY.description,
COMPANY.address_line_1,
COMPANY.address_line_2,
COMPANY.city,
COMPANY.state,
COMPANY.zip_code,
CLOSING_HUD_HEADER.borrower_name_1,
CLOSING_HUD_HEADER.borrower_name_2,
CLOSING_HUD_HEADER.borrower_name_3,
CLOSING_HUD_HEADER.borrower_name_4,
CLOSING_HUD_HEADER.borrower_name_5,
seller_1=isnull(seller_name_1,''),seller_2=isnull(seller_name_2,''),seller_3=isnull(seller_name_3,''),seller_4=isnull(seller_name_4,''),seller_5=isnull(seller_name_5,''),CLOSING_HUD_LINE.hud_line_text,closing_address=isnull(CLOSING_HUD_HEADER.closing_address,''),closing_city=isnull(CLOSING_HUD_HEADER.closing_city,''),closing_state=isnull(CLOSING_HUD_HEADER.closing_state,''),closing_zip_code=isnull(CLOSING_HUD_HEADER.closing_zip_code,''),seller_address=isnull(CLOSING_HUD_HEADER.seller_address,''),seller_city=isnull(CLOSING_HUD_HEADER.seller_city,''),seller_state=isnull(CLOSING_HUD_HEADER.seller_state,''),seller_zip_code=isnull(CLOSING_HUD_HEADER.seller_zip_code,''),company_name=isnull(CLOSING_HUD_HEADER.company_name,''),lender_address_1=isnull(CLOSING_HUD_HEADER.lender_address_line_1,''),lender_address_2=isnull(CLOSING_HUD_HEADER.lender_address_line_2,''),lender_city=isnull(CLOSING_HUD_HEADER.lender_city,''),lender_state=isnull(CLOSING_HUD_HEADER.lender_state,''),lender_zip_code=isnull(CLOSING_HUD_HEADER.lender_zip_code,'') FROM CLOSING, CLOSING_HUD_LINE, CLOSING_HUD_HEADER,COMPANY
WHERE ( CLOSING.company_id = CLOSING_HUD_HEADER.company_id )
and ( CLOSING.closing_no = CLOSING_HUD_HEADER.closing_no )
and ( CLOSING_HUD_HEADER.company_id = CLOSING_HUD_LINE.company_id )
and ( CLOSING_HUD_HEADER.closing_no = CLOSING_HUD_LINE.closing_no )
and ( CLOSING_HUD_HEADER.hud_type in ('HUD-1', 'HUD-12') )
and( COMPANY.company_id = 13)
and ( ( CLOSING.company_id = 13 )
AND ( CLOSING.closing_no = 12743 )
AND( CLOSING_HUD_LINE.line_no not in ( 3032, 6032 ) )
and( CLOSING_HUD_LINE.line_no between 700
and 1400 )
AND( CLOSING_HUD_LINE.buyer_allocation_amount <> 0 ) )

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-22 : 16:11:18

Have you tried this?

Column1 AS NewName

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

doco
Yak Posting Veteran

77 Posts

Posted - 2010-05-11 : 23:30:48

Or tried



.
.
.
(CLOSING_HUD_LINE.buyer_allocation_amount) as amount,
'C' as cut_check,
.
.
.

Education is what you have after you've forgotten everything you learned in school

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources