Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Micros Res 3700 SQL Statement 4

Status
Not open for further replies.
Andy16H

Andy16H

Technical User
May 14, 2011
30
US
I'm learning SQL as a because it is almost impossible to get service from Oracle. I've written the following query which runs but it creates duplicates. The 4th Inner Join seems to be the one that makes the duplicates show up. I'm pretty sure there is a much better way to write this query. Any help is appreciated.
SQL: 
SELECT sale_dtl.trans_seq, sls_itmzr_01, sls_itmzr_02, sls_itmzr_03, sls_itmzr_04, sls_itmzr_05, sls_itmzr_06, sls_itmzr_07, sls_itmzr_08, txbl_sls_itmzr_02, txbl_sls_itmzr_07, tax_itmzr_02, tax_itmzr_07, 
sale_dtl.other_svc_ttl, net_sls_ttl, item_dsc_ttl, tax_coll_ttl, credit_ttl, non_txbl_ttl, rounding_ttl, change_grand_ttl, sale_dtl.pymnt_ttl, sale_dtl.gross_rcpts, chg_rcpts_ttl, charged_tip_ttl, cur_ot_net_sls_ttl, 
prev_ot_net_sls_ttl, sale_dtl.amt_due_ttl, incl_tax_by_chk_itmzr_02, incl_tax_by_chk_itmzr_07, chk_begun_ttl, trans_dtl.trans_seq, trans_dtl.chk_seq, chk_dtl.chk_num, dtl.dtl_seq, dtl.dtl_type,
dtl.dtl_name, dtl.rpt_inclusive_tax_ttl_ex, mi_dtl.maj_grp_seq, maj_grp_def.name, mi_dtl.fam_grp_seq, fam_grp_def.name
FROM micros.sale_dtl
INNER JOIN micros.trans_dtl on micros.sale_dtl.trans_seq = micros.trans_dtl.trans_seq
Inner join micros.chk_dtl on micros.trans_dtl.chk_seq = micros.chk_dtl.chk_seq
INNER JOIN micros.dtl on micros.trans_dtl.trans_seq = micros.dtl.trans_seq
INNER JOIN micros.mi_dtl on micros.sale_dtl.trans_seq = micros.mi_dtl.trans_seq
INNER JOIN micros.maj_grp_def on micros.mi_dtl.maj_grp_seq = micros.maj_grp_def.maj_grp_seq
INNER JOIN micros.fam_grp_def on micros.mi_dtl.fam_grp_seq = micros.fam_grp_def.fam_grp_seq
where trans_dtl.trans_seq = '957021'
 
Sort by date Sort by votes
you are correct about the 4th join... needs to also join to dtl to mi_dtl
SQL: 
SELECT sale_dtl.trans_seq, sls_itmzr_01, sls_itmzr_02, sls_itmzr_03, sls_itmzr_04, sls_itmzr_05, sls_itmzr_06, sls_itmzr_07, sls_itmzr_08, txbl_sls_itmzr_02, txbl_sls_itmzr_07, tax_itmzr_02, tax_itmzr_07, 
sale_dtl.other_svc_ttl, net_sls_ttl, item_dsc_ttl, tax_coll_ttl, credit_ttl, non_txbl_ttl, rounding_ttl, change_grand_ttl, sale_dtl.pymnt_ttl, sale_dtl.gross_rcpts, chg_rcpts_ttl, charged_tip_ttl, cur_ot_net_sls_ttl, 
prev_ot_net_sls_ttl, sale_dtl.amt_due_ttl, incl_tax_by_chk_itmzr_02, incl_tax_by_chk_itmzr_07, chk_begun_ttl, trans_dtl.trans_seq, trans_dtl.chk_seq, chk_dtl.chk_num, dtl.dtl_seq, dtl.dtl_type,
dtl.dtl_name, dtl.rpt_inclusive_tax_ttl_ex, mi_dtl.maj_grp_seq, maj_grp_def.name, mi_dtl.fam_grp_seq, fam_grp_def.name
FROM micros.sale_dtl
INNER JOIN micros.trans_dtl on micros.sale_dtl.trans_seq = micros.trans_dtl.trans_seq
Inner join micros.chk_dtl on micros.trans_dtl.chk_seq = micros.chk_dtl.chk_seq
INNER JOIN micros.dtl on micros.trans_dtl.trans_seq = micros.dtl.trans_seq
INNER JOIN micros.mi_dtl on micros.sale_dtl.trans_seq = micros.mi_dtl.trans_seq 
   and micros.dtl.dtl_seq = micros.mi_dtl.dtl_seq
