SQL coding problem

Forum for users that want to write their own custom queries against the PT database either via the Structured Query Language (SQL) or using the PT3 custom stats/reports interface.

Moderator: Moderators

SQL coding problem

Postby daniguru » Mon Apr 08, 2013 11:34 am

hi i got a problem that when using the posgres code i get value 1 from a player i tested it instead of value 2....did i miss something in the table relation ?

"SELECT sum((case when(holdem_hand_player_statistics.flg_f_cbet)\
AND (holdem_hand_player_statistics.flg_t_cbet) \
AND (holdem_hand_player_statistics.flg_R_cbet) \
AND ((lookup_hand_ranks.id_group>8) or (holdem_hand_player_combinations.flg_r_1pair \
AND lookup_hand_ranks.id_group=8 \
AND holdem_hand_player_combinations.id_r_hand_strength<3 )) \
AND (holdem_hand_player_detail.val_r_bet_made_pct >100) then 1 else 0 end)) as result \
FROM holdem_hand_player_statistics, holdem_hand_player_detail, holdem_hand_player_combinations, lookup_hand_ranks, player as P \
WHERE holdem_hand_player_statistics.id_hand = holdem_hand_player_combinations.id_hand AND holdem_hand_player_statistics.id_final_hand = lookup_hand_ranks.id_hand_rank AND holdem_hand_player_statistics.id_player = P.id_player AND holdem_hand_player_statistics.id_hand = holdem_hand_player_detail.id_hand \


many thanks

daniel
daniguru
 
Posts: 69
Joined: Mon Aug 11, 2008 10:20 am

Re: SQL coding problem

Postby kraada » Mon Apr 08, 2013 11:48 am

I think you want flg_r_cbet not flg_R_cbet but I'm not certain this will cause the behavior you're seeing. Select for the id_hands in question and look at the hands - what's going on in the hand that's incorrect?
kraada
Moderator
 
Posts: 54431
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: SQL coding problem

Postby daniguru » Tue Apr 09, 2013 6:53 pm

hi kraada many thanks. would be very cool if you could find the failure here...this is a peace of code and i get the failure here. bracketing should not be a problem

AND ( (holdem_hand_summary.card_1 - ((holdem_hand_summary.card_1 - 1) % 13) = holdem_hand_summary.card_2 - ((holdem_hand_summary.card_2 - 1) % 13)) \
AND NOT ( holdem_hand_summary.card_1 - ((holdem_hand_summary.card_1 - 1) % 13) = holdem_hand_summary.card_3 - ((holdem_hand_summary.card_3 - 1) % 13)) \
AND NOT ( holdem_hand_summary.card_1 - ((holdem_hand_summary.card_1 - 1) % 13) = holdem_hand_summary.card_4 - ((holdem_hand_summary.card_4 - 1) % 13)) \
AND NOT ( holdem_hand_summary.card_1 - ((holdem_hand_summary.card_1 - 1) % 13) = holdem_hand_summary.card_5 - ((holdem_hand_summary.card_5 - 1) % 13))) \
or \
(( ( holdem_hand_summary.card_1 - ((holdem_hand_summary.card_1 - 1) % 13) = holdem_hand_summary.card_3 - ((holdem_hand_summary.card_3 - 1) % 13))) \till here it works
AND NOT (( holdem_hand_summary.card_1 - ((holdem_hand_summary.card_1 - 1) % 13) = holdem_hand_summary.card_2 - ((holdem_hand_summary.card_2 - 1) % 13)) \
AND NOT ( holdem_hand_summary.card_1 - ((holdem_hand_summary.card_1 - 1) % 13) = holdem_hand_summary.card_4 - ((holdem_hand_summary.card_4 - 1) % 13)) \
AND NOT ( holdem_hand_summary.card_1 - ((holdem_hand_summary.card_1 - 1) % 13) = holdem_hand_summary.card_5 - ((holdem_hand_summary.card_5 - 1) % 13)) )) \
or \
(( ( holdem_hand_summary.card_2 - ((holdem_hand_summary.card_2 - 1) % 13) = holdem_hand_summary.card_3 - ((holdem_hand_summary.card_3 - 1) % 13))) \
AND NOT (( holdem_hand_summary.card_2 - ((holdem_hand_summary.card_2 - 1) % 13) = holdem_hand_summary.card_1 - ((holdem_hand_summary.card_1 - 1) % 13)) \
AND NOT ( holdem_hand_summary.card_2 - ((holdem_hand_summary.card_2 - 1) % 13) = holdem_hand_summary.card_4 - ((holdem_hand_summary.card_4 - 1) % 13)) \
AND NOT ( holdem_hand_summary.card_2 - ((holdem_hand_summary.card_2 - 1) % 13) = holdem_hand_summary.card_5 - ((holdem_hand_summary.card_5 - 1) % 13)) ))


many thanks

daniel
daniguru
 
Posts: 69
Joined: Mon Aug 11, 2008 10:20 am

Re: SQL coding problem

Postby kraada » Wed Apr 10, 2013 8:03 am

I don't see anything obvious going wrong here - what behavior are you seeing?
kraada
Moderator
 
Posts: 54431
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: SQL coding problem

Postby daniguru » Wed Apr 10, 2013 6:22 pm

hi kraada you are right not getting any failure code or something just that when i instert the code postgers seems to crash. without the card code lines everything works so i thought it must be something to do with the or and and not structure
daniguru
 
Posts: 69
Joined: Mon Aug 11, 2008 10:20 am

Re: SQL coding problem

Postby kraada » Thu Apr 11, 2013 7:43 am

You might want to put ()s around the entire thing after the very first AND in case you're missing those that could cause things to take a whole lot longer depending on what else you're using it with, but everything does look OK to me otherwise.
kraada
Moderator
 
Posts: 54431
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: SQL coding problem

Postby daniguru » Mon Apr 15, 2013 11:26 am

hi i tried but no succes so far. may you take a look at another problem i am struggling right now?

this is the code i have on custom stats under colums...here i get value 1

sum(if[ holdem_hand_player_statistics.flg_r_float AND
((lookup_hand_ranks.id_group>8) or (holdem_hand_player_combinations.flg_r_1pair AND lookup_hand_ranks.id_group=8 AND holdem_hand_player_combinations.id_r_hand_strength<3))
AND holdem_hand_player_detail.val_r_bet_made_pct >50
AND holdem_hand_player_detail.val_r_bet_made_pct <=75, 1, 0 ])

and this is the postrges code here i get value 2....any idea what could be wrong?

"SELECT sum((case when(holdem_hand_player_statistics.flg_r_float\
AND holdem_hand_player_detail.val_r_bet_made_pct >50 \
AND holdem_hand_player_detail.val_r_bet_made_pct <=75 \
AND (lookup_hand_ranks.id_group>8 \
or holdem_hand_player_combinations.flg_r_1pair \
AND lookup_hand_ranks.id_group=8 \
AND holdem_hand_player_combinations.id_r_hand_strength<3)) then 1 else 0 end)) as result \
FROM holdem_hand_player_statistics, holdem_hand_player_detail, holdem_hand_player_combinations, lookup_hand_ranks, player as P \
WHERE holdem_hand_player_statistics.id_hand = holdem_hand_player_combinations.id_hand AND holdem_hand_player_statistics.id_final_hand = lookup_hand_ranks.id_hand_rank AND holdem_hand_player_statistics.id_player = P.id_player AND holdem_hand_player_statistics.id_hand = holdem_hand_player_detail.id_hand \

thousand thanks

daniel
daniguru
 
Posts: 69
Joined: Mon Aug 11, 2008 10:20 am

Re: SQL coding problem

Postby kraada » Mon Apr 15, 2013 3:37 pm

Those look like they should give the same data to me. Have you looked at our log file? That shows the exact SQL query that is sent to PostgreSQL and you can compare it to the one that you built. I'm not very familiar with that one particular lookup table so it's possible the join needs to be done a little differently and that's why you have a problem. You can find the log file by either enabling logging (Configure -> Options -> Logging Enabled) or starting PT3 with the Logging Enabled link from your Start menu and then looking in the C:\Program Files (x86)\PokerTracker 3 folder - the file you want is called PokerTracker.log.
kraada
Moderator
 
Posts: 54431
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: SQL coding problem

Postby daniguru » Mon Apr 15, 2013 4:00 pm

hi krada on the look up thing i did also thout maybe that could be the problem.

about pokertracker log i was aware of that problem there i got it is pretty mixxed in the log. the WHERE AND FROM section is not attached to the stat so how can i find out what stat belongs to WHERE AND FROM? :?
daniguru
 
Posts: 69
Joined: Mon Aug 11, 2008 10:20 am

Re: SQL coding problem

Postby kraada » Mon Apr 15, 2013 5:49 pm

If you look at the log file it contains the full query including the WHERE and FROM lines that we use.
kraada
Moderator
 
Posts: 54431
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Next

Return to Custom Stats, Reports, and SQL [Read Only]

Who is online

Users browsing this forum: No registered users and 6 guests

cron
highfalutin