INNER JOIN micros.maj_grp_def on micros.mi_dtl.maj_grp_seq = micros.maj_grp_def.maj_grp_seq
INNER JOIN micros.fam_grp_def on micros.mi_dtl.fam_grp_seq = micros.fam_grp_def.fam_grp_seq
where trans_dtl.trans_seq = '957021'
 
Upvote 0 Downvote
Here are a few quick tips for SQL in general. Stuff like this will help when you're troubleshooting, and especially when you have to ask for help.
[ul]
[li]Use tabs and lines. Putting each element of a query on it's own line makes it much easier to read, and using tabs can help show lines that are related to each other. It takes up more space, but is much easier to read through. You'll probably get more responses when you ask for help this way too.[/li]
[li]Always put the table name or alias in front of the field names. It's a big help troubleshooting if you can tell right away which table a particular field comes from.[/li]
[li]Use table aliases. It's much easier when you're typing, cuts back on typos, and if you use caps in your aliases it makes them stand out in the query.[/li]
[li]The "inner" portion of a join is implied, so doesn't need to be typed. Leaving it off will help outer joins stand out more.[/li]
[li]Either capitalize your keywords or don't, but make them consistent.[/li]
[li]Make sure you give aliases to ambiguous fields. For instance, your family group and major group name columns were both "name".[/li]
[li]Join table to table whenever possible. In your query you have sale_dtl.trans_seq = mi_dtl.trans_seq and dtl.trans_seq = mi_dtl.trans_seq. You should use dtl.trans_seq = mi_dtl.trans_seq and dtl.trans_seq = mi_dtl.trans_seq. It doesn't matter in this query, but it's good practice. It can make a difference when you have queries with outer joins[/li]
[/ul]

I prefer all lower case (because I'm lazy) except for table names, which area all in caps. I put brackets, [], around alias declarations to make them easy to spot. Every field name uses a table alias. Join fields have the master table on the left and the joining table on the right, (again, very important when using outer joins).

This is just my style of programming. It's by no means the only, or even best, way to do things, but I'm consistent, it's easy enough to read for others to jump in and understand what's going on, and it works for me. I rewrote your query in the style I use to show what I mean by all of that. Hope it helps in some way.


Code: 
select 
	SD.trans_seq
	,SD.sls_itmzr_01
	,SD.sls_itmzr_02
	,SD.sls_itmzr_03
	,SD.sls_itmzr_04
	,SD.sls_itmzr_05
	,SD.sls_itmzr_06
	,SD.sls_itmzr_07
	,SD.sls_itmzr_08
	,SD.txbl_sls_itmzr_02
	,SD.txbl_sls_itmzr_07
	,SD.tax_itmzr_02
	,SD.tax_itmzr_07
	,SD.other_svc_ttl
	,SD.net_sls_ttl
	,SD.item_dsc_ttl
	,SD.tax_coll_ttl
	,SD.credit_ttl
	,SD.non_txbl_ttl
	,SD.rounding_ttl
	,SD.change_grand_ttl
	,SD.pymnt_ttl
	,SD.gross_rcpts
	,SD.chg_rcpts_ttl
	,SD.charged_tip_ttl
	,SD.cur_ot_net_sls_ttl
	,SD.prev_ot_net_sls_ttl
	,SD.amt_due_ttl
	,SD.incl_tax_by_chk_itmzr_02
	,SD.incl_tax_by_chk_itmzr_07
	,SD.chk_begun_ttl
	,TR.trans_seq
	,TR.chk_seq
	,CHK.chk_num
	,DTL.dtl_seq
	,DTL.dtl_type
	,DTL.dtl_name
	,DTL.rpt_inclusive_tax_ttl_ex
	,MI.maj_grp_seq
	,MAJ."name" [MG_Name]
	,MI.fam_grp_seq
	,FAM."name" [FG_Name]
from 
	micros.sale_dtl [SD]
	join micros.trans_dtl [TR]
		on SD.trans_seq = TR.trans_seq
	join micros.chk_dtl [CHK]
		on TR.chk_seq = CHK.chk_seq
	join micros.dtl [DTL]
		on TR.trans_seq = DTL.trans_seq
	join micros.mi_dtl [MI]
		on DTL.trans_seq = MI.trans_seq
        and DTL.dtl_seq = MI.dtl_seq
	join micros.maj_grp_def [MAJ]
		on MI.maj_grp_seq = MAJ.maj_grp_seq
	join micros.fam_grp_def [FAM]
		on MI.fam_grp_seq = FAM.fam_grp_seq
where 
	TR.trans_seq = '957021'
 
Upvote 0 Downvote
Thank you so much for correcting my problem but most importantly giving me a very quick crash course.
 
Upvote 0 Downvote
One more question, I can't find item level discounts. Any idea where that hides?
I've found item_dsc_ttl which is all zeros same for sttl_dsc_ttl
I've also found a column for pending_discount_ttl which is also all zeros. I ran the query and omitted the Where statment and I only found 1 item disc listed.
What am I missing?
 
Upvote 0 Downvote
They're in the dsvc_dtl table. The parent_trans_seq and parent_dtl_seq can be linked back to the trans_seq and dtl_seq columns in the dtl table.

If you don't know about it already, there's a database management program installed on Micros servers called Sybase Central. You can get to it through the start menu:

Start / All Programs / Micros Applications / Utilities / Database / Sybase SQL Anywhere / Sybase Central

If you don't see a "folders" pane on the left, click View / Folders
Right click SQL Anywhere, and click Connect
Use the same credentials you use for dbisql, making sure to select ODBC connection in the middle dropdown box

When it opens, you'll be able to see all the tables, stored procedures and functions in the database. The views may be locked out, depends on what version you have. Don't know why Micros did that.
When you select a table, the columns will be listed in the main pane, which is very helpful, but the most helpful section is in the Referencing Constraints tab. This shows you all the tables that refer back to the table you have selected, and the columns used to link them together.


 
Upvote 0 Downvote
The only column in the dsvc_dtl table that looked like it would help me was the percentage column, which i'm assuming is the percentage discount. Below is the query where i added the new table you told me about. My current problem with this query does not return transactions that were not discounted.
SQL: 
Select 
	SD.trans_seq
	,SD.sls_itmzr_01
	,SD.sls_itmzr_02
	,SD.sls_itmzr_03
	,SD.sls_itmzr_04
	,SD.sls_itmzr_05
	,SD.sls_itmzr_06
	,SD.sls_itmzr_07
	,SD.sls_itmzr_08
        ,SD.txbl_sls_itmzr_01
	,SD.txbl_sls_itmzr_02
        ,SD.txbl_sls_itmzr_03
        ,SD.txbl_sls_itmzr_04
        ,SD.txbl_sls_itmzr_05
        ,SD.txbl_sls_itmzr_06
	,SD.txbl_sls_itmzr_07
        ,SD.txbl_sls_itmzr_08
        ,SD.tax_itmzr_01
	,SD.tax_itmzr_02
        ,SD.tax_itmzr_03
        ,SD.tax_itmzr_04
        ,SD.tax_itmzr_05
        ,SD.tax_itmzr_06
	,SD.tax_itmzr_07
        ,SD.tax_itmzr_08
	,SD.net_sls_ttl
	,SD.tax_coll_ttl
	,SD.change_grand_ttl
	,SD.pymnt_ttl
        ,SD.chk_begun_cnt
        ,SD.chk_paid_cnt
	,SD.gross_rcpts
	,SD.cur_ot_net_sls_ttl
	,SD.prev_ot_net_sls_ttl
	,SD.amt_due_ttl
        ,SD.incl_tax_by_chk_itmzr_01
	,SD.incl_tax_by_chk_itmzr_02
        ,SD.incl_tax_by_chk_itmzr_03
        ,SD.incl_tax_by_chk_itmzr_04
        ,SD.incl_tax_by_chk_itmzr_05
        ,SD.incl_tax_by_chk_itmzr_06
	,SD.incl_tax_by_chk_itmzr_07
        ,SD.incl_tax_by_chk_itmzr_08
	,SD.chk_begun_ttl
	,TR.chk_seq
        ,TR.business_date
	,CHK.chk_num
        ,CHK.sub_ttl
        ,CHK.Pymnt_ttl
	,DTL.dtl_seq
	,DTL.dtl_type
	,DTL.rpt_inclusive_tax_ttl_ex
        ,DTL.chk_cnt
        ,DTL.chk_ttl
        ,DTL.rpt_cnt
        ,DTL.rpt_ttl
	,DTL.dtl_name
        ,DTL.rpt_inclusive_tax_ttl
        ,DTL.suppressed_rpt_ttl
        ,MI.sls_itmzr_seq
        ,MI.price_lvl
        ,MI.dsc_itmzr
        ,MI.svc_itmzr
	,MAJ."name" [MG_Name]
	,MI.fam_grp_seq
	,FAM."name" [FG_Name]
        ,DSVC.percentage

from 
	micros.sale_dtl [SD]
	join micros.trans_dtl [TR]
		on SD.trans_seq = TR.trans_seq
	join micros.chk_dtl [CHK]
		on TR.chk_seq = CHK.chk_seq
	join micros.dtl [DTL]
		on TR.trans_seq = DTL.trans_seq
	join micros.mi_dtl [MI]
		on DTL.trans_seq = MI.trans_seq
                and DTL.dtl_seq = MI.dtl_seq
	join micros.maj_grp_def [MAJ]
		on MI.maj_grp_seq = MAJ.maj_grp_seq
	join micros.fam_grp_def [FAM]
		on MI.fam_grp_seq = FAM.fam_grp_seq
        join micros.dsvc_dtl [DSVC]
                on DTL.trans_seq = DSVC.parent_trans_seq
                and DTL.dtl_seq = DSVC.parent_dtl_seq
where 
business_date = '2016-05-27'
 
Upvote 0 Downvote
Use a left outer join for micros.dsvc_dtl. That will give you everything in the dtl table, and nulls in the dsvc_dtl table fields when there's no discount.
 
Upvote 0 Downvote
I think i figured it out with a left join. I also summed some columns. Is this correct? Is the summed information already summed in another column?
SQL: 
Select 
        (ISNull(SD.sls_itmzr_01,0) +
	ISNull(SD.sls_itmzr_02,0) +
	ISNull(SD.sls_itmzr_03,0) +
	ISNull(SD.sls_itmzr_04,0) +
	ISNull(SD.sls_itmzr_05,0) +
	ISNull(SD.sls_itmzr_06,0) +
	ISNull(SD.sls_itmzr_07,0) +
	ISNull(SD.sls_itmzr_08,0)) 
as sls_itmzr_total
        ,(ISNull(SD.txbl_sls_itmzr_01,0) +
	ISNull(SD.txbl_sls_itmzr_02,0) +
	ISNull(SD.txbl_sls_itmzr_03,0) +
	ISNull(SD.txbl_sls_itmzr_04,0) +
	ISNull(SD.txbl_sls_itmzr_05,0) +
	ISNull(SD.txbl_sls_itmzr_06,0) +
	ISNull(SD.txbl_sls_itmzr_07,0) +
	ISNull(SD.txbl_sls_itmzr_08,0)) 
as txbl_sls_itmzr_total
        ,(ISNull(SD.tax_itmzr_01,0) +
	ISNull(SD.tax_itmzr_02,0) +
        ISNull(SD.tax_itmzr_03,0) +
        ISNull(SD.tax_itmzr_04,0) +
        ISNull(SD.tax_itmzr_05,0) +
        ISNull(SD.tax_itmzr_06,0) +
	ISNull(SD.tax_itmzr_07,0) +
        ISNull(SD.tax_itmzr_08,0))
as tax_itmzr_total
	,SD.net_sls_ttl
	,SD.tax_coll_ttl
	,SD.change_grand_ttl
	,SD.pymnt_ttl
        ,SD.chk_begun_cnt
        ,SD.chk_paid_cnt
	,SD.gross_rcpts
	,SD.cur_ot_net_sls_ttl
	,SD.prev_ot_net_sls_ttl
	,SD.amt_due_ttl
        ,(ISNull(SD.incl_tax_by_chk_itmzr_01,0) +
	ISNull(SD.incl_tax_by_chk_itmzr_02,0) +
        ISNull(SD.incl_tax_by_chk_itmzr_03,0) +
        ISNull(SD.incl_tax_by_chk_itmzr_04,0) +
        ISNull(SD.incl_tax_by_chk_itmzr_05,0) +
        ISNull(SD.incl_tax_by_chk_itmzr_06,0) +
	ISNull(SD.incl_tax_by_chk_itmzr_07,0) +
        ISNull(SD.incl_tax_by_chk_itmzr_08,0))
as incl_tax_by_chk_itmzr_total
	,SD.chk_begun_ttl
	,TR.chk_seq
        ,TR.business_date
	,CHK.chk_num
        ,CHK.sub_ttl
        ,CHK.Pymnt_ttl
	,DTL.dtl_seq
	,DTL.dtl_type
	,DTL.rpt_inclusive_tax_ttl_ex
        ,DTL.chk_cnt
        ,DTL.chk_ttl
        ,DTL.rpt_cnt
        ,DTL.rpt_ttl
	,DTL.dtl_name
        ,DTL.rpt_inclusive_tax_ttl
        ,DTL.suppressed_rpt_ttl
        ,MI.sls_itmzr_seq
        ,MI.price_lvl
        ,MI.dsc_itmzr
        ,MI.svc_itmzr
	,MAJ."name" [MG_Name]
	,MI.fam_grp_seq
	,FAM."name" [FG_Name]
        ,DSVC.percentage

from 
	micros.sale_dtl [SD]
	join micros.trans_dtl [TR]
		on SD.trans_seq = TR.trans_seq
	join micros.chk_dtl [CHK]
		on TR.chk_seq = CHK.chk_seq
	join micros.dtl [DTL]
		on TR.trans_seq = DTL.trans_seq
	join micros.mi_dtl [MI]
		on DTL.trans_seq = MI.trans_seq
                and DTL.dtl_seq = MI.dtl_seq
	join micros.maj_grp_def [MAJ]
		on MI.maj_grp_seq = MAJ.maj_grp_seq
	join micros.fam_grp_def [FAM]
		on MI.fam_grp_seq = FAM.fam_grp_seq
        left join micros.dsvc_dtl [DSVC]
                on DTL.trans_seq = DSVC.parent_trans_seq
                and DTL.dtl_seq = DSVC.parent_dtl_seq
where 
business_date = '2016-05-27'
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

B
  • Locked
  • Question
MICROS RES 3700 - Desperate!
Replies
4
Views
807
bdavidson024
B
RickysRestaurants
  • Locked
  • Question
Micros 3700 Res 4.11
Replies
2
Views
315
RickysRestaurants
RickysRestaurants
JimFromCanada
  • Locked
  • Question
RE: MICROS RES 3700: IFS error 1
Replies
8
Views
860
pradeepckmeta
P
bpcore
  • Locked
  • Question
create itemizer, for discounts micros res 3700?
Replies
0
Views
142
bpcore
bpcore
LeonCL
  • Locked
  • Question
Micros RES 3700 Sample Database 1
Replies
5
Views
264
Andrew Miller
Andrew Miller

Part and Inventory Search

Sponsor

Back
Top